Les bases de données SQL et plus particulièrement MySQL restent une des pierres angulaires de l’immense majorité des sites internet. MySQL fonctionne très bien out of the box, cependant, dès que la base se trouve assez sollicitée, on s’aperçoit que les réglages par défaut méritent une petite optimisation. Jetons un œil à tout ça !

Récemment, un des serveurs MariaDB d’un client commençait à avoir de sacrés pics de load lors des heures de forte charge, avec MySQL pour principal responsable. Une grosse centaine de connexions simultanées et quelques dizaines de requêtes par seconde sur des bases de plusieurs dizaines de giga. C’est un peu plus que le WordPress moyen, mais rien d’ingérable pour un MySQL bien réglé.

Dès lors, avant d’imaginer prendre un serveur plus puissant, sharder les tables ou je ne sais quoi encore, il faut tirer le maximum de notre cher SGBDR.

Nous nous concentrerons ici sur le moteur InnoDB, lequel tend à supplanter MyISAM. Cependant, ce n’est pas la réponse magique à tous les types d’usages. Par ailleurs, même si nous parlons de MySQL, c’est avec son fork MariaDB que j’ai l’habitude de travailler depuis quelques années. Vu le peu de soin qu’Oracle apporte au développement de MySQL, Maria est même devenu la variante MySQL par défaut dans Debian.

Dans la suite de cet article, nous travaillerons la plupart du temps directement depuis le shell MySQL pour afficher et modifier les réglages (qui pourront ensuite être répercutés dans le fichier de conf pour subsister au redémarrage).

Aperçu d’ensemble

Tout n’est pas à prendre au pied de la lettre, mais si le serveur SQL tourne depuis quelques temps (au moins quelques heures, au mieux quelques jours), un petit coup de MySQLTuner peut fournir un bon aperçu des réglages de base, de son usage et des axes d’amélioration potentiels.

Une bonne partie des variables qui pourront nous intéresser pour le diagnostique sont les variables de global status, on les affichera donc avec un petit :

SHOW GLOBAL STATUS;

# de nombreuses variables de conf sont accessible avec
SHOW GLOBAL VARIABLES;

On pourra éventuellement filtrer tout cela avec l’opérateur like si l’on cherche quelque chose de précis.

Les connexions

MySQLTuner vous l’a sûrement déjà mentionné, mais on peut vérifier le nombre de connexions maximum autorisées. Et le nombre de connexions maximum qui ont été ouvertes en même temps.

SELECT @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               100 |
+-------------------+

SHOW GLOBAL STATUS like "%used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 98    |
+----------------------+-------+

Dans le cas présent, il peut s’avérer judicieux d’augmenter la limite. Cela n’allègera certainement pas les ressources consommées par le serveur, tout au contraire, mais permettra de servir plus de clients à la fois. Sinon, lorsque le max_connections est atteint, les nouvelles connexions sont mises en file d’attente jusqu’à ce qu’une place se libère.

Il ne s’agit donc pas d’augmenter cette limite à l’infini, mais si votre serveur peut tolérer plus de connexions et que vous vous approchez ou atteignez le maximum autorisé, n’hésitez pas à l’augmenter.

Les caches

table_open_cache

Pour voir la limite du table open cache :

SELECT @@table_open_cache;
\+--------------------+
| @@table_open_cache |
\+--------------------+
|                400 |
\+--------------------+

Chaque session concurrente accède aux tables de manière indépendante, cela permet un gain de performance au détriment d’une consommation mémoire un peu plus importante (forcement, lorsqu’on met des choses en cache…).

table_open_cache indique le nombre maximum de tables que le serveur peut laisser ouvertes. Ainsi, ces tables sont réutilisées au lieu d’être ré-ouvertes. Lorsque ce chiffre est atteint, toute nouvelle session éjecte la table qui n’a pas été utilisée depuis le plus longtemps.

Afin de savoir s’il peut être bénéfique d’augmenter ce nombre, on regarde le nombre de tables ouvertes :

show global status like 'opened_tables';
\+---------------+-------+
| Variable_name | Value |
\+---------------+-------+
| Opened_tables | 211   |
\+---------------+-------+

Il est évidemment à mettre en relation avec le temps depuis lequel la base de données est démarrée. Quelques centaines de tables ouvertes en plusieurs semaines ne pose pas de problème. En revanche, si ce nombre augmente rapidement, on aura tout intérêt à augmenter ce chiffre jusqu’à ce que le nombre de tables ouvertes se stabilise.

query_cache

Le query_cache sauvegarde les résultats des SELECT et permet de les retourner ensuite bien plus efficacement et rapidement lors des SELECT consécutifs. Il peut être très intéressant de l'activer dans le cas de site web où les mêmes requêtes sont souvent répétées.

Le query cache n’est pas actif par défaut sur toutes les versions de MySQL ou MariaDB. Avant toute chose, voyons donc s’il est disponible et actif :

SELECT @@have_query_cache;
\+--------------------+
| @@have_query_cache |
\+--------------------+
| YES                |
\+--------------------+

# il est dispo, est-il actif ?

SELECT @@query_cache_size;
\+--------------------+
| @@query_cache_size |
\+--------------------+
|                  0 |
\+--------------------+

# afficher les stats du query_cache

SHOW GLOBAL STATUS LIKE 'Q%';
\+-------------------------+----------+
| Variable_name           | Value    |
\+-------------------------+----------+
| Qcache_free_blocks      | 2813     |
| Qcache_free_memory      | 42007088 |
| Qcache_hits             | 19826725 |
| Qcache_inserts          | 22279221 |
| Qcache_lowmem_prunes    | 860544   |
| Qcache_not_cached       | 4775166  |
| Qcache_queries_in_cache | 5724     |
| Qcache_total_blocks     | 14732    |
| Queries                 | 98211573 |
| Questions               | 98211573 |
\+-------------------------+----------+

À partir de la version 10.1.7 de MariaDB, le serveur active automatiquement le cache si le query_cache_size est supérieur à zero. L'état du cache peut être vérifié avec la variable @@query_cache_state.

# réglage du cache à 1MB
SET GLOBAL query_cache_size = 1000000;

Le query_cache n'est pas la solution à tous les problèmes et il a ses propres inconvénients. Le cache utlise un verou exclusif lorsque des valeurs lui sont ajoutées. Si sa taille est trop faible, de nombreuses valeurs seront effacées – pour faire place à de nouvelles – alors qu'elles auraient pu être utilisées. Si sa taille est trop importante, les performances seront dégradées dues à de trop nombreux lock car il y aura trop d'ajouts de nouvelles valeurs.

En outre, il faut savoir que dès qu'une table reçoit un update, toutes les valeurs associées dans le cache sont supprimées. Ainsi, l'espace utilisé ne peut pas augmenter de manière exponentielle et, si le cache est trop gros, l'invalidation peut prendre du temps.

D'une manière générale, 100 à 200MB représente le grand maximum pour la valeur du cache, mais seule l'expérience saura dire si la valeur est la bonne. Cela dépend en effet de votre trafic et du matériel utilisé.

On commence donc avec une valeur faible et on surveille Qcache_free_blocks et Qcache_lowmem_prunes. Cette dernière valeur indique le nombre de requêtes éjectées du cache par manque de mémoire. L'idéal est de trouver l'équilibre entre une valeur Qcache_lowmem_prunes qui n'augmente pas trop tout en utilisant efficacement la mémoire disponnible. Ainsi si Qcache_lowmem_prunes est faible et que Qcache_free_blocks est important, on peut diminuer la taille du cache.

Comme le cache possède des inconvénients et peut-être invalidé souvent dans les tables où il y a beaucoup d'écritures, il est essentiel de calculer le hit rate de ce cache. La formule est la suivante ((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100). Dans le cas de faible résultat, il peut être judicieux de désactiver le cache et d'observer quelles sont les performances obtenues.

tmp_table_size

Bien qu'il ne s'agisse pas de cache à proprement parler, tmp_table_size définit la taille maximale que les tables temporaires peuvent prendre en RAM. Au delà elles sont écrites sur le disque.

Il suffit de comparer les variables Created_tmp_disk_tables et Created_tmp_tables afin de voir si une grosse proportion des tables sont écrites sur disque. Si c'est le cas, il peut être intéressant d'accorder un espace plus important à tmp_table_size et max_heap_table_size. Cette seconde variable concerne les tables MEMORY mais la plus basse de ces deux variables s'appliquera aux tables temporaires.

innodb_buffer_pool_size

Le buffer pool stoque les données et les indexes en mémoire de manière à éviter au maximum les I/O disque. Vous l'aurez donc compris, c'est un élément essentiel des performances du moteur InnoDB.

Sur un serveur dédié à la base de données, on pourra lui consacrer 70% à 80% du total de la RAM du serveur. Sur une machine où les ressources sont partagées avec d'autres services, à vous de voir combien allouer à la BDD et combien vous devez réserver pour le reste. Gardez à l'esprit que la mémoire allouée sera généralement 10% supérieure à ce que vous spécifiez car le système utilise de la mémoire supplémentaire pour les structures de contrôle et les buffers.

Il est par ailleur à mettre en relation avec la taille de vos données et des index. S'il n'est pas toujours possible d'allouer assez d'espace pour que tout tienne en RAM, les index doivent y tenir et une bonne partie des données est conseillé.

innodb_flush_method

Cette variable spécifie pour les systèmes UNIX (aucun effet sur Windows) la manière dont InnoDB lit les données et vide le cache. Généralement O_DIRECT est conseillé afin d'éviter que l'OS mette en buffer des données déjà cachées par InnoDB.

innodb_log_file_size

Avec le buffer pool size, ces deux paramètres conditionnent une grosse partie des performances d’InnoDB. Le moteur de table fonctionne en mémoire, cependant, pour survire à un crash, toutes les modifications sont loggées dans un fichier de transactions.

La taille de ce fichier conditionne donc la taille des données modifiées qui peuvent n'être conservées qu'en mémoire dans un laps de temps donné. Moins d'I/O donc meilleures performances. Néanmoins, plus ce fichier est gros, plus le temps de récupération après crash sera élevé.

Si vous voulez creuser le sujet, il existe de bons articles \[en\] sur le blog de Percona.

innodb_flush_log_at_trx_commit

Sur un système avec beaucoup d’écriture, si les propriétés ACID ne sont pas primordiales et que vous pouvez vous permettre de perdre la dernière seconde de transaction en cas de crash de l’OS (ou de coupure de courant), vous pouvez mettre cette valeur à 2 plutôt qu’à 1 (par défaut). Le buffer de transactions sera alors écrit après chaque commit plutôt qu’après chaque transaction, vous gagnerez donc un peu en I/O.

Gestions des threads

InnoDB peut créer de nombreux threads, chacun consommant du temps processeur. Il est possible de limiter ce nombre, auquel cas, lorsque la valeur de innodb_thread_concurrency est atteinte, les processus à créer sont placés dans une file d’attente. La valeur conseillée est de deux fois le nombre de CPU plus le nombre de disque.

Optimiser les requêtes

La base peut être aussi bien optimisée que possible, s’il s’avère qu’elle est requêtée n’importe comment, sans index etc, il ne sera pas possible de tirer de bonnes performances.

On peut se faire une idée rapide des requêtes en cours d’exécution avec SHOW PROCESSLIST. Par ailleurs, dans le fichier de config /etc/mysql/my.cnf, on pourra activer le slow query log afin de voir quelles requêtes sont lentes à exécuter, et consomme donc des ressources.

Une fois des requêtes potentiellement problématiques identifiées, vous pourrez les profiler directement dans la console SQL. La commande SHOW PROFILE détaille les ressources utilisées par la base de données pour l’exécution d’une requête donnée.

# on active le profiling
sql > SET profiling = 1;

# on exécute ensuite le requête à examiner
sql > SELECT id, fname, slug, address JOIN Meta on user.id = meta.user_id;

# on affiche ensuite le résultat du profilage
sql > SHOW PROFILE;

SHOW PROFILE affiche la dernière requête profilée. Vous pouvez utiliser SHOW PROFILES pour afficher l’ensemble des requêtes profilées et ensuite fournir l’id de la requête à examiner à la commande SHOW PROFILE.

Enfin, afin de comprendre comment le moteur SQL exécute la requête, on utilisera la commande EXPLAIN.

sql > EXPLAIN SELECT id, fname, slug, address JOIN Meta on user.id = meta.user_id;

Ainsi, fort de détails sur les étapes d’exécution de notre requête, nous pourrons sans doute l’améliorer.

Théoriquement, vous avez là déjà des réglages qui permettront de bien adapter votre base à son usage ainsi qu’en adéquation avec le matériel sur lequel elle est installée. Bien entendu, des centaines d’autres paramètres existent, mais nous avons là passé en revue les principaux concernant InnoDB.

N’hésitez pas à faire part de votre expérience en commentaire. Et si quelques optimisations que je n’ai pas listé donnent de bons résultats, share the love !