:Utilisation élevée du CPU dans PolarDB for MySQL

Avec le développement de votre activité, les spécifications actuelles de votre cluster peuvent devenir insuffisantes. Les graphiques de performance affichent généralement une métrique, telle que le QPS ou les IOPS, dont la tendance haussière suit un schéma similaire à celui de l'utilisation du CPU

Si le CPU constitue le goulot d'étranglement, les spécifications de votre cluster sont probablement inadaptées à votre trafic métier. Ajoutez un nœud en lecture seule à votre cluster de base de données ou augmentez les spécifications du cluster pour résoudre ce problème.

• Pour une charge de travail composée principalement de requêtes en lecture, ajoutez un nœud en lecture seule afin d'étendre le cluster et de répartir le trafic de lecture. Pour plus d'informations, consultez Ajouter ou supprimer des nœuds.

• Si votre charge de travail consiste essentiellement en des requêtes d'écriture, l'ajout d'un nœud en lecture seule n'améliorera pas les performances. Dans ce cas, augmentez manuellement les spécifications du cluster, par exemple en passant d'une configuration à 4 cœurs à une configuration à 8 cœurs.

Requêtes lentes

Une utilisation élevée du CPU résulte souvent d'instructions SQL inefficaces engendrant des requêtes lentes et une accumulation de threads actifs. Déterminez d'abord si les requêtes lentes constituent la cause racine de cette surconsommation ou si un autre goulot d'étranglement ralentit les requêtes et augmente indirectement l'utilisation du CPU.

Consultez les requêtes lentes dans la console PolarDB en naviguant vers Diagnostics and Optimization > Slow SQL. Dans l'onglet Slow Log Details, si la valeur des Scanned Rows est nettement supérieure à celle des Returned Rows, les requêtes lentes provoquent probablement l'utilisation élevée du CPU.

Les requêtes TP impliquent un volume très faible de lectures et d'écritures. Si une requête analyse une grande quantité de données, un index est probablement manquant. Par exemple, si une requête figurant dans la liste des requêtes lentes indique que plus de 10 000 lignes ont été analysées pour une seule ligne retournée, cela signale clairement l'absence d'index sur la colonne name.

SELECT * FROM table1 WHERE name='testname';

Exécutez l'instruction suivante pour vérifier l'existence d'un index sur la colonne name.

SHOW index FROM table1;

• Si la colonne name ne possède pas d'index, ajoutez-en un à l'aide de l'instruction suivante pour éliminer les requêtes lentes causées par des analyses de données à grande échelle.

  ALTER TABLE table1 ADD KEY ix_name (name);

• Si la colonne name dispose déjà d'un index, utilisez l'instruction suivante pour afficher le plan d'exécution SQL et confirmer que l'index approprié est bien utilisé.

  EXPLAIN SELECT * FROM table1 WHERE name='testname';

  Si un index existe sur la colonne name mais n'est pas utilisé, des statistiques inexactes ont pu générer un plan d'exécution incorrect. Exécutez l'instruction suivante pour régénérer les statistiques de la table et corriger le plan.

  ANALYZE TABLE table1;

  Une fois la commande terminée, vérifiez à nouveau le plan d'exécution pour confirmer que l'index correct est désormais utilisé :

  EXPLAIN SELECT * FROM table1 WHERE name='testname';

FAQ : Pourquoi des instructions SQL identiques et des volumes d'analyse similaires entraînent-ils une utilisation du CPU très différente selon les clusters PolarDB ?

La saturation du CPU provient généralement de requêtes lentes. Même si le texte SQL et le volume d'analyse théorique semblent identiques sur différents clusters, le comportement d'exécution réel peut varier. Examinez les aspects suivants :

1. Comparez les détails réels des journaux de requêtes lentes : Analysez et comparez les journaux de requêtes lentes des deux clusters afin de vérifier si les lignes analysées, les lignes retournées et le temps d'exécution diffèrent réellement. Une même instruction SQL peut avoir des plans d'exécution différents ou rencontrer des distributions de données distinctes selon les clusters.

2. Vérifiez les différences d'architecture des clusters : Assurez-vous que le cluster à forte charge ne comporte pas moins de nœuds en lecture seule que le cluster à faible charge. Si les requêtes en lecture ne sont pas déchargées vers les nœuds en lecture seule, le nœud principal supporte une charge plus lourde, ce qui accroît l'utilisation du CPU.

3. Recommandations d'optimisation : Pour éviter la saturation du CPU, concentrez-vous sur l'optimisation des requêtes lentes et la réduction du nombre réel de lignes analysées par les instructions SQL, plutôt que de simplement vérifier l'identité du texte SQL.

Nombre élevé de threads actifs

Un nombre élevé de threads actifs augmente systématiquement l'utilisation du CPU. Dans MySQL, chaque cœur de CPU ne peut traiter qu'une seule requête à la fois. Ainsi, un cluster à 16 cœurs peut gérer au maximum 16 requêtes simultanées au niveau du noyau, ce qui diffère de la concurrence au niveau applicatif. Consultez les informations de session dans la console PolarDB en naviguant vers Diagnostics and Optimization > One-click diagnosis > Sessions.

Si les requêtes lentes ne sont pas la cause des échecs de traitement, l'accumulation de threads actifs résulte généralement d'une hausse du trafic de production. Vérifiez ce point en consultant les courbes de performance. Si les tendances globales du trafic et des requêtes correspondent à l'accumulation de threads actifs, les ressources du cluster ont atteint leur limite. Ajoutez des nœuds en lecture seule au cluster de base de données ou augmentez ses spécifications pour résoudre ce problème.

Lorsque le nombre de threads actifs atteint un point critique, une contention du CPU peut survenir et générer de nombreux verrous mutex dans le noyau. Les graphiques de performance affichent alors une utilisation élevée du CPU, un grand nombre de threads actifs et des IOPS ou un QPS faibles. Un pic soudain de trafic, avec un taux élevé de nouvelles connexions, peut également entraîner une contention du CPU et une accumulation de requêtes en attente. L'activation de la fonctionnalité de pool de threads du cluster permet souvent d'atténuer ce problème grâce au contrôle de flux. Si le nombre de threads actifs diminue, vérifiez si des tâches restent en attente côté application. Si la charge du CPU et le nombre de threads actifs demeurent élevés, envisagez également d'augmenter les spécifications du cluster.

Une tempête de connexions frontales peut aussi provoquer un pic de trafic instantané sur le cluster. Ce trafic anormal, souvent dû à des robots d'exploration Web, peut être bloqué par limitation SQL. Pour plus d'informations, consultez Gestion des sessions.

Configuration inappropriée du noyau

Les paramètres par défaut d'une instance MySQL auto-gérée sont conçus pour des scénarios généralistes ; ils peuvent ne pas être optimaux pour toutes les charges de travail et nécessiter un ajustement fin. Certains problèmes n'apparaissent pas aux premiers stades d'une application lorsque le volume de données est faible, mais peuvent surgir à mesure que les données augmentent et que des conditions spécifiques sont réunies.

La contention mémoire est un problème fréquent. Dans l'architecture MySQL, la mémoire sert principalement à la mise en cache des données. Les zones mémoire les plus sollicitées sont le buffer pool et l'innodb_adaptive_hash_index. La zone de cache globale du système de base de données concentre les échanges de données les plus fréquents. En cas de mémoire insuffisante ou de contention de pages mémoire, diverses exceptions et requêtes lentes peuvent s'accumuler. Un symptôme typique est un pic soudain de l'utilisation du CPU à son niveau maximal, accompagné de requêtes lentes. Si l'investigation révèle que l'index n'est pas manquant, le problème peut provenir du système de mémoire.

Par exemple, lors d'une opération truncate table, MySQL parcourt le buffer pool pour évacuer toutes les pages de données de la table faisant l'objet du truncate. Dans un cluster à grande échelle, si innodb_buffer_pool_instances vaut 1 et que la concurrence est relativement élevée, des problèmes de contention peuvent survenir. Détectez ce problème tôt dans le cycle de vie du service ; évitez-le généralement en alignant la valeur de innodb_buffer_pool_instances sur le nombre de cœurs CPU et en partitionnant le buffer pool en compartiments.

Un autre scénario concerne la contention liée à l'innodb_adaptive_hash_index. Un symptôme évident est un grand nombre d'attentes hash0hash.cc.

SHOW ENGINE innodb STATUS;

Dans la section AHI de la sortie, vous observerez une asymétrie significative des données.

insert 0, delete mark 0, delete 0
Hash table size 25499819, node heap has 20720 buffer(s)
Hash table size 25499819, node heap has 25111 buffer(s)
Hash table size 25499819, node heap has 23884 buffer(s)
Hash table size 25499819, node heap has 16835 buffer(s)
Hash table size 25499819, node heap has 23132 buffer(s)
Hash table size 25499819, node heap has 189284 buffer(s)
Hash table size 25499819, node heap has 38864 buffer(s)
Hash table size 25499819, node heap has 49094 buffer(s)
5469.32 hash searches/s, 5282.36 non-hash searches/s

Pour ce type de problème, désactivez le paramètre innodb_adaptive_hash_index, ce qui désactive la fonctionnalité AHI. Les données montrent que dans des scénarios mixtes de lecture et d'écriture, l'AHI peut impacter négativement les performances, mais sa désactivation n'affecte pas significativement l'activité globale.

Anomalie des ressources mémoire entraînant une utilisation élevée du CPU ou un basculement primaire/secondaire

Des anomalies des ressources mémoire peuvent indirectement provoquer une utilisation élevée du CPU, des pannes d'instance ou des basculements primaire/secondaire (HA). Les scénarios suivants couvrent les causes fréquentes, notamment une mémoire saturée à 100 %, des événements de manque de mémoire (OOM), des pannes, des basculements primaire/secondaire, des événements HA, des instructions Prepare non fermées, des instructions SQL longues, des déclencheurs (Triggers) et des problèmes liés au Query Parser.

Scénario 1 : OOM (manque de mémoire) provoquant la panne de l'instance

Cause : Un nombre excessif de connexions (notamment de nombreuses connexions inactives) et des instructions Prepare non fermées en temps voulu entraînent une consommation mémoire excessive, déclenchant finalement un événement OOM et la panne de l'instance.

Solutions :

• Côté application, recyclez rapidement les connexions inactives.

• Fermez les instructions Prepare en temps utile et recherchez la cause racine des initiations concurrentes de Prepare.

• Mettez à niveau vers une version mineure ultérieure du moteur pendant les heures creuses pour bénéficier des optimisations mémoire au niveau du noyau.

• Réduisez la valeur du paramètre innodb_buffer_pool_size et observez si le problème s'améliore.

Scénario 2 : Concentration mémoire du Query Parser entraînant une croissance soutenue de la mémoire ou un basculement primaire/secondaire

Cause : Des instructions SQL longues et des Triggers volumineux provoquent une consommation mémoire élevée du Parser. Notez les points suivants :

• Même si une instruction SQL spécifique a été optimisée ou n'est pas routée vers le nœud d'écriture, d'autres instructions SQL longues ou Triggers peuvent toujours déclencher le problème.

• Les opérations DML doivent être exécutées sur le nœud principal, indépendamment de toute configuration de répartition lecture/écriture.

Solutions :

• Divisez les instructions SQL longues en plusieurs instructions plus courtes.

• Optimisez les Triggers ou réduisez leur taille.

• Contrôlez la longueur des instructions SQL au niveau applicatif.

• Si nécessaire, augmentez les spécifications de l'instance ou ajustez le paramètre innodb_buffer_pool_size.

Scénario 3 : Instruction SQL avec analyse volumineuse portant la mémoire à 100 % et déclenchant un basculement primaire/secondaire (non reflété dans les rapports de diagnostic)

Cause : Les instructions SQL avec analyse volumineuse — telles que les requêtes comportant des conditions complexes ou des agrégations COUNT — consomment de grandes quantités de mémoire. Ces instructions peuvent ne pas apparaître dans les rapports de diagnostic, ce qui rend leur détection plus difficile.

Solutions :

• Analysez le journal des requêtes lentes pour identifier les instructions SQL spécifiques à analyse volumineuse.

• Optimisez les instructions SQL identifiées en ajoutant des index appropriés ou en réécrivant les requêtes.

Bugs système

Les bugs système représentent une cause relativement rare de problèmes liés au CPU. Parmi les exemples issus de versions antérieures figurent les interblocages de processus et les analyses complètes de tables dues à la réinitialisation à zéro des statistiques de table. Avec l'évolution du produit, les problèmes de CPU causés par des bugs système sont devenus moins fréquents. Cependant, le dépannage nécessite souvent des informations approfondies au niveau du noyau et peut s'avérer difficile à résoudre seul. Nous vous recommandons de nous contacter pour obtenir une assistance technique.