MariaDB 10.1 vs MySQL 5.7: Real-World Performances
I) The Best Performances Ever ? Let's have a closer lookMariaDB 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 PerformancesOLTP 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 220.127.116.11 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)
- 1827 categories
- 10843 cart rules
- 1904 products
- 2M product attributes
- 16.5M attribute combinations
2) The ResultsFirst, 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