如何查看MySQL的表空间

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 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

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
22天前
|
关系型数据库 MySQL
MySQL delete后怎么释放表空间
MySQL delete后怎么释放表空间
|
28天前
|
存储 SQL 关系型数据库
面试官:mysql 表删除一半数据,表空间会变小吗?
这期面试官提的问题是: MySQL 表删除一半数据,表空间是否会变小?为什么?
|
2月前
|
SQL 存储 关系型数据库
MySQL学习笔记-如何有效的回收表空间
MySQL学习笔记-如何有效的回收表空间
20 0
|
3月前
|
存储 关系型数据库 MySQL
第9章 【MySQL】InnoDB的表空间
第9章 【MySQL】InnoDB的表空间
28 0
第9章 【MySQL】InnoDB的表空间
|
6月前
|
存储 SQL 固态存储
MySQL的表空间到底是什么
MySQL的表空间到底是什么
350 0
|
7月前
|
存储 关系型数据库 MySQL
一文带你了解MySQL之InnoDB表空间
通过前边的内容,相信大家都知道了表空间是一个抽象的概念,对于系统表空间来说,对应着文件系统中一个或多个实际文件;对于每个独立表空间来说,对应着文件系统中一个名为表名.ibd的实际文件。 大家可以把表空间想象成被切分为许多个页的池子,当我们想为某个表插入一条记录的时候,就从池子中捞出一个对应的页来把数据写进去。 本章内容会深入到表空间的各个细节中,带领大家在InnoDB存储结构的池子中畅游。由于本章中将会涉及比较多的概念,虽然这些概念都不难,但是却相互依赖,所以奉劝大家看的时候:不要跳着看
105 0
|
7月前
|
存储 关系型数据库 MySQL
一文带你了解MySQL之InnoDB表空间
通过前边的内容,相信大家都知道了表空间是一个抽象的概念,对于系统表空间来说,对应着文件系统中一个或多个实际文件;对于每个独立表空间来说,对应着文件系统中一个名为表名.ibd的实际文件。 大家可以把表空间想象成被切分为许多个页的池子,当我们想为某个表插入一条记录的时候,就从池子中捞出一个对应的页来把数据写进去。 本章内容会深入到表空间的各个细节中,带领大家在InnoDB存储结构的池子中畅游。由于本章中将会涉及比较多的概念,虽然这些概念都不难,但是却相互依赖,所以奉劝大家看的时候:不要跳着看
68 0
|
7月前
|
存储 缓存 关系型数据库
Mysql专栏 - 缓冲池补充、数据页、表空间简述
上一节提到了冷热数据分离,其实冷数据不可能是在缓冲池满的时候才会进行刷新的,而是会在LRU冷数据的尾部随机找几个缓存页刷入磁盘,他会有一个定时任务,每隔一段时间就进行刷新的操作,同时将刷新到磁盘之后的数据页加入到free链表当中。所以LRU的链表会定期把数据刷入到磁盘当中进行处理,并且在缓存没有用完的时候会清空一些无用的缓存页。
127 0
|
9月前
|
存储 关系型数据库 MySQL
MySQL的表空间是干什么的?底层原理是什么?
MySQL的表空间是干什么的?底层原理是什么?
469 0
|
SQL 缓存 监控
MySQL批量导入数据时,为何表空间膨胀了N倍
本文目录 问题缘起 排查思路 问题发现
158 0
推荐文章
更多