# MySQL replication slave parallel workers only work 1 worker **URL:** \ **Category:** MySQL & MariaDB\ **Created:** [October 11, 2021, 3:46pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521 "2021-10-11T15:46:21Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [October 11, 2021, 3:46pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/1 "2021-10-11T15:46:21Z")
I have mysql replication using gtid on and set slave\_parallel\_workers to 4 because very slow apply lagging on slave servers. # Topology : 1 master server with 3 slaves server Mysql percona version : 8.0.18 Test replication performance using insert 10 rows of 1.2MB and nohup in shell script looping 100x concurrently. When check on show processlist, 3 workers almost always having event : waiting for coordinator. Is there any config at /etc/my.cnf i need to add ? ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [October 11, 2021, 3:56pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/2 "2021-10-11T15:56:07Z")
In our scenario, replication lag with 100 concurrent sessions and each insert 10 rows of 1.2MB, delay lag could be ~ 500-700 seconds on second\_behind\_master (show slave status\G), which is too long / very slow replication performance. Innodb\_buffer\_pool\_size set to 3GB on each 7GB memory on each of 4 servers. slave\_parallel\_type = LOGICAL\_CLOCK; slave\_parallel\_workers = 4; slave\_preserve\_commit\_order = OFF; Really frustated since try on mariadb, the slave parallel workers is working. Why on percona mysql NOT WORKING 😭 ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [October 11, 2021, 4:58pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/3 "2021-10-11T16:58:27Z")
Hello @Hany_Widodo, What are your other InnoDB parameters? What is your flush\_log\_at\_trx\_commit? What is your log\_buffer\_size? All of these parameters, and the server hardware itself was the same when you tested on MariaDB? What does the processlist show on the master? ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [October 12, 2021, 6:05am UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/4 "2021-10-12T06:05:26Z")
Thanks @matthewb for the reply. Master : flush\_log\_at\_trx\_commit = 1 Slave : flush\_log\_at\_trx\_commit = 2 All servers has innodb\_log\_file\_size=150M On Mariadb, I haven’t tested yet, since the Mariadb environments not on my side. I just have percona environments. My colleague told me on Mariadb the slave workers running well on parallel. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [October 12, 2021, 1:53pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/5 "2021-10-12T13:53:11Z")
@Hany_Widodo, Your innodb\_log\_file\_size is small. You should set this to 1G and restart MySQL. This small log file could be bottlenecking writes to disk. ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [October 13, 2021, 5:39pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/6 "2021-10-13T17:39:09Z")
Hi @matthewb Thanks so so so much … will try to test increase it tommorow, test in UAT environment with stresstest. After delay lag issue solved, then deploy to production env. ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [October 14, 2021, 2:17pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/7 "2021-10-14T14:17:21Z")
Hi @matthewb We have increase innodb\_log\_file\_size to 1G from 150M. But the workers only working 1 worker. If there’s no solution, we think temporary to using rsync. Or the last option, migrate the database to mysql enterprise (mysql oracle) ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [October 14, 2021, 2:42pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/8 "2021-10-14T14:42:15Z")
Our add new parameter on my.cnf Key\_buffer\_size=2G Key\_cache\_age\_threshold=7200 Key\_cache\_block\_size=16384 Key\_cache\_division\_limit=50 Slave\_allow\_batching=ON Log\_slave\_updates=0 Slave\_parallel\_type=LOGICAL\_CLOCK slave\_parallel\_workers=4 Slave\_transaction\_retries=80 Slave\_pending\_job\_size\_max=2G max\_allowed\_packet=1G Mysqlx\_max\_allowed\_packet=1G Thread\_handling=pool-of-threads Thread\_pool\_size=4 Mysqlx\_min\_worker\_threads=10 Slave\_checkpoint\_period=60 Slave\_checkpoint\_group=2048 wait\_timeout=60 Innodb\_flush\_log\_at\_trx\_commit=2 Innodb\_thread\_concurrency=8 Innodb\_buffer\_pool\_size=4G innodb\_log\_file\_size=1G innodb\_io\_capacity=30000 Innodb\_write\_io\_threads=8 Innodb\_log\_buffer\_size=1G ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [October 14, 2021, 2:50pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/9 "2021-10-14T14:50:22Z")
Lots of problems here: > Key\_buffer\_size=2G This is only for MyISAM tables. If you have no MyISAM tables, remove this parameter and all other Key\_\* params. > Thread\_handling=pool-of-threads Unless you have 20,000 connections on a regular basis, remove this and Thread\_pool\_size. Thread pooling is only beneficial when you have massive amount of connections. > Slave\_checkpoint\_\* Remove these as these place limits on parallel replication > Innodb\_thread\_concurrency This should be 0 unless you are facing CPU/context issues. > innodb\_io\_capacity=30000 This parameter is wrong. There are many blog posts on why increasing this value can have BAD performance. Please change this to 800. Make the above changes and restart MySQL. > Or the last option, migrate the database to mysql enterprise (mysql oracle) Because Percona is a rebase of MySQL-Oracle, you will find the same behavior. There is no reason to switch to Enterprise, unless you like paying more $$$ for MySQL. ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [October 14, 2021, 3:52pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/10 "2021-10-14T15:52:26Z")
Will try your recommendation @matthewb … updated you soon 12 hours again ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [November 18, 2021, 8:36am UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/11 "2021-11-18T08:36:00Z")
Parallel workers on slave still didn’t working with ur recommendation @matthewb ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [November 18, 2021, 8:40am UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/12 "2021-11-18T08:40:20Z")
Therefore data lagging still happen ☹ ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 18, 2021, 1:13pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/13 "2021-11-18T13:13:15Z")
Hello @Hany_Widodo Unfortunately, without access to your system I cannot further diagnose what the issue might be. LOGICAL\_CLOCK replication creates a dependency tree based on transactions. If txn B is dependent on tnx A, then B and A cannot be processed in parallel and must be serialized. You can do more diagnosis on this by using mysqlbinlog tool on your binary logs. The sequence\_no field in the binlogs is this dependency tree. If every binlog event is a new number, then your transactions are forcing serialization. ---
**Author:** ![Hany\_Widodo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/hany_widodo/32/4715_2.png) [@Hany\_Widodo](https://forums.percona.com/u/Hany_Widodo)\ **Post date:** [November 19, 2021, 4:53am UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/14 "2021-11-19T04:53:40Z")
Hi @matthewb … update : at last parallel workers running after few parameter changing Thanks so much for the help 🙏🏻 Regards, Hany Haryadi Widodo ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [November 19, 2021, 5:30pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/15 "2021-11-19T17:30:21Z")
Hello @Hany_Widodo, What parameters did you change, so that others can benefit from this post? ---
**Author:** ![vdamyanov](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vdamyanov](https://forums.percona.com/u/vdamyanov)\ **Post date:** [March 12, 2024, 6:49pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/16 "2024-03-12T18:49:32Z")
at @Hany_Widodo . I’m facing similar problem , what parameters were changed in your case ? ---
**Author:** ![abdullahzahid](https://avatars.discourse-cdn.com/v4/letter/a/ce73a5/32.png) [@abdullahzahid](https://forums.percona.com/u/abdullahzahid)\ **Post date:** [June 21, 2024, 7:30pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/17 "2024-06-21T19:30:18Z")
Hi @matthewb, I am trying to talk to you from last 10 days. I searched you everywhere. I need your help regarding mysql parallel worker settings on my slave servers. Is there any way through which I can talk to you directly. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 21, 2024, 8:40pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/18 "2024-06-21T20:40:10Z")
@abdullahzahid These forums are a volunteer effort by Percona employees. If you need immediate 1 on 1 assistance, please [contact us](https://www.percona.com/about/contact) for a support contract. Otherwise, you can post your question here on the forums and someone may be able to help you during their free time. ---
**Author:** ![abdullahzahid](https://avatars.discourse-cdn.com/v4/letter/a/ce73a5/32.png) [@abdullahzahid](https://forums.percona.com/u/abdullahzahid)\ **Post date:** [June 22, 2024, 9:54am UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/19 "2024-06-22T09:54:53Z")
@matthewb We are using mysql 5.7.39 and we want to enable parallel workers for replication. We enable 8 worker and as per our analysis only 2 workers are getting all the load and performing slaves responsibility while all other workers are not working and almost showing 2,3 entries. Need your suggestion on this. From where we can start this configuration. Please guide us step by step if possible. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [June 22, 2024, 12:20pm UTC](https://forums.percona.com/t/mysql-replication-slave-parallel-workers-only-work-1-worker/12521/20 "2024-06-22T12:20:37Z")
@abdullahzahid Please share your my.cnf and your analysis that shows only 2 workers getting the load.