Tutoriel pour apprendre à optimiser les performances de MySQL

Quentin Busuttil

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 chargement (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-octets. 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 (partitionnement horizontal) 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 configuration (conf) pour subsister au redémarrage). Commentez

- Titre : Tutoriel pour apprendre à optimiser les performances de MySQL

- Auteur : Quentin Busuttil

- Parution : 19 juin 2017

- Licence : Copyright ® 2017 Quentin Busuttil. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Aperçu d'ensemble

- II. Les connexions

- III. Les caches

- IV. Gestions des threads

- V. Optimiser les requêtes

- VI. Note de la rédaction de Developpez.com

I - Aperçu d'ensemble

Tout n'est pas à prendre au pied de la lettre, mais si le serveur SQL tourne depuis quelque 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 diagnostic sont les variables de global status, on les affichera donc avec un petit :

SHOW GLOBAL STATUS;

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

II - Les connexions

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

1. SELECT @@max_connections;

2. +-------------------+

3. | @@max_connections |

4. +-------------------+

5. |               100 |

6. +-------------------+

8. SHOW GLOBAL STATUS like "%used_connections";

9. +----------------------+-------+

10. | Variable_name        | Value |

11. +----------------------+-------+

12. | Max_used_connections | 98    |

13. +----------------------+-------+

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.

III - Les caches

table_open_cache

1. Pour voir la limite du table-open-cache :

1. SELECT @@table_open_cache;

2. +--------------------+

3. | @@table_open_cache |

4. +--------------------+

5. |                400 |

6. +--------------------+

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, lorsque l'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 rouvertes. 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 :

1. show global status like 'opened_tables';

2. +---------------+-------+

3. | Variable_name | Value |

4. +---------------+-------+

5. | Opened_tables | 211   |

6. +---------------+-------+

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 posent 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

1. 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 :

1. SELECT @@have_query_cache;

2. +--------------------+

3. | @@have_query_cache |

4. +--------------------+

5. | YES                |

6. +--------------------+

8. # il est dispo, est-il actif ?

9. SELECT @@query_cache_size;

10. +--------------------+

11. | @@query_cache_size |

12. +--------------------+

13. |                  0 |

14. +--------------------+

À partir de la version 10.1.7 de MariaDB, le serveur active automatiquement le cache si le query_cache_size est supérieur à zéro. 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

1. 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 est écrite 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

1. Le buffer_pool stocke les données et les index 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

1. 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

1. 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 survivre à 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

1. Sur un système avec beaucoup d'écritures, 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.

IV - 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 d'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 disques.

V - 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 consomment 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.

1. # on active le profiling

2. sql > SET profiling = 1;

4. # on exécute ensuite la requête à examiner

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

7. # on affiche ensuite le résultat du profilage

8. 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ées donnent de bons résultats, share the love!

VI - Note de la rédaction de Developpez.com

Nous tenons à remercier Quentin Busuttil qui nous a aimablement autorisés à publier son tutoriel : Optimiser les performances de MySQL. Nous remercions également Winjerome pour la mise au gabarit et Jacques Jean pour la relecture orthographique