Résoudre les problèmes de performances des requêtes dans Azure Database pour MySQL – Serveur flexible

Les performances des requêtes peut être affectée par plusieurs facteurs. Il est donc important d’examiner d’abord l’étendue des symptômes que vous rencontrez dans votre instance de serveur flexible Azure Database pour MySQL. Par exemple, les performances des requêtes sont lentes pour :

• Est-ce que les requêtes s’exécutent sur l’instance de serveur flexible Azure Database pour MySQL ?
• Un ensemble spécifique de requêtes ?
• Une requête spécifique ?

Gardez également à l’esprit que les modifications récentes apportées à la structure ou aux données sous-jacentes des tables que vous interrogez peuvent affecter les performances.

Activation de la fonctionnalité de journalisation

Avant d’analyser des requêtes individuelles, vous devez définir des points de référence de requête. Avec ces informations, vous pouvez implémenter la fonctionnalité de journalisation sur le serveur de base de données pour tracer les requêtes qui dépassent un seuil que vous spécifiez en fonction des besoins de l’application.

Avec le serveur flexible Azure Database pour MySQL, il est recommandé d’utiliser la fonctionnalité de journal des requêtes lentes pour identifier les requêtes qui mettent plus de N secondes à s’exécuter. Une fois que vous avez identifié les requêtes à partir du journal des requêtes lentes, vous pouvez utiliser les diagnostics MySQL pour résoudre ces requêtes.

Avant de commencer à tracer des requêtes longues, vous devez activer le paramètre slow_query_log à l’aide du Portail Azure ou d’Azure CLI. Avec ce paramètre activé, vous devez également configurer la valeur du paramètre long_query_time pour spécifier le nombre de secondes pendant lesquelles les requêtes peuvent s’exécuter avant d’être identifiées comme des requêtes « lentes ». La valeur par défaut du paramètre est de 10 secondes, mais vous pouvez ajuster la valeur pour répondre aux besoins du contrat SLA de votre application.

Bien que le journal des requêtes lentes soit un excellent outil pour le suivi des requêtes longues, il existe certains scénarios dans lesquels cela peut ne pas être efficace. Par exemple, le journal des requêtes lentes :

• Impacte négativement les performances si le nombre de requêtes est très élevé ou si l’instruction de requête est très volumineuse. Ajustez la valeur du paramètre long_query_time en conséquence.
• Peut ne pas être utile si vous avez également activé le paramètre log_queries_not_using_index, qui spécifie de journaliser les requêtes censées récupérer toutes les lignes. Les requêtes effectuant une analyse d’index complète tirent parti d’un index, mais elles seraient journalisées, car l’index ne limite pas le nombre de lignes retournées.

Récupération des informations à partir des journaux

Les journaux d’activité sont disponibles pendant sept jours à compter de leur création. Vous pouvez lister et télécharger les journaux des requêtes lentes à l’aide du Portail Azure ou de l’interface de ligne de commande Azure. Dans le Portail Azure, accédez à votre serveur, sous Surveillance, sélectionnez Journaux du serveur, puis sélectionnez la flèche vers le bas en regard d’une entrée pour télécharger les journaux associés à la date et à l’heure que vous examinez.

En outre, si vos journaux de requêtes lentes sont intégrés aux journaux Azure Monitor par le biais des journaux de diagnostic, vous pouvez exécuter des requêtes dans un éditeur pour les analyser plus en profondeur :

AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

L’instantané suivant illustre un exemple de requête lente.

# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @  [172.30.209.6]  Id: 735026
# Query_time: 25.314811  Lock_time: 0.000000 Rows_sent: 126  Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;

La requête s’est exécutée en 26 secondes, a examiné plus de 443 000 lignes et retourné 126 lignes de résultats.

En règle générale, vous devez vous concentrer sur les requêtes avec des valeurs élevées pour Query_time et Rows_examined. Toutefois, si vous remarquez des requêtes avec une valeur Query_time élevée mais seulement quelques Rows_examined, cela indique souvent la présence d’un goulot d’étranglement des ressources. Dans ce cas, vous devez vérifier s’il existe une limitation d’E/S ou une utilisation du processeur.

Profilage d’une requête

Une fois que vous avez identifié une requête en cours d’exécution lente spécifique, vous pouvez utiliser la commande EXPLAIN et le profilage pour collecter davantage de détails.

Pour vérifier le plan de requête, exécutez la commande suivante :

EXPLAIN <QUERY>

En plus de créer un plan EXPLAIN pour une requête, vous pouvez utiliser la commande SHOW PROFILE, qui vous permet de diagnostiquer l’exécution des instructions qui ont été exécutées dans la session active.

Pour activer le profilage et profiler une requête spécifique dans une session, exécutez l’ensemble de commandes suivant :

SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;

Examinons de plus près l’utilisation de ces commandes pour profiler une requête. Tout d’abord, activez le profilage pour la session active, exécutez la SET PROFILING = 1 commande :

SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Ensuite, exécutez une requête non optimale qui effectue une analyse complète de la table :

mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
| +----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ |
| 1 row in set (27.60 sec) |

Ensuite, affichez la liste de tous les profils de requête disponibles en exécutant la commande SHOW PROFILES :

SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration | Query |
| +----------+-------------+----------------------------------------------------+ |
| 1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
| +----------+-------------+----------------------------------------------------+ |
| 1 row in set, 1 warning (0.00 sec) |

Enfin, pour afficher le profil de la requête 1, exécutez la commande SHOW PROFILE FOR QUERY 1.

SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status | Duration |
| +----------------------+-----------+ |
| starting | 0.000102 |
| checking permissions | 0.000028 |
| Opening tables | 0.000033 |
| init | 0.000035 |
| System lock | 0.000018 |
| optimizing | 0.000017 |
| statistics | 0.000025 |
| preparing | 0.000019 |
| executing | 0.000011 |
| Sending data | 27.594038 |
| end | 0.000041 |
| query end | 0.000014 |
| closing tables | 0.000013 |
| freeing items | 0.000088 |
| cleaning up | 0.000020 |
| +----------------------+-----------+ |
| 15 rows in set, 1 warning (0.00 sec) |

Liste des requêtes les plus utilisées sur le serveur de base de données

Chaque fois que vous résolvez les problèmes de performances des requêtes, il est utile de comprendre quelles requêtes sont les plus souvent exécutées sur votre instance de serveur flexible Azure Database pour MySQL. Vous pouvez utiliser ces informations pour évaluer si l’une des principales requêtes prend plus de temps que d’habitude pour s’exécuter. En outre, un développeur ou un administrateur de base de données peut utiliser ces informations pour identifier si une requête a une augmentation soudaine du nombre et de la durée d’exécution des requêtes.

Pour répertorier les 10 requêtes les plus exécutées sur votre instance de serveur flexible Azure Database pour MySQL, exécutez la requête suivante :

SELECT digest_text AS normalized_query,
 count_star AS all_occurrences,
 Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
 Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
 Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
 Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
 Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS sum_rows_selected,
 sum_rows_examined AS sum_rows_scanned,
 sum_created_tmp_tables,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Liste des 10 requêtes les plus coûteuses par temps d’exécution total

La sortie de la requête suivante fournit des informations sur les 10 principales requêtes exécutées sur le serveur de base de données et leur nombre d’exécutions sur le serveur de base de données. Elle fournit également d’autres informations utiles telles que les latences de requêtes, leur temps de verrouillage, le nombre de tables temporaires créées dans le cadre du runtime de requête, etc. Utilisez cette sortie de requête pour suivre les principales requêtes sur la base de données et les modifications apportées à des facteurs tels que les latences, ce qui pourrait indiquer une possibilité d’affiner davantage la requête pour éviter les risques futurs.

SELECT REPLACE(event_name, 'statement/sql/', '') AS statement,
 count_star AS all_occurrences ,
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency,
 Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency,
 Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time  ,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS  sum_rows_selected,
 sum_rows_examined AS  sum_rows_scanned,
 sum_created_tmp_tables,  sum_created_tmp_disk_tables,
 IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables /
 sum_created_tmp_tables * 100, 0))) AS
 tmp_disk_tables_percent,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_global_by_event_name
WHERE event_name LIKE 'statement/sql/%'
 AND count_star > 0
ORDER BY sum_timer_wait DESC
LIMIT 10;

Surveillance du garbage collection InnoDB

Lorsque le garbage collection InnoDB est bloqué ou retardé, la base de données peut développer un décalage de vidage important qui peut affecter négativement l’utilisation du stockage et les performances des requêtes.

La longueur de la liste d’historique des segments de restauration InnoDB mesure le nombre d’enregistrements de modification stockés dans le journal d’annulation. Une valeur HLL croissante indique que les threads de GC de InnoDB (threads de vidage) ne respectent pas la charge de travail d’écriture ou que la purge est bloquée par une requête ou une transaction de longue durée.

Des retards excessifs dans le garbage collection peuvent avoir des conséquences graves et négatives :

• L’espace de table système InnoDB s’étend, ce qui accélère la croissance du volume de stockage sous-jacent. Parfois, l’espace de table système peut s’étendre de plusieurs téraoctets suite à un vidage bloqué.
• Les enregistrements marqués pour suppression ne seront pas supprimés en temps voulu. Cela peut entraîner la croissance des espaces de table InnoDB et empêcher le moteur de réutiliser le stockage occupé par ces enregistrements.
• Les performances de toutes les requêtes peuvent se dégrader et l’utilisation du processeur peut augmenter en raison de la croissance des structures de stockage InnoDB.

Par conséquent, il est important de surveiller les valeurs, les modèles et les tendances HLL.

Recherche de valeurs HLL

Vous pouvez trouver la valeur HLL en exécutant la commande show engine innodb status. La valeur est répertoriée dans la sortie, sous l’en-tête TRANSACTIONS :

show engine innodb status\G

****************** 1. row ******************

(...)

------------
TRANSACTIONS
------------
Trx id counter 52685768
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle
History list length 2964300

(...)

Vous pouvez également déterminer la valeur HLL en interrogeant la table information_schema.innodb_metrics :

mysql> select count from information_schema.innodb_metrics
    -> where name = 'trx_rseg_history_len';

+---------+
| count |
| +---------+ |
| 2964300 |
| +---------+ |
| 1 row in set (0.00 sec) |

Interprétation des valeurs HLL

Lors de l’interprétation des valeurs HLL, tenez compte des instructions répertoriées dans le tableau suivant :

Résolution des valeurs HLL excessives

Si le HLL affiche des pics importants ou présente un modèle de croissance périodique, examinez immédiatement les requêtes et les transactions en cours d’exécution sur votre instance de serveur flexible Azure Database pour MySQL. Vous pouvez ensuite résoudre les problèmes de charge de travail susceptibles d’empêcher la progression du processus de garbage collection. Bien qu’il ne soit pas prévu que la base de données soit exempte de décalage de vidage, vous ne devez pas laisser le décalage croître de façon incontrôlable.

Pour obtenir des informations de transaction à partir de la table information_schema.innodb_trx, par exemple, exécutez les commandes suivantes :

select * from information_schema.innodb_trx
order by trx_started asc\G

Les détails de la colonne trx_started vous aideront à calculer l’âge de la transaction.

mysql> select * from information_schema.innodb_trx
    -> order by trx_started asc\G

****************** 1. row ******************
                    trx_id: 8150550
                 trx_state: RUNNING
               trx_started: 2021-11-13 20:50:11
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 19
                 trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(...)

Pour plus d’informations sur les sessions de base de données actuelles, notamment le temps passé dans l’état actuel de la session, vérifiez la table information_schema.processlist. La sortie suivante, par exemple, montre une session qui exécute activement une requête pendant les 1462 dernières secondes :

mysql> select user, host, db, command, time, info
    -> from information_schema.processlist
    -> order by time desc\G

****************** 1. row ******************
   user: test
   host: 172.31.19.159:38004
     db: employees
command: Query
   time: 1462
   info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';

(...)

Recommandations

• Vérifiez que votre base de données dispose de suffisamment de ressources pour exécuter vos requêtes. Vous devriez peut-être mettre à l’échelle la taille de l’instance pour obtenir plus de cœurs de processeur et de la mémoire supplémentaire de façon à prendre en charge votre charge de travail.

• Évitez les transactions volumineuses et les transactions durables en les décomposant en transactions plus petites.

• Configurez innodb_purge_threads conformément à votre charge de travail pour améliorer l’efficacité des opérations de vidage en arrière-plan.

  Notes

  Testez les modifications apportées à cette variable serveur pour chaque environnement afin de mesurer le changement de comportement du moteur.

• Utilisez des alertes sur « Pourcentage du processeur hôte », « Pourcentage de mémoire hôte » et « Nombre total de connexions » pour obtenir des notifications si le système dépasse l’un des seuils spécifiés.

• Utilisez les analyses Query Performance Insight ou des classeurs Azure pour identifier les requêtes problématiques et les requêtes lentes, puis les optimiser.

• Pour les serveurs de base de données de production, collectez des diagnostics à intervalles réguliers pour vérifier que tout s’exécute correctement. Si ce n’est pas le cas, résolvez les problèmes que vous identifiez.

Étape suivante