« dim_STAT v.9.0-u14 Core Update is here! | Main | MySQL Performance: More in depth with LinkBench Workload on MySQL 5.7, MariaDB 10.1 and also Percona Server 5.6 »

Reading the article published on MariaDB site about performance evaluation of MySQL 5.7 -vs- MariaDB 10.1 I've got a double feeling: from one side I have no any reason to have a doubt in obtained results, and from another side - my feeling says me there is something going odd.. But well, I don't have any attention to blame anybody (and sorry if you think so) -- my willing here is only to understand what is going on, why, and what are the challenges on the observed test workload(s). I'll cover here only my observations on the LinkBench workload as it was not yet enough tested and involving many questions around..



LinkBench Benchmark :

developed by Facebook Team, so represents a true real life workload

written in Java

from the beginning since it creation was reported as very sensible to the index lock contention in InnoDB

for this reason was modified to have partitions in the most hot table (to split a single index contention)

this helped to improve performance, but index lock contention remained limiting..

the issue with InnoDB index lock was fixed only in MySQL 5.7 giving a significant performance boost in LinkBench workload

indeed, having compression feature within any database engine is very valuable

however, compression by itself cannot improve an overall performance (except if you have some limitation on other levels, like slow storage, etc.)..

and on extremely fast Fusion-IO flash storage there is no doubt that performance result will be better when compression is not used, and it's exactly what we see in the presented results..

but then it's hard to believe for me that without using any compression MySQL 5.7 which is having the fix for InnoDB index lock contention is running slower than MariaDB 10.1 which is based on InnoDB from MySQL 5.6 and not having such a fix..

Linux box with 40cores-HT 2.3Ghz running Oracle Linux 6.5

Fusion-IO flash storage using NVMFS filesystem

LinkBench database of 150G

let's start with a Buffer Pool (BP) = 50% of database size to follow MariaDB test conditions (so, 75G in my case)

I'm usually using 32 BP instances

as BP will not be able to keep the whole data set, we may expect constant IO reads

so a high activity on LRU to expect + LRU flushing

means LRU depth should be tuned well, I'll set it to 4000 (not too big, not too small, as it's per BP instance, this will give me 32 x 4000 free pages amount to expect which should cover a potential page IO read speed from Fusion-IO)

REDO log size is also important, as a bigger REDO is allowing to delay page flushing and keep flushing/checkpoint activity more smooth -- 12GB REDO here seems to be enough

adaptive hash index (AHI) -- in most RW workloads it's better to have disabled (and even in some RO), but curiously here has no impact at all..

using AIO + O_DIRECT is the must

all other setting is just following general "best practices" for most of workloads..

Performance Schema (PFS) is enabled + mutex instrumentation too

#1 - MySQL 5.7 no doublewrite (innodb_flush_log_at_trx_commit=1, checksums crc32, innodb_doublewrite=0)

#2 - MySQL 5.7 secure ((innodb_flush_log_at_trx_commit=1, with checksums crc32, innodb_doublewrite=1)

#3 - MariaDB 10.1 no doublewrite (same options as MySQL 5.7 in #1)

#4 - MariaDB 10.1 secure

#5 - MariaDB 10.1 with atomic writes (without doublewrite, but atomic writes instead)

Observations

without doublewrite MySQL 5.7 is near x2 times faster than MariaDB 10.1 (20K TPS -vs- 12K TPS)

within "secure" configuration MySQL 5.7 is loosing 25% in performance (20K -> 15K TPS)

interesting that this workload is pretty sensible to REDO flushing (sync)

but the main overhead is coming from a doublewrite anyway..

however, as you can see, it's still better than MariaDB

interesting to see that atomic writes in MariaDB are not lowering too much TPS

so, once atomic writes will be available in MySQL 5.7 I may expect the same 20K TPS in secure config here

NOTE: to avoid doublewrite overhead you may also use O_DSYNC flush setting combined with EXT4 data journaling as it was recently demonstrated by Percona

Observations

MariaDB 10.1 is hitting a severe contention on the index lock

while MySQL 5.7 is not having it anymore

and that's why MariaDB is running slower on this workload..

Observations

Checkpoint Age did not reach any critical level (max 7GB from 12GB in REDO was used)

no LRU single page flush either, so the amount of free pages was always enough..

Observations

MySQL 5.7 is requiring 20K / 18K free pages/sec

while MariaDB 10.1 is only 10K..

the excessive page scanning seen in MariaDB is fixed in MySQL 5.7 (still remains in MySQL 5.6, but in fact not impacting a final performance)

Observations

without a doublewrite MySQL 5.7 is out passing MariaDB 10.1 by x2.5 times (10K TPS -vs- 4K TPS)..

in a "secure" mode MySQL 5.7 is still x1.5 times better (over 6K TPS -vs- 4K TPS)

NOTE: I've also replayed the MariaDB test with using its multithreaded flushing feature (innodb_mtflush_threads=16, innodb_use_mtflush=1) -- this is helping to reach 4500-5000 TPS, but still not enough to attend the MySQL 5.7 level..

Observations

MySQL 5.7 is blocked mainly by fil_system mutex and log_sys mutex contentions

fils_system mutex contention is killing on all IO-bound workloads (and that's why testing Read-Only workloads is important too to better understand the problems on Read+Write ;-))

bunch of other lock contentions is blocking MariaDB here..

Observations

Checkpoint Age did not reach even 2GB

no LRU single page flush either

all ok..

Observations

free page rate is much higher, but not enormous (I've already observed 70K-80K pages/sec on this server with Fusion-IO on IO-bound workloads)

page rate on MariaDB is lower than on MySQL 5.7

Compression : indeed, compression on IO level is much more simple an optimal for any application (not only databases), and more and more will come over a time for sure with a goal to reach as max as possible a performance level of "uncompressed" workload..

LinkBench : seems to me needs some more love and more testing.. - would be happy to discuss with developers and any other observations related to this workload. It give me an impression to be "self limited" as with 64 concurrent users CPU is not really used, looks like there are some internal waits are happening during processing. I've also suspected garbage collection time on JVM level, but monitoring JVM stats showed that it's not so.. Open to any discussion ;-)

[mysqld] max_connections=4000 # myisam key_buffer_size=4000M ft_max_word_len = 16 low_priority_updates=1 # general table_open_cache = 8000 table_open_cache_instances=16 back_log=1500 query_cache_type=0 # files innodb_file_per_table innodb_log_file_size=1024M innodb_log_files_in_group=12 innodb_open_files=4000 # buffers innodb_buffer_pool_size= 75000M / 16000M innodb_buffer_pool_instances=32 innodb_log_buffer_size=64M # tune innodb_checksums=1 innodb_checksum_algorithm=crc32 innodb_doublewrite= 0 / 1 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 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 specific: innodb_compression_algorithm=0 # innodb_mtflush_threads=16 # innodb_use_mtflush=1 # with atomic writes : innodb_use_fallocate=1 innodb_use_atomic_writes=1



Categories:

Posted by Dimitri at 17:44Categories: MySQL

Well, I may be wrong as well, but the following is what I'm observing on LinkBench workload on my system.There are 5 test cases executed in the following order:Each test case was running during 30min (if was several hours initially, but then I've decided to reduce the test duration time --because once the load became stable on the beginning, then not too much is happening later, and I don't have unlimited time ahead ;-))So far, why MariaDB is getting a lower TPS than MySQL 5.7 ?..Let's get a look on what Performance Schema is reporting:Let's check there is nothing abnormal going with page flushing :What is the page IO read rate in this workload?Well, all observed is confirming my initial worry.Now, let's see how both engines will perform within "expected" test conditions (Facebook Team is mentioning in LinkBench notes that the data set should be bigger at least x10 times than a Buffer Pool size to reproduce their environment).. -- and for sure, having only 16GB Buffer Pool within the same workload will involve much more page IO reads, where storage IO level will play a huge role.What about internal contentions?Any flushing related issues?What about page IO read rate?Well, yet another reason why MySQL 5.7 just rocks and will be the next the best ever MySQL release :-)I'm not pretending either the config setting I'm using and workload conditions are the most optimal here. I'm just sharing what I'm observing and curious for any input/idea about how this workload performance could be improved..Few other notes:MySQL configuration setting I've used :Any comments are welcome! ;-)Rgds,-Dimitri