优化GreatSQL日志文件空间占用
GreatSQL对于日志文件磁盘空间占用,做了一些优化,对于binlog、relay log、slow log和audit log的总空间占用举行了限制,使DBA免除了大量日志生成导致磁盘满的顾虑,极大的方便了数据库磁盘空间管理。
1.binlog二进制日志
- binlog_space_limit
- GreatSQL增加了静态参数 binlog_space_limit,限制数据库binlog的最大磁盘占用。当所有binlog文件的空间占用合计,凌驾此设置时,GreatSQL主动清理最旧的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的如下控制参数:
- max_slowlog_size
- max_slowlog_files
- 数据库保留的slow log的文件个数,慢查询日志总的磁盘空间占用是max_slowlog_size*max_slowlog_files。
参数行为体现测试:
- #参数默认值为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用于控制审计日志。
- audit_log_rotate_on_size
- 审计日志文件大小凌驾此设置时,举行轮转,存储为新的文件。
- audit_log_rotations
- 除audit.log外,保留的audit.log轮转文件的个数。
参数行为体现测试:
- 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个轮转的审计日志文件。
Enjoy GreatSQL
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |