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 ?
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
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?
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.
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.
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.
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)
Our add new parameter on my.cnf
Lots of problems here:
This is only for MyISAM tables. If you have no MyISAM tables, remove this parameter and all other Key_* params.
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.
Remove these as these place limits on parallel replication
This should be 0 unless you are facing CPU/context issues.
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.
Will try your recommendation @matthewb … updated you soon 12 hours again
Parallel workers on slave still didn’t working with ur recommendation @matthewb
Therefore data lagging still happen
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.
Hi @matthewb … update : at last parallel workers running after few parameter changing
Thanks so much for the help
Hany Haryadi Widodo
What parameters did you change, so that others can benefit from this post?