数据库空间爆了怎么处理

作者：

马文斌

时间：

2024-1-29

标签：

mysql 磁盘空间 爆满 binlog

背景

近期数据库空间一直告警，平时这套数据库集群是不会有磁盘空间告警的，难道是最近业务量猛增了吗？咱们来瞧瞧到底怎么回事？

清理binlog

先清理一些历史的binlog，这样可以马上释放磁盘空间

PURGE BINARY LOGS TO 'mysql-bin.059306';

统计大表

test_plat_order_record
test_plat_order_record_original

统计这个大表的数据量

SELECT COUNT(*), DATE_FORMAT(test_update_time, '%Y-%m') AS formatted_update_time
    -> FROM test_plat_order_record
    -> GROUP BY formatted_update_time
    -> ORDER BY formatted_update_time;
​
+----------+-----------------------+
| COUNT(*) | formatted_update_time |
+----------+-----------------------+
|       11 | 2019-10               |
|     1021 | 2019-11               |
|      343 | 2019-12               |
|        1 | 2020-04               |
|        1 | 2020-06               |
|        2 | 2020-08               |
|       50 | 2020-11               |
|        4 | 2021-01               |
|       52 | 2021-04               |
|       29 | 2021-05               |
|      100 | 2021-06               |
|       37 | 2021-07               |
|      102 | 2021-08               |
|       29 | 2021-09               |
|       84 | 2021-10               |
|       86 | 2021-11               |
|      115 | 2021-12               |
|       70 | 2022-01               |
|       45 | 2022-02               |
|       27 | 2022-03               |
|       42 | 2022-04               |
|       35 | 2022-05               |
|       11 | 2022-06               |
|        5 | 2022-07               |
|       11 | 2022-08               |
|       11 | 2022-09               |
|       18 | 2022-10               |
|       66 | 2022-11               |
|       59 | 2022-12               |
|       23 | 2023-01               |
|       36 | 2023-02               |
|       15 | 2023-03               |
|        7 | 2023-04               |
|       50 | 2023-05               |
|      209 | 2023-06               |
|     1624 | 2023-07               |
|  1589513 | 2023-08               |
|  2340076 | 2023-09               |
|  2234520 | 2023-10               |
|  5123385 | 2023-11               |
|  2307748 | 2023-12               |
|  2211829 | 2024-01               |
+----------+-----------------------+
42 rows in set (3 min 2.90 sec)
​

统计每天产生的binlog日志文件大小，可以看到每天大概产生250G的日志文件

ls --full-time | grep ^- | \
awk '{s[$6]+=$5} END{for(i in s) {printf("%s %0.2f\n", i,s[i]/1024/1024)}}' | sort
​
2024-01-21 254712.17
2024-01-22 106553.17

解释下这个命令

这个命令是一个用于统计文件大小并按日期分类的Linux命令。让我们一步步解释：
​
ls --full-time: 列出当前目录下所有文件的详细信息，包括文件大小和最后修改时间。
​
grep ^-: 过滤出只有普通文件（不包括目录、链接等）的行。这是通过^符号表示行的开头是普通文件来实现的。
​
awk '{s[$6]+=$5} END{for(i in s) {printf("%s %0.2f\n", i,s[i]/1024/1024)}}':
​
s[$6]+=$5: 使用awk脚本，创建一个关联数组s，其中索引是文件的修改日期（第6列），值是文件大小（第5列）。这将对相同日期的文件大小进行累加。
END{for(i in s) {printf("%s %0.2f\n", i,s[i]/1024/1024)}}: 在处理完所有行后，使用END块循环遍历数组s，打印每个日期和对应的总文件大小（以MB为单位）。
sort: 对结果进行排序。
​
所以，最终输出将是按照日期分类的文件大小总和，以MB为单位。日期是文件的最后修改日期。这对于查看目录中每天创建或修改的文件的总大小是有用的。

保留1.5天binlog

binlog_expire_logs_seconds=129600

应用层面分析

用my2sql分析binlog

分析脚本
#!/bin/bash
createtime=`date +%Y-%m-%d_%H-%M-%S`
datadir=/tmp/$createtime
mkdir $datadir
/usr/local/bin/my2sql  -user testuser -password xxxxx -host 192.168.1.1 -work-type 2sql  -start-file mysql-bin.061073 -stop-file mysql-bin.061075 -output-dir $datadir
echo "请在 这个文件查看输出结果 cd $datadir"
​
分析binlog
cat binlog_status.txt |awk '{print $2,$7,$NF}'|sort -k2,2nr > sort_status.txt

通过分析是binlog，发现2张表分析很频繁，每次都是几千条数据一起更新，其中test_plat_order_record 有个大对象字段 mediumtext ，其中 L< 2 的24次方，等于最大可以存16MB内容，业务说是一些操作的报文内容。

[root@db-oms-slave-32-228 2024-01-23_10-12-48]# more sort_status.txt 
2024-01-23_08:01:34 3371 test_order_status
2024-01-23_08:01:34 3342 test_plat_order_record
2024-01-23_08:00:34 2720 test_order_status
2024-01-23_08:00:34 2599 test_plat_order_record
2024-01-23_07:57:21 2518 test_order_status
2024-01-23_07:57:21 2484 test_plat_order_record
2024-01-23_08:00:00 2085 test_order_status

1问其能否优化这个大对象报文内容吗？

答曰：暂时无法改造

2问最近是业务量猛增吗？公司要起飞啦

答曰：是其他电商平台的数据导入过来的，统一存储管理统计，业务量并无增长。

数据库层面优化

检查大对象sql：

SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'ec_order'
AND data_type IN ('text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob');

binlog压缩

主从读得开启才行

mysql> set persist binlog_transaction_compression=on;
mysql> set persist binlog_transaction_compression_level_zstd=10;

binlog压缩后的结论

1. MySQL 新推出的 binlog 压缩功能，当压缩级别设置为 10 时，压缩率约为 50% 左右，能够较大程度减少 binlog 所占用的空间。
2. 压缩功能能够一定程度提升因网络带宽所带来的主从延迟，集群tps不降低，略微提升。

大表压缩

alter table test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

大概可以节省50%的空间

添加硬盘

/data 文件系统+300G的磁盘空间

总结

1、磁盘空间暴涨很多时候是因为表中有大对象字段，开发没有提前跟你说，这时候就需要sql审核层面多留心下，发现有大对象字段上线问其原因，能否减少写入的内容

2、紧急情况可以先清理一部分binlog 释放空间、先不影响业务

3、binlog暴涨的话，可以用my2sql工具分析binlog，并做排序，看看那些表变更插入频繁

4、了解业务 为什么要存了一些报文内容到数据库层面，能否做优化

5、数据库层面 表+binlog的压缩

6、添加磁盘空间

作者公众号：

参考资料：

新特性解读 | binlog 压缩

MySQL :: MySQL 8.0 Reference Manual :: 11.7 Data Type Storage Requirements

MySQL :: MySQL 8.0 Reference Manual :: 12.8 String Functions and Operators