1

Environment

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.

Issue

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.

Question

Where to look in order to find out what is causing this and what steps can be taken to improve this situation?

Sup Question 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.

  • Not sure if relevant but do the tables have a primary key? – jkavalik Aug 16 '15 at 8:32
  • Yep they sure do. And updates without replication are as fast as they should be – Hanky Panky Aug 16 '15 at 9:04
  • Let's see the UPDATE and SHOW CREATE TABLE. – Rick James Aug 17 '15 at 2:29
  • What is binlog_format? – Rick James Aug 17 '15 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.