Aujourd'hui, les développeurs continuent de développer et de déployer des applications en utilisant l'architecture LAMP (Linux®, Apache, MySQL et PHP/Perl). Cependant, les administrateurs de serveur ont souvent peu de contrôle sur les applications eux-mêmes, car celles-ci ont été écrites par quelqu'un d'autre. Cette série en trois parties aborde un certain nombre de problèmes de configuration de serveur pouvant avoir un impact sur les performances des applications. Cet article, la troisième et dernière partie de cette série, se concentrera sur le réglage de la couche base de données pour une efficacité maximale.
Concernant le réglage de MySQL,
il existe trois méthodes pour accélérer la vitesse de fonctionnement du serveur MySQL. L'ordre d'efficacité est le suivant :
Remplacer le matériel problématique. Ajustez les paramètres du processus MySQL. Optimisez la requête.
Le remplacement du matériel problématique est souvent notre première préoccupation, principalement parce que les bases de données peuvent consommer beaucoup de ressources. Mais cette solution a ses limites. En fait, vous pouvez souvent doubler la vitesse de votre unité centrale (CPU) ou disque et augmenter la mémoire de 4 à 8 fois.
La deuxième méthode consiste à régler le serveur MySQL (également connu sous le nom de mysqld). Ajuster ce processus signifie allouer la mémoire de manière appropriée et indiquer à mysqld à quel type de charge il sera soumis. L'accélération du fonctionnement du disque n'est pas aussi importante que la réduction du nombre d'accès au disque requis. De même, garantir que le processus MySQL fonctionne correctement signifie qu'il passe plus de temps à traiter les requêtes qu'à effectuer des tâches en arrière-plan telles que l'utilisation de tables de disques temporaires ou l'ouverture et la fermeture de fichiers. Le réglage de mysqld est au centre de cet article.
La meilleure approche consiste à s’assurer que la requête a été optimisée. Cela signifie que les index appropriés sont appliqués à la table et que les requêtes sont écrites de manière à tirer pleinement parti des fonctionnalités de MySQL. Bien que cet article ne couvre pas le réglage des requêtes (un sujet qui a été abordé dans de nombreux livres), il configure mysqld pour signaler les requêtes pouvant nécessiter un réglage.
Bien que l'ordre ait été attribué à ces tâches, vous devez toujours faire attention au matériel et aux paramètres de mysqld pour régler correctement la requête. Ce n'est pas grave si la machine est lente, j'ai vu des machines très rapides échouer en raison d'une charge importante lors de l'exécution de requêtes bien conçues, car mysqld était occupé par beaucoup de travail et ne pouvait pas répondre à la requête.
Journalisation des requêtes lentes
Dans un serveur SQL, les tables de données sont stockées sur le disque. Les index fournissent au serveur un moyen de rechercher des lignes de données spécifiques dans une table sans avoir à parcourir la table entière. Lorsque la table entière doit être recherchée, cela s’appelle une analyse de table. D'une manière générale, vous souhaiterez peut-être uniquement obtenir un sous-ensemble des données de la table, donc une analyse complète de la table fera perdre beaucoup d'E/S disque, et donc beaucoup de temps. Ce problème est aggravé lorsque les données doivent être jointes, car plusieurs lignes de données des deux côtés de la jointure doivent être comparées.
Bien sûr, les analyses de table ne posent pas toujours de problèmes ; il est parfois plus efficace de lire la table entière que de sélectionner un sous-ensemble de données (le planificateur de requêtes du processus serveur est utilisé pour prendre ces décisions). Si l'index est utilisé de manière inefficace, ou ne peut pas être utilisé du tout, cela ralentira les requêtes, et ce problème deviendra plus important à mesure que la charge sur le serveur et la taille de la table augmenteront. Les requêtes dont l'exécution prend plus de temps qu'une plage de temps donnée sont appelées requêtes lentes.
Vous pouvez configurer mysqld pour enregistrer ces requêtes lentes dans un journal de requêtes lentes correctement nommé. Les administrateurs examineront ensuite ce journal pour les aider à déterminer quelles parties de l'application nécessitent une enquête plus approfondie. Le listing 1 montre la configuration qui doit être effectuée dans my.cnf pour activer la journalisation lente des requêtes.
Listing 1. Activation du journal des requêtes lentes MySQL
[mysqld] ; active le journal des requêtes lentes, par défaut 10 secondeslog-slow-queries ; enregistre les requêtes prenant plus de 5 secondeslong_query_time = 5 ; enregistre les requêtes qui n'utilisent pas d'index même si elles prennent moins de long_query_time ; requêtes-n'utilisant-pas-d'index
Ces trois paramètres sont utilisés ensemble pour enregistrer les requêtes dont l'exécution prend plus de 5 secondes et n'utilisent pas d'index. Veuillez noter l'avertissement concernant les requêtes de journalisation sans utilisation d'index : vous devez utiliser MySQL 4.1 ou supérieur. Les journaux de requêtes lentes sont enregistrés dans le répertoire de données MySQL et sont nommés hostname-slow.log. Si vous souhaitez utiliser un nom ou un chemin différent, vous pouvez utiliser log-slow-queries = /new/path/to/file dans my.cnf pour y parvenir.
Il est préférable de lire les journaux de requêtes lentes via la commande mysqldumpslow. En spécifiant le chemin d'accès au fichier journal, vous pouvez voir une liste triée des requêtes lentes, ainsi que le nombre de fois qu'elles se produisent dans le fichier journal. Une fonctionnalité très utile est que mysqldumpslow supprime toutes les données spécifiées par l'utilisateur avant de comparer les résultats, de sorte que différents appels à la même requête sont comptés comme un seul, ce qui peut aider à identifier la requête qui nécessite le plus de travail.
Requêtes de mise en cache
De nombreuses applications LAMP s'appuient fortement sur des bases de données mais exécutent les mêmes requêtes encore et encore. Chaque fois qu'une requête est exécutée, la base de données doit effectuer le même travail : analyser la requête, déterminer comment l'exécuter, charger les informations à partir du disque et renvoyer les résultats au client. MySQL dispose d'une fonctionnalité appelée cache de requêtes, qui stocke les résultats des requêtes (qui seront utilisés ultérieurement) en mémoire. Dans de nombreux cas, cela améliorera considérablement les performances. Le problème, cependant, est que la mise en cache des requêtes est désactivée par défaut.
Ajoutez query_cache_size = 32M à /etc/my.conf pour activer un cache de requêtes de 32 Mo.
Surveillance du cache de requêtes
Après avoir activé le cache de requêtes, il est important de comprendre s'il est utilisé efficacement. MySQL dispose de plusieurs variables que vous pouvez consulter pour comprendre ce qui se passe dans le cache. Le listing 2 montre l'état du cache.
Listing 2. Affichage des statistiques du cache de requêtes
mysql> AFFICHER LE STATUT COMME 'qcache%';+-----------------------------+------------+ | Nom_variable |+---------------+------------+| Qcache_free_blocks | 14640664 ---+----------------+8 lignes dans un ensemble (0,00 sec)
L’explication de ces éléments est présentée dans le tableau 1.
Tableau 1. Description du nom de la variable du cache de requêtes MySQL
Qcache_free_blocks Le nombre de blocs de mémoire adjacents dans le cache. Un nombre élevé indique qu'il peut y avoir des fragments. FLUSH QUERY CACHE défragmente le cache pour obtenir un bloc libre.
Qcache_free_memory Mémoire libre dans le cache.
Qcache_hits est incrémenté chaque fois qu'une requête arrive dans le cache.
Qcache_inserts est incrémenté à chaque fois qu'une requête est insérée. Le taux d'échec est le nombre de hits divisé par le nombre d'insertions ; soustrayez cette valeur de 1 pour obtenir le taux de réussite. Dans l'exemple ci-dessus, environ 87 % des requêtes atteignent le cache.
Qcache_lowmem_prunes Le nombre de fois où le cache a manqué de mémoire et a dû être purgé pour faire de la place pour davantage de requêtes. Ce nombre est mieux visualisé sur une longue période ; s’il augmente, cela peut indiquer une fragmentation sévère ou une mémoire insuffisante. (Les free_blocks et free_memory ci-dessus peuvent vous indiquer de quel cas il s'agit).
Qcache_not_cached Le nombre de requêtes qui ne conviennent pas à la mise en cache, généralement parce qu'il ne s'agit pas d'instructions SELECT.
Qcache_queries_in_cache Le nombre de requêtes (et de réponses) actuellement mises en cache.
Qcache_total_blocks Nombre de blocs dans le cache.
La différence peut souvent être constatée en affichant ces variables à quelques secondes d'intervalle, ce qui peut aider à déterminer si le cache est utilisé efficacement. L'exécution de FLUSH STATUS peut réinitialiser certains compteurs, ce qui peut être très utile si le serveur est en cours d'exécution depuis un certain temps.
Il est très tentant d’utiliser un très grand cache de requêtes et d’espérer tout mettre en cache. Étant donné que mysqld doit effectuer une maintenance sur le cache, comme effectuer un élagage lorsque la mémoire devient faible, le serveur peut s'enliser en essayant de gérer le cache. En règle générale, si le FLUSH QUERY CACHE prend beaucoup de temps, le cache est trop volumineux.
Application des limites
Vous pouvez imposer des limites dans mysqld pour garantir que la charge du système ne provoque pas l'épuisement des ressources. Le listing 3 montre quelques paramètres importants liés aux ressources dans my.cnf.
Listing 3. Paramètres des ressources MySQL
set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100
Le nombre maximum de connexions est géré en première ligne. Semblable à MaxClients dans Apache, l’idée est de garantir que seul le nombre de connexions autorisé par le service est effectué. Pour déterminer le nombre maximum de connexions actuellement établies sur le serveur, exécutez SHOW STATUS LIKE 'max_used_connections'.
La ligne 2 indique à mysqld de mettre fin à toutes les connexions restées inactives pendant plus de 10 secondes. Dans une application LAMP, le temps nécessaire pour se connecter à la base de données est généralement le temps nécessaire au serveur Web pour traiter la requête. Parfois, si la charge est trop lourde, la connexion se bloque et occupe de l'espace sur la table de connexion. Si vous avez plusieurs utilisateurs interactifs ou utilisez des connexions persistantes à la base de données, il n'est pas conseillé de définir cette valeur à une valeur inférieure !
La dernière ligne est une méthode sûre. Si un hôte a des problèmes de connexion au serveur et réessaye plusieurs fois avant d'abandonner, l'hôte sera verrouillé et ne pourra pas être exécuté avant FLUSH HOSTS. Par défaut, 10 échecs suffisent pour provoquer un verrouillage. Changer cette valeur à 100 donnera au serveur suffisamment de temps pour se remettre du problème. Si la connexion ne peut pas être établie après 100 tentatives, l'utilisation d'une valeur plus élevée ne sera pas d'une grande aide et la connexion risque de ne pas se connecter du tout.
Tampons et mise en cache
MySQL prend en charge plus de 100 paramètres réglables mais heureusement, en maîtriser quelques-uns satisfera la plupart des besoins ; Pour trouver les valeurs correctes pour ces paramètres, vous pouvez afficher les variables d'état via la commande SHOW STATUS, qui peut déterminer si mysqld fonctionne comme prévu. La mémoire allouée aux tampons et aux caches ne peut pas dépasser la mémoire disponible sur le système, le réglage nécessite donc généralement certains compromis.
Les paramètres réglables MySQL peuvent s'appliquer à l'ensemble du processus mysqld ou à des sessions client individuelles.
Paramètres côté serveur
Chaque table peut être représentée comme un fichier sur le disque, qui doit d'abord être ouvert puis lu. Pour accélérer le processus de lecture des données des fichiers, mysqld met en cache ces fichiers ouverts jusqu'à un nombre maximum spécifié par table_cache dans /etc/mysqld.conf. Le listing 4 montre un moyen d'afficher l'activité liée à l'ouverture d'une table.
Listing 4. Afficher les activités qui ouvrent des tables
mysql> AFFICHER LE STATUT COMME 'open%tables';+---------------+-------+ Nom_variable |+-------- -------+-------+| Tables_ouvertes | 5000 || Tables_ouvertes |+--------------+------- +2 lignes dans l'ensemble (0,00 sec)
Le listing 4 montre qu'il y a actuellement 5 000 tables ouvertes et que 195 tables doivent être ouvertes car il n'y a aucun descripteur de fichier disponible dans le cache (puisque les statistiques ont été effacées plus tôt, il peut y avoir seulement 5 000 tables ouvertes pour 195 dossiers d'enregistrement ouverts). . Si Opened_tables augmente rapidement lors de la réexécution de la commande SHOW STATUS, cela indique que le taux de réussite du cache est insuffisant. Si Open_tables est beaucoup plus petit que le paramètre table_cache, la valeur est trop grande (mais avoir de la place pour grandir n'est jamais une mauvaise chose). Par exemple, utilisez table_cache = 5000 pour ajuster le cache de la table.
Semblable au cache de table, il existe également un cache pour les threads. mysqld génère des threads selon les besoins lors de la réception des connexions. Sur un serveur occupé où les connexions changent rapidement, la mise en cache des threads pour une utilisation ultérieure peut accélérer la connexion initiale.
Le listing 5 montre comment déterminer si suffisamment de threads sont mis en cache.
Listing 5. Affichage des statistiques d'utilisation des threads
mysql> AFFICHER LE STATUT COMME 'threads%';+-------------------+--------+ Nom_variable |+---- ---------------+--------+| Threads_cached || Threads_connecté 15 || Threads_running | ---------------+--------+4 lignes dans l'ensemble (0,00 sec)
La valeur importante ici est Threads_created, cette valeur est incrémentée à chaque fois que mysqld doit créer un nouveau thread. Si ce nombre augmente rapidement lors de l'exécution de commandes SHOW STATUS successives, vous devez essayer d'augmenter le cache des threads. Par exemple, vous pouvez utiliser thread_cache = 40 dans my.cnf pour y parvenir.
Le tampon de clé contient le bloc d'index de la table MyISAM. Idéalement, les requêtes pour ces blocs devraient provenir de la mémoire plutôt que du disque. Le listing 6 montre comment déterminer combien de blocs ont été lus sur le disque et combien ont été lus depuis la mémoire.
Listing 6. Détermination de l'efficacité des mots clés
mysql> affiche le statut comme '%key_read%';+-----------------------+----------+ Variable_name |+ ----+----------+| Requêtes_lecture_clé || -----------+-----------+2 lignes dans l'ensemble (0,00 sec)
Key_reads représente le nombre de requêtes arrivant sur le disque et Key_read_requests est le nombre total. Le taux d'échecs correspond au nombre de requêtes de lecture qui atteignent le disque divisé par le nombre total de requêtes de lecture - dans ce cas, environ 0,6 échecs en mémoire pour 1 000 requêtes. Si le nombre d'accès au disque dépasse 1 pour 1 000 requêtes, vous devez envisager d'augmenter la mémoire tampon des mots clés. Par exemple, key_buffer = 384M définira le tampon sur 384 Mo.
Les tables temporaires peuvent être utilisées dans des requêtes plus avancées où les données doivent être enregistrées dans une table temporaire avant un traitement ultérieur (comme une clause GROUP BY, idéalement, la table temporaire est créée en mémoire) ; Mais si la table temporaire devient trop volumineuse, elle doit être écrite sur le disque. Le listing 7 donne des statistiques liées à la création de tables temporaires.
Listing 7. Détermination de l'utilisation des tables temporaires
mysql> AFFICHER LE STATUT COMME 'created_tmp%';+-------------------------+-------+| Valeur_nom_variable | | +--------------+-------+| Tables_tmp_créées || | +-----------+-------+3 lignes dans l'ensemble (0,00 sec)
Created_tmp_tables sera augmenté chaque fois qu'une table temporaire est utilisée ; Created_tmp_disk_tables sera également augmenté pour les tables basées sur disque. Il n’y a pas de règles strictes pour ce ratio, car il dépend de la requête concernée. Regarder Created_tmp_disk_tables au fil du temps vous montrera le ratio de tables de disques créées et vous pourrez déterminer l'efficacité de votre configuration. tmp_table_size et max_heap_table_size contrôlent tous deux la taille maximale des tables temporaires, alors assurez-vous que les deux valeurs sont définies dans my.cnf.
Paramètres par session
Les paramètres suivants sont spécifiques à chaque session. Soyez très prudent lors du paramétrage de ces nombres car multipliés par le nombre de connexions pouvant exister, ces options représentent une grande quantité de mémoire ! Vous pouvez modifier ces numéros au sein d'une session via du code, ou modifier ces paramètres dans my.cnf pour toutes les sessions.
Lorsque MySQL doit trier, il alloue un tampon de tri pour contenir les lignes de données au fur et à mesure de leur lecture sur le disque. Si les données à trier sont trop volumineuses, elles doivent être enregistrées dans un fichier temporaire sur le disque et triées à nouveau. Si la variable d'état sort_merge_passes est grande, cela indique une activité du disque. Le listing 8 montre quelques informations de compteur d'état liées au tri.
Listing 8. Affichage des statistiques de tri
mysql> AFFICHER LE STATUT COMME "sort%";+-------------------+---------+ Nom_variable |+--- ----------------+---------+| Sort_merge_passes || Sort_range || -------------------+---------+4 lignes dans l'ensemble (0,00 sec)
Si sort_merge_passes est grand, cela signifie que vous devez faire attention à sort_buffer_size. Par exemple, sort_buffer_size = 4M définit le tampon de tri sur 4 Mo.
MySQL alloue également de la mémoire pour lire la table. Idéalement, un index fournit suffisamment d'informations pour lire uniquement les lignes dont vous avez besoin, mais parfois une requête (mal conçue ou en raison de la nature des données) doit lire une grande quantité de données de la table. Pour comprendre ce comportement, vous devez savoir combien d'instructions SELECT ont été exécutées et combien de fois la ligne de données suivante de la table a dû être lue (plutôt que d'y accéder directement via l'index). La commande permettant d'obtenir cette fonctionnalité est présentée dans le listing 9.
Listing 9. Détermination du taux de numérisation de la table
mysql> AFFICHER LE STATUT COMME "com_select";+---------------+--------+ Nom_variable |+--------- ------+--------+| Com_select |+---------+--------+1 ligne dans l'ensemble (0,00 sec) mysql> AFFICHER LE STATUT COMME "handler_read_rnd_next";+-----------------------+--------------- +| Variable_name | |+-------------+-----------+| Gestionnaire_read_rnd_next |+- ----- -------------------+----------+1 ligne dans l'ensemble (0,00 sec)
Handler_read_rnd_next / Com_select donne un taux d'analyse de table - dans ce cas 521:1. Si la valeur dépasse 4000, vous devez vérifier read_buffer_size, par exemple read_buffer_size = 4M. Si ce nombre dépasse 8M, il est temps de discuter du réglage de ces requêtes avec les développeurs !
3 Outils essentiels
Bien que la commande SHOW STATUS puisse être très utile pour comprendre une configuration spécifique, vous aurez également besoin de certains outils pour interpréter les grandes quantités de données fournies par mysqld. Il existe trois outils que je trouve essentiels ; vous pouvez trouver des liens vers eux dans la section Ressources.
La plupart des administrateurs système connaissent la commande top, qui fournit une vue continuellement mise à jour du processeur et de la mémoire consommés par les tâches. mytop émule top ; il fournit une vue de tous les clients connectés et des requêtes qu'ils exécutent. mytop fournit également des données en direct et historiques sur l'efficacité du tampon de mots clés et du cache de requêtes, ainsi que des statistiques sur les requêtes en cours d'exécution. Il s'agit d'un outil utile pour voir ce qui se passe dans votre système (disons dans les 10 secondes), vous pouvez obtenir une vue des informations sur l'état du serveur et afficher toutes les connexions qui posent des problèmes.
mysqlard est un démon connecté au serveur MySQL, chargé de collecter les données toutes les 5 minutes et de les stocker dans une base de données Round Robin en arrière-plan. Il existe une page Web qui affiche des données telles que l'utilisation du cache de table, l'efficacité des mots clés, les clients connectés et l'utilisation des tables temporaires. Bien que mytop fournisse un instantané des informations sur l'état du serveur, mysqlard fournit des informations sur l'état à long terme. En prime, mysqlard utilise certaines des informations recueillies pour donner quelques suggestions sur la façon de régler le serveur.
Un autre outil pour collecter les informations SHOW STATUS est mysqlreport. Son reporting est bien plus complexe que celui de mysqlard car chaque aspect du serveur doit être analysé. Il s'agit d'un excellent outil pour régler votre serveur car il effectue des calculs appropriés sur les variables d'état pour vous aider à déterminer quels problèmes doivent être résolus.
Conclusion
Cet article a introduit quelques connaissances de base sur le réglage de MySQL et a conclu cette série en 3 parties sur le réglage des composants LAMP. Le réglage implique en grande partie de comprendre le fonctionnement des composants, de déterminer s'ils fonctionnent correctement, d'effectuer quelques ajustements et de réévaluer. Chaque composant – Linux, Apache, PHP ou MySQL – a des exigences différentes. Comprendre chaque composant individuellement peut aider à réduire les goulots d'étranglement qui peuvent ralentir votre application.