常规方法
通常情况下,我们一般的思路是通过下面的方式获取表空间:
SELECT CONCAT( table_schema, '.', table_name ) table_name,
CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length,
CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size,
CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
(100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = '[NAME_OF_DPA_DATABASE]'
AND (100*(data_free/(data_length + index_length+data_free))) > [MINIMUM_PERCENT_WASTED]
AND (data_length + index_length + data_free) > [MINIMUM_FILESIZE_IN_BYTES]
ORDER BY (data_length + index_length + data_free) DESC;
例如:
SELECT CONCAT( table_schema, '.', table_name ) table_name,
CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length,
CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size,
CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
(100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = 'dpa_repo'
AND (100*(data_free/(data_length + index_length+data_free))) > 75
AND (data_length + index_length + data_free) > 52428800
ORDER BY (data_length + index_length + data_free) DESC;
可能没有注意到的是
对于上述方法中的data_length 或 index_length的值MySQL并不是实时更新的,而是周期性地维护,通过测试发现当10%的行被改变时,data_length 或 index_length与正在更新的统计数据一致。
而table_rows, data_free 或 update_time却是实时更新的。
那么有没有方法在我们查询information_schema时获取到data_length和 index_length的值呢?
在MySQL 5.7里面如果想获取information_schema精确值,就要disable innodb_stats_persistent 和 enable innodb_stats_on_metadata,这两种方法都有明显的副作用。
disable innodb_stats_persistent意味着每次在MySQL启动时才会刷新统计信息,这中代价非常昂贵并且会在重启之间生成易失性查询计划。
enabling innodb_stats_on_metadata会使每次访问information_schema的时候非常非常慢。
有没有更好的办法
可以查看information.INNODB_SYS_TABLESPACES查看真实的文件大小,不像index_length and data_length,INNODB_SYS_TABLESPACES的值是实时更新的,也不需要额外的配置:
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/sbtest1' G
*************************** 1. row ***************************
SPACE: 42
NAME: sbinnodb/sbtest1
FLAG: 33
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 245937209344
ALLOCATED_SIZE: 245937266688
1 row in set (0.00 sec)
用这个表还有一个更好的事情是:它可以处理MySQL 5.7 新的“Innodb Page Compression”,这不同于file_size(它是在磁盘上的逻辑文件的大小),也不同于allocated_size(它是文件已经分配的空间,可以小很多)
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/testcomp' G
*************************** 1. row ***************************
SPACE: 48
NAME: sbinnodb/testcomp
FLAG: 33
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
FILE_SIZE: 285212672
ALLOCATED_SIZE: 113004544
1 row in set (0.00 sec)
结论
查INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES获取INNODB表的真实文件大小。
参考
https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/
https://support.solarwinds.com/Success_Center/Database_Performance_Analyzer_(DPA)/Knowledgebase_Articles/MySQL_repository_table_optimization_for_DPA
https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html