I) The Best Performances Ever ? Let's have a closer look

MariaDB 10.1 and MySQL 5.7 have been officially GAed for a few weeks now, and each company claims to have awesome performances. MariaDB claims that the biggest improvement on its new version is scalability on massively multithreaded hardware, reaching more than 1 million queries per second on a simplified OLTP benchmark. On the MySQL side, the same type of improvement is achieved on mixed OLTP_RO benchmark.

II) The Real-World Performances

OLTP benchmarks are great, but what is the behaviour of MySQL / MariaDB on real world applications ? We chose to mesure the performance of MySQL and MariaDB on Prestashop 1.6.1.2 which includes our optimizations to improve both the single-threaded and multi-threaded performances.

1) The Configuration

We used a massively multithreaded hardware, an IBM Power 8 S822LC system with 2x10 cores @2.92GHz running in SMT8 mode (160 threads), 256GB of RAM to have the full DB in memory and running on Ubuntu 15.10.

We tuned the PHP and MySQL/MariaDB configuration to optimize systems with such high amount of threads. PHP Configuration :

• Apache 2.4.10, with mpm_event and the following configuration:

ServerLimit             16
MaxClients              400
StartServers            3
ThreadLimit             64
ThreadsPerChild         25
MaxRequestWorkers       400
MaxConnectionsPerChild  0

• PHP 7.0.0 RC 7 & Memcached + Zend OpCache in PHP-FPM mode with a high start_servers to not slow down the benchmark because of PHP servers creation:

pm =                    dynamic
pm.max_children       = 400
pm.start_servers      = 240
pm.min_spare_servers  = 200
pm.max_spare_servers  = 280

• Both MySQL and MariaDB are using optimized my.cnf to avoid innodb flushes on disk during the benchs. They are compiled from source using the IBM Advance Toolchain 9.0 to take advantage of the IBM Power 8 optimizations. On MariaDB 10.1.8, specific optimizations are used to try to reduce the mutex locking contention points on massively multithreaded hardware:

performance_schema                  = OFF
thread_handling                     = pool-of-threads
thread_pool_size                    = 48 # best performances on P8
#innodb_adaptive_hash_index_partitions / innodb_adaptive_hash_index_part is not efficient enough
innodb_adaptive_hash_index          = OFF 
innodb_stats_on_metadata            = OFF
innodb_buffer_pool_size             = 40G
innodb_buffer_pool_instances        = 40
innodb_sync_array_size              = 48

• On MySQL 5.7.9, the same optimizations are used, but without the pool-of-threads which is not available on the community edition. Anyway, Dimitri from MySQL claims pool-of-threads is not needed anymore to achieve high performances. We also have to disable the new only_full_group_by SQL mode (we filled a bug in mysql bug tracker about it)

We benched a real shop with:

• 1827 categories
• 10843 cart rules
• 1904 products
• 2M product attributes
• 16.5M attribute combinations

Memcached is used with 128MB of memory. When used, the query cache is set to 128MB. The benchmark results are the average of 5 "siege -i -b -t 20S" (20s benchmark), on a few representative pages, which are generating both read and write operations.

2) The Results

First, let's run the benchmark without the query cache enabled.   In this configuration, we can see three things:

• MariaDB 10.1 is faster than MySQL 5.7 at low concurrency, up to 11% @20 concurrent connections. It confirms what we can read from the community, complaining the single threaded performances on the MySQL side are problematics.
• At high concurrency level MySQL is clearly performing and scaling better, up to 25% @220 concurrent connections
• MySQL is performing really well even without the pool-of-threads enabled

Although we often read the query cache is really bad for massively multithreaded hardware because of mutex locking contention, let's take a look at the real results with query cache enabled: In that case, MariaDB 10.1 takes a real benefit from the query cache : it's now faster than MySQL 5.7 in every cases, and up to 266% at 220 connections. At 220 connections, MySQL 5.7 without query cache is also 31% faster than with query cache : we clearly see here the effect of the query cache mutex contention issue. It also shows Prestashop 1.6.1.2 is scaling really well! Here is an additional graph which shows much clearer the difference in performance with and without the query cache with MariaDB 10.1:

3) And the Winner is...

In real world web server configuration, the query cache is often enabled, which is great most of the time. In this configuration, and extrapolating the performance we are seeing with Prestashop, MariaDB 10.1 clearly outperforms MySQL 5.7 on massively multithreaded hardware. We should be able to run a few more tests on multicore Intel processor in a few weeks. We're receiving a lot of questions about our optimizations on the kernel side, more to come on that too, stay tuned and register to our newsletter (no spam, we promise!).