However, this can be misleading, because it only measures the difference between the timestamps of the relay log most recently executed, versus the relay log entry most recently downloaded by the IO_THREAD.
If there are more binlogs on the master, the slave doesn’t figure them into the calculation of Seconds_behind_master.
You can read detailed description of show slave status output fields here.
Also, the Seconds_Behind_Master parameter shows a huge delay in seconds.
You can get a more accurate measure of slave lag using pt-heartbeat from Percona Toolkit.
So, we learned how to check replication delays – either it’s slave IO_THREAD or slave SQL_THREAD.
This is sometimes because of different hardware between master/slave, different schema indexes, workload.mysql-bin.01819 This indicates that the slave SQL_THREAD is applying events fast enough, but it’s lagging too, which can be observed from the difference between Read_Master_Log_Pos & Exec_Master_Log_Pos from show slave status output.You can calculate slave SQL_THREAD lag from Read_Master_Log_Pos – Exec_Master_Log_Pos in general as long as Master_Log_File parameter output from show slave status and Relay_Master_Log_File parameter from show slave status output are the same.Moreover, the slave OLTP workload sometimes causes replication delays because of locking.For instance, if a long-running read against a My ISAM table blocks the SQL thread, or any transaction against an Inno DB table creates an IX lock and blocks DDL in the SQL thread.Now, let me provide some tips and suggestions for what exactly causing this delay.Tips and Suggestions What Causing Replication Delay & Possible Fixes Usually, the slave IO_THREAD is behind because of slow network between master/slave.In today’s post, however, I will share some new ways of identifying delays in replication – including possible causes of lagging slaves – and how to cure this problem.How to identify Replication Delay My SQL replication works with two threads, IO_THREAD & SQL_THREAD.As you can see the Master log file is mysql-bin.018196) which indicates slave IO_THREAD is reading from that file, while on master it’s writing on mysql-bin.018196, so the slave IO_THREAD is behind by 4 binlogs.Meanwhile, the slave SQL_THREAD is reading from same file i.e.