0

I am using MariaDB 10.4 and have question about Out-of-Order parallel replication.

I am optimizing databases(about 400GB of data) on Master every week. As optimizing DB on master do not have any negative impact, then replicated OPTIMIZE commands on slaves results in huge replication lags (up to half an hour). Can i use Out-of-Order parallel replication (https://mariadb.com/kb/en/parallel-replication/#out-of-order-parallel-replication) and set different gtid_domain_id for the OPTIMIZE commands on master to fix this ?

Please note that tables on master are being updated all the time. I am unsure if this is safe approach or not ?

Maybe it will be safer to NOT replicate OPTIMIZE commands on master and run them independly on all slaves ? Will this affect replication / lag ?

Improve this question

1 Answer 1

Reset to default
0

Anti-OPTIMIZE

If you are using InnoDB (which you should be using), then OPTIMIZE TABLE is almost totally useless. Stop running that.

If you are DELETEing huge chunks of a table, and that led you to want to OPTIMIZE, then let's discuss alternatives.

Replication issues

It is possible to avoid replicating any command(s):

 SET SESSION binlog = OFF;   -- I forget the details
 OPTIMIZE TABLE ...;
 SET ...   -- restore replication

But it seems like you would want to OPTIMIZE on the Slaves, where you are doing SELECTs?

If you have a graceful way to take a Slave out of rotation, you could do a "rolling" OPTIMIZE across the slaves, one at a time.

ANALYZE

If the goal is to refresh the "statistics", then ANALYZE TABLE is much faster, hence less intrusive.

PARTITION

If "old" data is being purged, then Partitioning is much faster than DELETE, and has no need for OPTIMIZE; should we discuss that?

Improve this answer
2
  • Yes, all tables are InnoDB. Many tables have thousands of rows removed / added each hour. Sometimes it grows by few milions rows per day, and sometimes it shrinks by few million rows. Also OPTIMIZE TABLE for innodb is doing recreate + ANALYZE which sometimes also help optimizer to take better decisions. Many of these tables have 450M+ rows and dozens of queries per second and it makes a difference.
    – coredo
    Commented Jun 17, 2020 at 13:12
  • Thank you for your reply. I will do optimize tables on master with disabled binlog and independently optimize tables on slaves on different days as slaves are behind proxysql which automatically drop ones with replication lag so this will solve the problem.
    – coredo
    Commented Jun 18, 2020 at 11:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.