MySQL versions performance comparison

This blog aims to make a performance comparison between the different MySQL versions/editions and also comparing the differents MySQL forks such as Percona Server and MariaDB.  Indeed number of improvements as been done to innodb storage engine in the last MySQL versions. You can find below some of the performance improvements applied to InnoDB these last years (non exhaustive list):

MySQL 5.0

1. New compact storage format which can save up to 20% of the disk space required in previous MySQL/InnoDB versions.
2. Faster recovery from a failed or aborted ALTER TABLE.
3. Faster implementation of TRUNCATE TABLE.

MySQL 5.5

1. MySQL Enterprise Thread Pool, As of MySQL 5.5.16, MySQL Enterprise Edition distributions include a thread pool plugin that provides an alternative thread-handling model designed to reduce overhead and improve performance.
2. Changes to the InnoDB I/O subsystem enable more effective use of available I/O capacity. The changes also provide more control over configuration of the I/O subsystem.

MySQL 5.6

1. Improvements to the algorithms for adaptive flushing make I/O operations more efficient and consistent under a variety of workloads. The new algorithm and default configuration values are expected to improve performance and concurrency for most users. Advanced users can fine-tune their I/O responsiveness through several configuration options.
2. InnoDB has several internal performance enhancements, including reducing contention by splitting the kernel mutex, moving flushing operations from the main thread to a separate thread, enabling multiple purge threads, and reducing contention for the buffer pool on large-memory systems.
3. You can now set the InnoDB page size for uncompressed tables to 8KB or 4KB, as an alternative to the default 16KB. This setting is controlled by the innodb_page_size configuration option. You specify the size when creating the MySQL instance. All InnoDB tablespaces within an instance share the same page size. Smaller page sizes can help to avoid redundant or inefficient I/O for certain combinations of workload and storage devices, particularly SSD devices with small block sizes.

MySQL 5.7

1. In MySQL 5.7.2, InnoDB buffer pool dump and load operations are enhanced. A new system variable, innodb_buffer_pool_dump_pct, allows you to specify the percentage of most recently used pages in each buffer pool to read out and dump. When there is other I/O activity being performed by InnoDB background tasks, InnoDB attempts to limit the number of buffer pool load operations per second using the innodb_io_capacity setting.

2. As of MySQL 5.7.4, InnoDB supports multiple page cleaner threads for flushing dirty pages from buffer pool instances. A new system variable, innodb_page_cleaners, is used to specify the number of page cleaner threads. The default value of 1 maintains the pre-MySQL 5.7.4 configuration in which there is a single page cleaner thread. This enhancement builds on work completed in MySQL 5.6, which introduced a single page cleaner thread to offload buffer pool flushing work from the InnoDB master thread.

You can find an exhaustive performance improvement list on:

http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html

http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html

http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html

http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

Test limitations

This test won’t take into consideration all new possible optimizations provided through new variables and functionnalities. The aim of this one is simply to demonstrate the performance improvement with a non optimized but consistent configuration. In this context, a limited set of variables available in all MySQL versions (since version 5.0) have been set up.

This test is obvisously not representative of your own environnement (hardware, queries, database schema, storage engine, data type, etc..). Therefore you probably won’t have the same performance behavior.

MySQL performance test

Hardware configuration

This test has been done with sysbench 0.5, it has been run on a laptop equiped with a Processor Intel(R) Core(TM) i7-4700MQ CPU @ 2.40GH and 16Go RAM. The data are stored on a Samsung SSD 840 PRO Series.

First step: Installation

The first step consists in installing several different MySQL versions. Thanks to mysql_multi I’ve been able to run the following versions in parallel:

These servers have been setup with the same settings. However depending on the MySQL version, the default MySQL settings are different. For instance, on MySQL 5.0.15 the default value for global variable innodb_buffer_pool_size is 8388608 wheras on MySQL 5.1.73 the default value is 134217728. The default MySQL version settings have not been changed.

The only variables which have been set up are the following:

• max_connections = 8000
• table_open_cache=8000
• open_files_limit = 8192

max_connections: The maximum permitted number of simultaneous client connections
table_open_cache: (or table_cache): The number of open tables for all threads:
open_files_limit: The number of files that the operating system permits mysqld to open. The value of this variable at runtime is the real value permitted by the system and might be different from the value you specify at server startup.

The OFA (Optimal Flexible Architecture) directory structure has been used to install the MySQL Servers.

You can find below an example of this structure:

port           = 33001
mysqladmin     = /u00/app/mysql/product/mysql-5.0.15/bin/mysqladmin
mysqld         = /u00/app/mysql/product/mysql-5.0.15/bin/mysqld
socket         = /u00/app/mysql/admin/mysqld1/socket/mysqld1.sock
pid-file       = /u00/app/mysql/admin/mysqld1/socket/mysqld1.pid
log-error      = /u00/app/mysql/admin/mysqld1/log/mysqld1.err
datadir        = /u01/mysqldata/mysqld1
basedir        = /u00/app/mysql/product/mysql-5.0.15

Second step: Test preparation

Once all MySQL Server installed and running, the second step is to prepare the table containing the records where the queries will be performed. In this test I decided to create only one table. This one is automatically named sbtest1 by sysbench. Notice that it is possible to create several tables by using “oltp-table-count” parameter.

The number of rows in this table is specified by the parameter “oltp-table-size”. This test table will contain 20’000’000 rows. The test mode is OLTP. According to sysbench documentation, this test mode was written to benchmark a real database performance.

At the prepare stage the following table is created:


mysql> desc sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| k     | int(10) unsigned | NO   | MUL | 0       |                |
| c     | char(120)        | NO   |     |         |                |
| pad   | char(60)         | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
Each record contains random strings in the fields c and pad and random integers between 1 and oltp-table-size in the field k as presented in the following picture:

Sysbench prepare script:

sysbench 
--db-driver=mysql 
--mysql-table-engine=innodb 
--oltp-table-size=20000000 
--mysql-socket=/u00/app/mysql/admin/mysqld1/socket/mysqld1.sock 
--mysql-port=33301 
--mysql-db=sysbench 
--mysql-user=sbtest 
--mysql-password=sbtest 
--test=/home/mysql/sysbench/oltp.lua 
prepare

In order to be sure to have the same set of data on each server a MySQL dump has been done on the server after the first load. This dump has been imported on each server.

Third step: Running the test

The test has been run with different number of threads in order to understand how the different version/edition and fork of MySQL scale depending on the number of threads. The parameter max-request limits the total number of requests. The OLTP test mode (oltp.lua) has been written to improve performance’s benchmarking of database servers by providing a realistic scenario of an OLTP database.

sysbench
--db-driver=mysql
--test=oltp
--num-threads=1
--mysql-user=sbtest
--mysql-password=sbtest
--mysql-db=sysbench
--max-requests=10000
--oltp-test-mode=complex
--test=/home/mysql/sysbench/oltp.lua
--mysql-socket=/u00/app/mysql/admin/mysqld1/socket/mysqld1.sock
--oltp-table-name=sbtest1
run

In order to ensure correct results, avoiding any side effects due to external process and ensuring consistent results over time, the benchmark has been run twice.

Fourth step: Collecting results

All the results have been collected in an excel sheet and the following graph directly comes from these results:

System Summary (MySQL 5.6.21 and Percona 5.6.21 are configured with Thread pool enabled):

disk Total KB/s:

Fifth step: results analysis

1. innodb has been improved over time in regards of scalability and the tests results tempt to proove that. The performance with 64 threads are radically different depending on the MySQL Version:

MySQL 5.0.15 – 1237 tps
MySQL 5.1.73 – 1818 tps
MySQL 5.5.39 –  2978 tps
MySQL 5.6.20 – 2801 tps
MySQL 5.6.21 – 2830 tps
MySQL 5.7.4 – 2725 tps
Percona 5.6.21 – 2853 tps
Mariadb 10.0.15 – 2941 tps

2. For application using only one thread the peformance between MySQL version (with default settings) is more or less equivalent (+/-10%):

MySQL 5.0.15 – 163 tps
MySQL 5.1.73 – 158 tps
MySQL 5.5.39 –  150 tps
MySQL 5.6.20 – 145 tps
MySQL 5.6.21 – 149 tps
MySQL 5.7.4 – 145 tps
Percona 5.6.21 – 145 tps
Mariadb 10.0.15 – 143 tps

3. For large number of threads it definitively worth to use pool of threads plugin from Percona. During these tests a improvement factor of x30 has been observed. Unfortunately I didn’t see any performance improvement with MySQL 5.6.21 with the thread_pool plugin and thread_pool_size parameter set to 36 ( Best performances with Sysbench according to http://dev.mysql.com/doc/refman/5.6/en/thread-pool-tuning.html) . Regarding Percona I set up the parameter thread_pool_high_prio_mode to transactions. You can find below the results with 4096 thread:

MySQL 5.0.15 – error
MySQL 5.1.73 – 3.97 tps
MySQL 5.5.39 –  9.05 tps
MySQL 5.6.20 – 9.29 tps
MySQL 5.6.21 – 9.07 tps
MySQL 5.6.21 pool of thread plugin – 8.75
MySQL 5.7.4 – 5.64 tps
Percona 5.6.21 – 9.83 tps
Percona 5.6.21 pool of thread plugin – 295.4 tps
Mariadb 10.0.15 – 8.04 tps

It is interesting to notice that performance degradation can occur with the thread pool plugin activated for MySQL and for Percona. This performance degradation has been observed for a number of thread between 16 and 128 for Percona and 32 and 512 with MySQL.

Conclusion

These results tempt to prove that last MySQL releases perform better than older ones especially with several threads (64 threads in this case). The only exception is MySQL 5.7.4 which is a development release.
Applications using only one thread won’t benefit from a huge performance improvement with the last MySQL versions. However enhancements provided in last versions such as ONLINE DDL, faster deadlock detection, dynamic innodb_buffer_pool_size parameter, etc, etc.. will for sure save you lots of time.
MySQL forks such as Percona and MariaDB, perform as MySQL Server. In addition I didn’t observe any performance difference between MySQL Enterprise Edition and MySQL Community Edition. It is interesting to notice that thread pool plugin provided by Percona provide a huge performance improvement with large number of threads compared to standard behavior.
Regarding MySQL Enterprise Edition I haven’t been able to see any performance improvement with MySQL Thread Pool plugin activated even with large number of threads. This is perhaps due to a misconfiguration from my side… however I presented these results to an Oracle MySQL specialist present on the Oracle UKOUG booth and he hasn’t been able to find any error in my configuration.