第 2 回|妙手神医瑶池诊疗实录:数据库存储空间去哪儿了?

本文涉及的产品
数据传输服务 DTS,数据同步 small 3个月
推荐场景:
数据库上云
数据传输服务 DTS,数据迁移 small 3个月
推荐场景:
MySQL数据库上云
数据管理 DMS,安全协同 3个实例 3个月
推荐场景:
学生管理系统数据库
简介: 近日,寒霜城数据库突发奇症,明明才检查好的存储空间,在2个小时内逼近磁盘峰值,即将被锁。这一急症背后的根因是什么?妙手神医瑶池又将如何应对呢?

文:侯豪、王安妮


导读


上回说到,在风起云涌的数字江湖,瑶池派潜心修炼多年,已经研制出一套独有的「云原生一站式数据管理与服务」诊疗秘籍,所到之处数据业务都能持续在线,数据价值被不断放大。


近日,寒霜城数据库突发奇症,明明才检查好的存储空间,在2个小时内逼近磁盘峰值,即将被锁


这一急症背后的根因是什么?妙手神医瑶池又将如何应对呢?


👉第 1 回|慢SQL夜袭数据库,妙手神医瑶池诊疗心法大公开


single-2.png


01、急症突发:谁动了我的存储空间?


这天,晨钟刚刚敲过,数据库侍卫的急报声就响彻了寒霜城整座城池:“报,云数据库RDS MySQL监控触发报警,数据库实例磁盘满,即将被锁定!”


“奇怪,我们明明刚检查过实例存储空间,剩余空间较多,理论上不应该这么快被写满啊。”上一班的侍卫挠头不解道。


image.png


危急之时,寒霜城向瑶池派发出求助信号,妙手神医瑶池即刻现身,经过一番探查,发现是数据库中「临时文件数据量(temp_file_size)」在一段时间内快速增长,占用了较多空间,耗尽剩余存储。


“你们看,其实是刚刚在数据库实例上执行了类似这样的SQL,才导致了大量磁盘临时表空间占用。”

/* 问题SQL简化示例 */select*from table_name_1 t1 join table_name_2 t2 where t1.id>5000orderby t2.iddesc;


“我们都知道,MySQL临时表可以临时存储数据,辅助复杂查询的执行,提高查询效率。


TIPS:MySQL在执行包含UNION、UNION ALL + ORDER BY、ORDER BY、GROUP BY、GROUP BY x AND ORDER BY z、DISTINCT + ORDER BY、INSERT...SELECT相同的表、多表更新、GROUP_CONCAT()、COUNT(DISTINCT)、使用SQL_BIG_RESULT修饰符、使用了派生表的查询时,以及执行OPTIMIZE TABLE或其他DDL时,可能会使用内部临时表。


在创建内部临时表时,MySQL首先在内存中创建Memory引擎临时表,当临时表的尺寸过大时,会自动转换为磁盘上的MyISAM引擎临时表,当查询涉及到Blob或Text类型字段,MySQL会直接使用磁盘临时表。


若该类查询语句大量执行且没有及时处理,临时文件数据量将快速增长,有较大风险打满磁盘,导致实例锁定从而影响业务。”


02、常见病因:哪些场景会导致存储空间快速增长?


这次数据库存储空间的突发急症,让寒霜城众人不禁深感后怕,待“临时文件切除术”操作完毕后,大家连忙问道:“除了临时文件,还有哪些场景可能导致数据库存储空间被快速占用呢?”


“还有这两个常见病因。”说着,妙手神医瑶池拿出了一张「急症防范要点」——


2.1 Binlog


Binlog即二进制文件,记录了数据库发生的变化,如库表增删、表中数据变化等。


Binlog主要用于主从复制和数据恢复,但在部分情况下,Binlog文件生成速度会大于清理速度,如大事务、涉及大字段的DML操作等,可能会导致上传Binlog文件到备份空间且从实例空间中删除的处理速度跟不上实例生成Binlog文件的速度,这就有可能导致实例空间满影响业务。


不过你们可以根据城中业务情况,通过“本地日志保留策略”,合理设置Binlog保留时长以及存储空间占用限制


2.2 Undo Log


另一个需要注意的是Undo Log。Undo表空间包含Undo日志,记录了如何回滚事务(增删改操作)的信息。Undo日志的不合理增长与大事务、长事务有关,比如:未提交的大事务会快速撑大undo。


在MySQL 5.7中,把innodb_undo_log_truncate设置为ON,即可开启undo表空间的自动截断(Automated Truncation)。需要注意的是,当线上purge相关参数配置不合理时,如每秒产生的事务数大于purge速度,undo空间也会持续膨胀。这种情况下,可以临时调整purge相关参数加快Undo空间的回收:


  1. innodb_purge_threads,后台执行的purge线程数量;
  2. innodb_purge_batch_size,控制从历史列表中批量清除undo日志的页数;
  3. 将innodb_purge_rseg_truncate_frequency值调小,提高purge线程释放回滚段的频率。


MySQL 8.0新增Manual Truncation,可以使用SQL语句来管理Undo表空间。Manual Truncation需要至少3个活跃的Undo表空间。首先将需要处理的undo表空间状态设置为inactive:

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;

设置为inactive后,undo表空间被标记为截断,purge线程会增加返回频率,快速清空并截断undo表空间,该undo表空间状态变为empty(empty状态的undo表空间也可以被删除),此时可以重新激活使用该undo表空间。

ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;


03、防微杜渐:常见占用存储空间但不易被感知的场景


为了让寒霜城能够尽量避免再患“存储空间急症”,本着“未病先防”的观念,急诊术处理完毕后,妙手神医瑶池特意在城中开了一场“存储空间急症根因交流会”,和大家揭秘了更多在数据库中占用存储空间的可能病因


3.1 information_schema.tables统计数据问题


“瑶池大夫,我有一个问题。”交流会前排的一名数据侍卫拿出一张急诊纪录单——

image.png


“如上图中的第一行,information_schema.tables中统计数据该表占用空间179.36GB,但单独查看每张表物理文件大小后,发现实际该表物理文件占用481.88GB存储空间,即真实大小为481.88GB,与统计数据相差约300GB。


不仅如此,其实在日常工作中,我们也发现了很多次,空间占用趋势图中‘用户数据库数据量’和从information_schema.tables中查询到的库表数据量加和有比较大的差异,请问这是为什么?”


“该类情况最常见的原因是information_schema.tables中数据统计更新机制问题。”


对于MySQL 8.0,information_schema.tables中保存的是缓存值,缓存值过期时间由参数information_schema_stats_expiry控制,该参数默认值为86400(秒),即24小时。如果没有缓存的统计信息或者统计信息已过期,在information_schema.tables查询表的信息时将从存储引擎重新拉取相关统计数据。


当information_schema.tables中统计数据与物理文件大小差异较大时,如有需要,可在业务低峰时期执行ANALYZE TABLE更新统计数据。如果业务上始终需要最新的统计数据,也可以考虑将参数information_schema_stats_expiry设置为0


对于MySQL 5.7,没有参数直接控制information_schema.tables中数据的失效时间,但如果表使用的是Innodb引擎,则底层可以通过参数innodb_stats_auto_recalc控制是否自动更新表相关的统计信息,该参数值为ON的时候,表中数据有变化的行数超过10%时,则会异步触发数据变化超过阈值的表的统计数据更新,MySQL 5.7的information_schema.tables何时再读取InnoDB更新后的数据不能直接控制。MySQL 8.0也同样有innodb_stats_auto_recalc参数。


3.2 DDL残留文件


3.2.1 Orphan Intermediate Tables


MySQL 5.7中,如果在使用ALGORITHM=INPLACE的方式执行ALTER TABLE操作的中间过程中出现了异常,则相关的中间表文件(Orphan Intermediate Tables)可能不会被清理,遗留在系统中占用着存储空间。另外,如果空的通用表空间中遗留有类似的中间表文件,则会导致无法删除该通用表空间。那要如何识别并删除异常情况遗留的中间表文件呢?


image.png

image.png


遗留的中间表名字以#sql-ib为前缀,如:#sql-ib6754-742530817.ibd,与之相关的frm文件以#sql-为前缀,如:#sql-19184-a6.frm。


可以通过查询information_schema.innodb_sys_tables确认是否有遗留的中间表文件

select*from information_schema.innodb_sys_tableswhere name like'%#sql%';


如果有遗留的中间表,则可以通过如下步骤进行删除:


1. 重命名frm文件,使其与ibd文件的名字相同。

$> mv #sql-19184-a6.frm #sql-ib6754-742530817.frm


2. 通过DROP TABLE命令删除遗留的中间表,但需要在表名前增加前缀#mysql50#,并用反单引号将增加了前缀后的整个字符串包起来。


mysql>DROPTABLE `#mysql50##sql-ib6754-742530817`;


3.2.2 Orphan Temporary Tables


MySQL 5.7中,与Orphan Intermediate Tables类似的,还存在Orphan Temporary Tables,当使用ALGORITHM=COPY的方式执行ALTER TABLE操作时出现异常,则可能会遗留一个Orphan temporary table,预期外的占用存储空间,与Orphan Intermediate Table不同的是,该类情况遗留的表的ibd、frm文件名是一样的,都是以#sql-为前缀,如:#sql-540_3.ibd,#sql-540_3.frm,查看是否有Orphan temporary table和查看Orphan Intermediate Table的语句是一样的,删除时不需要更改frm文件的名字,直接执行命令即可

mysql>DROPTABLE `#mysql50##sql-540_3`;

3.3 全文索引


“此外,我们平时会用全文索引来加速对文本数据的查询和DML操作,但需要注意的是,它会额外占用较多存储空间。”


瑶池神医拿出另一张记录单,上面显示:information_schema.tables中该表表空间大小为39GB,但实际上该表相关的全文索引文件额外占用了53GB空间。

image.png

这是由于在创建全文索引时,会同时创建一组索引表,如下示例将在空的full_text_index_test库中创建opening_lines表,并在opening_line字段上设置全文索引:

mysql>CREATETABLE opening_lines (->        id INTUNSIGNED AUTO_INCREMENT NOTNULL PRIMARY KEY,->        opening_line TEXT(500),->        author VARCHAR(200),->        title VARCHAR(200),->        FULLTEXT idx (opening_line)->) ENGINE=InnoDB;Query OK,0 rows affected (0.07 sec)


可以从information_schema.innodb_tables中看到除了opening_lines表,还额外生成了11个相关的表:

mysql>select table_id, name, space from information_schema.innodb_tableswhere name like'full_text_index_test/%';+----------+--------------------------------------------------------------------+--------+| table_id | name                                                               | space  |+----------+--------------------------------------------------------------------+--------+|101649| full_text_index_test/opening_lines                                 |100322||101650| full_text_index_test/fts_0000000000018d11_being_deleted            |100323||101651| full_text_index_test/fts_0000000000018d11_being_deleted_cache      |100324||101652| full_text_index_test/fts_0000000000018d11_config                   |100325||101653| full_text_index_test/fts_0000000000018d11_deleted                  |100326||101654| full_text_index_test/fts_0000000000018d11_deleted_cache            |100327||101655| full_text_index_test/fts_0000000000018d11_000000000003d54d_index_1 |100328||101656| full_text_index_test/fts_0000000000018d11_000000000003d54d_index_2 |100329||101657| full_text_index_test/fts_0000000000018d11_000000000003d54d_index_3 |100330||101658| full_text_index_test/fts_0000000000018d11_000000000003d54d_index_4 |100331||101659| full_text_index_test/fts_0000000000018d11_000000000003d54d_index_5 |100332||101660| full_text_index_test/fts_0000000000018d11_000000000003d54d_index_6 |100333|+----------+--------------------------------------------------------------------+--------+12 rows inset(0.04 sec)

在information_schema.tables中只能看到opening_lines表:

mysql>select*from information_schema.tableswhere table_schema='full_text_index_test'\G
***************************1. row ***************************  TABLE_CATALOG: def
   TABLE_SCHEMA: full_text_index_test
     TABLE_NAME: opening_lines
     TABLE_TYPE: BASE TABLE         ENGINE: InnoDB
        VERSION:10     ROW_FORMAT: Dynamic
     TABLE_ROWS:0 AVG_ROW_LENGTH:0    DATA_LENGTH:16384MAX_DATA_LENGTH:0   INDEX_LENGTH:32768      DATA_FREE:0 AUTO_INCREMENT:NULL    CREATE_TIME:2023-09-1417:08:16    UPDATE_TIME:NULL     CHECK_TIME:NULLTABLE_COLLATION: utf8_general_ci
       CHECKSUM:NULL CREATE_OPTIONS:  TABLE_COMMENT:1 row inset(0.09 sec)


如果只通过information_schema.tables查询,可能会与“用户数据库数据量”存在差异,让人疑惑部分存储空间去哪里了。


3.4 碎片空间


相比于上述场景,碎片空间更容易被发现。


如下示例中,数据空间加索引空间大约445GB,但整个表空间约604GB,相当于有159GB左右的碎片空间。


image.png


频繁的删改、事务回滚或者可能导致页分裂的插入操作等,都可能导致较大的碎片空间。例如,历史数据清理,相关的数据库记录只是被标记为删除,但空间不会自动回收。可在业务低峰时期通过OPTIMIZE TABLE等命令回收碎片空间


3.5 无流量表、索引以及重复和冗余索引


其实,还有一类主要是业务变化后导致的空间浪费。例如:由于业务变化部分索引不再使用、甚至部分表直接不再有流量;新增加的索引导致原有索引成为冗余索引;在某些列创建了完全重复的索引。其中,无流量表、无流量索引主要是浪费了存储空间,冗余索引、重复索引不仅浪费存储空间,还会增加数据写入时的性能消耗


image.png


“对于这一问题,我派的数据库自治服务DAS推出的新版性能洞察可以帮助识别发现无流量表和索引,能够有效提升数据库的稳定性。关于上述的数据库存储空间急症诱因,在日后一定要多加注意哦~”


倏尔,一阵鹤唳响起,众人望向寒霜城外,隐约在暮色之中的琅风山方向烽烟渐起,妙手神医瑶池来不及和大家一一告别,便迅速腾云离开,向着数据纷争中心疾驰而去。


image.png




阿里云瑶池拥有国内强大且丰富的云数据库产品家族,涵盖关系型数据库、非关系型数据库、数据仓库、数据库生态工具四大版块,可以为企业提供一站式数据管理与服务。

数据库自治服务DAS是一种基于机器学习和专家经验实现数据库自感知、自修复、自优化、自运维及自安全的云服务让数据库实现了“自动驾驶”,够帮助用户消除数据库管理的复杂性及人工操作引发的服务故障,有效保障数据库服务的稳定、安全及高效。点击链接了解数据库自治服务DAS新版性能洞察更多信息。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
Sqoop 企业级大数据迁移方案实战
Sqoop是一个用于在Hadoop和关系数据库服务器之间传输数据的工具。它用于从关系数据库(如MySQL,Oracle)导入数据到Hadoop HDFS,并从Hadoop文件系统导出到关系数据库。 本课程主要讲解了Sqoop的设计思想及原理、部署安装及配置、详细具体的使用方法技巧与实操案例、企业级任务管理等。结合日常工作实践,培养解决实际问题的能力。本课程由黑马程序员提供。
相关文章
|
5月前
|
人工智能 多模数据库 Cloud Native
揽获多项殊荣,阿里云瑶池数据库亮相2024可信数据库发展大会
在2024可信数据库发展大会上,阿里云被选为中国信通院数据库金融工作组共建单位。同时,阿里云Lindorm成为首批通过中国信通院多模数据库产品测试的产品,展示出在多模数据处理能力上的领先性。
|
6月前
|
Cloud Native 关系型数据库 分布式数据库
《阿里云产品四月刊》—瑶池数据库云原生化和一体化产品能力升级
阿里云瑶池数据库云原生化和一体化产品能力升级,多款产品更新迭代
104 1
|
1月前
|
存储 关系型数据库 MySQL
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
287 2
|
3月前
|
存储 关系型数据库 MySQL
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
191 5
|
2月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
47 0
|
4月前
|
人工智能 关系型数据库 分布式数据库
DB+AI会擦出怎样的火花?一站式带你了解阿里云瑶池数据库经典的AI产品服务与实践!
从 DB+AI 精选解决方案、特惠权益等,一站式带你了解阿里云瑶池数据库经典的AI产品服务与实践。
|
4月前
|
SQL 关系型数据库 数据库
数据库空间之谜:彻底解决RDS for SQL Server的空间难题
【8月更文挑战第16天】在管理阿里云RDS for SQL Server时,合理排查与解决空间问题是确保数据库性能稳定的关键。常见问题包括数据文件增长、日志文件膨胀及索引碎片累积。利用SQL Server的动态管理视图(DMV)可有效监测文件使用情况、日志空间及索引碎片化程度。例如,使用`sp_spaceused`检查文件使用量,`sys.dm_db_log_space_usage`监控日志空间,`sys.dm_db_index_physical_stats`识别索引碎片。同时,合理的备份策略和文件组设置也有助于优化空间使用,确保数据库高效运行。
96 2
|
6月前
|
运维 数据库
【全新体验】诚邀您体验瑶池数据库控制台
诚邀您体验瑶池数据库控制台,感受多种场景体验提升。
|
6月前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之 MySQL数据库中,执行delete命令删除数据后,存储空间通常不会立即释放,该如何优化
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
140 2
|
6月前
|
SQL 存储 运维
网易游戏如何基于阿里云瑶池数据库 SelectDB 内核 Apache Doris 构建全新湖仓一体架构
随着网易游戏品类及产品的快速发展,游戏数据分析场景面临着越来越多的挑战,为了保证系统性能和 SLA,要求引入新的组件来解决特定业务场景问题。为此,网易游戏引入 Apache Doris 构建了全新的湖仓一体架构。经过不断地扩张,目前已发展至十余集群、为内部上百个项目提供了稳定可靠的数据服务、日均查询量数百万次,整体查询性能得到 10-20 倍提升。
网易游戏如何基于阿里云瑶池数据库 SelectDB 内核 Apache Doris 构建全新湖仓一体架构