# Replication lag on MySQL server after increasing the number of inserts on the master **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 12, 2018, 3:41am UTC](https://forums.percona.com/t/replication-lag-on-mysql-server-after-increasing-the-number-of-inserts-on-the-master/6457 "2018-07-12T03:41:24Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![spirit1984](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/spirit1984/32/1233_2.png) [@spirit1984](https://forums.percona.com/u/spirit1984)\ **Post date:** [July 12, 2018, 3:41am UTC](https://forums.percona.com/t/replication-lag-on-mysql-server-after-increasing-the-number-of-inserts-on-the-master/6457/1 "2018-07-12T03:41:24Z")
I’ve ran recently to a problem. We have a MySQL 5.7 Innodb replica with RBR replication (8 slave parallel workers). Now, until this Monday, our master executed 2500 inserts per second (in average), and the replica seemed to be fine with that. But recently the number of inserts went to 2800 inserts per second, and the replica does not seem to catch on (at evenings maybe). I checked the slave status. It seems like the IO thread seems to work just fine (there is no problem with getting events from the master), but the SQL threads don’t run that fast. So the question is - how can I improve that - increase of parallel workers does not seem to help at all. By the way, here is the result of sql show status: > [@](#): > > Slave\_IO\_State: Waiting for master to send event > Master\_User: repl > Master\_Port: 3306 > Connect\_Retry: 60 > Master\_Log\_File: mysql-bin.001634 > Read\_Master\_Log\_Pos: 268027451 > Relay\_Log\_File: mysql-relay-bin.004672 > Relay\_Log\_Pos: 386106741 > Relay\_Master\_Log\_File: mysql-bin.001632 > Slave\_IO\_Running: Yes > Slave\_SQL\_Running: Yes > Replicate\_Do\_DB: > Replicate\_Ignore\_DB: > Replicate\_Do\_Table: > Replicate\_Ignore\_Table: > Replicate\_Wild\_Ignore\_Table: > Last\_Errno: 0 > Last\_Error: > Skip\_Counter: 0 > Exec\_Master\_Log\_Pos: 386106528 > Relay\_Log\_Space: 2415514767 > 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: 1688 > 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: 75 > Master\_UUID: 4db1d460-6b9b-11e8-a372-005056956c40 > Master\_Info\_File: /opt/mysql-data/master.info > SQL\_Delay: 0 > SQL\_Remaining\_Delay: > Slave\_SQL\_Running\_State: Waiting for dependent transaction to commit > Master\_Retry\_Count: 86400 > Master\_Bind: > Last\_IO\_Error\_Timestamp: > Last\_SQL\_Error\_Timestamp: ---
**Author:** ![spirit1984](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/spirit1984/32/1233_2.png) [@spirit1984](https://forums.percona.com/u/spirit1984)\ **Post date:** [July 12, 2018, 4:23am UTC](https://forums.percona.com/t/replication-lag-on-mysql-server-after-increasing-the-number-of-inserts-on-the-master/6457/2 "2018-07-12T04:23:06Z")
I have a MySQL 5.7 innodb replica with row based replication. I’ve noticed something curious. The master has about 3000 inserts per second, and the replica seems to catch up fine with that. But if I run a long-time query (let’s say for a minute or so) scanning the big table with 300 million rows, the replica starts to lag. I’ve checked the slave status, the IO thread seems to be reading from master just fine, but the slave sql thread seems not to be doing so well. Not sure why is that. Now zabbix shows me, that my select query seems to be causing high disk utilization (since it needs parts of the table that are on disk), I guess that could slow down the reading of relay log file or applying the transactions. Not sure what I have to tune here in order to get rid of this - how come a single select query causes such a heavy replication log (the number of inserts on the replica falls down to 1500 inserts per second instead of 3000). ---
**Author:** ![spirit1984](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/spirit1984/32/1233_2.png) [@spirit1984](https://forums.percona.com/u/spirit1984)\ **Post date:** [July 17, 2018, 6:26am UTC](https://forums.percona.com/t/replication-lag-on-mysql-server-after-increasing-the-number-of-inserts-on-the-master/6457/3 "2018-07-17T06:26:01Z")
I finally found the solution myself. First, I increased the number of slave\_parallel\_workers to 4, but that gave me nothing (even with LOGICAL\_CLOCK), because the transactions were highly dependent. However, it turned out, that after I increased on master binlog\_group\_commit\_sync\_delay to 10000 (that is, 10 milliseconds), the lag disappeared. This setting is very important, since it is the setting that actually allows the replication servers to execute something in parallel. ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [July 17, 2018, 6:54am UTC](https://forums.percona.com/t/replication-lag-on-mysql-server-after-increasing-the-number-of-inserts-on-the-master/6457/4 "2018-07-17T06:54:38Z")
Hi there, thank your for posting your solution it may well help someone else. Sorry we didn’t get to you more quickly but really pleased that you found an answer. I’ll highlight your solution to the team here too. 🙂 ---
**Author:** ![spirit1984](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/spirit1984/32/1233_2.png) [@spirit1984](https://forums.percona.com/u/spirit1984)\ **Post date:** [July 17, 2018, 8:02am UTC](https://forums.percona.com/t/replication-lag-on-mysql-server-after-increasing-the-number-of-inserts-on-the-master/6457/5 "2018-07-17T08:02:31Z")
> [@lorraine.pocklington;51874](#): > > Hi there, thank your for posting your solution it may well help someone else. Sorry we didn’t get to you more quickly but really pleased that you found an answer. I’ll highlight your solution to the team here too. 🙂 I actually suggest two things. One - someone from the mysql community should really fix the documentation for [slave\_parallel\_workers](https://dev.mysql.com/doc/refman/8.0/en/replication-options-slave.html#sysvar_slave_parallel_workers) giving at least some hint for binlog\_group\_commit\_sync\_delay. Second - I would like to write something in the blog about our experience, since it was a really curious one. ---
**Author:** ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)\ **Post date:** [July 17, 2018, 8:22am UTC](https://forums.percona.com/t/replication-lag-on-mysql-server-after-increasing-the-number-of-inserts-on-the-master/6457/6 "2018-07-17T08:22:23Z")
Well I would love that, it could be very suitable for our new Community Blog [URL][https://www.percona.com/community-blog/[/URL]](https://www.percona.com/community-blog/%5B/URL%5D) Could you email me? [lorraine.pocklington@percona.com](mailto:lorraine.pocklington@percona.com) reaches me. Thank you!!