2 MySQL version 5.6.26 Dedicated MySQL Servers connected on a LAN.
Servers Have 16 Cores with 32 GB RAM each and are really fast.
Master-Master Crash Safe Replication is setup on both servers and is working fine. Although both are masters but application ensures that data is written only to 1 server unless it goes down and then a switch is to be made to the secondary master.
All the safety checks are in place to avoid auto increment conflicts.
UPDATE queries are extremely slow, as slow as a 20 year old server. If you insert 1 Million rows in a table that takes less time than if you UPDATE 2000 of them using a deterministic query.
Where to look in order to find out what is causing this and what steps can be taken to improve this situation?
I noticed that we did not exclusively setup 1 database for replication, nor did we ignore any so it replications even the
mysql schema. Since --relay-log-info-repository is set to
TABLE can that be it? I mean wouldn't that mean even the relay log repository table is being replicated so that makes a lot of queries for 1 update?
I know that
mysql schema is being replicated because every user on primary master was replicated on secondary master as well.