Optimiser les performances de MySQL

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 de Peerus 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 |
+--------------------+

À 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;
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éresser 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.

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ètre conditionne 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.

Néanmoins, plus ce fichier est gros, plus le temps de récupération après crash sera élevé.

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 !

Déjà 2 réponses, rejoignez la discussion !

  • f4b1

    dit :

    Pas mal du tout ces différents axes d’optimisation, je vais en mettre plusieurs en place sur mon serveur des que j’ai un peu de temps. Ca pourrait bien améliorer les choses de mon coté.

    • Buzut

      dit :

      Merci pour ton commentaire. Je serai curieux que tu nous en dises un peu plus sur les résultats obtenus après optimisation. C’est toujours intéressant d’avoir des feedback de différents cas concrets !

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *