binary logs make MySQL 5.7 slower

Researching a performance issue, we came to a startling discovery:

MySQL 5.7 + binlogs is by default 37-45% slower than MySQL 5.6 + binlogs when otherwise using the default MySQL settings.

Test server MySQL versions used:
i7, 8 threads, SSD, Centos 7.2.1511
mysql-5.6.30-linux-glibc2.5-x86_64
mysql-5.7.12-linux-glibc2.5-x86_64

mysqld –options:
--no-defaults --log-bin=mysql-bin --server-id=2

Run details:
Sysbench version 0.5, 4 threads, socket file connection

Sysbench Prepare: 

Sysbench Run:

Results:

5.6.30: transactions: 7483 (149.60 per sec.)
5.7.12: transactions: 4689 (93.71 per sec.)  — That is a 37.36% decrease!

Note: on high-end systems with premium IO (think Fusion-IO, memory-only, high-end SSD with good caching throughput), the difference would be much smaller or negligible.

The reason?

A helpful comment from Shane Bester on a related bug report made me realize what was happening. Note the following in the MySQL Manual:

“Prior to MySQL 5.7.7, the default value of sync_binlog was 0, which configures no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log’s contents from time to time as for any other file. MySQL 5.7.7 and later use a default value of 1, which is the safest choice, but as noted above can impact performance.” — https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_sync_binlog

The culprit is thus the --sync_binlog=1 change which was made in 5.7.7 (in 5.6 it is 0 by default). While this may indeed be “the safest choice,” one has to wonder why Oracle chose to implement this default change in 5.7.7. After all, there are many other options which aid crash safety.

A related blog post  from the MySQL HA team states;

“Indeed, [with sync_binlog=1,] it increases the total number of fsyncs called, but since MySQL 5.6, the server groups transactions and fsync’s them together, which minimizes greatly a potential performance hit.” — http://mysqlhighavailability.com/replication-defaults-in-mysql-5-7-7/ (ref item #4)

This seems incorrect given our findings, unless perhaps it requires tuning some other option.

This raises some actions points/questions for Oracle’s team: why change this now? Was 5.6 never crash-safe in terms of binary logging? How about other options that aid crash safety? Is anything [before 5.7.7] really ACID compliant by default?

In 2009 my colleague Peter Zaitsev had already posted on performance matters in connection with sync_binlog issues. More than seven years later, the questions asked in his post may still be valid today;

“May be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help? Or may be binlog pre-allocation would be good solution.” — PZ

Testing the same setup again, but this time with sync_binlog=0  and sync_binlog=1  synchronized/setup on both servers, we see;

Results for sync_binlog=0:

5.6.30: transactions: 7472 (149.38 per sec.)
5.7.12: transactions: 6594 (131.86 per sec.)  — A 11.73% decrease

Results for sync_binlog=1:

5.6.30: transactions: 3854 (77.03 per sec.)
5.7.12: transactions: 4597 (91.89 per sec.)  — A 19.29% increase

Note: the increase here is to some extent negated by the fact that enabling sync_binlog is overall still causes a significant (30% on 5.7 and 48% on 5.6) performance drop. Also interesting is that this could be the effect of “tuning the defaults” of/in 5.7, and it also makes one think about the possibility o further defaults tuning/optimization in this area.

Results for sync_binlog=100:

5.6.30: transactions: 7564 (151.12 per sec.)
5.7.12: transactions: 6515 (130.22 per sec.) — A 13.83% decrease

Thus, while 5.7.12 made some improvements when it comes to --sync_binlog=1, when --sync_binlog is turned off or is set to 100, we still see a ~11% decrease in performance. This is the same when not using binary logging at all, as a test with only --no-defaults  (i.e. 100% vanilla out-of-the-box MySQL 5.6.30 versus MySQL 5.7.12) shows;

Results without binlogs enabled:

5.6.30: transactions: 7891 (157.77 per sec.)
5.7.12: transactions: 6963 (139.22 per sec.)  — A 11.76% decrease

This raises another question for Oracle’s team: with four threads, there is a ~11% decrease in performance for 5.7.12 versus 5.6.30 (both vanilla)?

Discussing this internally, we were interested to see whether the arbitrary low number of four threads skewed the results and perhaps only showed a less realistic use case. However, testing with more threads, the numbers became worse still:

Results with 100 threads:

5.6.30. transactions: 20216 (398.89 per sec.)
5.7.12. transactions: 11097 (218.43 per sec.) — A 45.24% decrease

Results with 150 threads:

5.6.30. transactions: 11852 (233.01 per sec.)
5.7.12. transactions: 6606 (129.80 per sec.) — A 44.29% decrease

The findings in this article were compiled from a group effort.

12 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Singer Wang

I’d argue that sync_binlogs isn’t needed for the technical definition of ACID.

0
Scott Klasing

Seems a bit misleading to suggest 5.7.x is slower than 5.6.x since comparing sync_binlog=0 is not an apple to apple comparison of sync_binlog=1. Granted when both versions use sync_binlog configuration defaults it is slower, but the point is even with 5.6 by itself, 5.6.x is slower when sync_binlog=1 because it waits for the OS to push to disk. More importantly the discussion should highlight that yes systems are at risk when sync_binlog=0 and the database crashes from an OOM. All data in flight not persisted to disk will be lost, and often corrupts the database to such extent it is not repairable. Therefore a system should always default to safe values and let the administrator choose when to be at risk for performance reasons. This reminds me of relational databases back in the 1980’s where IBM’s Database 2 (DB2) defaulted to some of the worst locking configurations(isolation level and timeout) and it required a knowledgeable consultant to know the default values were bad.

0
Mark Callaghan

I think the title for this blog post is misleading. 5.7 tests ran with sync_binlog=1, 5.6 ran with sync_binlog=0. You ran a test with different configurations. I do that all the time. I prefer that the default not get changed in the middle of the 5.7 lifecycle, but that needs a different title.

0
Roel Van de Paar

@Scott & @Mark – In regards the (now removed) title “Binary logs make MySQL 5.7 slower than 5.6” – yes! It looks like our blog team edited the original. I have restored my original title (“MySQL 5.7 By Default 1/3rd Slower Than 5.6 When Using Binary Logs”) and introduction! Thank you for the heads up!

0
brandentimm

It would be interesting to see the results with Performance Schema turned off. A quick investigation shows a 15% increase in the number of instruments enabled by default between 5.6 and 5.7.

0
Dave Holmes

@Roel great analysis and your blog post and I think the title of the article was spot on, we were in an emergency situation and had to clone a MySQL slave server and had no choice but to throw an 5.7 box into production.

We saw the IO usage rise significantly but IO throughput remain low totally unexpected and not something we had seen with other 5.7 production servers.

As ever your information rocks guys! hope to make Amsterdam this year! #perconalive

0
Stephen N

Thanks for this great post. We just upgraded from 5.6 to 5.7 and have been scratching our heads trying to figure out why its slower.

0
Mannoj

But why you leave default settings for file sync variables. Would this be better if such important variables are hardcoded and not picked as default.

0
Roel Van de Paar

Hi Mannoj. Not sure I am following your reply. Please note that this is in the (what is upstream for us) MySQL server. It also makes sense to be able to configure these variables on/off for different configurations. Let me know if I missed anything.

0
Davoice

Thank you for writing this article, we didn’t know what was making the writes so slow in our system! In our case it was not 1/3x slower, but 1300x slower!!!

0
Rick Pizzi

guys please mind the consequences of setting this to zero

-1
Meral Sönmezer

great post, thanks for this article.

0