Better Replication when running both InnoDB and MyRocks (or other Storage-Engines)

Kristian Nielsen is working on a new feature for MariaDB 10.3 and he published very interesting results.  This feature is MDEV-12179: Per-engine mysql.gtid_slave_pos tables.  He writes about replicating twice as fast in the worst case when using two storage engines (InnoDB and MariaRocks in his tests, but could also be InnoDB and TokuDB or TokuDB and MyRocks).  I will let you read all the details on his blog about Improving replication with multiple storage engines.

Why am I posting this here ?  Mostly because I want to share with you that:

• I am also involved in this project,
• I am working closely with Kristian on this feature,
• and that Booking.com is financing Kristian's time on this development.

If you are also interested in this, feel free to comment in the JIRA MDEV, to leave a comment below, or on Kristian's post.

A discussion about sync-master-info and other replication parameters

Some time ago, feedback was requested on new replication default after MySQL 5.7.  Some of the suggested default are:

• relay-log-info-repository = TABLE (previous default FILE)
• relay-log-recovery = ON (previous default OFF)
• master-info-repository = TABLE (previous default FILE)
• sync-master-info = 1,000 (previous default 10,000)
• sync-relay-log = 1,000 (previous default 10,000)

I agree on the suggestions for relay-log-info-repository and relay-log-recovery: they are needed for crash safe replication, and having crash safe replication enabled by default is a good thing.  I have doubts about master-info-repository: I do not see what benefits are introduced by this change.  I have much bigger doubts about sync-master-info and sync-relay-log: those changes bring an illusion of safety, which is bad.  Let's dive in more details about those parameters.

Replication crash safety with MTS in MySQL 5.6 and 5.7: reality or illusion?

Reminder: MTS = Multi-Threaded Slave.

Update 2017-04-17: since the publication of this post, many things happened:

• the procedure for fixing a crashed slave has been automated (Bug#77496)
• Bug#80103 as been closed at the same time as Bug#77496
• but I still think there are unfixed things, see Bug#81840

Update 2018-08-20: it was brought to my attention that MySQL 5.6 with GTID enabled and with sync_binlog != 1 is not replication crash safe, even with single threaded replication.  This is reported in Bug#70659 - Make crash safe slave work with gtid + less durable settings.  Thanks Valeriy for mentioning, in your last blog post, the bug from Sveta Smirnova (Bug#90997) which pointed me to Yoshinori Matsunobu's bug.  To my knowledge, MySQL 5.7 and 8.0 are also affected by this bug, even if this should not be the case.  I will soon open a new bug report about this and will put the bug number below.

End of updates.

I will be talking about parallel replication at FOSDEM in Brussel on January 30th and at Percona Live Santa Clara in April (link to the talk descriptions here and here).  Come to one (or both) of those talks to learn more about this subject.

Do not run those commands with MariaDB GTIDs - part # 2

Update 2016-01-30: restarting the IO_THREAD might be considered useful in some situations (avoiding MDEV-9138).  Look for "in contrast, if the IO thread was also stopped first" in MDEV-6589 for more information.

In a previous post, I listed some sequences of commands that you should not run on a MariaDB slave that is lagging and which is using the GTID protocol.  Those are the following (do not run them, it's a trap):

• "STOP SLAVE; START SLAVE UNTIL ...;",
• or "STOP SLAVE; START SLAVE;" (to remove an UNTIL condition as an example),
• or "STOP SLAVE; SET GLOBAL slave_parallel_threads=...; START SLAVE;",
• and maybe others.

MySQL Crash-safe replication, Binlog Servers and Percona Live London

I just publish a post on the Booking.com blog: http://blog.booking.com/better_crash_safe_replication_for_mysql.html  Spoiler: it uses Binlog Servers.

This is also the opportunity to tell you that I will be at Percona Live London at the beginning of November, and that I will give a talk about Binlog Servers: High Availability, Disaster Recovery and Extreme Read Scaling using Binlog Servers.  I will not talk too much about Binlog Server for crash-safe replication, but I will present a new use-case for Binlog Servers that I did not blog about yet.  I am looking forward to meet you there.