Not the answer you need?
Register and ask your own question!

Mysql 5.7 slave performance issues

JwtjeJwtje Current User Role Beginner

We recently reinstalled 3 of our mysql servers running in master slave with percona 5.7 and ubuntu 18.04 instead of oracle linux 7. These servers are currently connected to a mysql 5.6 master. And since the reinstallation we are getting way more slave lag than when we were running mysql 5.6 on the slaves (it was almost non existent).

The 5.7 servers have the same hardware just some more disks. And instead of one span of 12 disks the reinstalled servers are using 2 spans of 10disks. Both are running on an lvm.

All servers 5.6 and 5.7 are running the noop scheduler and we tested the server raid performance before installing using fio which was all fine (200k random iops). We tried to fine tune the 5.7 settings to mimic the 5.6 master as much as possible.

set global optimizer_switch='derived_merge=off,duplicateweedout=off,condition_fanout_filter=off';

We are using sync_binlog = 1 we checked iostats and are seeing the same iops usage on the slaves as we see on the master. The cpus are not loaded at all. What we do see is is system locks on our replication threads. Maybe this has something to do with the slow performance?

We run a fairly big database 4TB on dual cpu xeon's 24cores with ht total with 512GB ram. It looks like the slaves are lagging on the sql_thread and not the io thread. Do you guys have any idea why the performance of 5.7 is worse then 5.6?

Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role

    Hi @Jwtje

    Those optimizer switches don't have anything to do with replication so changing them won't affect replication. Make sure your source is in ROW-based replication. You can also enable parallel replication on the replica by doing 'STOP SLAVE; SET GLOBAL slave_parallel_threads=4; START SLAVE' to enable multi-threaded replication.

    sync_binlog=1 will certainly slow things down.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.