MySQL vs. MariaDB Binary Logging Difference

Question

I've been doing some testing with both MySQL and MariaDB and I've noticed a big difference with regards to the growth of binary logs - I can't find out much about this online.

I have a test database, and I have written a script which simulates transactional activity (selects, updates, inserts, deletes etc) with the aim of doing some simple load testing and later, for testing point in time recovery procedures.

What I've noticed is that on MySQL running my automated script for half an hour causes about 400MB of binary logs.

When I do the same thing on MariaDB, I only get around 2MB of binary logs.

All settings are the same, ie.

Max Binlog Size: 100MB Buffer Pool: 2GB max

MySQL is running v 5.7 and Mariadb is running 10.3 - both are running in Docker on my local laptop.

I've also noticed that MariaDB is a little bit slicker performance wise, I get more query throughput in half an hour than I do with MySQL.

Obviously the big difference here is with binary log size. The smaller size of MariaDB means I can roll forward on the binary log much quicker for point in time recovery than I can on MySQL.

Is there any documented evidence out there to support this behaviour? Is MariaDB more efficient than MySQL when looking at binary logs and recovery?

Are there some hidden/non-obvious variables I should be checking that may be having a big impact on this testing?

Any help understanding this would be great.

Answer 1

There are many differences in default values for system variables between MariaDB 10.3 and MySQL 5.7, so that can explain both some of the performance and binlog size differences. Additionally, MariaDB 10.3 is of course newer code, being the most recent major MariaDB release at the moment, whereas MySQL 5.7 is not the most recent MySQL major version since the release of 8.0.

For example, the default value for binlog_format is different between MySQL 5.7: ROW and MariaDB 10.3: MIXED. MIXED means statement-based in most cases, and statement-based binlog format has the advantage of less data written to log files:

When updates or deletes affect many rows, this results in much less storage space required for log files. This also means that taking and restoring from backups can be accomplished more quickly.

MariaDB also has log_bin_compress, see Compressing Events to Reduce Size of the Binary Log, though this is disabled by default.

• Molenpad: That's an interesting point you made about binary log format, as it's a difference that I missed. MySQL was running with row format, and mariadb as mixed. My understanding is that row is generally seen as 'safer' for replication, but can be slow. Mixed is mainly transaction based so is in theory faster than row to restore, and with less storage overhead?
• dbdemon: @Molenpad You're right, I had misread the documentation, the default for MySQL 5.7.7+ is ROW, not STATEMENT. I've updated the answer, and I now think this alone can explain a lot of the difference in file size that you're seeing.