MySQL · TokuDB · TokuDB数据文件大小计算

想要查看TokuDB某个表占了多少磁盘空间,目前还没有太好的方法。
如果你使用’show table status’命令,得到的结果只会让你更迷茫:

           Name: toku
         Engine: TokuDB
        Version: 10
     Row_format: tokudb_zlib
           ROWS: 28874059
 Avg_row_length: 30
    Data_length: 868159449
Max_data_length: 9223372036854775807
   Index_length: 2099497463
      Data_free: 0

我们来看看test_toku表磁盘文件大小:

$du -b _test_toku*
124427776       _test_toku_A_B.tokudb  --KEY(A,B)索引数据文件
215959552       _test_toku_B_C.tokudb  --KEY(B,C)索引数据文件
149504000       _test_toku_main.tokudb --主数据文件

Data_lengthIndex_length都对不上,而且差的还不是一点,真头疼,总不能每次都要去服务器上du吧?其实,我们可以通过information_schema.TokuDB_fractal_tree_info得到:

mysql> SELECT * FROM TokuDB_fractal_tree_info WHERE TABLE_NAME='test_toku'\G;
*************************** 1. ROW ***************************
        dictionary_name: ./test/test_toku_A_B
     internal_file_name: _test_toku_A_B.tokudb
bt_num_blocks_allocated: 449
   bt_num_blocks_in_use: 449
      bt_size_allocated: 124416093
         bt_size_in_use: 123563101
           table_schema: test
             TABLE_NAME: toku
  table_dictionary_name: key-test_toku_A_B
*************************** 2. ROW ***************************
        dictionary_name: ./test/test_toku_B_C
     internal_file_name: ./_test_toku_B_C.tokudb
bt_num_blocks_allocated: 612
   bt_num_blocks_in_use: 612
      bt_size_allocated: 215945353
         bt_size_in_use: 214784137
           table_schema: test
             TABLE_NAME: toku
  table_dictionary_name: key-test_toku_B_C
*************************** 3. ROW ***************************
        dictionary_name: ./test/test_toku-main
     internal_file_name: ./_test_toku_main.tokudb
bt_num_blocks_allocated: 486
   bt_num_blocks_in_use: 486
      bt_size_allocated: 149491745
         bt_size_in_use: 148580897
           table_schema: test
             TABLE_NAME: toku
  table_dictionary_name: main

bt_size_allocated字段是TokuDB内部维护当前数据文件最后分配的offset,跟数据文件磁盘大小基本一致。

再回到刚才的问题:

show table status

Data_lengthIndex_length为什么跟实际大小出入这么大呢?

ha_tokudb::info()里:

stats.data_file_length = dict_stats.bt_dsize;

bt_dsize的值来自toku_ft_stat64:

bt_dsize = ft->in_memory_stats.numbytes;

in_memory_stats.numbytes是TokuDB在内存中维护的一个变量,用于记录变更数据的增量大小,每次checkpoint的时候持久化到数据文件(包括索引文件)的header里,所以是个出入比较大的参考变量(无压缩),已不能通过此变量来反应表文件的真实大小。

这么不爽的地方,我们(RDS MySQL)已准备好patch把它修掉,让

show table status

可以拿到TokuDB表文件的真实大小。

SQL语句示例

TokuDB表大小排序

SELECT
    TABLE_NAME, 
    table_dictionary_name, 
    round(bt_size_allocated/1024/1024,2) AS `Size (MB)`
FROM
    information_schema.`TokuDB_fractal_tree_info`
ORDER BY
  (bt_size_allocated)
DESC;

非TokuDB表大小排序

SELECT
  TABLE_SCHEMA AS `Database`,
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

非TokuDB指定表排序

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
    TABLE_SCHEMA = "bookstore"
  AND
    TABLE_NAME = "book"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

还没有评论,快来抢沙发!

发表评论

  • 😉
  • 😐
  • 😡
  • 😈
  • 🙂
  • 😯
  • 🙁
  • 🙄
  • 😛
  • 😳
  • 😮
  • emoji-mrgree
  • 😆
  • 💡
  • 😀
  • 👿
  • 😥
  • 😎
  • 😕
  • 64 queries in 0.213 seconds