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:
# strace -e open service mysql start [...] open("/var/lib/mysql5621/mysql-bin.000004", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000003", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000002", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13 [...]
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:
# strace -e open service mysql start [...] open("/var/lib/mysql5621/mysql-bin.000010", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000002", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000003", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000004", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000005", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000006", O_RDONLY) = 13 -> First binlog file with 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?
# strace -e open service mysql start [..] open("/var/lib/mysql5621/mysql-bin.000011", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000006", O_RDONLY) = 13 [...]
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:
# strace -e open service mysql start [...] open("/var/lib/mysql5621/mysql-bin.000012", O_RDONLY) = 13 open("/var/lib/mysql5621/mysql-bin.000001", O_RDONLY) = 13 [...]
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.
The post Faster restarts for MySQL and Percona Server 5.6.21+ appeared first on MySQL Performance Blog.