Wednesday, 04 October, 2017
release candidate of MySQL 8.0 is here, and I'm happy to share few
performance stories about. This article will be about the "most simple"
one -- our in-memory Read-Only performance ;-))
However, the used test workload was here for double reasons :
- 1) validate MySQL 8.0 performance
- 2) fully evaluate the "New" Sysbench developed by Alex (https://github.com/akopytov/sysbench)
Going ahead to the second point, the main worry about New Sysbench was about its LUA overhead (the previous version 0.5 was running slower than the old one 0.4 due LUA) -- a long story short, I can confirm now that the New Sysbench is running as fast as the oldest "most lightweight" Sysbench binary I have in use ! so, KUDOS Alex !!! ;-))
While regarding the improvements coming with MySQL 8.0 on Read-Only workloads I'd mention :
- several "overheads" were fixed
- the most notable one is related to UTF8, of course
- however, even latin1 related functions were improved little bit
- but this was only about "overheads", and nothing about "scalability"
- because the main "scalability" gap was already made with MySQL 5.7 two years ago ;-))
- so, our main merit with MySQL 8.0 here will be rather NOT TO LOOSE the already obtained gain !
- (agree, sounds very odd, but if you'll just look on the list of the all new features coming with 8.0 you can imagine our code path is not going to be shorter, right ? ;-))
- so the fair test here will be to compare 8.0 vs 5.7 and 5.6 with latin1 encoding
- (for UTF8 the winner is 8.0 and from very far, which you already know)
The most "sensible" RO workload in Sysbench is Point-Selects, so here is my test scenario:
- workload : New Sysbench RO point-selects
- data volume : 8 tables of 10M rows each
- encoding : latin1
- user load levels : 1, 2, 4, .. 1024
- engines : MySQL 8.0, MySQL 5.7, MySQL 5.6
- server : 96cores-HT 4CPU sockets 2.2Ghz (Broadwell), OL7.3
and here is the result :
- 2.1M SQL Query/sec for MySQL 8.0 -- our current new Max QPS record obtained in MySQL history !
- which is great, no doubt !
- however, there is a clearly seen small, but visible QPS regression on lower load levels..
- which is not really cool (even if could be easily explained by increased code path + new DD + etc.. etc..)
- well, adding it to my list of "low load" performance issues and will investigate later..
So far, the 2.1M QPS result is obtained on the "old" Broadwell CPU, there is no any bottlenecks observed (only potential overheads), so for the moment I have no idea what to expect on the same workload on 4CPU sockets Skylake, will share the results once have such a box in my hands ;-))
Then, many people are constantly asking me about how to reproduce the presented results, so the following all all exactly details you may need:
1) install and start your MySQL 8.0 instance (rc or current labs release)
2) the config settings I've used is here :
[mysqld] # general max_connections=4000 table_open_cache=8000 table_open_cache_instances=16 back_log=1500 default_password_lifetime=0 ssl=0 performance_schema=OFF max_prepared_stmt_count=128000 skip_log_bin=1 character_set_server=latin1 collation_server=latin1_swedish_ci transaction_isolation=REPEATABLE-READ # files innodb_file_per_table innodb_log_file_size=1024M innodb_log_files_in_group=3 innodb_open_files=4000 # buffers innodb_buffer_pool_size=32000M innodb_buffer_pool_instances=16 innodb_log_buffer_size=64M # tune innodb_doublewrite=0 innodb_thread_concurrency=0 innodb_flush_log_at_trx_commit=1 innodb_max_dirty_pages_pct=90 innodb_max_dirty_pages_pct_lwm=10 join_buffer_size=32K sort_buffer_size=32K innodb_use_native_aio=1 innodb_stats_persistent=1 innodb_spin_wait_delay=6 innodb_max_purge_lag_delay=300000 innodb_max_purge_lag=0 innodb_flush_method=O_DIRECT_NO_FSYNC innodb_checksum_algorithm=none innodb_io_capacity=4000 innodb_io_capacity_max=20000 innodb_lru_scan_depth=9000 innodb_change_buffering=none innodb_read_only=0 innodb_page_cleaners=4 innodb_undo_log_truncate=off # perf special innodb_adaptive_flushing=1 innodb_flush_neighbors=0 innodb_read_io_threads=16 innodb_write_io_threads=16 innodb_purge_threads=4 innodb_adaptive_hash_index=0 # monitoring innodb_monitor_enable='%'
NOTE: yes, I know, PFS is turned OFF, the results comparing PFS=on/off are coming later, no worry ;-))
4) download the tarball with my scripts + sysbench binary + my.conf, and then untar it into "/" directory
5) this will create /BMK directory with all the stuff inside, so edit the ".bench" file to provide the account details to connect to your MySQL instance (user, passwd, host, port)
6) create "sysbench" database
7) run the load data script :
this will load 8 tables with 8M rows each$ cd /BMK $ sh sb_exec/sb11-Prepare_10M_8tab-InnoDB.sh
8) run ANALYZE on each table within sysbench database to be sure your stats for these tables are up-to-date
9) run the test :
$ cd /BMK $ for nn in 1 2 4 8 16 32 64 128 256 512 1024 do sh sb_exec/sb11-OLTP_RO_10M_8tab-uniform-ps-p_sel1-notrx.sh $nn 300 sleep 60 done
10) enjoy ;-))
stay tuned, more to come..