MYSQL - Recherche des causes de lenteur d'un serveur de base
Sommaire
Introduction
Cet article décrit l'ensemble des actions pouvant être menées pour détecter un problème ralentissant directement ou indirectement le fonctionnement d'une base mysql ou d'un serveur de base mysql.
Causes de lenteur d'un serveur MySQL
Causes réseau
Dans cette catégorie se classent les causes de ralentissement liées à des phénomènes de "goulet d'étranglement" au niveau TCP.
En synthèse, l'ensemble des accès aux différents ports TCP ouverts sur le serveur est si important que cela ralentit ou rend impossible (timeout) par la même occasion l'accès au port 3306.
Ces causes ne sont valables que pour une machine faisant office de serveur de base, i.e. dont le port 3306 écoute sur l'interface publique du serveur.
Causes système
Dans cette catégorie se classent les causes de ralentissement liées à des effets de bord engendrés par le fonctionnement global du système.
Si, sur la machine portant le serveur de bases, des processus systèmes non liés (*) provoquent une surcharge processeur, mémoire ou réseau (**) le serveur de bases peut encore une fois servir ses réponses plus lentement ou ne pas les servir du tout (timeout).
Un exemple typique de ce genre de causes est survenu lors de la mise en place de PHP-Syslog-NG sur un serveur faisant office de serveur de bases: les traitements de nettoyage de la base de donnée liée à Syslog-NG, lancés par la cron à intervalles très réguliers, se faisaient par l'intermédiaires de scripts PHP dont la compléxité était telle que la machine ne disposait pas d'assez de ressources processeur pour parvenir à traiter la demande. Ces ralentissements provoquaient par effet de ricochet de gros dysfonctionnement sur les services s'appuyant sur le serveur de bases puisque les requêtes de ces services ne pouvaient plus être traitées.
* ne faisant pas partie de l'ensemble des processus directement liés à MySQL
** impossibilité ou lenteur pour ouvrir un socket à destination du serveur de bases
Causes applicative
Causes liées au serveur
Dans cette catégorie se classent les causes de ralentissement liées à la configuration du service MySQL lui même.
Un exemple simple de ce genre de problème: laisser les logs activés. Les temps d'accès au disque pour logger les requêtes et les infos de fonctionnement du serveur induisent de grosses lenteurs dans le fonctionnement réel de MySQL (le service ne gère pas de requêtes quand il écrit sur le disque=.
Causes liées à la base de donnée
Dans cette catégorie se classent les causes de ralentissement liées à des problèmes de conception des accès aux bases et aux tables. On parle ici de problèmes spécifiques à MySQL et à des algorithmes associés.
On peut notamment provoquer des surcharges, et par conséquent des lenteurs voir des blocages, de MySQL quand on lui demande de faire des accès non optimisés aux tables: jointures en trop grand nombre, ou sur un trop grand nombre de tables.
L'exemple cité plus haut concernant PHP-Syslog-NG fait aussi partie de ces causes: outre la trop grande complexité des algorithmes implémentés en PHP, les requêtes de tri dans la base étaient elles aussi d'une trop grande complexité et provoquaient des lenteurs ou des refus d'accès à MySQL de ce fait.
On fait, dans ce type de cas, référence à des problèmes de slow queries ou requêtes lentes: la lourdeur d'exécution de ces requêtes entraine des latences dans les réponses du serveur de base. Ces latences sont paramétrables, quand on cherche à détecter de telles requêtes, en fonction des performances que l'on attend du serveur.
Méthodes de détection
Rapport MySQL
La commande mysqlreport permet d'obtenir un rapport d'utilisation de MySQL en temps réel.
L'idéal, pour exploiter le plus judicieusement possible ces rapports, est de disposer d'un rapport post-installation, alors que le SGBD est fraichement installé et qu'il est très peu sollicité. Cela permet d'avoir une "baseline" de comparaison, une référence.
Une autre solution quand on ne dispose pas de cette baseline est de comparer des rapports édités à 2 dates différentes pour pouvoir comparer les valeurs et observer des évolutions.
root@hikaru:~# mysqlreport --password --all Password for database user root: ****** MySQL 5.0.38-Ubuntu_0ub uptime 1 21:10:17 Wed Feb 25 09:00:59 2009 __ Key _________________________________________________________________ Buffer used 825.00k of 16.00M %Used: 5.04 Current 2.33M %Usage: 14.57 Write ratio 0.619 Read ratio 0.003 <= 0.01 OK (1) __ Questions ___________________________________________________________ Total 1.42M 8.7/s [...] -Unknown 358 0.0/s 0.03 Slow 152.28k 0.9/s 10.76 %DMS: 51.75 %DMS <= 0.05 KO!!!!! (2) DMS 294.24k 1.8/s 20.79 SELECT 208.45k 1.3/s 14.73 70.84 UPDATE 47.98k 0.3/s 3.39 16.31 INSERT 24.21k 0.1/s 1.71 8.23 DELETE 13.60k 0.1/s 0.96 4.62 REPLACE 0 0/s 0.00 0.00 [...] __ Table Locks _________________________________________________________ Waited 8.05k 0.0/s %Total: 2.45 %Total <= 10% OK (3) Immediate 320.33k 2.0/s [...] root@hikaru:~#
Commentaires:
- Si le Read Ratio dépasse la valeur de 0,01, cela signifie en général que le serveur manque de RAM, et qu'il est nécessaire d'en rajouter. Attention ceci dit à bien vérifier la capacité de sa version de MySQL à prendre en charge la quantité de RAM allouée.
- Par défaut, et sauf configuration explicite dans my.cnf, MySQL considère comme lente 1 requête prenant plus de 10 secondes. La dernière valeur sur la ligne (%DMS) devrait être inférieure à 0,05. Au dela d'une valeur de 0,30 la documentation indique qu'on doit se rendre compte de problèmes (sans préciser lesquels). C'est la valeur qu'il importe le plus de diminuer.
- Le verrouillage (lock) de tables est le mécanisme utilisé par MySQL pour permettre le partage de données entre des utilisateurs simultanés. Le pourcentage total de requêtes en attente de verrouillage de table (i.e. en attente d'exécution) doit d'après la documentation être inférieur à 10%, sans quoi cela implique de manière non ambigue la présence de Slow Queries.
Slow Queries
Mise en place du loggage
Comme indiqué sur la page dédiée aux requêtes lentes sur le site de MySQL, il y a plusieurs méthodes pour mettre en place la journalisation des requêtes lentes.
- par un passage d'option au démon lors de son lancement
- par la modification du fichier de configuration du démon
Ci dessous un extrait de my.cnf contenant les configurations essentielles à cette journalisation:
# * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. #log = /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log NB: bien vérifier les droits sur ce fichier, si non loggage passer en 777 le temps des tests puis supprimer long_query_time = 5 log-queries-not-using-indexes
Vérification des valeurs
Vérification de la présence de slow queries sur le serveur:
root@hikaru:~# mysqladmin -p status Enter password: Uptime: 7588219 Threads: 14 Questions: 43446820 Slow queries: 112 Opens: 135009 Flush tables: 90 Open tables: 52 Queries per second avg: 5.726
Détermination du Top 10 des requêtes
- Passer le long_query_time à 1 et redémarrer MySQL. Toutes les requêtes sont loggées.
- Laisser ce loggage remplir le fichier pendant environ 1 journée.
- Après cette journée, rechercher le top 10 des requêtes les plus lentes grâce à la commande mysqlsla.
Exemple de résultat de mysqlsla:
Report for slow logs: /var/log/mysql/mysql-slow.log
8.72k queries total, 53 unique
Sorted by 't_sum'
Grand Totals: Time 1.14k s, Lock 78 s, Rows sent 13.53k, Rows Examined 122.80M
______________________________________________________________________ 001 ___
Count : 1.44k (16.46%)
Time : 958 s total, 667.131 ms avg, 0 to 3 s max (83.81%)
95% of Time : 872 s total, 639.296 ms avg, 0 to 1 s max
Lock Time (s) : 18 s total, 12.535 ms avg, 0 to 1 s max (23.08%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 0 avg, 0 to 0 max (0.00%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database : ent
Users :
portail@chapu.mondomaine.fr 10.5.1.12 : 34.33% (493) of query, 33.71% (2941) of all users
portail@chapi.mondomaine.fr 10.5.1.3 : 33.29% (478) of query, 32.88% (2869) of all users
portail@chapo.mondomaine.fr 10.5.1.6 : 32.38% (465) of query, 31.75% (2770) of all users
Query abstract:
UPDATE up_ss_user_parm SET param_val='S' WHERE user_id=N AND profile_id=N AND ss_id=N AND ss_type=N AND param_name='S';
Query sample:
UPDATE UP_SS_USER_PARM SET PARAM_VAL='layout' WHERE USER_ID=1905 AND PROFILE_ID=1 AND SS_ID=3 AND SS_TYPE=1 AND PARAM_NAME='current_structure';
______________________________________________________________________ 002 ___
Count : 1.43k (16.40%)
Time : 92 s total, 64.291 ms avg, 0 to 2 s max (8.05%)
95% of Time : 17 s total, 12.509 ms avg, 0 to 1 s max
Lock Time (s) : 53 s total, 37.037 ms avg, 0 to 2 s max (67.95%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 1 avg, 1 to 1 max (10.58%)
Rows examined : 33.79k avg, 33.79k to 33.79k max (39.37%)
Database : ent
Users :
portail@chapu.mondomaine.fr 10.5.1.12 : 34.45% (493) of query, 33.71% (2941) of all users
portail@chapi.mondomaine.fr 10.5.1.3 : 33.47% (479) of query, 32.88% (2869) of all users
portail@chapo.mondomaine.fr 10.5.1.6 : 32.08% (459) of query, 31.75% (2770) of all users
Query abstract:
SELECT param_val FROM up_ss_user_parm WHERE user_id=N AND profile_id=N AND ss_id=N AND ss_type=N AND param_name='S';
Query sample:
SELECT PARAM_VAL FROM UP_SS_USER_PARM WHERE USER_ID=1905 AND PROFILE_ID=1 AND SS_ID=3 AND SS_TYPE=1 AND PARAM_NAME='current_structure';
______________________________________________________________________ 003 ___
Count : 62 (0.71%)
Time : 41 s total, 661.29 ms avg, 0 to 1 s max (3.59%)
95% of Time : 37 s total, 637.931 ms avg, 0 to 1 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 1 avg, 1 to 1 max (0.46%)
Rows examined : 642.59k avg, 642.59k to 642.59k max (32.44%)
Database : syslog
Users :
nagios2@monitor2.mondomaine.fr 10.4.1.24 : 66.13% (41) of query, 0.47% (41) of all users
nagios@monitor.mondomaine.fr 10.4.1.10 : 33.87% (21) of query, 0.24% (21) of all users
Query abstract:
SELECT COUNT(*) FROM logs WHERE msg='S';
Query sample:
select count(*) from `logs` where `msg`='check_logserver: Nagios Log Server UDP Check 1235539516.74';
______________________________________________________________________ 004 ___
[...]
Les solutions
En synthèse les solutions sont assez peu nombreuses:
- concernant les problèmes de configuration du service MySQL, il faut s'assurer que les paramètres du service sont bien en adéquation avec les paramètres du "serveur physique" (mémoire, temps d'accès disque, etc) et avec les contraintes imposées par d'autres services tournant sur le même serveur physique.
- concernant les problèmes de conception de base, il vaut mieux en général déléguer le travail aux concepteurs, l'idée étant d'éviter des requêtes algorithmiquement très lourdes (cf exemple des jointures cité plus haut), d'accélérer la recherche des données par la création et l'utilisation de vues, et de créer des index pour toutes les tables sur lesquelles les mêmes requêtes sont exécutées de façon récurrente.
Pour les administrateurs n'étant pas très à l'aise avec la manipulation de MySQL en CLI, il est possible d'effectuer toutes les opérations d'optimisation de bases grâce à des interfaces graphiques:
- applis web comme phpmyadmin (l'inconvénient est alors lié aux failles de sécurité de l'outil),
- client lourd comme HeidiSQL (l'inconvénient est alors d'ouvrir le service sur l'interface publique du serveur).
Dans des cas extrèmes où la base est trop grosse pour être gérée par 1 seul serveur, il est possible d'améliorer le service en mettant plusieurs MySQL en cluster. Le gain est alors un gain de performance et de fiabilité (meilleure résistance aux pannes).
Liens utiles
Documentation officielle MySQL 5.1 sur le loggage des Slow Queries
Guide d'isolation des Slow Queries pour les newbies, en Anglais
Guide d'interprétation du rapport MySQL, en Anglais
Lien vers l'utilitaire MySQL mysqlsla, non fournit par défaut
