Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Monday, 19 February 2018

MySql Replication - Slave Lagging behind master

Issue: second_behind_master is continuously increasing --

Some common causes of replication slave lags include:
  1. CPU load: Slave Server is to busy to process replication events in a timely manner.
  2. Missing indexes: no primary key/Unique key on the table that is being updated .
  3. Lock wait timeouts: lock wait timeouts caused by uncommitted transaction on the slave.
  4. Data consistency: frequent slave stops and starts due to data consistency issues (duplicate keys or row not found).
  5. Slow queries: Some Sql_thread take much more time to execute queries.
  6. Network issues: due to somehow network issue like server bandwidth/data transfer on network speed etc.

We can perform below troubleshooting to minimize the lag...

First of all match the server timestamp of both Master-Slave.

Analyze which thread is reason for behind IO_THREAD or SQL_THREAD
MySQL> show slave status \G;

Keep an eye on the Relay_Master_Log_File and make sure that it is going up and catching up with the Master_Log_File.
If Master_Log_File: "mysql-bin.000990" and Relay_Master_Log_File: "mysql-bin.000990"  is equal it's means IO_THREAD is caching up master log properly.

Stop Slave IO_THREAD;

Monitor Exec_Master_Log_Pos: xxxxxxxxxx - if this is constant it's means SQL_THREAD is facing some issue while executing . If it's also increasing then you can check below other parameters.

           Slave_IO_State: Waiting for master to send event
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000173
      Read_Master_Log_Pos: 15909435
           Relay_Log_File: mysqld-relay-bin.000079
            Relay_Log_Pos: 91173356
    Relay_Master_Log_File: mysql-bin.000093
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
               Last_Errno: 0
             Skip_Counter: 0
      Exec_Master_Log_Pos: 91173210
          Relay_Log_Space: 8179978166
          Until_Condition: None
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
    Seconds_Behind_Master: 486330
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
           Last_SQL_Errno: 0
         Master_Server_Id: 1
1 row in set (0.00 sec)

No query specified
Herein you can check Seconds_Behind_Master that high, Now I Look at the following:
Relay_Log_Space: 8179978166
You have approx 7GB of relay logs to process.
Master_Log_File: mysql-bin.000173
Relay_Master_Log_File: mysql-bin.000093
This tell me that you have read up to mysql-bin.000173, but you are currently processing from the mysql-bin.000093.

This also tell me you have about 80 binary logs on the Master, each about 100 MB.
The Seconds_Behind_Master is simply the NOW() minus the TIMESTAMP set at mysql-bin.000093(Relay_Master_Log_File) position 91173210 (Exec_Master_Log_Pos).

As long as Slave_SQL_Thread is Yes, the relay logs get processed
  • Relay_Log_Space will decrease each time a relay log is done
  • Exec_Master_Log_Pos will increase until current relay log is done, then resets to the beginning of the next relay
  • TIMESTAMP keeps increasing, which make Seconds_Behind_Master decrease (NOW() minus the TIMESTAMP set at Relay_Master_Log_File position Exec_Master_Log_Pos)
After all troubleshooting you can do following some changes on Slave Server and then check..

  • Set innodb_flush_log_at_trx_commit=0
  • innodb_lock_wait_timeout=200 (According to database working performance)
  • transaction-isolation=READ-COMMITTED    (This is global parameter ,so please first check with set global tx_isolation='READ-COMMITTED'; )
  • Enable Slow Query Log and observe/optimize if any slow queries found.
  • Check CPU Load and server performance.
  • Check network latency/speed/bandwidth etc.

Note: Please read the impact of all above mentioned parameter from MYSQL website before doing any changes on your Server. 

For any Clarification please write us Click Here

No comments:

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...