Page read performance: MySQL 5.7 vs previous releases

For this test I used sysbench to determine the peak rate for disk reads per second from InnoDB with fast storage. Fast storage in this case is the OS filesystem cache. I want to know the peak for MySQL 5.7.5 and whether there is a regression from 5.6. My summary is:

• 5.7.5 is 12% to 15% worse than 5.6.21 at 1 thread. I hope we can fix this via bug 74342.
• 5.7.5 is 2% to 4% worse than 5.6.21 at 8, 16 and 32 threads
• 5.6/5.7 are much better than 5.0 especially at high concurrency
• Page size (16k, 8k, 4k) has a big impact on the peak only when compression is used. 

setup

The test server has 40 hyperthread cores and is shared by the sysbench client and mysqld server.

I used my sysbench 0.4 fork configured to fetch 1 row by primary key via SELECT and HANDLER statements. The InnoDB buffer cache was 1G with innodb_flush_method="" (buffered IO). There were 8 tables in the test database with 16M rows per table.  The test database was either 32G uncompressed or 16G compressed so tests with compression had a better InnoDB buffer pool hit rate. Tests were run with 1, 8, 16 and 32 threads. The 1 thread test was limited to 1 table (2G or 4G of data) so this benefits from a better InnoDB buffer pool hit rate. The 8, 16 and 32 thread tests uniformly distribute clients across 8 tables.

Tests were done for MySQL 5.0.85, 5.6.21 and 5.0.85. All versions were tested for 16k pages without compression (see -16k-c0 below). Some MySQL 5.7.5 tests were repeated for 8k and 4k pages (see -4k- and -8k- below). Some MySQL 5.7.5 tests were repeated for 2X compression via zlib (see -c1- below).

results at 1 thread

The results at 1 thread show that peak QPS for 5.7.5 is 12% to 15% worse than for 5.6.21. I hope this can be fixed. I assume the problem is a longer code path from new features in 5.7.

results at 8 threads

The difference between 5.6.21 and 5.7.5 is smaller than at 1 thread. I assume this is the benefit from less mutex contention in 5.7.

results at 16 threads

The difference between 5.6.21 and 5.7.5 is smaller than at 1 thread. I assume this is the benefit from less mutex contention in 5.7.

results at 32 threads

The difference between 5.6.21 and 5.7.5 is smaller than at 1 thread. I assume this is the benefit from less mutex contention in 5.7.

configuration

I used this my.cnf for 5.7.5 and modified it for 5.6:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

table-definition-cache=1000

table-open-cache=2000

table-open-cache-instances=8

max_connections=20000

key_buffer_size=200M

metadata_locks_hash_instances=256 

query_cache_size=0

query_cache_type=0

server_id=9

performance_schema=0

binlog_format=row

skip_log_bin

innodb_buffer_pool_instances=8

innodb_io_capacity=32000

innodb_lru_scan_depth=4000

innodb_checksum_algorithm=CRC32

innodb_thread_concurrency=0

innodb_buffer_pool_size=1G

innodb_log_file_size=1900M

innodb_flush_log_at_trx_commit=2

innodb_doublewrite=0

# innodb_flush_method=O_DIRECT

innodb_thread_concurrency=0

innodb_max_dirty_pages_pct=80

innodb_file_format=barracuda

innodb_file_per_table

datadir=...

This is an example command line for a test that uses SELECT and 1 thread per table. My testing script starts 8 sysbench clients, one per table. The value for --seed-rng different for every sysbench client (function of current time and table number 1 to 8) to access different keys each run:

./sysbench --batch --batch-delay=10 --test=oltp --mysql-host=127.0.0.1 --mysql-db=test --oltp-table-size=16000000 --max-time=120 --max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --oltp-table-name=sbtest1 --oltp-read-only --oltp-skip-trx --oltp-test-mode=simple --oltp-point-select-all-cols --oltp-dist-type=uniform --oltp-range-size=10 --oltp-connect-delay=0 --percentile=99 --num-threads=1 --seed-rng=1413134256 run