How Binary Logs Affect MySQL 8.0 Performance
How Binary Logs Affect MySQL 8.0 Performance
Learn how the recently released MySQL 8.0 performance is affected in similar scenarios, especially as binary logs are enabled by default.
Join the DZone community and get the full member experience.
Join For FreeMariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.
As part of my benchmarks of binary logs, I've decided to check how the recently released MySQL 8.0 performance is affected in similar scenarios, especially as binary logs are enabled by default. It is also interesting to check how MySQL 8.0 performs against the claimed performance improvements in redo logs subsystem.
I will use a similar setup as in my last article with MySQL 8.0 using the utf8mb4 charset.
I have a few words about MySQL 8.0 tuning. Dimitri recommends in his blog posts to use innodb_undo_log_truncate=off and innodb_doublewrite=0. However, in my opinion, using these settings is the same as participating in a car race without working brakes: you will drive very fast, but it will end badly. So, contrary to Dimitri's recommendations, I used innodb_undo_log_truncate=on and innodb_doublewrite=1.
Servers Comparison
For the first run, let's check the results without binary logs vs. with binary logs enabled, but with sync_binlog=1 for Percona Server for MySQL 5.7 vs. MySQL 8.0.
In tabular form:
Binary Log Effect
In tabular form:
Conclusions
It seems that binary logs have quite an effect MySQL 8.0, and we see up to a 30% performance penalty as opposed to the 13% for Percona Server for MySQL 5.7.
In general, for in-memory workloads, Percona Server for MySQL 5.7 outperforms MySQL 8.0 by 10-20% with binary logs enabled, and 4-9% without binary logs enabled.
For io-bound workloads (buffer pool size <= 30GB), the performance numbers for Percona Server for MySQL and MySQL are practically identical.
Hardware Spec
Supermicro server:
- Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz
- 2 sockets / 28 cores / 56 threads
- Memory: 256GB of RAM
- Storage: SAMSUNG SM863 1.9TB Enterprise SSD
- Filesystem: ext4/xfs
- Percona-Server-5.7.21-20
- OS: Ubuntu 16.04.4, kernel 4.13.0-36-generic
Extra Raw Results, Scripts, and Config
My goal is to provide fully repeatable benchmarks. I have shared all scripts and settings I used in this GitHub repo.
MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.
Like This Article? Read More From DZone
Published at DZone with permission of Vadim Tkachenko , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}