如何查看MySQL的表空间-阿里云开发者社区

开发者社区> 漫天花雨> 正文

如何查看MySQL的表空间

简介: SELECT CONCAT( table_schema, '.', table_name ) table_name, CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, CONCAT( R.
+关注继续查看

常规方法

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

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
在AIX中查看文件夹占用空间大小
在AIX中查看文件夹占用空间大小 用du -sg 可看出当前文件夹的大小(以G为单位);用du -sg filename 可看出当前文件夹下所有文件或文件夹的大小(以G为单位);du -sk ,du -sm 则分别以KB,MB为单位。
576 0
Sql Server用管理器建表后如何查看创建表的语句
右键建的那个表-->编写表脚本为-->CREATE到-->新建编辑器查看窗口,就会出现新建表的SQL语句.
1536 0
linux命令----查看磁盘空间
linux命令----查看磁盘空间今天用“web发布平台”发布测试的服务,两个节点中发现有一个节点没有发布成功,压测TPS始终上不去,排查后发现只有一个节点在打日志,另一个节点的服务进程都没有在运行,由此断定应该是没有发布成功,有点坑的是发布平台上的发布记录是SUCCESS的,打开发布日志后才看到有一个节点是发布失败的。
1070 0
MySql状态查看方法 MySql如何查看连接数和状态?
原文:MySql状态查看方法 MySql如何查看连接数和状态? 如果是root帐号,你能看到所有用户的当前连接。如果是其它普通帐号,只能看到自己占用的连接  怎么进入mysql命令行呢? mysql的安装目录下面有个bin目录,先用命令行进入该目录,然后用 mysql -uroot -p123456 来登录(注意:用户名和密码不用包含“”)  命令: show processlist; 如果是root帐号,你能看到所有用户的当前连接。
811 0
+关注
漫天花雨
支持阿里云数据库,希望能为更多云用户服务;之前支持阿里巴巴集团数据库,承担了业务的数据库设计,架构优化,异地多活,库存热点优化,稳定性保障等工作,经历数次双11,有丰富的实战和优化经验和对业务的敏感度。关注数据库、数据仓库、大数据等领域。
44
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载