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.
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/ .
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]
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.
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.
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
- 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.