Ensuring continuity and open collaboration

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.

 

10 Comments

  1. Tim Callaghan Tim Callaghan
    2014-09-09    

    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?

    • Mark Callaghan Mark Callaghan
      2014-09-11    

      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?

  2. 2014-09-09    

    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

    • Mark Callaghan Mark Callaghan
      2014-09-11    

      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.

    • Jan Lindström Jan Lindström
      2014-09-12    

      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.

  3. Dimitri Dimitri
    2014-09-10    

    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

    • Jan Lindström Jan Lindström
      2014-09-12    

      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.

      • Torben Mathiasen Torben Mathiasen
        2014-09-12    

        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

  4. Mark Callaghan Mark Callaghan
    2014-09-11    

    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.

    • Jan Lindström Jan Lindström
      2014-09-12    

      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.

No Pings Yet

  1. MySQL Performance: More in depth with LinkBench Workload on MySQL 5.7, MariaDB 10.1 and also Percona Server 5.6 | InsideMySQL on 2014-09-20 at 01:02

Leave a Reply

Your email address will not be published. Required fields are marked *

Sponsors

MariaDB Foundation sponsors

Tweets by @mariadbfdn

Code statistics