优化 GreatSQL 日志文件空间占用
GreatSQL 对于日志文件磁盘空间占用,做了一些优化,对于 binlog、relay log、slow log 和 audit log 的总空间占用进行了限制,使 DBA 免除了大量日志生成导致磁盘满的顾虑,极大的方便了数据库磁盘空间管理。
1.binlog 二进制日志
参数行为表现测试:
# 查询参数配置值
greatsql> SHOW GLOBAL VARIABLES LIKE 'binlog_space_limit';
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| binlog_space_limit | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)
# 进行业务处理,检查binlog大小
greatsql> SHOW BINARY LOGS;
+-----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+-----------+-----------+
| mybinlog.000009 | 433980378 | No |
| mybinlog.000010 | 7590652 | No |
| mybinlog.000011 | 208398410 | No |
| mybinlog.000012 | 414221182 | No |
+-----------------+-----------+-----------+
4 rows in set (0.00 sec)
# 达到binlog_space_limit的1G限制,清理了最旧的binlog文件
greatsql> SHOW BINARY LOGS;
+-----------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-----------------+-----------+-----------+
| mybinlog.000010 | 7590652 | No |
| mybinlog.000011 | 208398410 | No |
| mybinlog.000012 | 455964041 | No |
+-----------------+-----------+-----------+
3 rows in set (0.00 sec)
复制代码
2.relay log 中继日志
relay_log_space_limit
静态参数,此选项为复制副本上所有中继日志的总大小(以字节为单位)设置了上限。这对于磁盘空间有限的副本服务器主机非常有用。值为 0 表示“无限制”。当达到限制时,I/O(接收器)线程停止从源服务器读取二进制日志事件,直到 SQL 线程赶上并删除了一些不再使用的中继日志。
请注意,此限制不是绝对的:在某些情况下,SQL 线程需要更多的事件才能删除中继日志。在这种情况下,会超过此限制,直到 SQL 线程可以删除一些中继日志,因为不这样做会导致死锁。不应将--relay-log-space-limit
设置为小于--max-relay-log-size
值的 2 倍。在这种情况下,IO 线程可能会等待空闲空间,因为超过了--relay-log-space-limit
限制,但 SQL 线程没有中继日志可以清除,无法满足 IO 线程的需求。这会迫使 IO 线程暂时忽略--relay-log-space-limit
限制。
参数行为表现测试:
修改配置后,重启 mysqld,将 slave 改为延时 8 小时应用。
greatsql> SHOW GLOBAL VARIABLES LIKE 'relay_log_space_limit';
+-----------------------+------------+
| Variable_name | Value |
+-----------------------+------------+
| relay_log_space_limit | 3221225472 |
+-----------------------+------------+
1 row in set (0.01 sec)
greatsql> STOP SLAVE SQL_THREAD;CHANGE MASTER TO MASTER_DELAY=28800;START SLAVE SQL_THREAD;
复制代码
在主节点,业务加压,生成 relay log,当 relay log 的文件总和达到 3G 时,relay log 不在增加。
$ du -sm /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/*relay*
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000018
596 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000019
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000020
1025 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000021
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000022
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000023
1028 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000024
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000025
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000026
426 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000027
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.index
复制代码
检查 slave status,Slave 的 IO 和 SQL 线程都是 Yes,当 IO 线程已停止接收新的事务。
greatsql> pager grep -E '_Log_|Gtid|Running'
PAGER set to 'grep -E '_Log_|Gtid|Running''
greatsql> SHOW SLAVE STATUS\G
Master_Log_File: greatsql-bin.000006
Read_Master_Log_Pos: 445825094
Relay_Log_File: greatsql-relay.000019
Relay_Log_Pos: 410
Relay_Master_Log_File: greatsql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 459045495
Relay_Log_Space: 3221226704
Until_Log_File:
Until_Log_Pos: 0
Slave_SQL_Running_State: Waiting until SOURCE_DELAY seconds after source executed event
Retrieved_Gtid_Set: d02f35e9-afdc-11ef-a49b-00163e0ea4a5:138815-387659
Executed_Gtid_Set: d02f35e9-afdc-11ef-a49b-00163e0ea4a5:1-138814
1 row in set, 1 warning (0.00 sec)
greatsql> SHOW SLAVE STATUS\G
Master_Log_File: greatsql-bin.000006
Read_Master_Log_Pos: 445825094
Relay_Log_File: greatsql-relay.000019
Relay_Log_Pos: 410
Relay_Master_Log_File: greatsql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 459045495
Relay_Log_Space: 3221226704
Until_Log_File:
Until_Log_Pos: 0
Slave_SQL_Running_State: Waiting until SOURCE_DELAY seconds after source executed event
Retrieved_Gtid_Set: d02f35e9-afdc-11ef-a49b-00163e0ea4a5:138815-387659
Executed_Gtid_Set: d02f35e9-afdc-11ef-a49b-00163e0ea4a5:1-138814
1 row in set, 1 warning (0.00 sec)
复制代码
将 slave 改为延时 10 分钟应用,sql 线程在应用完 greatsql-relay.000019 的事务后,io 线程开始接收新的事务到 greatsql-relay.000027,sql 线程在应用完 greatsql-relay.000021 的事务之前,IO 线程再次暂停。
greatsql> STOP SLAVE SQL_THREAD;CHANGE MASTER TO MASTER_DELAY=600;START SLAVE SQL_THREAD;
复制代码
relay log 的始终合计控制在 3G 左右。
$ du -sm /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/*relay*
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000020
1025 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000021
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000022
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000023
1028 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000024
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000025
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000026
1450 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000027
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.index
......
$ du -sm /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/*relay*
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000032
1025 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000033
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000034
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000035
1025 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000036
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000037
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000038
1024 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.000039
1 /data/greatsqldata/16315fff-72c5-4ea6-b26e-3604869063a6/dbdata/greatsql-relay.index
复制代码
slow log 慢日志
GreatSQL 增加 slow log 的如下控制参数:
参数行为表现测试:
#参数默认值为0,表示不限制
greatsql> SHOW GLOBAL VARIABLES LIKE 'max_slow%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| max_slowlog_files | 0 |
| max_slowlog_size | 0 |
+-------------------+-------+
2 rows in set (0.01 sec)
#修改参数值
greatsql> SET GLOBAL max_slowlog_files=2;
Query OK, 0 rows affected (0.00 sec)
greatsql> SET GLOBAL max_slowlog_size=1024*1024;
Query OK, 0 rows affected (0.01 sec)
greatsql> SHOW VARIABLES LIKE 'max_slow%';
+-------------------+---------+
| Variable_name | Value |
+-------------------+---------+
| max_slowlog_files | 2 |
| max_slowlog_size | 1048576 |
+-------------------+---------+
2 rows in set (0.01 sec)
复制代码
slow.log 当达 max_slowlog_size 设置的大小时,会发生轮转生成一个新的文件,当文件数达到 max_slowlog_files 设置的数量时,删除最旧的文件。
$ ll slow*
-rw-r----- 1 GreatSQL GreatSQL 1048621 Dec 17 10:52 slow.log.000010
-rw-r----- 1 GreatSQL GreatSQL 1043247 Dec 17 10:53 slow.log.000011
#--继续生成slow.log,删除了slow.log.000010
$ ll slow*
-rw-r----- 1 GreatSQL GreatSQL 1048692 Dec 17 10:53 slow.log.000011
-rw-r----- 1 GreatSQL GreatSQL 46119 Dec 17 10:53 slow.log.000012
复制代码
audit log 审计日志
GreatSQL 的 audit 插件参数audit_log_rotate_on_size
,audit_log_rotations
用于控制审计日志。
参数行为表现测试:
greatsql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Query OK, 0 rows affected (0.12 sec)
#默认值为0,不轮转,不限制文件大小
greatsql> SHOW GLOBAL VARIABLES LIKE 'audit_log_rotat%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| audit_log_rotate_on_size | 0 |
| audit_log_rotations | 0 |
+--------------------------+-------+
2 rows in set (0.01 sec)
#设置参数值
greatsql> SET GLOBAL audit_log_rotate_on_size=1048576;
Query OK, 0 rows affected (0.00 sec)
greatsql> SET GLOBAL audit_log_rotations=4;
Query OK, 0 rows affected (0.00 sec)
greatsql> SHOW VARIABLES LIKE 'audit_log_rota%';
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| audit_log_rotate_on_size | 1048576 |
| audit_log_rotations | 4 |
+--------------------------+---------+
2 rows in set (0.01 sec)
复制代码
检查 audi.log 文件的变化:
$ ll audit*
-rw-r----- 1 GreatSQL GreatSQL 492556 Dec 17 11:26 audit.log
-rw-r----- 1 GreatSQL GreatSQL 1050204 Dec 17 11:26 audit.log.1
-rw-r----- 1 GreatSQL GreatSQL 1050485 Dec 17 11:26 audit.log.2
-rw-r----- 1 GreatSQL GreatSQL 1051191 Dec 17 11:26 audit.log.3
-rw-r----- 1 GreatSQL GreatSQL 1050767 Dec 17 11:26 audit.log.4
# audit.log发生轮转
$ ll audit*
-rw-r----- 1 GreatSQL GreatSQL 993 Dec 17 11:26 audit.log
-rw-r----- 1 GreatSQL GreatSQL 1542990 Dec 17 11:26 audit.log.1
-rw-r----- 1 GreatSQL GreatSQL 1050204 Dec 17 11:26 audit.log.2
-rw-r----- 1 GreatSQL GreatSQL 1050485 Dec 17 11:26 audit.log.3
-rw-r----- 1 GreatSQL GreatSQL 1051191 Dec 17 11:26 audit.log.4
复制代码
日志写入 audit.log 文件,当达到audit_log_rotate_on_size
时,发生轮转:
删除 audit.log.4,audit.log.3 轮转为 audit.log.4,audit.log.2 轮转为 audit.log.3,audit.log.1 轮转为 audit.log.2,audit.log 轮转为 audit.log.1
共保留 audit_log_rotations 个轮转的审计日志文件。
评论