Update on Performance measurement on MariaDB 10.1 and MySQL 5.7.4-labs-tplc

Introduction

This blog is a follow up to my original blog in https://blog.mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/ . First of all I would like to thank for all the comments I received. Based on the comments there was a concern if the differences seen on performance was due to different configuration setup. Furthermore, I did not know that there was a configuration variable to get similar multi-threaded flush mechanism on MySQL as there is on MariaDB. To find out if the different configuration variables or different defaults were the reason for different performance, I ran several rounds of new tests.

Test 1

Changing number of buffer pool instances or value of innodb thread concurrency do not seem to have significant effect on at least LinkBench benchmark performance results. However, changing the number of threads on flushing has an affect. Below I present results from both MariaDB and MySQL using page compression.  Lowest line is the original results already presented on https://blog.mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/ .

LinkBench_measure_new2

LinkBench_measure_new1

Changing parameters had an effect and performance results from LinkBench benchmark improved. However the improvements are similar for both servers. Clearly, MariaDB still outperforms MySQL on this benchmark using this configuration. Below, is the full set of configuration variables used in this test.

[mysqld]
basedir=/usr/local/mysql
user=root
socket=/tmp/mysql.sock
server_id=1
local_infile=1
pid-file=/tmp/server1.pid
port=3306
max_connections = 3000
back_log = 1500
open_files_limit = 1500
table_open_cache = 520
table_open_cache_instances = 32
key_buffer_size = 16M
query_cache_type = 0
join_buffer_size = 32K
sort_buffer_size = 32K
skip-grant-tables

innodb_buffer_pool_size=50G
innodb_use_native_aio=1
innodb_data_file_path=ibdata1:50M:autoextend
innodb_file_per_table=1
innodb_open_files=100
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout = 120
innodb_doublewrite=0
innodb_buffer_pool_instances=16
innodb_mtflush_threads=16
innodb_compression_level=6
innodb_compression_algorithm=2
max-prepared-stmt-count=400000
innodb_fast_shutdown=0

innodb_log_buffer_size=256M
innodb_log_files_in_group=3
innodb_log_file_size=8G

innodb_thread_concurrency=32
innodb_flush_method = O_DIRECT
innodb_write_io_threads=16
innodb_read_io_threads=16

innodb_max_dirty_pages_pct=90
skip-name-resolve
innodb_adaptive_flushing=1
innodb_file_format=barracuda
innodb_fast_shutdown=0

#used on MariaDB
innodb_mtflush_threads=16
innodb_use_mtflush=1

#used on MySQL
#innodb_page_cleaners=16
#innodb_compression_punch_hole=1

# IO
innodb_checksum_algorithm=crc32  
innodb_flush_neighbors=0
innodb_lru_scan_depth=2500 
innodb_io_capacity=25000 
innodb_io_capacity_max=35000

#xtradb
ignore-builtin-innodb
plugin-load=innodb=ha_innodb.so
plugin-dir=/mnt/fiob/10.0-FusionIO/storage/innobase
#innodb_log_block_size=4096

[mysql]
local-infile=1
[client]

Test2

While, I was still testing different configuration setups on the previous test an excellent blog on similar performance measurements were published on http://dimitrik.free.fr/blog/archives/2014/08/mysql-performance-analyzing-linkbench-workload-on-mysql-57-and-mariadb-101.html. This blog contained again a different configuration (and naturally different hardware). Results were so interesting that I decided to try to repeat them.

I started from the results I obtained using uncompressed tables and in my system I have Intel Xeon CPU e5-2690 2.90GHz, 2 sockets, 8 cores using hyper threading, i.e. total of 32 cores. I use CentOs 6.4 and Linux 3.4.12. Storage is Fusion-io ioDrive2 Duo, Driver version 3.3.3 build 716 with Firmware v7.2.5 formatted as NVMFS.

LinkBench_measure_new3

There is clear difference to results obtained on DimitriK’s blog. First of all these results for both servers are significantly better. The system, I used has less cores but they are higher frequency. Additionally, this system has a more recent version of NVMFS. In my results, I used a 24 hour measure time and 150G database. DimitriK’s had 30 minutes in his blog post. Also, I used almost the same configuration, with one exception. I had exactly same amount of innodb_mtflush_threads as innodb_page_cleaners). In my results, MariaDB still offers better performance than MySQL. But, lets take a look at the page compressed results also.

 

LinkBench_measure_new4

Clearly, these new parameters are better than previously used parameters. I thank DimitriK for pointing out these.

Difference between MariaDB and MySQL is even bigger and still MariaDB offers better performance compared to MySQL. Bottom line is that I can’t repeat the results in the blog. This version of MariaDB indeed does not contain the fix for InnoDB index lock contention, but that does not seem to be the problem in this environment. Looking into performance schema numbers, it can be seen.

Just for a record, here is the used configuration variables.

[mysqld]
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=root
port=3306
max_connections=4000
skip_grant_tables

#myisam
key_buffer_size = 4000M
ft_max_word_len = 16
low_priority_updates = 2

#general
table_open_cache = 8000
table_open_cache_instances=16
back_log=1500
query_cache_type = 0

#files
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_log_file_size=1024M
innodb_log_files_in_group=12
innodb_open_files=4000

#buffers
innodb_buffer_pool_size = 75000M
innodb_buffer_pool_instances=32
innodb_log_buffer_size=64M

#tune
innodb_checksums=1
innodb_checksum_algorithm=crc32
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=4000
#for MYSQL
#innodb_page_cleaners=4

join_buffer_size=32K
sort_buffer_size=32K
innodb_use_native_aio=1
innodb_stats_persistent = 1
innodb_spin_wait_delay=6

#perf special
innodb_adaptive_flushing=1
innodb_flush_neighbors=0
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=15000
innodb_purge_threads=4
innodb_max_purge_lag_delay=30000000
innodb_max_purge_lag=1000000
innodb_adaptive_hash_index=0

#monitoring
innodb_monitor_enable = '%'
performance_schema=ON
performance_schema_instrument='%sync%=on'

#mariadb
innodb_compression_algorithm=2
innodb_use_mtflush=1
innodb_mtflush_threads=4
innodb_use_fallocate=1
innodb_use_atomic_writes=1
ignore-builtin-innodb
plugin-load=ha_innodb.so
plugin-dir=/mnt/fioa/MariaDB-10.1/storage/innobase

Conlusions

My observations:

  • Configuration variable values can affect significantly to performance and thus it is important to tune the variables based on benchmark and hardware to get best possible performance.
  • Performance measurements on my original blog are correct if not optimal.
  • I could not repeat the significant difference on performance presented on DimitriK’s blog. Some possible reasons:
    • We have different distributions and hardware
    • Blog used short 30min measure time (I do not think this is the issue)
    • Blog used only uncompressed tables (based on my results, this is not the issue)
    • Blog used XtraDB on MariaDB (as it is default), this could have some effect but not all
    • I used default compilation (cmake . ; make )
  • MariaDB performance is not optimal because of the index lock contention pointed out by DimitriK. This will be fixed on later versions of MariaDB 10.1.