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 Transaction log file for (ib_logfile0) How long will it take to wait . 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 'binlog Size , Unit is mb'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT ' Binary 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;

MySQL How to calculate statistics redo log More articles about the size of

  1. say something MySQL Medium Redo log Undo log What are you doing

        In the database system , There are files that hold data , There are also log files . Logs are also cached in memory Log buffer, There are also disk files log file, This article mainly describes the file that stores the log .     MySQL Log files in , ...

  2. Detailed analysis MySQL Transaction log (redo log and undo log)

    innodb The transaction log includes redo log and undo log.redo log It's redoing the log , Provide roll forward operation ,undo log It's a rollback log , Provides rollback operations . undo log No redo log The reverse process of , In fact, it ...

  3. 【 turn 】 say something MySQL Medium Redo log Undo log What are you doing

    Read the directory (Content) 1 undo 1.1 undo What is it 1.2 undo Parameters 1.3 undo Space management 2 redo 2.1 redo What is it 2.2 redo Parameters 2.3 redo Space management ...

  4. 【MySQL ( 6、 ... and ) | Detailed analysis MySQL Transaction log redo log】

    Reference:  https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html introduction In order to avoid data writing as much as possible IO ...

  5. Detailed analysis MySQL Transaction log (redo log and undo log) It shows why mysql Don't lose data

    innodb The transaction log includes redo log and undo log.redo log It's redoing the log , Provide roll forward operation ,undo log It's a rollback log , Provides rollback operations . undo log No redo log The reverse process of , In fact, it ...

  6. Oracle change redo log size or increase redo log Group

    (1)redo log The size of can affect DBWR and checkpoint : (2)arger redo log files provide better performance. Undersize ...

  7. MYSQL file types redo log

    https://blogs.oracle.com/mysqlinnodb/entry/data_organization_in_innodb https://blogs.oracle.com/mysq ...

  8. Mysql undo And redo Log

    http://mysql.taobao.org/monthly/2015/04/01/ http://www.cnblogs.com/Bozh/archive/2013/03/18/2966494.h ...

  9. adjustment innodb redo log files Number and size of specific methods and steps

    Compare with Oracle Online adjustment of redo Number and size of logs ,mysql There is a deficiency in this point , Even now mysql80 edition , Not right innodb redo The number and size of logs are adjusted online , Here's just mysql adjustment inno ...

  10. mysql Business ( One )——redo log And undo log

    Data transactions Namely support ACID Four characteristics . A:atomicity           Atomicity —— All operations in the transaction are successful , Or all failed , Roll back to the initial state C:consistency     Uniformity —— ...

Random recommendation

  1. Python Base64 code

    0x00 Base64 brief introduction 0x01 Examples of common scenarios 0x02 Ed . Decoding process 0x03 Python in Base64 Encoding and decoding 0x00 Base64 brief introduction We know that any data in the computer is by pressing ascii Code storage ...

  2. Java Reading notes ( 6、 ... and ) Reference type

    Java Use in long Type data should be followed by the value L, Otherwise, it will be parsed as an integer . Reference type A reference type is an object type , Its value is a reference to memory space , It's the address , The memory pointed to holds a value or a set of values represented by the variable . i ...

  3. Flutter Introduce - Flutter,H5,React Native Contrast between

    Flutter Introduce Flutter yes Google Open source mobile application development framework launched . Developers can develop a set of code to run at the same time iOS and Android platform . It USES Dart Language development , And finally compiled into various platforms Nat ...

  4. Use the new tag to be compatible with the lower version IE

    HTML semantic significance : Structured by content ( semantic ), Choose the right label , Easy for developers to read and write more elegant code , At the same time, the crawler and machine of the rover can better analyze . Use as few semantic tags as possible div and span It's not obvious in semantics ...

  5. 【Alpha Stage 】 Test report

    buglist: link 1. The test found BUG From the black box test results before going online bug detailed list : The button to enter the report is grayed out The website crashes while the browser is browsing The experimental report doesn't show Favorites change to 1071 The report data generated is not correct Personal collection ...

  6. SharePoint Infopath Form services -PowerShell

    1. Activate a browsable form template Set-SPInfoPathFormsService -AllowUserFormBrowserEnabling $true -AllowUserFormBrowserRe ...

  7. java NIO Programming ( turn )

    One . Concept In traditional java Network programming , They all create one on the server side ServerSocket, Then create a separate thread for each client Thread Handle each request separately , Because for CPU for , The overhead of threads is very high , Infinite creation line ...

  8. linux centos7 Minimize installation NAT Mode network settings

    1. The network connection is set to NAT Pattern 2. Turn on CentOS7, With root land 3.vi /etc/sysconfig/network-scripts/ifcfg-ensXXXX4. Set up BOOTPROTO=dhcp ...

  9. REST Service usage @RestController example , Output xml/json

    REST Service usage @RestController example , Output xml/json Service annotations that need to be used org.springframework.web.bind.annotation.RestControlle ...

  10. 11- be based on dev Of bug( I haven't figured it out yet )

    Hexadecimal to octal http://lx.lanqiao.cn/problem.page?gpid=T51 Problem description Given n Six positive hexadecimal integers , Output their corresponding octal numbers . Input format The first line of input is a positive integer n ...