在MySQL中如何计算、统计重做日志(redo log)的生成情况呢? 例如10分钟内,生成了多少M的redo log呢?30分钟内又生成了多少M的redo log.....。MySQL没有像Oracle中那样的系统视图统计这些数据,但是我们可以通过一些方法曲线的统计二进制日志的生成量。
虽然我在这篇博客“MySQL中Redo Log相关的重要参数总结”中介绍了,MySQL 8.0引入了innodb_dedicated_server自适应参数,可基于服务器的内存来动态设置innodb_buffer_pool_size,innodb_log_file_size和innodb_flush_method。默认情况下,此参数是关闭的。但是在MySQL 8.0之前,通过计算重做日志(redo log)的生成量来判断判断innodb_log_buffer_size和innodb_log_file_size的大小是否合适是非常必要的,个人认为即使MySQL 8.0版本下,这个也是非常有参考和研究意义的。我们通过统计、分析计算重做日志(redo log)的生成量,从而判断InnoDB的事务日志文件大概能支撑多长时间就会切换。有具体数据支撑,你才好分析判断,否则巧妇也难为无米之炊。
在MySQL的information_schema.global_status或performance_schema.global_status中有个服务器状态变量(Server Status Variables)Innodb_os_log_written,它记录了Innodb的重做日志(redo log)的生成量,它记录写入InnoDB重做日志文件的字节数,它是一个累积值。官方文档关于此服务器状态变量的描述如下
Innodb_os_log_written
The number of bytes written to the InnoDB redo log files.
我们主要通过一个计划任务/事件调度定期的去采集Innodb_os_log_written服务器状态变量获取重做日志的大小,将其存储在innodb_log_size_his表中,方便分析统计。具体脚本如下:
注意:performance_schema.global_status是MySQL 5.7引入的,而MySQL 8.0开始,information_schema.global_status直接被丢弃了。所以注意MySQL版本,选择合适脚本。
USE mysqls;
CREATE TABLE IF NOT EXISTS innodb_log_size_his
(
log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志编号',
log_date DATETIME COMMENT '记录当前数据的时间',
log_size DOUBLE COMMENT 'redo log的大小,单位为mb'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT 'redo日志大小信息表';
--MySQL 5.*版本用下面脚本
DELIMITER &&
DROP PROCEDURE IF EXISTS `Record_Innodb_Log_Size`&&
CREATE PROCEDURE Record_Innodb_Log_Size()
BEGIN
INSERT INTO mysql.`innodb_log_size_his`
(
log_date,
log_size
)
SELECT now() AS log_date,
ROUND(CAST(VARIABLE_VALUE AS DOUBLE )/1024/1024, 1) as log_size
FROM information_schema.global_status
WHERE VARIABLE_NAME = 'innodb_os_log_written';
END &&
DELIMITER ;
--MySQL 8.0以上版本使用下面脚本
DELIMITER &&
DROP PROCEDURE IF EXISTS `Record_Innodb_Log_Size`&&
CREATE PROCEDURE Record_Innodb_Log_Size()
BEGIN
INSERT INTO mysql.`innodb_log_size_his`
(
log_date,
log_size
)
SELECT now() AS log_date,
ROUND(CAST(VARIABLE_VALUE AS DOUBLE )/1024/1024, 1) as log_size
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'innodb_os_log_written';
END &&
DELIMITER ;
然后创建MySQL的计划任务/事件调度,这个可以根据需求弹性设定。
CREATE EVENT DPA_REDOLOG_SIZE
ON SCHEDULE EVERY 10 MINUTE STARTS '2020-10-16 08:00:00'
ON COMPLETION PRESERVE
DO CALL mysql.Record_Innodb_Log_Size;
然后你就可以基于这个表做一些简单的分析和统计了,例如,统计10分钟内生成重做日志生成了多少。如下所示:
SELECT m.*
,@lag AS last_redo_size
,ROUND(m.log_size - @lag,2) AS gen_redo_size
,@lag:=log_size
FROM mysql.`innodb_log_size_his` m, (SELECT @lag :='') AS n
WHERE m.log_date >= date_add(now(), interval -1 day)
ORDER BY m.log_id;