Surveillance des métriques de performance MySQL

author
19 minutes, 38 seconds Read

Cet article est la partie 1 d’une série en 3 parties sur la surveillance MySQL. La partie 2 concerne la collecte de métriques à partir de MySQL, et la partie 3 explique comment surveiller MySQL à l’aide de Datadog.

Qu’est-ce que MySQL ?

MySQL est le serveur de base de données relationnelle open source le plus populaire au monde. Appartenant à Oracle, MySQL est disponible dans l’édition communautaire téléchargeable gratuitement ainsi que dans les éditions commerciales avec des fonctionnalités et un support supplémentaires. Initialement publié en 1995, MySQL a depuis engendré des forks de haut niveau pour des technologies concurrentes telles que MariaDB et Percona.

Statistiques MySQL clés

Si votre base de données s’exécute lentement ou ne répond pas aux requêtes pour une raison quelconque, chaque partie de votre pile qui dépend de cette base de données souffrira également de problèmes de performances. Afin de garantir le bon fonctionnement de votre base de données, vous pouvez surveiller activement les métriques couvrant quatre domaines de performance et d’utilisation des ressources:

  • Débit des requêtes
  • Performances d’exécution des requêtes
  • Connexions
  • Utilisation du pool de tampons

Les utilisateurs de MySQL peuvent accéder à des centaines de métriques de la base de données, donc dans cet article, nous nous concentrerons sur une poignée de métriques clés qui vous permettront d’obtenir un aperçu en temps réel de la santé et des performances de votre base de données. Dans la deuxième partie de cette série, nous vous montrerons comment accéder et collecter toutes ces métriques.

Cet article fait référence à la terminologie métrique introduite dans notre série Monitoring 101, qui fournit un cadre pour la collecte de métriques et les alertes.

Compatibilité entre les versions et les technologies

Certaines des stratégies de surveillance discutées dans cette série sont spécifiques aux versions 5.6 et 5.7 de MySQL. Les différences entre ces versions seront soulignées en cours de route.

La plupart des métriques et des stratégies de surveillance décrites ici s’appliquent également aux technologies compatibles MySQL telles que MariaDB et Percona Server, avec quelques différences notables. Par exemple, certaines fonctionnalités de l’atelier MySQL, qui sont détaillées dans la partie 2 de cette série, ne sont pas compatibles avec les versions actuellement disponibles de MariaDB.

Les utilisateurs d’Amazon RDS devraient consulter nos guides de surveillance spécialisés pour MySQL sur RDS et pour Amazon Aurora compatible MySQL.

Débit de requête

 Requêtes MySQL
Nom Description Type métrique Disponibilité
Questions Nombre d’instructions exécutées (envoyées par le client) Travail : Débit Variable d’état du serveur
Com_select Instructions de sélection Travail : Débit Variable d’état du serveur
Écrit Insère, met à jour ou supprime le travail : Débit Calculé à partir de variables d’état du serveur

Votre principale préoccupation dans la surveillance de tout système est de vous assurer que son travail est effectué efficacement. Le travail d’une base de données exécute des requêtes, donc votre première priorité de surveillance doit être de vous assurer que MySQL exécute les requêtes comme prévu.

MySQL a un compteur interne (une « variable d’état du serveur », en langage MySQL) appelé Questions, qui est incrémenté pour toutes les instructions envoyées par les applications clientes. La vue centrée sur le client fournie par la métrique Questions la rend souvent plus facile à interpréter que le compteur Queries associé, qui compte également les instructions exécutées dans le cadre de programmes stockés, ainsi que des commandes telles que PREPARE et DEALLOCATE PREPARE exécutées dans le cadre d’instructions préparées côté serveur.

Pour interroger une variable d’état du serveur telle que Questions ou Com_select:

SHOW GLOBAL STATUS LIKE "Questions";+---------------+--------+| Variable_name | Value |+---------------+--------+| Questions | 254408 |+---------------+--------+

Vous pouvez également surveiller la répartition des commandes de lecture et d’écriture pour mieux comprendre la charge de travail de votre base de données et identifier les goulots d’étranglement potentiels. Les requêtes de lecture sont généralement capturées par la métrique Com_select. Les écritures incrémentent l’une des trois variables d’état, en fonction de la commande :

Écritures = Com_insert + Com_update + Com_delete

Mesure sur laquelle alerter: Questions

Le taux actuel de requêtes augmentera et diminuera naturellement, et en tant que tel, ce n’est pas toujours une mesure exploitable basée sur des seuils fixes. Mais il vaut la peine d’alerter sur les changements soudains du volume des requêtes — des baisses drastiques du débit, en particulier, peuvent indiquer un problème grave.

Performances de la requête

 Graphique de latence MySQL
Nom Description Type métrique Disponibilité
Temps d’exécution de la requête Temps d’exécution moyen, par schéma Travail: Performance Requête de schéma de performance
Erreurs de requête Nombre d’instructions SQL qui ont généré des erreurs Travail: Erreur Requête de schéma de performance
Slow_queries Nombre de requêtes dépassant la limite configurable long_query_time Travail: Performance Variable d’état du serveur

Les utilisateurs de MySQL disposent d’un certain nombre d’options pour surveiller la latence des requêtes, à la fois en utilisant les métriques intégrées de MySQL et en interrogeant le schéma de performances. Activée par défaut depuis MySQL 5.6.6, les tables de la base de données performance_schema dans MySQL stockent des statistiques de bas niveau sur les événements du serveur et l’exécution des requêtes.

Résumé des instructions de schéma de performance

De nombreuses mesures clés sont contenues dans la table events_statements_summary_by_digest du schéma de performance, qui capture des informations sur la latence, les erreurs et le volume de requêtes pour chaque instruction normalisée. Un exemple de ligne du tableau montre une instruction qui a été exécutée deux fois et qui a pris 325 millisecondes en moyenne à exécuter (toutes les mesures de temporisation sont en picosecondes):

*************************** 1. row *************************** SCHEMA_NAME: employees DIGEST: 0c6318da9de53353a3a1bacea70b4fce DIGEST_TEXT: SELECT * FROM `employees` WHERE `emp_no` > ? COUNT_STAR: 2 SUM_TIMER_WAIT: 650358383000 MIN_TIMER_WAIT: 292045159000 AVG_TIMER_WAIT: 325179191000 MAX_TIMER_WAIT: 358313224000 SUM_LOCK_TIME: 520000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 520048 SUM_ROWS_EXAMINED: 520048... SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2016-03-24 14:25:32 LAST_SEEN: 2016-03-24 14:25:55

La table digest normalise toutes les instructions (comme on le voit dans le champ DIGEST_TEXT ci-dessus), en ignorant les valeurs de données et en normalisant les espaces et les majuscules, de sorte que les deux requêtes suivantes soient considérées comme identiques:

select * from employees where emp_no >200;SELECT * FROM employees WHERE emp_no > 80000;

Pour extraire un temps d’exécution moyen par schéma en microsecondes, vous pouvez interroger le schéma de performances:

SELECT schema_name , SUM(count_star) count , ROUND( (SUM(sum_timer_wait) / SUM(count_star)) / 1000000) AS avg_microsec FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-------+--------------+| schema_name | count | avg_microsec |+--------------------+-------+--------------+| employees | 223 | 171940 || performance_schema | 37 | 20761 || sys | 4 | 748 |+--------------------+-------+--------------+

De même, pour compter le nombre total d’instructions par schéma qui ont généré des erreurs:

SELECT schema_name , SUM(sum_errors) err_count FROM performance_schema.events_statements_summary_by_digest WHERE schema_name IS NOT NULL GROUP BY schema_name;+--------------------+-----------+| schema_name | err_count |+--------------------+-----------+| employees | 8 || performance_schema | 1 || sys | 3 |+--------------------+-----------+

Le schéma sys

Interroger le schéma de performance comme indiqué ci-dessus fonctionne très bien pour récupérer par programme des métriques de la base de données. Pour les requêtes et enquêtes ad hoc, cependant, il est généralement plus facile d’utiliser le schéma sys de MySQL. Le schéma sys fournit un ensemble organisé de métriques dans un format plus lisible par l’homme, ce qui simplifie considérablement les requêtes correspondantes. Par exemple, pour trouver les instructions les plus lentes (celles du 95e centile par exécution):

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

Ou pour voir quelles instructions normalisées ont généré des erreurs:

SELECT * FROM sys.statements_with_errors_or_warnings;

De nombreux autres exemples utiles sont détaillés dans la documentation du schéma sys. Le schéma sys est inclus dans MySQL à partir de la version 5.7.7, mais les utilisateurs de MySQL 5.6 peuvent l’installer avec seulement quelques commandes. Voir la partie 2 de cette série pour les instructions.

Requêtes lentes

En plus de la richesse des données de performance disponibles dans le schéma de performance et le schéma sys, MySQL dispose d’un compteur Slow_queries, qui s’incrémente chaque fois que le temps d’exécution d’une requête dépasse le nombre de secondes spécifié par le paramètre long_query_time. Le seuil est fixé à 10 secondes par défaut:

SHOW VARIABLES LIKE 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+

Le paramètre long_query_time peut être réglé avec une seule commande. Par exemple, pour définir le seuil de requête lente à cinq secondes:

SET GLOBAL long_query_time = 5;

( Notez que vous devrez peut-être fermer votre session et vous reconnecter à la base de données pour que la modification soit appliquée au niveau de la session.)

Enquête sur les problèmes de performances des requêtes

Si vos requêtes s’exécutent plus lentement que prévu, il arrive souvent qu’une requête récemment modifiée en soit le coupable. Si aucune requête n’est jugée trop lente, les prochaines choses à évaluer sont des métriques au niveau du système pour rechercher des contraintes dans les ressources de base (CPU, E/S de disque, mémoire et réseau). La saturation du processeur et les goulots d’étranglement d’E / S sont des coupables courants. Vous pouvez également vérifier la métrique Innodb_row_lock_waits, qui compte la fréquence à laquelle le moteur de stockage InnoDB a dû attendre pour acquérir un verrou sur une ligne particulière. InnoDB est le moteur de stockage par défaut depuis la version 5.5 de MySQL, et MySQL utilise le verrouillage au niveau des lignes pour les tables InnoDB.

Pour augmenter la vitesse des opérations de lecture et d’écriture, de nombreux utilisateurs voudront ajuster la taille du pool de tampons utilisé par InnoDB pour mettre en cache les données de table et d’index. Plus d’informations sur la surveillance et le redimensionnement du pool de tampons ci-dessous.

Métriques à alerter sur

  • Temps d’exécution de la requête : La gestion de la latence pour les bases de données clés est essentielle. Si le temps d’exécution moyen des requêtes dans une base de données de production commence à augmenter, recherchez les contraintes de ressources sur vos instances de base de données, les conflits possibles pour les verrous de lignes ou de tables et les modifications des modèles de requêtes côté client.
  • Erreurs de requête: Une augmentation soudaine des erreurs de requête peut indiquer un problème avec votre application cliente ou votre base de données elle-même. Vous pouvez utiliser le schéma sys pour explorer rapidement les requêtes susceptibles de poser des problèmes. Par exemple, pour répertorier les 10 instructions normalisées qui ont renvoyé le plus d’erreurs:
      SELECT * FROM sys.statements_with_errors_or_warnings ORDER BY errors DESC LIMIT 10;  
  • Slow_queries: La façon dont vous définissez une requête lente (et donc comment vous configurez le paramètre long_query_time) dépend de votre cas d’utilisation. Quelle que soit votre définition de « lent », vous voudrez probablement enquêter si le nombre de requêtes lentes dépasse les niveaux de base. Pour identifier les requêtes réelles qui s’exécutent lentement, vous pouvez interroger le schéma sys ou plonger dans le journal de requêtes lentes optionnel de MySQL, qui est désactivé par défaut. Plus d’informations sur l’activation et l’accès au journal des requêtes lentes sont disponibles dans la documentation MySQL.

Connexions

 Connexions MySQL
Nom Description Type métrique Disponibilité
Threads_connected Connexions actuellement ouvertes Ressource: Utilisation Variable d’état du serveur
Threads_running Connexions en cours d’exécution Ressource: Utilisation Variable d’état du serveur
Connection_errors_internal Nombre de connexions refusées en raison d’une erreur de serveur Ressource : Erreur Variable d’état du serveur
Aborted_connects Nombre de tentatives de connexion échouées au serveur Ressource: Erreur Variable d’état du serveur
Connection_errors_ max_connections Nombre de connexions refusées en raison de max_connections limite Ressource : Erreur Variable d’état du serveur

Vérifier et définir la limite de connexion

Surveiller vos connexions client est essentiel, car une fois que vous avez épuisé vos connexions disponibles, les nouvelles connexions client seront refusées. La limite de connexion MySQL est par défaut de 151, mais peut être vérifiée avec une requête:

SHOW VARIABLES LIKE 'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections | 151 |+-----------------+-------+

La documentation de MySQL suggère que des serveurs robustes devraient être capables de gérer des connexions par centaines ou milliers :

« Linux ou Solaris devraient pouvoir prendre en charge de manière routinière 500 à 1000 connexions simultanées et jusqu’à 10 000 connexions si vous avez plusieurs gigaoctets de RAM disponibles et que la charge de travail de chacun est faible ou que le temps de réponse cible est peu exigeant. Windows est limité à (tables ouvertes × 2 + connexions ouvertes) < 2048 en raison de la couche de compatibilité Posix utilisée sur cette plate-forme. »

La limite de connexion peut être réglée à la volée:

SET GLOBAL max_connections = 200;

Cependant, ce paramètre revient à la valeur par défaut lorsque le serveur redémarre. Pour définir définitivement la limite de connexion, ajoutez une ligne comme celle-ci à votre fichier de configuration my.cnf (voir cet article pour obtenir de l’aide sur la localisation du fichier de configuration):

max_connections = 200

Surveillance de l’utilisation de la connexion

MySQL expose un thread de connexion de comptage métrique Threads_connected — un thread par connexion. En surveillant cette mesure parallèlement à votre limite de connexion configurée, vous pouvez vous assurer que vous disposez d’une capacité suffisante pour gérer les nouvelles connexions. MySQL expose également la métrique Threads_running pour isoler lequel de ces threads traite activement des requêtes à un moment donné, par opposition aux connexions ouvertes mais actuellement inactives.

Si votre serveur atteint la limite max_connections, il commencera à refuser les connexions. Dans ce cas, la métrique Connection_errors_max_connections sera incrémentée, de même que la métrique Aborted_connects qui suit toutes les tentatives de connexion ayant échoué.

MySQL expose une variété d’autres métriques sur les erreurs de connexion, ce qui peut vous aider à enquêter sur les problèmes de connexion. La métrique Connection_errors_internal est une bonne à regarder, car elle n’est incrémentée que lorsque l’erreur provient du serveur lui-même. Les erreurs internes peuvent refléter une condition de mémoire insuffisante ou l’incapacité du serveur à démarrer un nouveau thread.

Métriques sur lesquelles alerter

  • Threads_connected: Si un client tente de se connecter à MySQL lorsque toutes les connexions disponibles sont utilisées, MySQL renverra une erreur « Trop de connexions » et incrémentera Connection_errors_max_connections. Pour éviter ce scénario, vous devez surveiller le nombre de connexions ouvertes et vous assurer qu’il reste en toute sécurité en dessous de la limite max_connections configurée.
  • Aborted_connects: Si ce compteur augmente, vos clients essaient et ne parviennent pas à se connecter à la base de données. Étudiez la source du problème avec des métriques de connexion à grain fin telles que Connection_errors_max_connections et Connection_errors_internal.

Utilisation du pool de tampons

 Utilisation du pool de tampons MySQL
Nom Description Type métrique Disponibilité
Innodb_buffer_pool_pages_total Nombre total de pages dans le pool de tampons Ressource: Utilisation Variable d’état du serveur
Utilisation du pool de tampons Rapport des pages utilisées au total dans le pool de tampons Ressource : Utilisation Calculée à partir des variables d’état du serveur
Innodb_buffer_pool_read_requests Requêtes faites au pool de tampons Ressource: Utilisation Variable d’état du serveur
Innodb_buffer_pool_reads Demande que le pool de tampons n’a pas pu remplir la ressource : Saturation Variable d’état du serveur

Le moteur de stockage par défaut de MySQL, InnoDB, utilise une zone de mémoire appelée pool de tampons pour mettre en cache les données des tables et des index. Les métriques de pool de tampons sont des métriques de ressources par opposition aux métriques de travail et, en tant que telles, sont principalement utiles pour étudier (plutôt que détecter) les problèmes de performances. Si les performances de la base de données commencent à glisser pendant que les E /S de disque augmentent, l’extension du pool de tampons peut souvent apporter des avantages.

Dimensionnement du pool de tampons

Le pool de tampons est par défaut de 128 mebioctets, mais MySQL vous conseille de l’augmenter jusqu’à 80 % de la mémoire physique sur un serveur de base de données dédié. MySQL ajoute également quelques notes de prudence, car la surcharge de mémoire d’InnoDB peut augmenter l’empreinte mémoire d’environ 10% au-delà de la taille du pool de tampons alloué. Et si vous manquez de mémoire physique, votre système aura recours à la pagination et les performances en souffriront considérablement.

Le pool de tampons peut également être divisé en régions distinctes, appelées instances. L’utilisation de plusieurs instances peut améliorer la concurrence pour les pools de tampons dans la plage multi-GiB.

Les opérations de redimensionnement du pool de tampons sont effectuées par morceaux, et la taille du pool de tampons doit être définie sur un multiple de la taille du bloc multiplié par le nombre d’instances:

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

La taille du bloc est par défaut de 128 Mo mais est configurable à partir de MySQL 5.7.5. La valeur des deux paramètres peut être vérifiée comme suit:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

Si la requête innodb_buffer_pool_chunk_size ne renvoie aucun résultat, le paramètre n’est pas accordable dans votre version de MySQL et peut être supposé être de 128 MiO.

Pour définir la taille du pool de tampons et le nombre d’instances au démarrage du serveur:

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

À partir de MySQL 5.7.5, vous pouvez également redimensionner le pool de tampons à la volée via une commande SET spécifiant la taille souhaitée en octets. Par exemple, avec deux instances de pool de tampons, vous pouvez définir chacune une taille de 4 Gio en définissant la taille totale à 8 GiO:

SET GLOBAL innodb_buffer_pool_size=8589934592;

Métriques clés du pool de tampons InnoDB

MySQL expose une poignée de métriques sur le pool de tampons et son utilisation. Certaines des mesures les plus utiles sont le suivi de la taille totale du pool de tampons, de la quantité utilisée et de l’efficacité avec laquelle le pool de tampons sert les lectures.

Les métriques Innodb_buffer_pool_read_requests et Innodb_buffer_pool_reads sont essentielles pour comprendre l’utilisation du pool de tampons. Innodb_buffer_pool_read_requests suit le nombre de demandes de lecture logique, tandis que Innodb_buffer_pool_reads suit le nombre de demandes que le pool de mémoire tampon n’a pas pu satisfaire et a donc dû être lu à partir du disque. Étant donné que la lecture depuis la mémoire est généralement plus rapide que la lecture depuis le disque, les performances en souffriront si Innodb_buffer_pool_reads commence à grimper.

L’utilisation du pool de tampons est une mesure utile à vérifier avant d’envisager de redimensionner le pool de tampons. La mesure d’utilisation n’est pas disponible immédiatement, mais peut être facilement calculée comme suit:

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) / Innodb_buffer_pool_pages_total

Si votre base de données sert un grand nombre de lectures à partir du disque, mais que le pool de tampons est loin d’être plein, il se peut que votre cache ait récemment été effacé et se réchauffe encore. Si votre pool de tampons ne se remplit pas mais sert efficacement les lectures, votre ensemble de données de travail s’insère probablement confortablement dans la mémoire.

Une utilisation élevée du pool de tampons, en revanche, n’est pas nécessairement une mauvaise chose isolément, car les données anciennes ou inutilisées sont automatiquement retirées du cache à l’aide d’une stratégie LRU. Mais si le pool de tampons ne sert pas efficacement votre charge de travail de lecture, il est peut-être temps de faire évoluer votre cache.

Conversion des métriques de pool de tampons en octets

La plupart des métriques de pool de tampons sont signalées sous la forme d’un nombre de pages mémoire, mais ces métriques peuvent être converties en octets, ce qui facilite la connexion de ces métriques avec la taille réelle de votre pool de tampons. Par exemple, pour trouver la taille totale du pool de tampons en octets à l’aide de la variable d’état du serveur qui suit le nombre total de pages dans le pool de tampons:

Innodb_buffer_pool_pages_total * innodb_page_size

La taille de la page InnoDB est réglable mais la valeur par défaut est de 16 Ko, soit 16 384 octets. Sa valeur actuelle peut être vérifiée avec une requête SHOW VARIABLES:

SHOW VARIABLES LIKE "innodb_page_size";

Conclusion

Dans cet article, nous avons exploré une poignée des mesures les plus importantes que vous devez surveiller pour garder un œil sur l’activité et les performances de MySQL. Si vous développez votre surveillance MySQL, la capture des métriques décrites ci-dessous vous mettra sur la voie de la compréhension des modèles d’utilisation et des contraintes potentielles de votre base de données. Ils vous aideront également à identifier quand il est nécessaire d’étendre ou de déplacer vos instances de base de données vers des hôtes plus puissants afin de maintenir de bonnes performances applicatives.

  • Débit de requête
  • Latence et erreurs de requête
  • Connexions client et erreurs
  • Utilisation du pool de tampons

La partie 2 de cette série fournit des instructions pour collecter et surveiller toutes les métriques dont vous avez besoin à partir de MySQL.

Remerciements

Un grand merci à Dave Stokes d’Oracle et à Ewen Fortune de VividCortex pour avoir fourni de précieux commentaires sur cet article avant sa publication.

Similar Posts

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.