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

J

Jan Lindstrom

Guest
Introduction


This blog is 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 all the comments I received. Based on 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 configuration variable to get similar multi-thereaded flush mechanism on MySQL as there is on MariaDB. To find out if different configuration variables or different defaults was the reason for different performance, I run 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/ .





Changing parameters had an effect and performance results from LinkBench benchmark improved. However, improvement is similar for both servers. Clearly, MariaDB still outperforms MySQL on this benchmark using this configuration. Below, is presented full set of configuration variables used on 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 # backwards compatible
innodb_flush_neighbors=0
innodb_lru_scan_depth=2500 # Similar to io capacity
innodb_io_capacity=25000 # should be set low without dips
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 previous test a excellent blog on similar performance measurements were published on http://dimitrik.free.fr/blog/archiv...nch-workload-on-mysql-57-and-mariadb-101.html. This blog contained again different configuration (and naturally different hardware). Results, were so interesting that I decided to try to repeat them.

I start from 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. System, I used has less cores but they are higher frequency. Additionally, this system has more recent version of NVMFS. In my results, I used 24 hour measure time and 150G database as DimitriK’s blog and same configuration (except I used exactly same amount of innodb_mtflush_threads as innodb_page_cleaners). In my results, MariaDB offers still better performance than MySQL. But, lets see also page compressed results.





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 on blog. This version of MariaDB indeed does not contain 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 distribution 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 index lock contention pointed out by DimitriK’, but this will be fixed on later versions of MariaDB 10.1.

Continue reading...
 
Top