Menu Fermer

Comment optimiser MySQL

MySQL est une base de données très populaire.
Si ce dernier est mal configuré, vous pouvez rencontrer des ralentissements sur vos applications WEB.
Pire dans certains cas, cela peut faire planter le serveur WEB et votre site WordPress.

Un des symptômes de ces mauvais réglages est une utilisation à 100% du processus MySQL

Cet article vous aide à optimiser et accélérer MySQL.

Introduction

Cet article traite de mauvais réglages de MySQL qui peuvent provoquer des satures de la base de données.
En découle alors des problèmes au niveau des applications utilisent cette base de données.

Cet article part du principe que les applications qui utilisent la base de données sont optimisées dans le cas où ce sont des applications maisons (index, cache, etc).
Dans le cas observé, il s’agit d’une mauvaise configuration de MySQL qui fait que le site WordPress est ralenti, voire parfois, cela fait saturer à son tour le serveur WEB.

En utilisant la commande top, on identifie rapidement que le processus MySQL est constamment à 100%, le load de la machine s’envole alors.

Comment optimiser MySQL

En cas de plantage du serveur WEB, un minimum d’investigation en amont, comme une vérification des logs du serveur Apache et une vérification des requêtes effectuées (avec un SHOW PROCESSLIST;) est nécessaire.
Parfois, il peut s’agir d’attaques de type brute-forces ou un site abandonné ou oublié qui n’est pas à jour et à la merci de Spambot qui peuvent saturer MySQL.

Comment optimiser MySQL

Présentation des paramètres MySQL

MySQL possède beaucoup de paramètres qui peuvent influer sur ses performances et comportements.
Notamment, il existe notamment beaucoup de paramètres liés aux cache des requêtes qui sont à régler selon la configuration matérielle de la machine.
Ainsi, selon la quantité de mémoire que l’on souhaite allouer à MySQL, quelques ajustement peuvent être nécessaires.

Pour rappel le fichier de conf se trouve à l’emplacement /etc/mysql/my.cnf (selon la distribution utilisée).
La plupart de ces paramètres se placent dans la section [mysqld].

Paramètres généraux des caches MySQL

Parmi les paramètres de caches les plus utiles, on trouve :

  • table_open_cache : il s’agit du nombre maximal de tables que le serveur peut ouvrir. Lorsque ce chiffre est atteint, toute nouvelle session éjecte la table qui n’a pas été utilisée depuis le plus longtemps.
  • query_cache il s’agit du cache pour les requêtes de type SELECT, la réponse d’un select en cache est plus rapide qu’un select hors cache. Si les select sont redondants, il est fortement conseillés de l’activer.
  • tmp_table_size C’est la taille maximale de la mémoire utilisée par les tables temporaires. Au delà elles sont écrites sur le disque ce qui amoindrit les performances.
  • max_heap_table_size permet de fixer la taille maximale des tables avec pour moteur de stockage Memory (Heap est l’ancien nom de Memory).
  • key_buffer_size qui stocke les index les plus utilisés pour les tables InnoDB.
  • query_cache_size il s’agit de la taille du cache de MySQL. Pour être utilisé, have_query_cache doit être positionné à YES.

Les paramètres innodb

  • innodb_buffer_pool_size – Le buffer pool est un espace mémoire qui stocke les données en cache et les indexes. Il est donc important de définir la taille maximum pour cet espace. En général, on le règle à 50-70% de la mémoire RAM. Pour plus d’informations, reportez-vous à la documentation MySQL MySQL docs.
  • innodb_flush_log_at_trx_commit – est le paramètres des fichiers logs innodb. Avec le paramètre 1, le plus sûr, le fichier de log est enregistré sur le disque après chaque transaction, à utiliser si vous ne voulez aucune perte de données. Avec le paramètre 0 ou 2, le fichier de log est écrit après chaque commit, ce qui améliore un peu les performances I/O.
  • innodb_flush_method – c’est le paramètre à vérifier et qui doit être réglé sur O_DIRECT pour éviter le double-buffering à moins d’avoir un serveur avec un I/O de faible performance.

Comment vérifier les paramètres sur MySQL

Les paramètres se trouvent dans le fichier de configuration MySQL –  ex : /etc/mysql/my.cnf

On les déclare de la manière suivante :

# * Query Cache Configuration
#
query_cache_size = 64M
key_buffer = 256M
key_buffer_size = 128M
max_allowed_packet = 16M

mais il est possible d’interroger MySQL afin d’obtenir les valeurs de ces paramètres à partir d’un SELECT.
Cela se fait avec la syntaxe suivante : SELECT @@paramètres.

Exemple :

SELECT @@innodb_flush_method;
SELECT @@table_open_cache;

ou à partir des variables globales :

show global variables

sur lesquels on peut appliquer un like.

Comment régler ces paramètres d’optimisation de MySQL ?

Le réglage des variables se fait en général à partir d’informations que l’on récupère depuis MySQL et de calculs selon les résultats obtenus.
La récupération des informations a été expliquée dans le paragraphe précédent.
Voici quelques exemples de réglages de variables de cache MySQL.

Pour régler la variables table_open, il vous suffit de vérifier combien de tables sont ouvertes à travers la requête suivante : show global status like ‘opened_tables’
Si c’est l’écart est trop important, vous pouvez réduire la variable table_open.

En ce qui concerne le paramètre tmp_table_size, il faut le régler max_heap_table_size à la même valeur.
Vous pouvez visualiser le nombre de tables temporaires écrites avec la requête : SHOW GLOBAL STATUS LIKE ‘created_tmp%tables’; et show global variables like ‘tmp_table_size’;

Vous pouvez alors utiliser la formule suivante pour vérifier le ratio de tables temporaires sur le disque :

Tmp_disk_tables=((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables))

Si le résultat dépasse 25%, vous pouvez augmenter la valeur de tmp_table_size.

La plupart des autres variables utilisent le même type de formules.
Par exemple, le réglage de la variable query_cache_size peut être calculée de la manière suivante :

Qcache_hits lass="pun">/(Qcache_hits+Com_select)

Ces calculs et paramétrages peuvent être long, pour gagner du temps, il existe des utilitaires qui le font automatiquement comme MySQLTuner.

MySQLTuner

Il existe plusieurs outils qui analysent les réglages de MySQL et vous proposent de les optimiser.
Parmi les plus connus :

En lançant MySQLTuner, on obtient une liste de paramètres qui sont vérifiées et des alertes quand ces derniers sont mal réglés.
Il faut ensuite évaluer certains des paramètres pour les ajuster selon les besoins et la configuration matérielle du serveur.

 

Sur un MySQL non réglé, on obtient beaucoup d’alertes :

Comment optimiser MySQL

Comment optimiser MySQL

Ci-dessous la ligne Key Buffer size est donnée en correcte par MySQLTuner, mais on peut augmenter sa valeur.
Celle-ci est réglée à 64 Mo pour un total d’index de 116 Mo, ce qui laisse une marge de manœuvre pour l’augmenter..

Comment optimiser MySQL

et sur un MySQL optimisé, MySQLTuner retourne presque aucune erreur.

Comment optimiser MySQL

Comment optimiser MySQL

Par exemple dans le capture ci-dessous, le résultat est bien visible avec une amélioration de la vitesse et de stabilité du site depuis les améliorations des réglages MySQL.

Comment optimiser MySQL

Optimizer les tables

MySQL possède un utilitaire pour optimiser les tables de vos bases de données : mysqloptimize.

  • Optimise les tables utilisant les moteurs InnoDB, MyISQM et Archives.
  • Pour les tables InnoDB, celles-ci seront défragmentées et l’espace disque vide non utilisées sera récupérées.
  • Pour les tables InnoDB, l’optimisation consiste en un alter table pour récupérer l’espace libre.
  • Si vous avez des indexes, ces derniers seront réindexés et les stastistiques mises à jour.

L’optimisation peut être exécutée depuis MySQL avec la commande suivante :

OPTIMIZE TABLE TABLE1,TABLE2,TABLE3

ou depuis l’utilitaire mysqloptimize pour optimiser une base de données :

mysqloptimize -o -B nombd -u root -p

ou si vous voulez optimiser toutes les bases de données MySQL :

mysqloptimize -o -A -u root -p

Liens connexes

et si vous possédez un site WordPress pour accélérer ce dernier : Comment accélérer/optimiser WordPress