编程知识 cdmana.com

How MySQL calculates and counts the size of redo log

 

stay MySQL How to calculate 、 Statistics redo log (redo log) How about the generation of ? for example 10 Within minutes , How much is generated M Of redo log Well ?30 How much more... In minutes M Of redo log......MySQL Didn't like Oracle The system view like in counts the data , But we can use some methods to calculate the amount of binary log generated .

 

 

Although I am in this blog MySQL in Redo Log Summary of relevant important parameters This paper introduces the in ,MySQL 8.0 Introduced innodb_dedicated_server Adaptive parameters , It can be set dynamically based on the memory of the server innodb_buffer_pool_size,innodb_log_file_size and innodb_flush_method. By default , This parameter is off . But in MySQL 8.0 Before , By calculating redo logs (redo log) To judge by the amount of production innodb_log_buffer_size and innodb_log_file_size It's very necessary to know if the size is right , I think that even if MySQL 8.0 Under the version , This is also of great reference and research significance . We do statistics 、 Analyze and calculate redo logs (redo log) Production of , To judge InnoDB How long can the transaction log file of the transaction log file support be switched . Supported by specific data , You can analyze and judge , Otherwise, it's hard to make bricks without straw .

 

stay MySQL Of information_schema.global_status or performance_schema.global_status There is a server state variable in (Server Status Variables)Innodb_os_log_written, It records Innodb Redo log of (redo log) Production of , It records and writes InnoDB The number of bytes in the redo log file , It's a cumulative value . The official document describes this server state variable as follows

 

Innodb_os_log_written

The number of bytes written to the InnoDB redo log files.

 

We're mainly through a planned mission / Events are scheduled to be collected periodically Innodb_os_log_written The server state variable gets the size of the redo log , Store it in innodb_log_size_his In the table , Convenient for analysis and statistics . The specific script is as follows :

 

Be careful :performance_schema.global_status yes MySQL 5.7 Introduced , and MySQL 8.0 Start ,information_schema.global_status It's just thrown away . So pay attention to MySQL edition , Choose the right script .

 

USE mysqls;
 
CREATE TABLE IF NOT EXISTS innodb_log_size_his
(
    log_id          INT AUTO_INCREMENT PRIMARY KEY COMMENT ' Log number ',
    log_date        DATETIME COMMENT ' Time to record current data ',
    log_size        DOUBLE COMMENT 'redo log Size , Unit is mb'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT 'redo Log size information table ';
 
 
--MySQL 5.* The version uses the following script 
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 The above version uses the following script 
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 ;

 

Then create MySQL Scheduled tasks for / Event scheduling , This can be set according to demand elasticity .

 

CREATE EVENT DPA_BINGLOG_SIZE 
ON SCHEDULE EVERY 10 MINUTE STARTS '2020-10-16 08:00:00' 
ON COMPLETION PRESERVE 
DO CALL mysql.Record_Innodb_Log_Size;

 

Then you can do some simple analysis and statistics based on this table , for example , Statistics 10 How many redo logs generated in minutes . As shown below :

 

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;

 

clip_image001

 

 

 

版权声明
本文为[kerrycode]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225090502220x.html

Scroll to Top