Résoudre les problèmes d’utilisation élevée du processeur dans Azure Database pour MySQL - Serveur flexible

S'APPLIQUE À : Azure Database pour MySQL - Serveur unique Azure Database pour MySQL - Serveur flexible

Le serveur flexible Azure Database pour MySQL fournit une série de métriques permettant d’identifier les goulots d’étranglement des ressources et les problèmes de performances sur le serveur. Pour déterminer si votre serveur présente une utilisation élevée du processeur, surveillez notamment les métriques « Pourcentage du processeur hôte », « Nombre total de connexions », « Pourcentage de mémoire hôte » et « Pourcentage d’E/S ». L’affichage d’une combinaison de ces métriques fournit parfois des insights sur ce qui peut entraîner l’augmentation de l’utilisation du processeur sur votre instance de serveur flexible Azure Database pour MySQL.

Par exemple, tenez compte d’une hausse soudaine du nombre de connexions qui déclenche un afflux de requêtes de base de données, ce qui fait grimper en flèche l’utilisation du processeur.

Outre la capture des métriques, il est important de suivre la charge de travail pour comprendre si une ou plusieurs requêtes provoquent le pic d’utilisation du processeur.

Causes des pics d’UC

Les pics d’UC peuvent se produire pour diverses raisons, principalement à cause de pics de connexions et de requêtes SQL mal écrites, ou d’une combinaison des deux :

Pic de connexions

Une augmentation des connexions peut entraîner une augmentation des threads, ce qui peut à son tour provoquer une hausse de l’utilisation du processeur car il doit gérer ces connexions ainsi que leurs requêtes et ressources. Pour résoudre un pic de connexions, vous devez vérifier la métrique Nombre total de connexions et consulter la section suivante pour plus d’informations sur ces connexions. Vous pouvez utiliser performance_schema pour identifier les hôtes et les utilisateurs actuellement connectés au serveur avec les commandes suivantes :

Hôtes actuellement connectés

   select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
   where CURRENT_CONNECTIONS > 0
   and host not in ('NULL','localhost');

Utilisateurs actuellement connectés

   select USER,CURRENT_CONNECTIONS from performance_schema.users
   where CURRENT_CONNECTIONS >0
   and USER not in ('NULL','azure_superuser');

Requêtes SQL mal écrites

Les requêtes coûteuses à exécuter et analysant un grand nombre de lignes sans index, ou celles qui effectuent des tris temporaires avec d’autres plans inefficaces, peuvent entraîner des pics d’utilisation du processeur. Bien que certaines requêtes puissent s’exécuter rapidement dans une seule session, elles peuvent provoquer des pics d’utilisation du processeur lorsqu’elles sont exécutées dans plusieurs sessions. Par conséquent, il est essentiel d’expliquer toujours vos requêtes que vous capturez à partir de la commande show processlist et de garantir que leurs plans d’exécution sont efficaces. Cela peut être réalisé en veillant à ce qu'elles analysent un nombre minimal de lignes en utilisant des filtres ou des clauses WHERE, en utilisant des index et en évitant d'utiliser des tris temporaires volumineux ainsi que d'autres mauvais plans d'exécution. Pour plus d’informations sur les plans d’exécution, consultez Format de sortie d’EXPLAIN.

Capture d’informations sur la charge de travail actuelle

La commande SHOW (FULL) PROCESSLIST affiche la liste de toutes les sessions utilisateur actuellement connectées à l’instance de serveur flexible Azure Database pour MySQL. Elle fournit également des informations sur l’état actuel et l’activité de chaque session.

Elle ne produit un instantané que de l’état actuel de la session, sans aucune information sur l’historique des activités de session.

Examinons un exemple de sortie après exécution de cette commande.

mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id    | User             | Host               | db            | Command     | Time   | State                       | Info                                     |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
|     1 | event_scheduler  | localhost          | NULL          | Daemon      |     13 | Waiting for next activation | NULL                                     |
|     6 | azure_superuser  | 127.0.0.1:33571    | NULL          | Sleep       |    115 |                             | NULL                                     
|
| 24835 | adminuser        | 10.1.1.4:39296     | classicmodels | Query       |      7 | Sending data                | select * from classicmodels.orderdetails;|
| 24837 | adminuser        | 10.1.1.4:38208     | NULL          | Query       |      0 | starting                    | SHOW FULL PROCESSLIST                    |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)

Notez qu’il existe deux sessions appartenant à l’utilisateur « adminuser » détenu par le client, provenant de la même adresse IP :

• La session 24835 a exécuté une instruction SELECT pendant les sept dernières secondes.
• La session 24837 exécute l’instruction « show full processlist ».

Le cas échéant, il peut être nécessaire de terminer une requête, par exemple une requête de création de rapports ou une requête HTAP qui a provoqué un pic d’utilisation du processeur de votre charge de travail de production. Toutefois, tenez toujours compte des conséquences potentielles de l’arrêt d’une requête avant d’effectuer l’action dans une tentative de réduction de l’utilisation du processeur. De même, si des requêtes durables entraînant une hausse de l’utilisation du processeur ont été identifiées, ajustez ces requêtes afin que les ressources soient exploitées de manière optimale.

Analyse détaillée de la charge de travail actuelle

Vous devez utiliser au moins deux sources d’informations pour obtenir des informations précises sur l’état d’une session, d’une transaction et d’une requête :

• La liste des processus du serveur indiquée dans la table INFORMATION_SCHEMA.PROCESSLIST, également accessible en exécutant la commande SHOW [FULL] PROCESSLIST
• Les métadonnées de transaction InnoDB indiquées dans la table INFORMATION_SCHEMA.INNODB_TRX

Avec des informations provenant d’une seule de ces sources, il est impossible de décrire l’état de la connexion et de la transaction. Par exemple, la liste des processus n’indique pas s’il existe une transaction ouverte associée à l’une des sessions. Les métadonnées de transaction, elles, n’affichent pas l’état de session ni le temps passé dans cet état.

Voici un exemple de requête qui combine des informations de la liste des processus avec certains des éléments importants des métadonnées de transactions InnoDB :

mysql> select    p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state,    substring(p.info, 1, 50) as info,    t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level   from information_schema.processlist p    left join information_schema.innodb_trx t    on p.id = t.trx_mysql_thread_id \G

L’exemple suivant montre la sortie de cette requête :

*************************** 1. row *************************** 
        session_id: 11 
               user: adminuser 
               host: 172.31.19.159:53624 
                 db: NULL 
            command: Sleep 
               time: 636 
              state: cleaned up 
               info: NULL 
        trx_started: 2019-08-01 15:25:07 
    trx_age_seconds: 2908 
  trx_rows_modified: 17825792 
trx_isolation_level: REPEATABLE READ 
*************************** 2. row *************************** 
         session_id: 12 
               user: adminuser 
               host: 172.31.19.159:53622 
                 db: NULL 
            command: Query 
               time: 15 
              state: executing 
               info: select * from classicmodels.orders 
        trx_started: NULL 
    trx_age_seconds: NULL 
  trx_rows_modified: NULL 
trx_isolation_level: NULL

Le tableau suivant présente une analyse de ces informations, session par session.

Notez que, si une session est signalée comme inactive, elle n’exécute plus d’instructions. Elle a à ce stade terminé tout le travail antérieur et attend de nouvelles instructions du client. Toutefois, elle reste responsable d’une certaine consommation du processeur et d’une utilisation de la mémoire.

Liste des transactions ouvertes

La sortie de la requête suivante fournit une liste de toutes les transactions en cours d’exécution sur le serveur de base de données dans l’ordre de début des transactions. Vous pouvez ainsi facilement déterminer si des transactions durables et bloquantes dépassent leur runtime attendu.

SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;

Présentation des états de thread

Les transactions qui contribuent à une utilisation plus élevée du processeur pendant l’exécution peuvent comporter des threads dans différents états (cf. sections suivantes). Utilisez ces informations pour mieux comprendre le cycle de vie des requêtes et différents états de thread.

Vérification des autorisations et ouverture de tables

Cet état signifie généralement que l’opération d’ouverture de table prend beaucoup de temps. Il est en règle générale possible d’augmenter la taille du cache de table pour régler le problème. Toutefois, les tables qui s’ouvrent lentement peuvent également indiquer d’autres problèmes, et notamment la présence d’un trop grand nombre de tables sous la même base de données.

Envoi de données

Cet état peut signifier que le thread envoie des données à travers le réseau, mais il peut également indiquer que la requête lit des données du disque ou de la mémoire. Il peut être dû à une analyse de table séquentielle. Vérifiez les valeurs de innodb_buffer_pool_reads et de innodb_buffer_pool_read_requests pour déterminer si un grand nombre de pages sont traitées dans la mémoire à partir du disque. Pour plus d’informations, consultez Résolution des problèmes de mémoire faible dans le serveur flexible Azure Database pour MySQL.

Mise à jour

Cet état signifie généralement que le thread effectue une opération d’écriture. Vérifiez la métrique liée aux E/S dans l’Analyseur de performances pour mieux comprendre l’activité des sessions actuelles.

Attente du verrou <lock_type>

Cet état indique que le thread attend un deuxième verrou. Dans la plupart des cas, il pourrait s’agir d’un verrou de métadonnées. Passez en revue tous les autres threads pour voir lequel prend le verrou.

Présentation et analyse des événements d’attente

Il est important de comprendre les événements d’attente sous-jacents dans le moteur MySQL, car des attentes longues ou nombreuses dans une base de données peuvent entraîner une utilisation accrue du processeur. Ce qui suit est illustre la commande et l’exemple de sortie appropriés.

SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
 Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event                           | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog              |            7090 | 255.54 s        | 36.04 ms      |
| wait/io/file/innodb/innodb_log_file  |           17798 | 55.43 s         | 3.11 ms       |
| wait/io/file/innodb/innodb_data_file |          260227 | 39.67 s         | 0.15 ms       |
| wait/io/table/sql/handler            |         5548985 | 11.73 s         | 0.00 ms       |
| wait/io/file/sql/FRM                 |            1237 | 7.61 s          | 6.15 ms       |
| wait/io/file/sql/dbopt               |              28 | 1.89 s          | 67.38 ms      |
| wait/io/file/myisam/kfile            |              92 | 0.76 s          | 8.30 ms       |
| wait/io/file/myisam/dfile            |             271 | 0.53 s          | 1.95 ms       |
| wait/io/file/sql/file_parser         |              18 | 0.32 s          | 17.75 ms      |
| wait/io/file/sql/slow_log            |               2 | 0.05 s          | 25.79 ms      |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)

Restriction du temps d’exécution des instructions SELECT

Si vous ne connaissez pas le coût ni le temps d’exécution des opérations de base de données impliquant des requêtes SELECT, une instruction SELECT durable peut entraîner une instabilité ou une volatilité dans le serveur de base de données. La taille des instructions et des transactions, ainsi que l’utilisation des ressources associées, continue d’augmenter en fonction de la croissance du jeu de données sous-jacent. En raison de cette expansion illimitée, les instructions et les transactions des utilisateurs finaux prennent de plus en plus de temps et consomment de plus en plus de ressources, jusqu’à surcharger le serveur de base de données. Lorsque vous utilisez des requêtes SELECT illimitées, nous vous recommandons de configurer le paramètre max_execution_time afin que toutes les requêtes dépassant cette durée soient abandonnées.

Recommandations

• Vérifiez que votre base de données dispose de suffisamment de ressources pour exécuter vos requêtes. Vous devrez peut-être parfois effectuer un scale up de la taille de l’instance pour obtenir plus de cœurs de processeur 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.
• Exécutez des instructions SELECT sur des serveurs réplicas en lecture si possible.
• Utilisez des alertes sur « Pourcentage du processeur hôte » 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.

Étapes suivantes

Pour trouver des réponses de pairs aux questions qui vous préoccupent le plus, ou pour poster une question ou répondre à une question, visitez le forum Stack Overflow.