MySQL | Réglage des performances | AU NOUVEAU BLOG

Optimisation des performances

À mesure que nos données augmentent dans la base de données, il est toujours important d’afficher les informations sur la base de données. Nous avons utilisé des requêtes complexes pour résoudre des problèmes difficiles sans vérifier leur impact sur les performances du serveur.

Il est toujours préférable de visiter fréquemment le schéma de performances.

USE performance_schema;

Par où commencer

Nous souhaitons améliorer les performances du cluster, mais nous ne savons pas par où commencer.

Couche SQL

Pourquoi les requêtes sont lentes

• Les requêtes sont des tâches composées de sous-tâches, et ces sous-tâches prennent du temps.
  • Pour optimiser la requête, nous devons optimiser les sous-tâches en les éliminant, en les faisant se produire moins de fois ou en les faisant se produire plus rapidement.
  • En ce qui concerne le cycle de vie de la requête, elle est d’abord analysée, puis planifiée, exécutée et enfin renvoyée au client.
  • Cela implique de nombreux appels au moteur de stockage pour récupérer les lignes et les exécutions post-récupération telles que le regroupement et le tri.
• Tout en accomplissant les tâches ci-dessus, la requête passe du temps sur le réseau dans le processeur pour des opérations telles que les statistiques, le verrouillage et, surtout, les appels au moteur de stockage pour récupérer les lignes.
• Cet appel consomme du temps en mémoire et en CPU, et surtout en E/S, si les données ne sont pas en mémoire. Un temps excessif est consommé car les transactions sont effectuées inutilement et trop de fois. Notre objectif est donc d’éliminer et de réduire les opérations en les rendant plus rapides.

Top 10 des requêtes chronophages

Pour optimiser la base de données, nous n’avons pas vraiment besoin de trouver la requête qui prend le maximum de temps, mais nous devons trouver la requête qui prend plus de temps et qui est appelée plus fréquemment.

• • SELECT (100 * SUM_TIMER_WAIT / sum(SUM_TIMER_WAIT) OVER ()) percent, SUM_TIMER_WAIT AS total, COUNT_STAR AS calls, AVG_TIMER_WAIT AS mean, substring(DIGEST_TEXT, 1, 75) FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Projection	Nom du champ	Description
total	SUM_TIMER_WAIT	Nombre de fois qu’une requête a été exécutée (COUNT_STAR) multiplié par Le temps d’exécution moyen. (AVG_TIMER_WAIT)
signifier	AVG_TIMER_WAIT	Temps de réponse moyen
sous-chaîne(DIGEST_TEXT, 1, 75)	Requête	Requête

EXPLIQUER

Liste des événements attendus lors de l’exécution de la requête (liste des sous-tâches).

EXPLIQUER

EXPLIQUER ANALYSER

Vous obtiendrez à la fois une estimation de ce à quoi le planificateur s’attendait et de ce qui s’est passé lors de l’exécution de la requête.

EXPLAIN DELETE de la table ne supprimera pas les lignes réelles ; par conséquent, il est sûr d’obtenir le plan de requête, mais EXPLAIN ANALYZE ne va pas seulement afficher le plan de requête ; il va supprimer ces lignes.

Comprendre comment ANALYSER une requête

Si nous exécutons la requête avec EXPLAIN ANALYZE, nous rechercherons toutes les sous-tâches, exécutant cette requête. Nous devons observer quelle sous-tâche présente le plus grand saut dans le temps d’exécution et voir si cela est justifié.

Le drapeau rouge dans EXPLAIN ANALYZE est une grande différence entre le nombre estimé de lignes et le nombre réel de lignes.

Recherche de requêtes de candidats pour l’optimisation

• Grand nombre de SUM_ROWS_EXAMINED, par rapport à SUM_ROWS_SENT
  • Cela peut suggérer une mauvaise utilisation de l’index puisqu’un grand nombre de lignes sont envoyées au client mais sont ensuite supprimées.
  • Si le nombre de jointures complètes est élevé, cela suggère qu’un index est nécessaire ou qu’une condition de jointure est manquante. Une analyse complète de la table aura lieu lorsqu’il n’y a pas d’index pour la condition de jointure et lorsqu’il n’y a pas de condition de jointure.
    • SELECT * FROM sys.statements_with_full_table_scans ORDER BY no_index_used_count DESC\G
  • 

    SUM_ROWS_SENT

• Si le nombre de jointures complètes est élevé, cela suggère qu’un index est nécessaire ou qu’une condition de jointure est manquante. Une analyse complète de la table aura lieu lorsqu’il n’y a pas d’index pour la condition de jointure et lorsqu’il n’y a pas de condition de jointure.

  • SELECT * FROM sys.statements_with_full_table_scans ORDER BY no_index_used_count DESC\G

• Lorsque le nombre de SUM_SELECT_RANGE_CHECK est élevé, cela peut suggérer que nous devons modifier les index sur les tables.
• Si le nombre de tables temporaires internes créées sur le disque est élevé, nous devons considérer quels index sont utilisés pour le tri et le regroupement ainsi que la quantité de mémoire autorisée pour les tables temporaires internes.
• L’écriture sur disque coûte toujours plus cher que la mémoire interne. Si vous constatez que le nombre SUM_SORT_MERGE_PASSES est élevé pour une requête (au moins égal au nombre de fois où la requête a été exécutée), augmentez la valeur sort_buffer_size uniquement pour les sessions nécessitant une taille plus grande.

Vérifier les tailles d’index

• SELECT TABLE_NAME AS `Table`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

Quelques faits intéressants et bons à connaître

• La conception de MySQL sépare le traitement des requêtes et les autres tâches du serveur du stockage et de la récupération des données.
• Lorsque nous récupérons des lignes depuis MySQL lors de l’utilisation d’une bibliothèque, par défaut, les lignes sont mises en mémoire tampon dans la mémoire de la bibliothèque, puis récupérées à partir de là.
• L’optimiseur ne se soucie pas du moteur de stockage utilisé par une table particulière. Néanmoins, le moteur de stockage affecte la manière dont le serveur optimise la requête. Par exemple, l’optimiseur interroge le moteur de stockage sur certaines de ses capacités et le coût de certaines opérations, ainsi que des statistiques sur les données de la table.

Requêtes qui créent des blocages

• SELECT * FROM performance_schema.events_errors_summary_by_account_by_error WHERE error_name="ER_LOCK_DEADLOCK" \G

Conclusion

events_statements_summary_by_digest dans performance_schema est la mine d’or pour rechercher des requêtes pouvant avoir des problèmes de performances.

Outre les performances, nous devons également prendre en compte les requêtes qui provoquent des erreurs et des délais d’attente de verrouillage.

Références