MySQL 8.0.15 performs worse in sysbench oltp_read_write than MySQL 5.7.25
Initially I was testing group replication performance and was puzzled why MySQL 8.0.15 performs consistently worse than MySQL 5.7.25.
It appears that a single server instance is affected by a performance degradation.
My testing setup
Hardware details:
Bare metal server provided by packet.net, instance size: c2.medium.x86
24 Physical Cores @ 2.2 GHz
(1 X AMD EPYC 7401P)
Memory: 64 GB of ECC RAM
Storage : INTEL® SSD DC S4500, 480GB
This is a server grade SATA SSD.
Benchmark
1 | sysbench oltp_read_write --report-interval=1 --time=1800 --threads=24 --tables=10 --table-size=10000000 --mysql-user=root --mysql-socket=/tmp/mysql.sock run |
In the following summary I used these combinations:
- innodb_flush_log_at_trx_commit=0 or 1
- Binlog: off or on
- sync_binlog=1000 or sync_binlog=1
The summary table, the number are transactions per second (tps – the more the better)
1 2 3 4 5 6 7 8 9 10 11 12 13 | +-------------------------------------------+--------------+--------------+-------+ | case | MySQL 5.7.25 | MySQL 8.0.15 | ratio | +-------------------------------------------+--------------+--------------+-------+ | trx_commit=0, binlog=off | 11402 tps | 9840(*) | 1.16 | +-------------------------------------------+--------------+--------------+-------+ | trx_commit=1, binlog=off | 8375 | 7974 | 1.05 | +-------------------------------------------+--------------+--------------+-------+ | trx_commit=0, binlog=on, sync_binlog=1000 | 10862 | 8871 | 1.22 | +-------------------------------------------+--------------+--------------+-------+ | trx_commit=0, binlog=on, sync_binlog=1 | 7238 | 6459 | 1.12 | +-------------------------------------------+--------------+--------------+-------+ | trx_commit=1, binlog=on, sync_binlog=1 | 5970 | 5043 | 1.18 | +-------------------------------------------+--------------+--------------+-------+ |
Summary: MySQL 8.0.15 is persistently worse than MySQL 5.7.25.
In the worst case with trx_commit=0 and sync_binlog=1000 , it is worse by 22%, which is huge.
I was looking to use these settings for group replication testing, but these settings, when used with MySQL 8.0.15, provide much worse results than I had with MySQL 5.7.25
(*) in the case of trx_commit=0, binlog=off, MySQL 5.7.25 performance is very stable, and practically stays at the 11400 tps level. MySQL 8.0.15 varies a lot from 8758 tps to 10299 tps in 1 second resolution measurements
Update:
To clarify some comments, I’ve used latin1 CHARSET in this benchmark for both MySQL 5.7 and MySQL 8.0
Appendix:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | [mysqld] datadir= /mnt/data/mysql socket=/tmp/mysql.sock ssl=0 #innodb-encrypt-tables=ON character_set_server=latin1 collation_server=latin1_swedish_ci skip-character-set-client-handshake #skip-log-bin log-error=error.log log_bin = binlog relay_log=relay sync_binlog=1000 binlog_format = ROW binlog_row_image=MINIMAL server-id=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Recommended in standard MySQL setup # general table_open_cache = 200000 table_open_cache_instances=64 back_log=3500 max_connections=4000 # files innodb_file_per_table innodb_log_file_size=15G innodb_log_files_in_group=2 innodb_open_files=4000 # buffers innodb_buffer_pool_size= 40G innodb_buffer_pool_instances=8 innodb_log_buffer_size=64M # tune innodb_doublewrite= 1 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit= 0 innodb_flush_method=O_DIRECT_NO_FSYNC innodb_max_dirty_pages_pct=90 innodb_max_dirty_pages_pct_lwm=10 innodb_lru_scan_depth=2048 innodb_page_cleaners=4 join_buffer_size=256K sort_buffer_size=256K innodb_use_native_aio=1 innodb_stats_persistent = 1 #innodb_spin_wait_delay=96 # perf special innodb_adaptive_flushing = 1 innodb_flush_neighbors = 0 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity=1500 innodb_io_capacity_max=2500 innodb_purge_threads=4 innodb_adaptive_hash_index=0 max_prepared_stmt_count=1000000 |
—
Photo by Suzy Hazelwood from Pexels
I’ve seen many benchmarks that show MySQL 8.0 faster than 5.7. For example this: https://severalnines.com/blog/mysql-performance-benchmarking-mysql-57-vs-mysql-80
And this: http://dimitrik.free.fr/blog/posts/mysql-performance-80-iobound-oltprw-vs-percona57.html
Are there any rules of thumb about the conditions under which MySQL 8.0 is generally faster? Something like MySQL 8.0 is faster for read only workload but 5.7 is faster for read-write?
Andy,
In my experience I have yet to see the case when MySQL 8.0 is faster. Soon I will publish my read-only benchmarks where MySQL 8.0 is also slower.
In my opinion Dimitri in his benchmarks is focusing on non-realistic configurations that are tuned and favorable for MySQL 8.0
Vadim,
That’s interesting. Do you recommend upgrading to MySQL 8.0 then?
Andy,
That’s tricky question. While the performance is important, it is not the only factor for the decision.
MySQL 8.0 brings a lot of new features:
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
So if you want to use some of those features, it might be worth to upgrade.
Otherwise I would wait a little longer, till 8.0 becomes more polished.
I agree sysbench is often not realistic, but I think there is a case where MySQL 8.0 is a lot faster and matches real-world use cases: utf8mb4.
It was not really a priority in earlier releases, but most users are using utf8mb4.
For mid and low loaded databases 5.5 is faster than 5.6, 5.6 better than 5.7. So no surprise here as well. I suppose this is the price for reacher functionality, that we get with every new version.
There is no free lunch as it says 🙂
I noticed almost instantly that 8 is slower than 5.7. Disturbingly so. I was wondering if maybe I needed some new settings in my.cnf that I didn’t know about.
Vadim,
Morgan brings an important question. I do not see character set configured in your my.cnf file. Are the servers using same character set for communication and table data ?
Peter,
I’ve used latin1 CHARSET in this benchmark for both MySQL 5.7 and MySQL 8.0
Vadim, Do you have thoughts on “what is a realistic workload?”. For example, is SysBench considered a realistic workload, DBT3, …? And is latin1 more realistic than utf8mb4, or is it the other way around ?
Geir,
I do not believe such thing as “realistic workload” exists. Different users and customers need different things.
The best we can do is to stress different subsystems to understand how database performs under give conditions.
my “realistic” rule of thumb is for app in dev. process use/switch to latest version.
Supporting factors:
1. Better for long run
2. as @vadimtk said “I do not believe such thing as “realistic workload” exists…”
Geir, In my opinion for modern applications it is utf8 what matters so MySQL 8 being faster with this charset is great! However if we only get utf8 handling in MySQL 8 much faster but everything else got slower it is not a great trend.
There’s a lot to digest here without a whole lot of information; however, I really think you need to modify most of your configuration options.
Remove the deprecated entry
Fix your log size (run the test to see what it should be)
https://www.percona.com/blog/2017/10/18/chose-mysql-innodb_log_file_size/
Increase join_buffer_size, it’s set to the minimum.
Fix innodb_file_per_table=1
Fix the spacings with = and value (they should be set without the weird arrangement of spaces)
etc… once fixed and optimized for your actual session needs, memory & cache, it should really chug along well.
mysql 8 is much slower than 5.7 for ‘create table’ and ‘create procedure’. The disk activity on my Windows 10 keeps 100% for a long time.
Confirming this issue. I had 100 GB DB (~200GB including indexes). Updated it from 5.7 to 8. My DB is used by a few people only, and almost 24/7 there running some heavy calculation scripts. Overall performance slowed down notably. Updated all my utf8 columns to utf8mb4. When disabled binlog the performance restored to the level of 5.7 with enabled binlog. Going to try Percona Server to see if it gives any better performance.