High Performance Drupal with MariaDB
I am back from 2014 Amsterdam Drupalcon where MariaDB Corporation was present as sponsor. It was my first time there and I must say I was really impressed by the amount of people attending the conference (around 2300 people) and the interest that the people showed for MariaDB.
We had many conversations with several kind of engineers, developers, providers and just for a few of them MariaDB was something new to discover; the great majority of them either were already using it or were planning to do it but they did not manage to find some “free” time to do it yet.
What impressed me, was that almost all of the MariaDB happy users just replaced their previous database server installation (MySQL or Percona) with MariaDB with the same approach they might have had for a standard database server upgrade: switch off, backup, install, switch on, done!
Well that’s great and we all know that MariaDB is a drop-in replacement for MySQL and with just this simple migration, all of them experienced a faster website.
What all of them clearly was not aware of is that they can achieve even better performances taking advantage of some of the MariaDB specific features.
We all know that a huge amount of work has been done in MariaDB around optimization since version 5.3 and the list of features is really impressing (check the Optimizer Feature Comparison Matrix) and I am sure the enthusiastic comments on how faster the Drupal websites were after installing MariaDB, have a strong part of their foundations on a mix of JOIN and SUBQUERY and Disk access optimization.
XtraDB Storage Engine
We all know how XtraDB has improved InnoDB and how it has been designed to better work in environments where high performance is crucial. It can scale much better on more cores architecture and use memory more efficiently.
XtraDB is the default MariaDB storage engine so new users will take advantage of it with no specific user action needed.
Sphinx Storage Engine
A lot of Drupal users are Sphinx users as well. I can easily say that no one I talked to was aware of MariaDB SphinxSE that allows users to do full text search with Sphinx search engine and still use the regular database.
This means that Drupal users (but not only them!!) can let Sphinx do what it has been designed to do at its best (indexing, searching, filtering, sorting) and JOIN with other MariaDB tables in a transparent way.
MySQL traditionally assigned a thread for every client connection; of course as the number of concurrent users grows this model shows performance drops. For what I know there are issues in using persistent connections with PDO, that is the interface for accessing databases in PHP and on the other hand if you have many concurrent clients or many users accessing a web site each of them will theoretically open one thread per connection and we know that many active threads are performance killers and it is sometimes not easy to properly set the max_connections limit.
MariaDB allows Drupal users to create a pool of threads that can be reused. Moreover the MariaDB pool is an adaptive pool, this means that it takes care of creating new threads in times of high demand and kill threads if they are idle.
Why are thread pools a good option for Drupal users? Because thread pools are most efficient when queries are relatively short and this often happens when users are reaching a website and the requested page does not hit the cache. So if you are a Drupal user with many concurrent clients or many users that potentially use a thread each simply turn thread pool on in your my.cnf.
https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/... https://mariadb.com/kb/en/mariadb/what-is-the-difference-between-mysql-a... https://mariadb.com/kb/en/mariadb/documentation/storage-engines/xtradb-a...