{"id":4240,"date":"2014-12-26T09:18:38","date_gmt":"2014-12-26T08:18:38","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/mysql-versions-performance-comparison\/"},"modified":"2014-12-26T09:18:38","modified_gmt":"2014-12-26T08:18:38","slug":"mysql-versions-performance-comparison","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/mysql-versions-performance-comparison\/","title":{"rendered":"MySQL versions performance comparison"},"content":{"rendered":"

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.\u00a0 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):<\/p>\n

MySQL 5.0<\/h2>\n

1. New compact storage format which can save up to 20% of the disk space required in previous MySQL\/InnoDB versions.
\n2. Faster recovery from a failed or aborted ALTER TABLE.
\n3. Faster implementation of TRUNCATE TABLE.<\/p>\n

MySQL 5.5<\/h2>\n

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.
\n2. 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.<\/p>\n

MySQL 5.6<\/h2>\n

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.
\n2. 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.
\n3. 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.<\/p>\n

MySQL 5.7<\/h2>\n

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.<\/p>\n

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.<\/p>\n

You can find an exhaustive performance improvement list on:<\/p>\n

http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/mysql-nutshell.html<\/a><\/p>\n

http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/mysql-nutshell.html<\/a><\/p>\n

http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/mysql-nutshell.html<\/a><\/p>\n

http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/mysql-nutshell.html<\/a><\/p>\n

<\/h4>\n

Test limitations<\/h2>\n

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.<\/p>\n

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.<\/p>\n

MySQL performance test<\/h2>\n

Hardware configuration<\/h3>\n

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.<\/p>\n

First step: Installation<\/h3>\n

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:<\/p>\n\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n
\n

Editor\/Provider<\/strong><\/p>\n<\/td>\n

\n

MySQL Server<\/strong><\/p>\n<\/td>\n

\n

Version<\/strong><\/p>\n<\/td>\n

\n

Port<\/strong><\/p>\n<\/td>\n

\n

Edition<\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

Oracle<\/p>\n<\/td>\n

\n

mysqld1<\/p>\n<\/td>\n

\n

5.0.15<\/p>\n<\/td>\n

\n

33001<\/p>\n<\/td>\n

\n

Community Edition<\/p>\n<\/td>\n<\/tr>\n

\n

Oracle<\/p>\n<\/td>\n

\n

mysqld2<\/p>\n<\/td>\n

\n

5.1.73<\/p>\n<\/td>\n

\n

33002<\/p>\n<\/td>\n

\n

Community Edition<\/p>\n<\/td>\n<\/tr>\n

\n

Oracle<\/p>\n<\/td>\n

\n

mysqld3<\/p>\n<\/td>\n

\n

5.5.39<\/p>\n<\/td>\n

\n

33003<\/p>\n<\/td>\n

\n

Community Edition<\/p>\n<\/td>\n<\/tr>\n

\n

Oracle<\/p>\n<\/td>\n

\n

mysqld4<\/p>\n<\/td>\n

\n

5.6.20<\/p>\n<\/td>\n

\n

33004<\/p>\n<\/td>\n

\n

Community Edition<\/p>\n<\/td>\n<\/tr>\n

\n

Oracle<\/p>\n<\/td>\n

\n

mysqld5<\/p>\n<\/td>\n

\n

5.7.4<\/p>\n<\/td>\n

\n

33005<\/p>\n<\/td>\n

\n

Community Edition<\/p>\n<\/td>\n<\/tr>\n

\n

Oracle<\/p>\n<\/td>\n

\n

mysqld6<\/p>\n<\/td>\n

\n

5.6.21<\/p>\n<\/td>\n

\n

33006<\/p>\n<\/td>\n

\n

Enterprise Edition<\/p>\n<\/td>\n<\/tr>\n

\n

Percona<\/p>\n<\/td>\n

\n

mysqld7<\/p>\n<\/td>\n

\n

5.6.20<\/p>\n<\/td>\n

\n

33007<\/p>\n<\/td>\n

\n

N\/A<\/p>\n<\/td>\n<\/tr>\n

\n

Mariadb<\/p>\n<\/td>\n

\n

mysqld8<\/p>\n<\/td>\n

\n

10.0.15<\/p>\n<\/td>\n

\n

33008<\/p>\n<\/td>\n

\n

N\/A<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

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.<\/p>\n

The only variables which have been set up are the following:<\/p>\n