前因
早上上班,发现监控数据中,好几张表的所占数据空间突增,有的突增甚至达到了8G,仔细检测数据库之后,没有发现数据异常,那么问题出在哪里?
通过对代码review,数据统计逻辑分析,mysql占用空间sql的排查,发现问题出在统计sql上。下面是我使用的统计sql
select data_length, table_name from tables where table_schema='database';
在统计表占用的空间的时候,如果使用上述语句,会导致表空间突增,那么为什么会突增呢?
表的大小,是存储在INFORMATION_SCHEMA.TABLES中吗?mysql的官方文档说:
MyISAM
,DATA_LENGTH
是数据文件的长度,以字节为单位。InnoDB
,DATA_LENGTH
是为聚簇索引分配的 memory 的近似值,以字节为单位。具体来说,它是聚集索引大小(以页为单位)乘以InnoDB
页大小。这就意味着,使用InnoDB
时,我们所获取的表空间大小,是不准确的。通过使用sysbench对mysql进行基准测试,批量数据插入数据库,可以发现,从 INFORMATION_SCHEMA.TABLES
获取的 data_length
和 index_length
所定义的表大小并不是实时更新的,如下图:
此图表明,mysql没有实时维护data_length
以及index_length
的大小,而是不规则的刷新它们对应的值。问题的原因找到了,tables表虽然可以获取到表空间的大小,但是该数据不是实时的。
如果想要通过information_schema
来查看表空间的实际大小,需要做两件事:
innodb_stats_persistent
innodb_stats_on_metadata
禁用持久性统计信息意味着每次服务器启动时 InnoDB 都必须刷新统计信息,这可能会在重新启动之间产生不稳定的查询计划。
如果需要实时获取表空间大小,可以使用INNODB_SYS_TABLESPACES
来获取。示例如下:
select \* from INFORMATION\_SCHEMA.INNODB\_SYS\_TABLESPACES where name='test/mytest' \G
**\*\*\*** 1. row **\*\*\***
SPACE: 24
NAME: test/mytest
FLAG: 33
FILE\_FORMAT: Barracuda
ROW\_FORMAT: Dynamic
PAGE\_SIZE: 16384
ZIP\_PAGE\_SIZE: 0
SPACE\_TYPE: Single
FS\_BLOCK\_SIZE: 4096
FILE\_SIZE: 98304
ALLOCATED\_SIZE: 98304
1 row in set (0.02 sec)
该表可以实时查看表空间的大小,file_size
(磁盘上的逻辑文件大小)、allocated_size
(为此文件分配的空间)。
清理表数据的时候,发现我的表空间并没有跟着降低,这是为什么?下面是我清理表数据的sql:
delete from table\_name where create\_time < '20200101';
上面这句sql是非常常见的,因为数据库中的数据过期,需要进行清理。但是在清理完数据之后,我们的表占用空间没有得到降低,这是为什么?
当我们使用innodb
的时候,已删除的数据只是被标记为已删除,并不是真正的释放空间,这就导致了为什么我们在删除表中的数据,但是表占用的空间确实不断增长的。那么我们该如何清理表空间?
如果使用的是innodb
那么应该先查看innodb_file_per_table
(是否独享表空间)。
show variables like 'innodb\_file\_per\_table';
+-----------------------+-------+
| Variable\_name | Value |
+-----------------------+-------+
| innodb\_file\_per\_table | ON |
+-----------------------+-------+
1 row in set (0.03 sec)
ON代表共享表空间打开,OFF代表开启共享表空间没有打开,即采用的是默认的共享表空间。那么我们先看下我们的数据库存放咋物理磁盘位置。
show global variables like "%datadir%";
+---------------+-----------------+
| Variable\_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
因为是共享表空间,所以这里是一个非常大的文件ibdata1
,这个文件中存放了所有innodb
表的数据以及索引。执行以下命令:
optimize table table\_name;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg\_type | Msg\_text |
+-----------------+----------+----------+-------------------------------------------------------------------+
| test.table\_name | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.table\_name | optimize | status | OK |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.21 sec)
表示该表不支持optimize
应该使用recreate
以及analyze
进行代替。
alter table table\_name ENGINE = 'InnoDB';
analyze table table\_name;
+-----------------+---------+----------+----------+
| Table | Op | Msg\_type | Msg\_text |
+-----------------+---------+----------+----------+
| test.table\_name | analyze | status | OK |
+-----------------+---------+----------+----------+
1 row in set (0.01 sec)
ibddata1
中,随着数据的增加会导致该文件越来越大,超过10g之后,查询速度会变的非常慢,因此最好开启独享表空间。或者定期清理表空间。innodb_file_per_table
参数=1,再将备份导入innodb_file_per_table
参数,然后将需要修改的所有innodb的表都运行一遍 alter table table_name engine=innodb;即可使用第二种方式修改后,原来库中的表中的数据会继续存放于ibdata1中,新建的表才会使用独立表空间上述方法,实在工作遇到的一点知识点的总结。如果雷同,非常正常。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。