EmergencyEMERGENCY? Get 24/7 Help Now!

Faster restarts for MySQL and Percona Server 5.6.21+

 | December 1, 2014 |  Posted In: Insight for DBAs, MySQL, Percona Server for MySQL

PREVIOUS POST
NEXT POST

Faster restarts for MySQL and Percona Server 5.6.21+By default in MySQL 5.6, each time MySQL is started (regular start or crash recovery), it iterates through all the binlog files when GTIDs are not enabled. This can take a very long time if you have a large number of binary log files. MySQL and Percona Server 5.6.21+ have a fix with the simplified-binlog-gtid-recovery option. Let’s explore this issue.

Understanding the issue

It was first reported by Yoshinori @ Facebook (bug #69097).

Let’s start by looking at a MySQL 5.6 instance where binary logging is enabled but GTIDs are disabled.

If we restart MySQL with strace, we’ll see:

MySQL opens all binary log files in reverse order. This can bite if you have lots of binlog files or if the binlog files are large.

This does not happen with MySQL 5.5, so why such a change? The reason is … GTIDs. If you look at the bug report, MySQL tries to initialize a few GTID-related settings even if gtid_mode = OFF

The same kind of issue happens when you have binlog files with GTIDs and binlog files without GTIDs:

Actually you can see that MySQL will do 2 scans: a reverse scan and a forward scan. Here not all binlogs need to be opened: MySQL will stop scanning files as soon as it finds GTID information. But that can again bite if you have just turned on GTIDs (and therefore most binlog files do not contain any GTID information).

Now what happens if you set gtid_mode = ON from the start or if all the binlog files without any GTID information have been removed?

Only the newest and the oldest binlog files are opened, which is expected.

The fix

The fix is easy: simply add simplified-binlog-gtid-recovery = 1 in your configuration file. When this is set, MySQL will open at most 2 binlog files: the newest one and the oldest one. See the documentation.

Let’s see what happens with our server containing some binlog files without GTID information:

What is the performance overhead of the binlog scans? Of course YMMV, but I did a quick test on my laptop by creating 80x 100MB binlog files: by default, startup takes 6s while with simplified-binlog-gtid-recovery=1 it only takes 2s. 3x improvement with a single setting, not bad!

Conclusion

It is good to see that regressions introduced in MySQL 5.6 are being fixed over time. This one is pretty nasty as most people not using GTIDs will never think that something related to GTIDs can actually create performance issues. Is there any drawback if you enable this setting? I can’t think of any, so I’m hoping it will be enabled by default in 5.7.

PREVIOUS POST
NEXT POST
Stephane Combaudon

Stéphane joined Percona in July 2012, after working as a MySQL DBA for leading French companies such as Dailymotion and France Telecom.

In real life, he lives in Paris with his wife and their twin daughters. When not in front of a computer or not spending time with his family, he likes playing chess and hiking.

5 Comments

  • Stephane,

    Just doing a number check here – 80 * 100MB files on your laptop means 8GB of space, which in 6 seconds should mean 1.3GB/sec or so which is only possible in 2 cases
    – binary log files are not really scanned completely.
    – binary log files have been cached in OS scan

    Do you know which one is it ?

  • Does this also apply to Percona Cluster? What are your thoughts or where is a good resource for reading about bin logging in a cluster setup? Thanks!

  • Peter,

    I checked again what strace says and actually only the first 8KB of each binlog file are read. I did another test with with 400x 1MB binlog files and this time startup took 17s. So the overhead is related to the number of binlog files, not their size.

    Nick,

    This also applies to PXC 5.6. While binary logging is not technically necessary with Galera, it’s recommended to enable it to be able to recover data after an accidental DROP TABLE/TRUNCATE TABLE and also if you need to set up asynchrous slaves. See https://www.percona.com/blog/2014/11/17/typical-misconceptions-on-galera-for-mysql/

Leave a Reply