1

We've set up a database replication about a week ago, and we are having an issue with keeping it in sync.

The setup is a master-master replication with MariaDB 10.1.35/MySQL 5.5.5. Only one database is being used to make calls on. The other database will only be used as a backup. I will refer to this one as the slave. And it's the slave we're having issues with. The replication is statement based.

The first 24 hours went fine. The next day, the slave was more and more behind, up until almost 24 hours. When we checked 24 hours later, the slave was back on track again, being behind on the master by just a few seconds.

Now again, it's starting to get behind more and more (over 5 hours of data now).

It's still syncing, so the replication itself is working. However, some queries just take way too long on the slave, which is delaying everything.

All queries are being executed quite fast, except for one UPDATE query. It's this one which stays in the processlist for 5, 10 and sometimes even 20 or 30 seconds. The query is being handled in less than a second on the master, and also when we execute this query manually on the slave, it's doesn't take longer than a second. So, we don't think it's related to the query itself. The structure of both databases/tables are exactly the same. The storage engine of the table is InnoDB.

At this point, we have no clue what could be causing this delay. Inserts are being processed instantly.

There's one difference in the processlist when the query is being executed on the slave; the command stays on 'Connect', while the command says 'Execute' on the master. Is this normal behaviour?

If I should provide more information, please let me know. It's clear that a slave only handles one query at a time and thus it can get behind if there are a lot of queries on the master, but it should not be necassary for that query to take up to 30 seconds, while it takes less than one when being executed manually.

Thank you.

P.S. We already optimized the table (OPTIMIZE) but unfortunately that didn't make a difference.

Raaamy
  • 91
  • 8
  • a master/master replica is used when both server can be the source of the changes. Since you have a master and a slave, why don't you set up the replica as master/slave? – Lelio Faieta Sep 05 '18 at 08:58
  • Because when something happens to our main server, we want to be able to switch automatically to this server as a fallback. Once this has been set up successfully, we'll do certain operations on the 'slave' instead of the current 'master'. – Raaamy Sep 05 '18 at 13:33
  • You can do it with master slave. Reason for master master is only if new data will flow in from both the server. But since you input only from one you don’t need it – Lelio Faieta Sep 05 '18 at 13:35
  • I see what you mean. However, we want to be able to switch to the other database at any moment, without having to do a 'master-slave-switch'. Also, when things will get too busy in the future for only this database, we will also query the replica database directly. – Raaamy Sep 05 '18 at 13:57
  • Please provide `SHOW CREATE TABLE` for the table(s) involved, plus the `UPDATE`. Is the 'Slave' being read from? Are there any long `SELECTs` at the same time as the slow update? – Rick James Oct 01 '18 at 22:41

0 Answers0