MySQL 大表DDL吃掉了存储空间
1、问题与概述
植物大战僵尸，玩过吗？僵尸吃掉了你的脑子。是不是很熟悉，也许你听说过MySQL的大表在进行DDL的时有很多的注意事项，比如锁，并且推荐你使用一些工具，如PT或者Ghost等等。甚至你也知道Algorithm的几种模式。当你熟悉这些的时候，真的就没有坑了吗？一个DDL语句执行下去也许数据库就各种报错，甚至直接停止服务了，例如DDL之后导致PXB备份失败的情况，怕的是你知道，但是客户不知道，今天需要分享的案例就是执行DDL语句，出现存储空间不足报错的问题。(现场服务最怕的是客户知道，你不知道。)
2、环境复现
数据库版本：8.0.35
[root@mydb01 ~]# mysql -V
mysql Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)
系统存储空间
[root@mydb01 data]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs tmpfs 3.9G 13M 3.9G 1% /run
tmpfs tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/sda2 xfs 92G 5.5G 87G 6% /
/dev/sdb1 xfs 5.0G 4.9G 116M 98% /mysql
tmpfs tmpfs 797M 0 797M 0% /run/user/0
/mysql目录已经快100%使用率。然后我们执行DDL
mysql> alter table sbtest1 modify pad varchar(120);
ERROR 1114 (HY000): The table '#sql-1f39_47' is full
直接报错，查看错误日志中的内容如下
2024-01-10T02:25:23.316910Z 71 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file ./test_db/#sql-1f39_47.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2024-01-10T02:25:23.342218Z 71 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2024-01-10T02:25:23.342245Z 71 [ERROR] [MY-012639] [InnoDB] Write to file ./test_db/#sql-1f39_47.ibd failed at offset 120586240, 1048576 bytes should have been written, only 0 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2024-01-10T02:25:23.342261Z 71 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'
2024-01-10T02:25:23.342276Z 71 [Warning] [MY-012145] [InnoDB] Error while writing 4194304 zeroes to ./test_db/#sql-1f39_47.ibd starting at offset 117440512
2024-01-10T02:25:23.342674Z 71 [ERROR] [MY-013132] [Server] The table '#sql-1f39_47' is full!
给了一大推的信息，最后还给你一个感叹号!。原因还是因为存储空间不足，然后执行DDL失败。
3、如何解决问题
其实解决问题的方法很多，什么定时监控主机的存储空间，做好扩容等，类似的方法也是可以的。各种预防措施都是没问题的，事前总比事后好，这里给出常见的几种思路，第一是使用表空间来处理，第二是使用软连接来处理(这是S操作)，当然还有一些方法，本次使用第一种来测试。
4、执行步骤
4.1、先改参数文件
[root@mydb01 ~]# vi /mysql/jerry/my.cnf
innodb_directories=/tmp
4.2、重启数据库并检查
[root@mydb01 ~]# systemctl restart mysql
[root@mydb01 ~]# mysql
mysql> select @@innodb_directories;
+----------------------+
| @@innodb_directories |
+----------------------+
| /tmp |
+----------------------+
1 row in set (0.00 sec)
4.3、创建表空间
mysql> create tablespace ddltbs add datafile '/tmp/ddl.ibd' engine=innodb;
Query OK, 0 rows affected (0.00 sec)
4.4、执行DDL操作并监控
session 1：
mysql> use test_db
mysql> alter table sbtest1 modify pad varchar(120),tablespace ddltbs;
Query OK, 1000000 rows affected (10.89 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
session 2:
mysql> SELECT stmt.THREAD_ID, stmt.SQL_TEXT, stage.EVENT_NAME AS State, stage.WORK_COMPLETED, stage.WORK_ESTIMATED,
lpad(CONCAT(ROUND(100*stage.WORK_COMPLETED/ stage.WORK_ESTIMATED, 2),"%"),10," ")
AS COMPLETED_AT, lpad(format_pico_time(stmt.TIMER_WAIT), 10, " ") AS STARTED_AGO, lpad(format_pico_time(stmt.TIMER_WAIT/ ROUND(100*stage.WORK_COMPLETED/ stage.WORK_ESTIMATED,2)*100), 10, " ") AS ESTIMATED_FULL_TIME, lpad(format_pico_time((stmt.TIMER_WAIT/ ROUND(100*stage.WORK_COMPLETED/
stage.WORK_ESTIMATED,2)*100)-stmt.TIMER_WAIT), 10, " ") AS ESTIMATED_REMAINING_TIME, current_allocated
MEMORY FROM performance_schema.events_statements_current stmt INNER JOIN sys.memory_by_thread_by_current_bytes
mt ON mt.thread_id = stmt.thread_id INNER JOIN performance_schema.events_stages_current stage ON stage.THREAD_ID = stmt.THREAD_ID\G
*************************** 1. row ***************************
THREAD_ID: 47
SQL_TEXT: alter table sbtest1 modify pad varchar(120),tablespace ddltbs
State: stage/innodb/alter table (read PK and internal sort)
WORK_COMPLETED: 54848
WORK_ESTIMATED: 61650
COMPLETED_AT: 88.97%
STARTED_AGO: 3.37 s
ESTIMATED_FULL_TIME: 3.79 s
ESTIMATED_REMAINING_TIME: 418.02 ms
MEMORY: 1.19 MiB
1 row in set (0.05 sec)
4.5、再次更改表空间
mysql> alter table sbtest1 tablespace=innodb_file_per_table;
Query OK, 0 rows affected (7.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
4.6、删除不需要的表空间
mysql> SELECT it.*, PATH
-> FROM INFORMATION_SCHEMA.INNODB_TABLESPACES it
-> JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF itb
-> ON itb.SPACE=it.SPACE WHERE it.NAME='ddltbs'\G
*************************** 1. row ***************************
SPACE: 26
NAME: ddltbs
FLAG: 18432
ROW_FORMAT: Any
PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
SPACE_TYPE: General
FS_BLOCK_SIZE: 4096
FILE_SIZE: 255852544
ALLOCATED_SIZE: 255852544
AUTOEXTEND_SIZE: 0
SERVER_VERSION: 8.0.35
SPACE_VERSION: 1
ENCRYPTION: N
STATE: normal
PATH: /tmp/ddl.ibd
1 row in set (0.01 sec)
mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM innodb_tables WHERE space=26;
Empty set (0.01 sec)
mysql> drop tablespace ddltbs;
Query OK, 0 rows affected (0.04 sec)
这里表空间查询的结果必须为空，才能删除
4.7、检查表结构和数据
mysql> select count(*) from test_db.sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.28 sec)
mysql>
mysql> show create table test_db.sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` varchar(120) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
4.8、查看数据文件
[root@mydb01 ~]# ls -lsa /mysql/data/3306/data/test_db/sbtest1.ibd
278532 -rw-r----- 1 mysql mysql 285212672 Jan 10 11:03 /mysql/data/3306/data/test_db/sbtest1.ibd
[root@mydb01 ~]# ls -lsa /tmp/mysql
mysql.sock mysqlx.sock mysqlx.sock.lock
[root@mydb01 ~]# ls -lsa /tmp/ddltbs
ls: cannot access /tmp/ddltbs: No such file or directory
[root@mydb01 ~]#
[root@mydb01 ~]# ls -lsa /tmp/ddl*
ls: cannot access /tmp/ddl*: No such file or directory
[root@mydb01 ~]# df -Th
Filesystem Type Size Used Avail Use% Mounted on
devtmpfs devtmpfs 3.9G 0 3.9G 0% /dev
tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm
tmpfs tmpfs 3.9G 13M 3.9G 1% /run
tmpfs tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup
/dev/sda2 xfs 92G 5.5G 87G 6% /
/dev/sdb1 xfs 5.0G 5.0G 84M 99% /mysql
tmpfs tmpfs 797M 0 797M 0% /run/user/0
5、一些必要的废话
如果数据库存储空间不足，可以考虑以下几种方法来解决问题：
- 清理不必要的数据： 检查数据库中是否存在不必要或过期的数据，可以删除或归档这些数据以释放空间。例如，删除历史日志、过时的备份或不再需要的临时数据。
- 优化表格和索引： 对数据库表格和索引进行优化，可以帮助减小存储空间的需求。删除或优化不必要的索引，以及对表格进行碎片整理，都是常见的优化手段。
- 调整数据库配置： 检查数据库配置参数，例如日志保留期限、缓存大小等，以确保它们符合实际需求。有时候，通过调整这些参数可以减小数据库的存储需求。
- 压缩数据： 一些数据库系统提供数据压缩功能，可以通过启用压缩来减小数据存储的空间占用。注意，压缩可能会对性能产生一些影响，因此需要谨慎使用。
- 增加存储空间： 如果以上方法无法满足需求，考虑增加数据库服务器的存储容量。这可以通过添加新的硬盘、扩展云存储等方式来实现。
- 归档数据： 将不常用的数据归档到另一个存储位置，例如归档到独立的数据库、数据仓库或冷存储。这样可以保留数据，同时释放主数据库的空间。
- 定期备份和清理日志： 定期备份数据库，并清理不再需要的事务日志。这有助于避免日志文件不断增长导致存储空间不足。
在执行上述操作之前，请确保在生产环境中进行充分的备份，并且谨慎操作，以防止数据丢失或损坏。最好在非高峰时段执行这些操作，以最小化对业务的影响。
如果执行DDL：(此段来自GPT，请自行思考)
- 避免长时间锁定： DDL 操作可能需要锁定表格或行，这可能导致其他查询和事务被阻塞。尽量在低峰期执行DDL操作，或者通过使用工具和技术来减小锁定时间。
- 注意约束： 在进行表结构更改时，确保已考虑到外键、唯一约束等。如果有外键关联，确保更改不会违反引用完整性。
- 避免热点表格： 在高并发环境中，对热点表格进行DDL操作可能会导致性能问题。尽量避免在繁忙的表格上执行DDL操作，或者考虑通过分阶段执行以减小影响。
- 检查依赖关系： 在执行DDL操作之前，检查是否有存储过程、触发器、视图等依赖于即将更改的表格或对象。确保这些依赖关系不会受到影响。
- 监控执行过程： 使用MySQL的日志和监控工具来追踪DDL操作的执行过程，以及执行后的性能影响。这有助于及时发现潜在问题。
- 版本控制： 对数据库结构的更改进行版本控制，以便能够追溯和还原结构更改。这对于团队协作和数据库维护非常重要。
- 定期维护： 定期进行数据库维护，包括优化表格、索引、统计信息，以及清理不必要的数据。
