Comprendre l'utilisation du processeur MySQL : outils et techniques

Qu’est-ce que l’utilisation du processeur dans MySQL ?

L'utilisation du processeur dans MySQL fait référence à la proportion des ressources du processeur d'un ordinateur que le serveur MySQL utilise pour exécuter des opérations, notamment le traitement des requêtes, la gestion des transactions de base de données et la maintenance du système de base de données. Essentiellement, il mesure la part de la capacité de calcul du processeur dédiée à l'exécution de tâches MySQL à un moment donné.

Lorsque l'utilisation du processeur MySQL est très élevée, cela peut indiquer que votre base de données traite activement un grand nombre de requêtes ou de requêtes complexes. D’un autre côté, une utilisation constamment faible du processeur peut suggérer que vous sous-utilisez les ressources. Cela peut signifier qu'il existe des inefficacités ou des goulots d'étranglement ailleurs dans le système.

Comment vérifier l'utilisation du processeur MySQL

Utilisation de la commande supérieure

La commande top est un outil générique disponible sous Linux et d'autres systèmes d'exploitation de type Unix (comme CentOS). Il fournit une vue dynamique en temps réel de l'état actuel de votre système, y compris l'utilisation du processeur par tous les processus.

1. Ouvrez votre terminal.
2. Exécutez la commande top.

Recherchez le processus mysql dans la liste. La colonne %CPU affichera l'utilisation actuelle du processeur de votre instance de MySQL.

Utilisation de la commande SHOW STATUS

La commande SHOW STATUS de MySQL est un outil puissant pour récupérer un large éventail d'informations sur l'état du serveur. Bien qu'il n'affiche pas directement l'utilisation du processeur, il vous permet de déduire la charge du processeur via divers compteurs et indicateurs d'état.
Accédez à votre serveur MySQL via la CLI ou un client MySQL.
Exécutez la commande AFFICHER LE STATUT : comme 'Threads_%'.

Dans la liste des statuts et des variables du serveur, threads_running et threads_connected sont les deux indicateurs les plus importants pour évaluer l'utilisation du processeur :

• Threads_running – Affiche le nombre de threads qui ne sont pas en veille. Des nombres élevés ici peuvent indiquer une charge CPU élevée.
• Threads_connected – Ceci affiche le nombre total de connexions ouvertes au serveur MySQL. Lorsque ce nombre est nettement supérieur à threads_running, cela peut suggérer que de nombreuses connexions sont inactives ou attendent des ressources CPU disponibles pour traiter leurs requêtes.

Utilisation de la commande SHOW PROCESSLIST

La commande SHOW PROCESSLIST fournit un instantané en temps réel de tous les threads actifs au sein de votre serveur MySQL.

1. Accédez à votre serveur MySQL via la CLI ou via un client MySQL.
2. Exécutez la AFFICHER LA LISTE DES PROCESSUS ; commande.

La sortie comprend plusieurs colonnes de données pour chaque fil. Portez une attention particulière aux éléments suivants :

• Colonne de commande – Indique le type d'opération en cours de formation. Les requêtes de longue durée, en particulier les requêtes gourmandes en CPU, peuvent être identifiées ici.
• Colonne de temps – Affiche la durée (en secondes) d'exécution de la commande. Les threads avec une valeur élevée dans la colonne Temps peuvent contribuer à la charge du processeur.

Utilisation du schéma de performances

Pour une analyse plus approfondie, le schéma de performances de MySQL peut être utilisé. Il fournit des informations détaillées sur les événements du serveur et peut être interrogé pour analyser l'utilisation du processeur par thread ou par requête.

Pour activer le schéma de performances (s'il n'est pas déjà activé) :

1. Localisez votre fichier de configuration MySQL, nommé my.cnf ou my.ini.
2. Ajoutez ou modifiez l'entrée suivante performance_schema=ON.
3. Redémarrez votre serveur MySQL pour appliquer cette modification.

Exécutez une requête qui joint la table performance_schema.threads à la table performance_schema.events_statements_summary_by_thread_by_event_name. Cette requête fournira des informations détaillées sur l'utilisation du processeur associée aux différents threads et requêtes exécutés par le serveur.

Utiliser des outils de surveillance externes

Une gamme de solutions de surveillance externes, notamment Percona Monitoring and Management (PMM) et Releem, offrent une analyse approfondie des mesures de performances de MySQL, telles que l'utilisation du processeur.

Ces outils fournissent un aperçu complet du processeur et des mesures associées via une interface intuitive, ce qui facilite grandement la détection des tendances et des problèmes.

Diagnostiquer une utilisation élevée du processeur dans MySQL

Une utilisation élevée du processeur dans les bases de données MySQL a un impact sur les performances et la fiabilité de vos applications dépendantes. Pour diagnostiquer efficacement ce problème, il est essentiel d'examiner en profondeur les mécanismes opérationnels de MySQL, les processus d'exécution des requêtes et l'environnement système plus large.

1. Requêtes inefficaces

L'une des causes les plus courantes d'une utilisation élevée du processeur est l'inefficacité ou la complexité des requêtes SQL. Les requêtes qui ne disposent pas d'une indexation appropriée ou qui impliquent des analyses de table complètes peuvent entraîner une consommation excessive de cycles CPU par le moteur de base de données. Cette inefficacité est due au fait que la base de données doit lire plus de données que nécessaire, traiter chaque ligne individuellement et effectuer des calculs complexes sans l'aide d'index.

• Utilisation sous-optimale des index – Sans index, MySQL doit effectuer des analyses de tables complètes, qui sont très gourmandes en CPU. Les index doivent être créés stratégiquement en fonction des modèles de requête et des colonnes fréquemment consultées.
• Jointures complexes – Les requêtes impliquant plusieurs jointures, en particulier sur les grandes tables ou celles dépourvues d'index appropriés, peuvent entraîner une utilisation élevée du processeur. Chaque opération de jointure peut augmenter de façon exponentielle la quantité de données traitées.
• Fonctions d'agrégation – SUM(), COUNT(), AVG()) sur de grands ensembles de données, sans indexation appropriée, oblige MySQL à analyser et traiter de gros volumes de données.

Vous pouvez configurer le journal des requêtes lentes (activer puis définir votre seuil de requêtes longues préféré) ou utiliser Releem Query Analytics pour identifier les requêtes spécifiques qui mettent trop de temps à s'exécuter. Ce sont les requêtes qui nécessitent votre attention.

2. Haute concurrence

MySQL est conçu pour gérer plusieurs connexions et transactions simultanément. Cependant, à mesure que le niveau de concurrence augmente, la complexité de la gestion de ces connexions simultanées augmente également. Un thread distinct gère chaque connexion active. La gestion d'un grand nombre de threads nécessite plus de ressources CPU, non seulement pour l'exécution des requêtes mais également pour la surcharge de changement de contexte entre les threads.

3. Verrouiller les conflits

Un conflit de verrouillage se produit lorsque plusieurs transactions tentent d'accéder aux mêmes données en même temps, ce qui conduit à un scénario dans lequel elles doivent attendre que l'autre libère les verrous avant de continuer. Lorsque les transactions sont en concurrence pour les verrous sur les lignes ou les tables, le moteur de base de données consacre des cycles CPU supplémentaires à la gestion de ces verrous.

Vous pouvez déterminer si un conflit de verrouillage se produit en consultant la table information_schema.innodb_lock_waits. Utilisez la requête suivante pour identifier les transactions verrouillé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;

Les transactions de longue durée peuvent également fournir des informations sur le niveau de conflit au sein de votre base de données. Utilisez la commande SHOW ENGINE INNODB STATUS pour afficher une liste de toutes les transactions ouvertes, de la plus récente à la plus ancienne. Inspectez les transactions les plus anciennes pour avoir une idée de la durée de leur exécution, des tables et des lignes impliquées et des instructions impliquées.

4. Serveur MySQL mal configuré

Les performances de MySQL dépendent fortement de sa configuration. Les paramètres qui ne sont pas optimisés pour votre charge de travail ou votre matériel peuvent entraîner une utilisation inefficace du processeur. Certains paramètres clés incluent :

• Taille du pool de tampons inappropriée – Le pool de tampons InnoDB réduit les E/S disque en mettant en cache les données et les index en mémoire. Un pool de mémoire tampon de taille incorrecte peut entraîner des lectures de disque fréquentes. Étant donné que le système gère les opérations d'E/S disque, l'utilisation du processeur MySQL est augmentée.
• Taille du cache des threads – Si le cache des threads est trop petit, MySQL peut consacrer des cycles de processeur supplémentaires à la création et à la destruction de threads pour chaque nouvelle connexion plutôt que de réutiliser les threads existants.

5. Problèmes de conception de schéma

La conception physique de la base de données, y compris les structures des tables et les types de données, peut également affecter l'efficacité du processeur. Si vous utilisez des types de données plus volumineux que nécessaire (comme BIGINT sur INT), MySQL traite plus de données que nécessaire, consommant plus de ressources CPU.

La normalisation est une technique de conception de base de données utilisée pour organiser les tables de manière à réduire la redondance des données et à améliorer l'intégrité des données. Le processus consiste à diviser une base de données en deux ou plusieurs tables et à définir les relations entre les tables. Alors qu'une surnormalisation peut conduire à des jointures complexes, une sous-normalisation peut entraîner un traitement de données redondant et des analyses plus volumineuses, qui augmentent toutes deux l'utilisation du processeur.

11 Tips for Mitigating High MySQL CPU Usage

1. MySQL's resource control features can help prevent any single user from consuming too much CPU time, ensuring a balanced load across applications. Percona published the article on that.
2. Analyze query patterns and create indexes on columns that are frequently used in WHERE clauses, JOIN conditions, and as part of ORDER BY and GROUP BY statements. Be cautious of over-indexing, as it can lead to increased overhead during write operations.
3. This command shows you how the database goes about executing a specific query, including which indexes are used. The execution plan can reveal if a query results in a full table scan or if a query is using an index inefficiently or not at all. These results would be target for query optimization.
4. Reduce the complexity of joins, especially those involving multiple tables or large datasets. All joined tables should have appropriate indexes on the join columns. Consider denormalizing your schema if certain joins are heavily impacting CPU usage.
5. Break down complex queries into simpler ones to avoid full table scans and reduce temporary table usage, especially those created on disk. Use subqueries and derived tables carefully.
6. Adjust the InnoDB buffer pool size to fit as much of the working dataset into memory as possible, reducing disk I/O and CPU usage for disk reads. The buffer pool size should ideally be set to about 70-80% of available system memory.
7. The default value for the thread cache size might be too low for systems under heavy load or with frequent connection turnover. Configure the thread cache size to reduce the overhead of creating and destroying threads for connections. A starting point can be to set the thread cache size to the number of concurrent connections that your system typically handles at peak times.
8. Implement connection pooling in the application layer to reduce the overhead of frequent connections and disconnections to the database. Connection pooling allows a set of connections to be reused among multiple clients, diminishing the CPU load associated with connection management.
9. MySQL should run on a server with dedicated resources. Sharing resources with other CPU-intensive applications can lead to contention and high CPU usage.
10. Use hardware that matches your workload requirements. In CPU-bound scenarios, choosing processors with higher core counts and faster clock speeds can improve performance. Consider SSDs over HDDs for storage to reduce CPU load associated with disk I/O operations.
11. MySQL's Performance Schema and the slow query log can be used to monitor database operations and identify high CPU usage patterns. These tools can help pinpoint inefficient queries and suboptimal configurations contributing to high CPU usage.

Releem’s Capabilities for MySQL CPU Optimization

Releem offers a range of capabilities to help you monitor and manage your CPU usage:

• Intuitive Interface: The user-friendly interface presents CPU usage trends over time in a visually accessible format. It simplifies the optimization process, allowing you to make informed decisions with ease. No need to manually interpret CPU usage from other variables or use the CLI.
• Monitoring: Releem monitors your server, capturing important parameters and key metrics such as CPU usage, InnoDB buffer pool size, thread cache size, and more. This real-time data collection provides invaluable insights into your server's performance dynamics. Automatic Performance Analysis By crunching the numbers gathered during monitoring, pinpoints areas where CPU efficiency can be improved.
• Configuration Recommendations: Releem then suggests easy-to-apply configuration recommendations to optimize your MySQL CPU usage. These recommendations are customized to your server's specific characteristics and workload patterns.
• Query Analytics: Digs into the slow query log to identify and analyze queries that might be hogging CPU resources. Armed with this knowledge, you can fine-tune queries, apply proper indexing, and take other corrective actions.

If you'd like to check out these capabilities, you can sign up for free to get started!

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!