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/ .
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.
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
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.
Are there plans to release benchmark results for Linkbench using the same server versions on more affordable IO, like an 8 drive 10K SAS RAID array?
SAS isn’t more affordable in general. Bytes are cheaper on SAS. IOPs are cheaper on flash. What is your budget, db size and IOPs demand?
For the first test.
Why did you reduce table_open_cache from its default value of 2000 to 520? Why did you reduce innodb_open_files from its default value of the same as table_open_cache to 100? What is the total number of ibd files used during this test?
Why did you reduce open_files_limit from its initial compile default of 5000 to 1500, also overriding its automatic configuration based on max_connections set to 3000, and table_open_cache set to 520? This combination of settings should have produced a “Changed limits” warning in the MySQL server error log telling you that you had set open_files_limits so low that the server had to reduce max_connections and table_open_cache to fit within the handle limit that you specified. What did that warning say, or was it suppressed by settings?
Unless you override it by giving a value for open_files_limit, the calculated value to use for open_files_limit is the higher of these values:
1. 10 + max_connections + (table_open_cache * 2) = 4050
2. max_connections * 5 = 15000
3. 5000, the compiled-in default.
So the expected calculated value for open_files_limit would be 15,000 based on your settings but you constrained the server to use no more than 1,500, forcing it to reduce other settings to fit within that limit.
Had you not set table_open_cache to below its usual default, rule 1 would have given 7010 and the rule 2 15000 would still have governed.
Your combination of these settings is puzzling, since you asked for some settings then used another setting to bar the server from using the values that you set.
As usual with table and table definition cache, what were the values for Opened_tables and Opened_table_definitions at the end of the test to check that those were at least sufficient rather than causing a lot of redundant table opening and closing?
James Day, MySQL Senior Principal Support Engineer, Oracle
Not sure those matter in this case as LinkBench uses 3 tables. But he didn’t tell us: #client threads used by linkbench or whether partitioning was used for the tables. So maybe it could matter.
There is 18 .ibd files and yes uses partitioning. LInkBench uses 64 clients. After run Opened_files is 98 and Opened_table_definitions 0. It seems that I made a typo with open_table_cache, it should have been 15200, but does not really matter. I tested, no affect on performance.
Hi Jan,
thanks for sharing the results! – still looking completely different from mines, but I think with some more patience we’ll find why..
first of all the question I have to you: the TPS numbers you’re showing on your graphs are reported by MySQL/MariaDB itself or Linkbench?.. – if LinkBench, then is it possible to show the graph with Commit/sec reported by database server itself?..
then: you’re mentioning that index lock was not seen in top of waits reported by PFS.. – may you show here which waits were in top for “uncompressed” tests for MySQL and MariaDB?..
regarding the difference: no, I did not use XtraDB in my tests (and as fas as I know XtraDB has no MariaDB config options, right? 😉 – don’t think either the test duration is playing a role here as on your tests the TPS level is lowering very quickly over a time, so even within 30min such a tendency should be visible; don’t think HW is playing a big role either..
Well, to clarify your results may you also run a test just on EXT4 created on the same Fusion-io device? (without any compression, atomics, doublewrite buffer, etc.) — this will show the result base for any further investigation.. As well don’t forget to monitor mutex waits reported by PFS — this will clarify the story as well..
More detailed results from my server I’ll publish soon, so stay tuned 😉
Rgds,
-Dimitri
Yes, I did use measure produced by benchmark.
Could you share you select clause that produced the results from commits and index contention, so that I would at least have results from same query, index_lock is seen there anyway.
In 10.0 and 10.1 XtraDB is default and used if you do not use –ignore-builtin-innodb, –plugin-load=ha_innodb.so and define plugin-dir, XtraDB has those extra MariaDB options because I implemented them there.
Sure, I can use EXT4 also, let me see.
But I think we really should use our energy and minds to find out why we can’t use the FusionIO hardware potential better. Based on FusionIO, card could do about 1.2GB/s on writes, with MySQL or MariaDB not even near that even on load phase.
So a couple of things could be tested to look at the performance potential. There’s a number of things in a DB that will limit performance. Serialized fsync, hard to keep big I/O queue depts, especially on reads, lock contention etc.
It may be interesting doing more multi-instance tests and see how performance changes (ie splitting 1 linkbench into 2 with 1/2 the BP, clients, etc).
It would also be interesting looking at what log writing at DRAM latencies would mean (use a ramdisk for testing).
Also Mysql has been using buffered log writing for a long time. The O_DIRECT option for log writing isn’t really useful, as the write itself is done with a _very_ busy mutex which means going to the backing store is way too expensive. It may make sense to look at having finer grained locking here, so you dont have to do:
get mutex
buffered write
release mutex
fsync()
The syscall overhead is clearly noticeable.
Regards,
Torben
Kind of funny. Here Jan reports QPS over time as logged by LinkBench. Dimitri reports MySQL/InnoDB metrics as consumed by his excellent performance monitoring tool chain. So results are hard to compare, but sometimes keeps us from wasting too much time over which one was faster.
I used the metrics that is produced by the Benchmark. Reporting InnoDB metrics is not same as what you would really see on application because it will take bigger than zero time to return that commit message from InnoDB engine to application.