作者：
马文斌
时间：
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.2fn", 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.2fn", i,s[i]/1024/1024)}}':
s[$6]+=$5: 使用awk脚本，创建一个关联数组s，其中索引是文件的修改日期（第6列），值是文件大小（第5列）。这将对相同日期的文件大小进行累加。
END{for(i in s) {printf("%s %0.2fn", 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、添加磁盘空间
作者公众号：
参考资料：
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
