您在這裡

試用阿裡雲RDS的MySQL壓縮存儲引擎TokuDB

James Qi 在 2015年7月12日 - 22:09 發表

  以前就用過自己搭建MySQL服務器的兩種存儲引擎MyISAM和InnoDB(也用過一點Memory方式),在今年初轉向阿裡雲關系型數據庫服務RDS的時候,看到可調參數中有一個TokuDB,不過不太了解也沒有管。

  最近同事轉給我阿裡雲介紹TokuDB的文章,其中壓縮存儲的特性對我們來說很有吸引力,因為我們的數據庫一般都偏大,已經轉到阿裡雲的就有幾百個GB了,加上以後要轉的肯定是TB數量級的,而且目前還是用的MyISAM,如果用InnoDB的話,那還要擴大數倍,僅僅是存儲的費用就讓人難以承受。但MyISAM存在表容易損壞的問題,往後用的人越來越少,Drupal 7 以後默認的支持引擎都改為InnoDB,阿裡雲也推薦不要使用MyISAM。

  據說這個TokuDB與InnoDB的特性很類似,而改用壓縮方式後特别适合大數據時代的應用,但數據的壓縮解壓必定帶來CPU在這方面的消耗,這不是大的問題,我關注的主要是IOPS和連接數是否會增加,如果這兩個參數基本維持穩定的話,用CPU來換存儲空間還是值得的、有餘地的。

  雖然今天是周末,但也還是找了幾篇文章、網站查看:

  然後馬上就把我博客所在的RDS中多數表都從InnoDB改為TokuDB了,這個RDS的空間從1.8G下降到約800M,下降了一多半,還是很明顯的,如果是更大數據量的表下降應該更加明顯,不過如果以前是MyISAM的話,下降可能就不那麼明顯了。

  另外,要設置loose_tokudb_buffer_pool_ratio為合适的比例,也就是tokudb占用tokudb與innodb共用緩存的比例,默認在tokudb不使用的情況下是0,如果全部都用tokudb可以改為100,也可以在innodb轉換tokudb前根據下面公式來計算:

select sum(data_length) into @all_size from information_schema.tables where engine='innodb';
select sum(data_length) into @change_size from information_schema.tables where engine='innodb' and concat(table_schema, '.', table_name) in ('XX.XXXX', 'XX.XXXX', 'XX.XXXX');
select round(@change_size/@all_size*100);

  或者轉換後根據我自己改寫的公式來計算:

select sum(data_length) into @innodb_size from information_schema.tables where engine='innodb';
select sum(data_length) into @tokudb_size from information_schema.tables where engine='tokudb';
select round(@tokudb_size/(@innodb_size+@tokudb_size)*100);

  改變後各種參數還在觀察中,如果效果好再推廣到其他RDS的其他數據上。


  參考與tokudb有關的變量:

mysql>show variables like '%tokudb%';
+---------------------------------+-----------------+
| Variable_name | Value |
+---------------------------------+-----------------+
| tokudb_alter_print_error | OFF |
| tokudb_analyze_delete_fraction | 1.000000 |
| tokudb_analyze_time | 5 |
| tokudb_block_size | 4194304 |
| tokudb_bulk_fetch | ON |
| tokudb_cache_size | 905969664 |
| tokudb_check_jemalloc | 1 |
| tokudb_checkpoint_lock | OFF |
| tokudb_checkpoint_on_flush_logs | OFF |
| tokudb_checkpointing_period | 60 |
| tokudb_cleaner_iterations | 5 |
| tokudb_cleaner_period | 1 |
| tokudb_commit_sync | ON |
| tokudb_cpu_nums | 0 |
| tokudb_create_index_online | ON |
| tokudb_data_dir | |
| tokudb_debug | 0 |
| tokudb_directio | OFF |
| tokudb_disable_hot_alter | OFF |
| tokudb_disable_prefetching | OFF |
| tokudb_disable_slow_alter | OFF |
| tokudb_disable_slow_update | OFF |
| tokudb_disable_slow_upsert | OFF |
| tokudb_empty_scan | rl |
| tokudb_fs_reserve_percent | 5 |
| tokudb_fsync_log_period | 0 |
| tokudb_hide_default_row_format | ON |
| tokudb_killed_time | 4000 |
| tokudb_last_lock_timeout | |
| tokudb_load_save_space | ON |
| tokudb_loader_memory_size | 100000000 |
| tokudb_lock_timeout | 4000 |
| tokudb_lock_timeout_debug | 1 |
| tokudb_log_dir | |
| tokudb_max_lock_memory | 113246208 |
| tokudb_optimize_index_fraction | 1.000000 |
| tokudb_optimize_index_name | |
| tokudb_optimize_throttle | 0 |
| tokudb_pk_insert_mode | 1 |
| tokudb_prelock_empty | ON |
| tokudb_read_block_size | 65536 |
| tokudb_read_buf_size | 131072 |
| tokudb_read_status_frequency | 10000 |
| tokudb_row_format | tokudb_zlib |
| tokudb_rpl_check_readonly | ON |
| tokudb_rpl_lookup_rows | ON |
| tokudb_rpl_lookup_rows_delay | 0 |
| tokudb_rpl_unique_checks | ON |
| tokudb_rpl_unique_checks_delay | 0 |
| tokudb_support_xa | ON |
| tokudb_tmp_dir | |
| tokudb_version | 7.5.6 |
| tokudb_write_status_frequency | 1000 |
+---------------------------------+-----------------+
共返回 53 行記錄,花費 117.83 ms.

  查看與tokedb有關的狀态:


mysql>show status like '%tokudb%';
+-----------------------------------------------------------------+--------------------------+
| Variable_name | Value |
+-----------------------------------------------------------------+--------------------------+
| Tokudb_DB_OPENS | 1074 |
| Tokudb_DB_CLOSES | 17 |
| Tokudb_DB_OPEN_CURRENT | 1057 |
| Tokudb_DB_OPEN_MAX | 1057 |
| Tokudb_CHECKPOINT_PERIOD | 60 |
| Tokudb_CHECKPOINT_LAST_BEGAN | Mon Jul 13 11:22:52 2015 |
| Tokudb_CHECKPOINT_LAST_COMPLETE_BEGAN | Mon Jul 13 11:21:52 2015 |
| Tokudb_CHECKPOINT_LAST_COMPLETE_ENDED | Mon Jul 13 11:22:09 2015 |
| Tokudb_CHECKPOINT_DURATION | 185 |
| Tokudb_CHECKPOINT_DURATION_LAST | 17 |
| Tokudb_CHECKPOINT_TAKEN | 23 |
| Tokudb_CHECKPOINT_FAILED | 0 |
| Tokudb_CHECKPOINT_BEGIN_TIME | 120980 |
| Tokudb_CHECKPOINT_LONG_BEGIN_TIME | 0 |
| Tokudb_CHECKPOINT_LONG_BEGIN_COUNT | 0 |
| Tokudb_CACHETABLE_MISS | 4604 |
| Tokudb_CACHETABLE_MISS_TIME | 4345137 |
| Tokudb_CACHETABLE_PREFETCHES | 10 |
| Tokudb_CACHETABLE_SIZE_CURRENT | 907181668 |
| Tokudb_CACHETABLE_SIZE_LIMIT | 996566630 |
| Tokudb_CACHETABLE_SIZE_WRITING | 0 |
| Tokudb_CACHETABLE_SIZE_NONLEAF | 1818161 |
| Tokudb_CACHETABLE_SIZE_LEAF | 902267903 |
| Tokudb_CACHETABLE_SIZE_ROLLBACK | 832 |
| Tokudb_CACHETABLE_SIZE_CACHEPRESSURE | 760812 |
| Tokudb_CACHETABLE_SIZE_CLONED | 3094772 |
| Tokudb_CACHETABLE_EVICTIONS | 60 |
| Tokudb_CACHETABLE_CLEANER_EXECUTIONS | 2156 |
| Tokudb_CACHETABLE_CLEANER_PERIOD | 1 |
| Tokudb_CACHETABLE_CLEANER_ITERATIONS | 5 |
| Tokudb_CACHETABLE_WAIT_PRESSURE_COUNT | 0 |
| Tokudb_CACHETABLE_WAIT_PRESSURE_TIME | 0 |
| Tokudb_CACHETABLE_LONG_WAIT_PRESSURE_COUNT | 0 |
| Tokudb_CACHETABLE_LONG_WAIT_PRESSURE_TIME | 0 |
| Tokudb_LOCKTREE_MEMORY_SIZE | 0 |
| Tokudb_LOCKTREE_MEMORY_SIZE_LIMIT | 113246208 |
| Tokudb_LOCKTREE_ESCALATION_NUM | 0 |
| Tokudb_LOCKTREE_ESCALATION_SECONDS | 0.000000 |
| Tokudb_LOCKTREE_LATEST_POST_ESCALATION_MEMORY_SIZE | 0 |
| Tokudb_LOCKTREE_OPEN_CURRENT | 1059 |
| Tokudb_LOCKTREE_PENDING_LOCK_REQUESTS | 0 |
| Tokudb_LOCKTREE_STO_ELIGIBLE_NUM | 0 |
| Tokudb_LOCKTREE_STO_ENDED_NUM | 84 |
| Tokudb_LOCKTREE_STO_ENDED_SECONDS | 0.001284 |
| Tokudb_LOCKTREE_WAIT_COUNT | 0 |
| Tokudb_LOCKTREE_WAIT_TIME | 0 |
| Tokudb_LOCKTREE_LONG_WAIT_COUNT | 0 |
| Tokudb_LOCKTREE_LONG_WAIT_TIME | 0 |
| Tokudb_LOCKTREE_TIMEOUT_COUNT | 0 |
| Tokudb_LOCKTREE_WAIT_ESCALATION_COUNT | 0 |
| Tokudb_LOCKTREE_WAIT_ESCALATION_TIME | 0 |
| Tokudb_LOCKTREE_LONG_WAIT_ESCALATION_COUNT | 0 |
| Tokudb_LOCKTREE_LONG_WAIT_ESCALATION_TIME | 0 |
| Tokudb_DICTIONARY_UPDATES | 0 |
| Tokudb_DICTIONARY_BROADCAST_UPDATES | 0 |
| Tokudb_DESCRIPTOR_SET | 0 |
| Tokudb_MESSAGES_IGNORED_BY_LEAF_DUE_TO_MSN | 207 |
| Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT | 2 |
| Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT_BYTES | 694272 |
| Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT_UNCOMPRESSED_BYTES | 5592182 |
| Tokudb_LEAF_NODES_FLUSHED_NOT_CHECKPOINT_SECONDS | 0.637258 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT | 0 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT_BYTES | 0 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT_UNCOMPRESSE | 0 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_NOT_CHECKPOINT_SECONDS | 0.000000 |
| Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT | 1155 |
| Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT_BYTES | 273844224 |
| Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT_UNCOMPRESSED_BYTES | 1223284397 |
| Tokudb_LEAF_NODES_FLUSHED_CHECKPOINT_SECONDS | 6.420306 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT | 506 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT_BYTES | 484352 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT_UNCOMPRESSED_BY | 600902 |
| Tokudb_NONLEAF_NODES_FLUSHED_TO_DISK_CHECKPOINT_SECONDS | 0.390991 |
| Tokudb_LEAF_NODE_COMPRESSION_RATIO | 4.476154 |
| Tokudb_NONLEAF_NODE_COMPRESSION_RATIO | 1.240631 |
| Tokudb_OVERALL_NODE_COMPRESSION_RATIO | 4.470456 |
| Tokudb_NONLEAF_NODE_PARTIAL_EVICTIONS | 5839 |
| Tokudb_NONLEAF_NODE_PARTIAL_EVICTIONS_BYTES | 1831390 |
| Tokudb_LEAF_NODE_PARTIAL_EVICTIONS | 135986 |
| Tokudb_LEAF_NODE_PARTIAL_EVICTIONS_BYTES | 12265410353 |
| Tokudb_LEAF_NODE_FULL_EVICTIONS | 56 |
| Tokudb_LEAF_NODE_FULL_EVICTIONS_BYTES | 63468429 |
| Tokudb_NONLEAF_NODE_FULL_EVICTIONS | 4 |
| Tokudb_NONLEAF_NODE_FULL_EVICTIONS_BYTES | 4662 |
| Tokudb_LEAF_NODES_CREATED | 87 |
| Tokudb_NONLEAF_NODES_CREATED | 0 |
| Tokudb_LEAF_NODES_DESTROYED | 0 |
| Tokudb_NONLEAF_NODES_DESTROYED | 0 |
| Tokudb_MESSAGES_INJECTED_AT_ROOT_BYTES | 61828 |
| Tokudb_MESSAGES_FLUSHED_FROM_H1_TO_LEAVES_BYTES | 60424 |
| Tokudb_MESSAGES_IN_TREES_ESTIMATE_BYTES | 1404 |
| Tokudb_MESSAGES_INJECTED_AT_ROOT | 911 |
| Tokudb_BROADCASE_MESSAGES_INJECTED_AT_ROOT | 0 |
| Tokudb_BASEMENTS_DECOMPRESSED_TARGET_QUERY | 51 |
| Tokudb_BASEMENTS_DECOMPRESSED_PRELOCKED_RANGE | 1 |
| Tokudb_BASEMENTS_DECOMPRESSED_PREFETCH | 0 |
| Tokudb_BASEMENTS_DECOMPRESSED_FOR_WRITE | 47 |
| Tokudb_BUFFERS_DECOMPRESSED_TARGET_QUERY | 3167 |
| Tokudb_BUFFERS_DECOMPRESSED_PRELOCKED_RANGE | 45 |
| Tokudb_BUFFERS_DECOMPRESSED_PREFETCH | 0 |
| Tokudb_BUFFERS_DECOMPRESSED_FOR_WRITE | 3428 |
| Tokudb_PIVOTS_FETCHED_FOR_QUERY | 3789 |
| Tokudb_PIVOTS_FETCHED_FOR_QUERY_BYTES | 96169472 |
| Tokudb_PIVOTS_FETCHED_FOR_QUERY_SECONDS | 1.042154 |
| Tokudb_PIVOTS_FETCHED_FOR_PREFETCH | 10 |
| Tokudb_PIVOTS_FETCHED_FOR_PREFETCH_BYTES | 327680 |
| Tokudb_PIVOTS_FETCHED_FOR_PREFETCH_SECONDS | 0.000682 |
| Tokudb_PIVOTS_FETCHED_FOR_WRITE | 118 |
| Tokudb_PIVOTS_FETCHED_FOR_WRITE_BYTES | 1263104 |
| Tokudb_PIVOTS_FETCHED_FOR_WRITE_SECONDS | 0.001498 |
| Tokudb_BASEMENTS_FETCHED_TARGET_QUERY | 126157 |
| Tokudb_BASEMENTS_FETCHED_TARGET_QUERY_BYTES | 1243361280 |
| Tokudb_BASEMENTS_FETCHED_TARGET_QUERY_SECONDS | 3.823805 |
| Tokudb_BASEMENTS_FETCHED_PRELOCKED_RANGE | 4673 |
| Tokudb_BASEMENTS_FETCHED_PRELOCKED_RANGE_BYTES | 40569344 |
| Tokudb_BASEMENTS_FETCHED_PRELOCKED_RANGE_SECONDS | 0.118158 |
| Tokudb_BASEMENTS_FETCHED_PREFETCH | 4345 |
| Tokudb_BASEMENTS_FETCHED_PREFETCH_BYTES | 30583808 |
| Tokudb_BASEMENTS_FETCHED_PREFETCH_SECONDS | 0.045414 |
| Tokudb_BASEMENTS_FETCHED_FOR_WRITE | 11625 |
| Tokudb_BASEMENTS_FETCHED_FOR_WRITE_BYTES | 138430464 |
| Tokudb_BASEMENTS_FETCHED_FOR_WRITE_SECONDS | 0.237772 |
| Tokudb_BUFFERS_FETCHED_TARGET_QUERY | 337 |
| Tokudb_BUFFERS_FETCHED_TARGET_QUERY_BYTES | 182784 |
| Tokudb_BUFFERS_FETCHED_TARGET_QUERY_SECONDS | 0.001289 |
| Tokudb_BUFFERS_FETCHED_PRELOCKED_RANGE | 28 |
| Tokudb_BUFFERS_FETCHED_PRELOCKED_RANGE_BYTES | 14848 |
| Tokudb_BUFFERS_FETCHED_PRELOCKED_RANGE_SECONDS | 0.000033 |
| Tokudb_BUFFERS_FETCHED_PREFETCH | 0 |
| Tokudb_BUFFERS_FETCHED_PREFETCH_BYTES | 0 |
| Tokudb_BUFFERS_FETCHED_PREFETCH_SECONDS | 0.000000 |
| Tokudb_BUFFERS_FETCHED_FOR_WRITE | 902 |
| Tokudb_BUFFERS_FETCHED_FOR_WRITE_BYTES | 487936 |
| Tokudb_BUFFERS_FETCHED_FOR_WRITE_SECONDS | 0.001105 |
| Tokudb_LEAF_COMPRESSION_TO_MEMORY_SECONDS | 117.437031 |
| Tokudb_LEAF_SERIALIZATION_TO_MEMORY_SECONDS | 6.225201 |
| Tokudb_LEAF_DECOMPRESSION_TO_MEMORY_SECONDS | 66.514842 |
| Tokudb_LEAF_DESERIALIZATION_TO_MEMORY_SECONDS | 29.246856 |
| Tokudb_NONLEAF_COMPRESSION_TO_MEMORY_SECONDS | 1.533011 |
| Tokudb_NONLEAF_SERIALIZATION_TO_MEMORY_SECONDS | 0.013500 |
| Tokudb_NONLEAF_DECOMPRESSION_TO_MEMORY_SECONDS | 0.038646 |
| Tokudb_NONLEAF_DESERIALIZATION_TO_MEMORY_SECONDS | 0.048904 |
| Tokudb_PROMOTION_ROOTS_SPLIT | 0 |
| Tokudb_PROMOTION_LEAF_ROOTS_INJECTED_INTO | 2422 |
| Tokudb_PROMOTION_H1_ROOTS_INJECTED_INTO | 369 |
| Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_0 | 97 |
| Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_1 | 1533 |
| Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_2 | 645 |
| Tokudb_PROMOTION_INJECTIONS_AT_DEPTH_3 | 171 |
| Tokudb_PROMOTION_INJECTIONS_LOWER_THAN_DEPTH_3 | 0 |
| Tokudb_PROMOTION_STOPPED_NONEMPTY_BUFFER | 420 |
| Tokudb_PROMOTION_STOPPED_AT_HEIGHT_1 | 83 |
| Tokudb_PROMOTION_STOPPED_CHILD_LOCKED_OR_NOT_IN_MEMORY | 4 |
| Tokudb_PROMOTION_STOPPED_CHILD_NOT_FULLY_IN_MEMORY | 14 |
| Tokudb_PROMOTION_STOPPED_AFTER_LOCKING_CHILD | 10 |
| Tokudb_BASEMENT_DESERIALIZATION_FIXED_KEY | 100372 |
| Tokudb_BASEMENT_DESERIALIZATION_VARIABLE_KEY | 46722 |
| Tokudb_CURSOR_SKIP_DELETED_LEAF_ENTRY | 5454 |
| Tokudb_TXN_BEGIN | 935566 |
| Tokudb_TXN_BEGIN_READ_ONLY | 15290 |
| Tokudb_TXN_COMMITS | 927289 |
| Tokudb_TXN_ABORTS | 23544 |
| Tokudb_LOGGER_WRITES | 1732 |
| Tokudb_LOGGER_WRITES_BYTES | 2942123 |
| Tokudb_LOGGER_WRITES_UNCOMPRESSED_BYTES | 2942123 |
| Tokudb_LOGGER_WRITES_SECONDS | 3.539385 |
| Tokudb_LOGGER_WAIT_LONG | 0 |
| Tokudb_LOADER_NUM_CREATED | 0 |
| Tokudb_LOADER_NUM_CURRENT | 0 |
| Tokudb_LOADER_NUM_MAX | 0 |
| Tokudb_MEM_ESTIMATED_MAXIMUM_MEMORY_FOOTPRINT | 0 |
| Tokudb_FILESYSTEM_THREADS_BLOCKED_BY_FULL_DISK | 0 |
| Tokudb_FILESYSTEM_FSYNC_TIME | 59781155 |
| Tokudb_FILESYSTEM_FSYNC_NUM | 4056 |
| Tokudb_FILESYSTEM_LONG_FSYNC_TIME | 0 |
| Tokudb_FILESYSTEM_LONG_FSYNC_NUM | 0 |
| Tokudb_rows_inserted | 998 |
| Tokudb_rows_read | 62354889 |
| Tokudb_rows_deleted | 169 |
| Tokudb_rows_updated | 1043 |
+-----------------------------------------------------------------+--------------------------+
共返回 180 行記錄,花費 189.37 ms.

  一些參數還需要摸索調整。


  2015年7月14日夜補充:前兩天看到tokudb的壓縮特性很高興,就連夜轉了好幾台RDS上的很多庫中的表,但這兩天RDS接連出現問題,主要是日志中有大量這樣的報錯:errno: 24 - Too many open files,查閱資料後得知tokudb是每個索引都要建一個文件(innodb/myisam都是按每個表來新建一組文件),那如果表多、索引多,就容易超出RDS打開文件數的限制。阿裡雲客服後來打來電話,建議:

  1. 把tokudb轉回innodb,解決目前的文件數報錯問題;
  2. 測試innodb壓縮,看能否替換tokudb的壓縮功能,并獲得合适的性能;
  3. 把myisam也轉為innodb壓縮格式,避免myisam文件出錯問題。

  今天又花了很多時間來做這方面的轉換和測試。

回應

很贊的文章。
TokuDB在文件組織方式上,一個索引2個文件,一個分區2個文件。所以總的文件數是:
索引數*分區數* 2
如果分區很多的話就會把open_files_limit用光。
如果可以修改這個參數,盡量修一下,否則會出現"Too many open files"的錯誤。

我沒有用到分區,隻是有幾百個庫、每個庫上百個表,這樣加起來有大約10萬張表,再如果算每個索引都是單獨文件的話,文件數量就真是天文數字了,RDS的文件數限制參數我們普通用戶是無法修改的,阿裡雲的管理員才有這樣的權限,但他們不建議這樣做。

所以我現在還是轉為InnoDB了,而且再添置了一台RDS分擔負載,算是花錢來求穩定。BTW:以前自己服務器的空間、帶寬、CPU、内存什麼的都沒有感到特别受限,成本可以接受,現在轉阿裡雲RDS後雖然精打細算能省就省,但成本還是增加了很多,希望換來可靠性和性能上的提升。

James Qi / 祁勁松

發表新回應

Plain text

  • 不允許使用 HTML 標籤。
  • 自動將網址與電子郵件地址轉變為連結。
  • 自動斷行和分段。