如何查看MySQL的表空间

  1. 云栖社区>
  2. 博客>
  3. 正文

如何查看MySQL的表空间

漫天花雨 2018-11-14 11:52:01 浏览2801
展开阅读全文

常规方法

通常情况下,我们一般的思路是通过下面的方式获取表空间:

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

网友评论

登录后评论
0/500
评论
漫天花雨
+ 关注