Optimiser l'utilisation élevée du processeur dans les instances

L'utilisation élevée du processeur dans une instance peut être causée par diverses raisons, telles que l'augmentation des charges de travail, des transactions lourdes, des requêtes lentes et des transactions de longue durée.

L'outil de recommandation d'instances sous-provisionnées analyse l'utilisation du processeur. Si les niveaux d'utilisation du processeur sont supérieurs ou égaux à 95 % au cours des 30 derniers jours, l'outil de recommandation vous avertit et fournit des insights supplémentaires pour vous aider à résoudre le problème.

Ce document explique comment examiner et optimiser une instance Cloud SQL pour MySQL si cette instance est identifiée par l'outil de recommandation d'instances sous-provisionnées comme ayant une utilisation élevée du processeur.

Recommandations

L'utilisation du processeur augmente proportionnellement à la charge de travail. Pour réduire l'utilisation du processeur, vérifiez les requêtes en cours d'exécution et optimisez-les. Voici quelques étapes pour vérifier la consommation du processeur.

  1. Vérifiez Threads_running et Threads_connected.

    Exécutez la requête suivante pour voir le nombre de threads actifs :

    > SHOW STATUS like 'Threads_%';

    Threads_running est un sous-ensemble de Threads_connected. Les autres threads sont inactifs. Une augmentation de Threads_running contribuerait à une augmentation de l'utilisation du processeur. Nous vous recommandons de vérifier ce qui s'exécute sur ces threads.

  2. Vérifier l'état des requêtes

    Exécutez la commande SHOW PROCESSLIST pour afficher les requêtes en cours. Elle renvoie tous les threads connectés dans l'ordre et leur instruction SQL en cours d'exécution. 

    mysql> SHOW [FULL] PROCESSLIST;

    Examinez attentivement les colonnes d'état et de durée. Vérifiez si de nombreuses requêtes sont bloquées au même état.

    • Si de nombreux threads affichent Updating, il peut y avoir un conflit de verrouillage d'enregistrements. Reportez-vous à l'étape suivante.
    • Si de nombreux threads affichent Waiting pour le verrouillage des métadonnées de table, vérifiez la requête pour identifier la table, puis recherchez un LDD (tel que ALTER TABLE) pouvant contenir le verrou de métadonnées. Un LDD peut également attendre le verrouillage des métadonnées de table si une requête précoce, telle qu'une requête SELECT query de longue durée, le maintient.

  3. Vérifier les conflits de verrouillage d'enregistrements

    Lorsque des transactions maintiennent des verrouillages sur des enregistrements d'index courants, elles bloquent les autres transactions demandant les mêmes verrouillages. Cela peut entraîner un effet en chaîne et le blocage d'un certain nombre de requêtes, ainsi qu'une augmentation de la valeur de Threads_running. Pour diagnostiquer les conflits de verrouillage, utilisez la table information_schema.innodb_lock_waits.

    La requête suivante répertorie chaque transaction bloquante et le nombre de transactions bloquées associées. 

    SELECT 
  t.trx_id, 
  t.trx_state, 
  t.trx_started, 
  COUNT(distinct w.requesting_trx_id) AS blocked_trxs
FROM 
  information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx t
   ON t.trx_id = w.blocking_trx_id 
GROUP BY t.trx_id,t.trx_state, t.trx_started
ORDER BY t.trx_id;

    L'utilisation d'un seul LMD volumineux et de nombreux petits LMD simultanés peut entraîner des conflits de verrouillage de ligne. Vous pouvez effectuer une optimisation côté application en procédant comme suit :

    • Évitez les longues transactions, car les verrouillages de ligne sont conservés jusqu'à la fin de la transaction.
    • Divisez un seul LMD en plusieurs petits LMD.
    • Combinez un seul LMD de ligne en petits fragments.
    • Réduisez les conflits entre les threads. Par exemple, si le code d'application utilise un pool de connexions, attribuez une plage d'ID au même thread.

  4. Rechercher les transactions de longue durée

    • Utiliser SHOW ENGINE INNODB STATUS

      Dans la section Transactions, vous pouvez voir toutes les transactions ouvertes triées de la plus récente à la plus ancienne. 

      mysql> SHOW ENGINE INNODB STATUS\G
……
------------
TRANSACTIONS
------------
…
---TRANSACTION 245762, ACTIVE 262 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 9210, OS thread handle 140262286128896, query id 202218 localhost root

      Commencez par les transactions les plus anciennes et trouvez des réponses aux questions suivantes :

      • Depuis combien de temps ces transactions sont-elles exécutées ?
      • Combien de structs de verrouillage et de verrouillages de ligne sont présents ?
      • Combien d'entrées de journal d'annulation y a-t-il ?
      • Quels sont les hôtes et les utilisateurs qui se connectent ?
      • Quelle est l'instruction SQL en cours ?

    • Utiliser information_schema.innodb_trx

      Si SHOW ENGINE INNODB STATUS a été tronqué, vous pouvez également examiner toutes les transactions ouvertes en utilisant la table information_schema.innodb_trx :

      SELECT 
 trx_id, trx_state, 
 timestampdiff(second, trx_started, now()) AS active_secs, 
 timestampdiff(second, trx_wait_started, now()) AS wait_secs, trx_tables_in_use,
 trx_tables_locked, 
 trx_lock_structs, 
 trx_rows_locked, 
 trx_rows_modified, 
 trx_query 
FROM information_schema.innodb_trx

    Si les transactions affichent les instructions de longue durée actuelles, vous pouvez décider d'arrêter ces transactions afin de réduire la pression sur le serveur ou d'attendre la fin de ces transactions critiques. Si les anciennes transactions n'affichent aucune activité, passez à l'étape suivante pour accéder à l'historique des transactions.

  5. Vérifier les instructions SQL des transactions de longue durée

    • Utiliser performance_schema

      Pour utiliser performance_schema, vous devez d'abord l'activer. Il s'agit d'une modification qui nécessite le redémarrage de l'instance. Une fois performance_schema activé, vérifiez que les outils et les utilisateurs sont activés :

      SELECT * FROM setup_consumers where name like 'events_statements_history';
SELECT * FROM setup_instruments where name like 'statement/sql/%';

      Si ce n'est pas le cas, activez-les :

      UPDATE setup_instruments SET ENABLED = 'YES', timed = 'YES' WHERE NAME LIKE 'statement/%';
UPDATE setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_statements%';

      Par défaut, chaque thread conserve les 10 derniers événements définis par performance_schema_events_statements_history_size. En général, ce nombre est suffisant pour localiser la transaction dans le code de l'application. Ce paramètre n'est pas dynamique.

      Avec mysql thread id, qui est processlist_id, interrogez les événements de l'historique :

      SELECT 
 t.thread_id, 
 event_name, 
 sql_text, 
 rows_affected, 
 rows_examined, 
 processlist_id, 
 processlist_time, 
 processlist_state 
FROM events_statements_history h 
INNER JOIN threads t 
ON h.thread_id = t.thread_id 
WHERE processlist_id = <mysql thread id>
ORDER BY event_id;

    • Utiliser le journal de requêtes lentes

      Pour le débogage, vous pouvez capturer toutes les requêtes ayant pris plus de N secondes dans le journal de requêtes lentes. Vous pouvez activer les journaux de requêtes lentes en modifiant les paramètres d'instance sur la page d'instance de la console Google Cloud ou dans gcloud CLI, puis consulter les journaux à l'aide de la visionneuse de journaux dans la console Google Cloud ou gloud CLI.

  6. Vérifier les conflits de sémaphore

    Dans un environnement simultané, le verrou mutex et en lecture/écriture sur les ressources partagées peut constituer le point de conflit, ce qui ralentit les performances du serveur. De plus, si le temps d'attente du sémaphore est supérieur à 600 secondes, le système peut planter pour sortir de la zone de blocage.

    Pour afficher les conflits de sémaphore, utilisez la commande suivante :

    mysql> SHOW ENGINE INNODB STATUS\G
----------
SEMAPHORES
----------
...
  --Thread 140396021667584 has waited at row0purge.cc line 862 for 241.00 seconds the semaphore:
  S-lock on RW-latch at 0x30c03e8 created in file dict0dict.cc line 1183
  a writer (thread id 140395996489472) has reserved it in mode  exclusive
  number of readers 0, waiters flag 1, lock_word: 0
  Last time read locked in file row0purge.cc line 862
  Last time write locked in file /build/mysql-5.7-FFKPr6/mysql-5.7-5.7.22/storage/innobase/dict/dict0stats.cc line 2376
...

    À chaque attente de sémaphore, la première ligne affiche le fil en attente, le sémaphore spécifique et la durée d'attente. Si des attentes de sémaphore sont fréquentes lors de l'exécution répétée de SHOW ENGINE INNODB STATUS, en particulier de plusieurs secondes, cela signifie que le système présente des goulots d'étranglement de simultanéité.

    Il existe différents points de conflit dans différentes charges de travail et configurations.

    Lorsque les sémaphores se trouvent souvent sur btr0sea.c, l'indexation de hachage adaptative peut être la source de conflits. Essayez de la désactiver à l'aide de la console Google Cloud ou de gcloud CLI.

  7. Optimiser les requêtes SELECT longues

    Commencez par examiner la requête. Identifiez l'objectif de la requête et la meilleure façon d'obtenir les résultats. Le meilleur plan de requête est celui qui minimise l'accès aux données.

    • Vérifiez le plan d'exécution de la requête :
    mysql> EXPLAIN <the query>;

    Pour savoir comment interpréter la sortie et évaluer l'efficacité des requêtes, consultez la documentation MySQL.

    • Utiliser l'index approprié

    Vérifiez la colonne de clé pour déterminer si l'index attendu est utilisé. Si ce n'est pas le cas, mettez à jour les statistiques de l'index :

    mysql> analyze table <table_name>

    Augmenter le nombre d'exemples de pages utilisées pour calculer les statistiques de l'index Pour en savoir plus, consultez la documentation MySQL.

    • Utiliser pleinement l'index

    Lorsque vous utilisez un index à plusieurs colonnes, vérifiez les colonnes key_len pour savoir si l'index est pleinement exploité pour filtrer les enregistrements. Les colonnes les plus à gauche doivent correspondre à des comparaisons égales, et l'index peut être utilisé jusqu'à la première condition de plage incluse.

    • Utiliser les suggestions de l'optimiseur

    Une autre façon d'être sûr d'utiliser l'index approprié consiste à appliquer la suggestion de l'index et la suggestion d'ordre de jointure de table.

  8. Éviter une longue liste d'historique avec READ COMMITTED

    La liste de l'historique est la liste des transactions non supprimées dans l'espace de table d'annulation. Le niveau d'isolation par défaut d'une transaction est REPEATABLE READ, ce qui nécessite qu'une transaction lise le même instantané tout au long de sa durée. Par conséquent, une requête SELECT bloque la suppression définitive des enregistrements de journal effectués depuis le début de la requête (ou de la transaction). Une longue liste d'historique ralentit donc les performances des requêtes. Un moyen d'éviter de créer une longue liste d'historique consiste à remplacer le niveau d'isolation des transactions par "READ COMMITTED". Avec READ COMMITTED, il n'est plus nécessaire de conserver la liste de l'historique pour une vue de lecture cohérente. Vous pouvez modifier le niveau d'isolation des transactions globalement pour toutes les sessions, pour une seule session ou pour la prochaine transaction unique. Pour en savoir plus, consultez la documentation MySQL.

  9. Ajuster la configuration du serveur

    Il y a beaucoup de choses à dire sur la configuration des serveurs. Bien que cela n'entre pas dans le cadre du présent document, notez que le serveur signale également diverses variables d'état donnant des indications sur les performances des configurations associées. Exemple :

    • Ajustez la valeur de thread_cache_size si la valeur de Threads_created/Connections est élevée. Un cache de threads approprié réduit le temps de création des threads et facilite l'exécution d'un grand nombre de charges de travail simultanées.
    • Ajustez table_open_cache si Table_open_cache_misses/Table_open_cache_hits n'est pas simple. Le fait de disposer de tables dans le cache des tables permet de réduire le temps d'exécution des requêtes et de faire une différence dans un environnement d'exécution hautement simultané.

  10. Mettre fin à une connexion indésirable

    Vous pouvez arrêter la requête si elle ne semble pas valide ou n'est plus requise. Pour savoir comment identifier et mettre fin à un thread MySQL, consultez la section Gérer les connexions à la base de données.

Enfin, si l'utilisation du processeur est toujours élevée et que les requêtes génèrent du trafic nécessaire, envisagez d'augmenter les ressources du processeur pour éviter tout plantage ou temps d'arrêt de la base de données.