Monday, October 13, 2014

Sysbench cached updates: MySQL 5.7 vs previous releases

I used MySQL 5.7.5 to repeat tests I did for MySQL 5.7.2 and 5.6.10 with the sysbench client and a cached database to compare performance for low and high concurrency. My configuration was optimized for throughput rather than crash safety. The performance summary:
  • MySQL 5.7.5 is 1.47X slower than 5.0.85 at 1 thread
  • MySQL 5.7.5 is a bit slower than 5.6.21 at 8 and 16 threads
  • MySQL 5.7.5 is faster than others at 32 threads
setup

I used my sysbench 0.4 fork and set the value of the c column to 0 in the sbtest tables as that column is incremented during the test. The test used 8 tables with 8M rows per table (about 16G total) and the InnoDB buffer pool was 32G. One table was used for the 1 thread case. For the other tests the clients were uniformly distributed across the 8 tables. All data was in the buffer pool at test start. The test was run for 10 minutes at each concurrency level (1, 8, 16, 32 threads). Throughput appeared to be stable throughout the test.

Tests were run for MySQL versions 5.0.85, 5.1.63, 5.6.21 and 5.7.5. Tests were repeated for 4k and 8k innodb_page_size with MySQL 5.7.5 (see -16k, -8k and -4k below). Tests were also repeated with changes to innodb_flush_neighbors (=0), innodb_write_io_threads (=8), innodb_page_cleaners (=4) and innodb_purge_threads (=4). Results for those changes are not provided here as there wasn't an impact on QPS. The change to flush_neighbors reduced the InnoDB page write rate, which is a good thing.

results

The regression from 5.0.85 to recent versions is visible in the single-thread results below.
MySQL 5.7.5 is the champ at 32 threads but is slightly worse than 5.6.21 at 8 and 16 threads. Recent versions are much better than old versions.

configuration

I used this my.cnf for MySQL 5.7.5 and then something similar for other versions:
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=32G
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

This was the command line for one of the tests:
./sysbench --batch --batch-delay=10 --test=oltp --mysql-host=127.0.0.1 --mysql-db=test --oltp-table-size=8000000 --max-time=600 --max-requests=0 --mysql-table-engine=innodb --db-ps-mode=disable --mysql-engine-trx=yes --oltp-table-name=sbtest1 --oltp-skip-trx --oltp-test-mode=incupdate --oltp-dist-type=uniform --oltp-range-size=10 --oltp-connect-delay=0 --percentile=99 --num-threads=1 --seed-rng=1413171854 run

No comments:

Post a Comment