When MySQL Lies: Wrong seconds_behind_master with slave_parallel_workers > 0

seconds_behind_masterIn today’s blog, I will show an issue with seconds_behind_master that one of our clients faced when running slave_parallel_workers > 0. We found out that the reported seconds_behind_master from SHOW SLAVE STATUS was lying. To be more specific, I’m talking about bugs #84415 and #1654091.

The Issue

MySQL will not report the correct slave lag if you have slave_parallel_workers > 0. Let’s show it in practice.

I’ll use MySQL Sandbox to speed up one master and two slaves on MySQL version 5.7.17, and sysbench to populate the database:

Monitor Replication lag via SHOW SLAVE STATUS:

On a separate terminal, DELETE some rows in the test.sbtest1 table on the master, and monitor the above output once the master completes the delete command:

Here is a sample output:

As you can see, node1 (which is running with slave_parallel_workers = 5) doesn’t report any lag.

The Workaround

We can workaround this issue by querying performance_schema.threads:

Let’s modify our monitoring script, and use the above query to monitor the lag on node1:

Please note that in our query to performance_schema.threads, we are filtering PROCESSLIST_STATE “NULL” and “!= Waiting for an event from Coordinator”. The correct state is “Executing Event”, but it seems like it doesn’t correctly report that state (#84655).


MySQL parallel replication is a nice feature, but we still need to make sure we are aware of any potential issues it might bring. Most monitoring systems use the output of SHOW SLAVE STATUS to verify whether or not the slave is lagging behind the master. As shown above, it has its caveats.

As always, we should test, test and test again before implementing any change like this in production!

Share this post

Comments (4)

  • Moll

    Nice blog, Marcelo.
    For sure, parallel replication is better then single-threaded in terms of slave lagging but I think – according to the bug – most of the benchmarking results and comparisons between Single-Threaded and Multi-Threaded-Slave which depend on the SHOW SLAVE STATUS output are giving fake and non-accurate results!

    January 27, 2017 at 6:38 pm
  • Andrew Gebo

    For anyone who wants to get away from using replication check out ClustrixDB. It is a MySQL compatible database that offers far superior performance and scalability without sharding or in this case, replication.

    January 30, 2017 at 5:38 pm
  • Andy

    Typo: in the text, you initially mention slave_parallel_works, not slave_parallel_workers. cheers!

    March 7, 2017 at 5:21 am
    • Marcelo Altmann

      Hi Andy,
      Thanks for the heads up. It’s fixed now.

      March 13, 2017 at 10:31 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.