Mysql 主从延迟

Mysql_slave_delay

Posted by BlueFat on Tuesday, November 28, 2017

innodb_flush_log_at_trx_commit和sync_binlog参数详解

案例

收到邮件报警,mysql主从延迟>1h,导致新的数据无法进行同步。

mysql工作目录下积累了很多的mysqld-relay文件

mysql> show  slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.52.35.185
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.011374
          Read_Master_Log_Pos: 88856621
               Relay_Log_File: mysqld-relay-bin.034139
                Relay_Log_Pos: 7119391
        Relay_Master_Log_File: mysql-bin.011371
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: performance_schema.*,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 7119228
              Relay_Log_Space: 2116679529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 5430
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 185
                  Master_UUID: 870ab79e-2a14-11ea-ba96-00505697e312
             Master_Info_File: /data/mysql/db/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

Seconds_Behind_Master: 越来越大。
Slave_SQL_Running_State: 一直处于Reading event from the relay log状态

innodb_flush_log_at_trx_commitsync_binlog两个参数是控制ySQL磁盘写入策略以及数据安全性的关键参数。当两个参数为不同值时,在性能,安全角度下会产生不同的影响。

参数名称 允许值 描述
innodb_flush_log_at_trx_commit 0, 1, 2 当重新安排并批量处理与提交相关的I/O操作时,可以控制磁盘的写入策略,严格遵守ACID合规性和高性能之间的平衡,该参数默认值为“1”
sync_binlog 0~4,294,967,295 同步binlog(MySQL持久化到硬盘,或依赖于操作系统)。

参数解析

  • innodb_flush_log_at_trx_commits

    • 0:日志缓存区将每隔一秒写到日志文件中,并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。
    • 1:每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中,并且刷新到磁盘中,该模式为系统默认。
    • 2:每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中,但是并不会同时刷新到磁盘上。该模式下,MySQL会每秒执行一次刷新磁盘操作。

    说明:

    • 当设置为0,该模式速度最快,但不太安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失;
    • 当设置为1,该模式是最安全的,但也是最慢的一种方式。在mysqld服务崩溃或者服务器主机宕机的情况下,日志缓存区只有可能丢失最多一个语句或者一个事务;
    • 当设置为2,该模式速度较快,较取值为0情况下更安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失;
  • sync_binlog=1 or N

    默认情况下,并不是每次写入时都将binlog日志文件与磁盘同步。因此如果操作系统或服务器崩溃,有可能binlog中最后的语句丢失。

    为了防止这种情况,你可以使用sync_binlog全局变量(1是最安全的值,但也是最慢的),使binlog在每N次binlog日志文件写入后与磁盘同步。

注意: 如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。
而且mysql服务默认是autocommit打开的

推荐配置组合

innodb_flush_log_at_trx_commit sync_binlog 描述
1 1 适合数据安全性要求非常高,而且磁盘写入能力足够支持业务。
1 0 适合数据安全性要求高,磁盘写入能力支持业务不足,允许备库落后或无复制。
2 0/N(0<N<100) 适合数据安全性要求低,允许丢失一点事务日志,允许复制延迟。
0 0 磁盘写能力有限,无复制或允许复制延迟较长。

说明:

  • innodb_flush_log_at_trx_commitsync_binlog两个参数设置为1的时候,安全性最高,写入性能最差。在mysqld服务崩溃或者服务器主机宕机的情况下,日志缓存区只有可能丢失最多一个语句或者一个事务。但是会导致频繁的磁盘写入操作,因此该模式也是最慢的一种方式。
  • 当sync_binlog=N(N>1 ),innodb_flush_log_at_trx_commit=2时,在当前模式下MySQL的写操作才能达到最高性能。