Memahami Penggunaan CPU MySQL: Alat dan Teknik

MySQL의 CPU 사용량은 무엇입니까?

MySQL의 CPU 사용량은 MySQL 서버가 쿼리 처리, 데이터베이스 트랜잭션 관리, 데이터베이스 시스템 유지 관리 등의 작업을 실행하는 데 사용하는 컴퓨터 프로세서 리소스의 비율을 나타냅니다. 본질적으로 이는 특정 시간에 MySQL 작업을 실행하는 데 CPU의 계산 용량이 얼마나 할당되는지를 측정합니다.

MySQL CPU 사용량이 매우 높으면 데이터베이스가 많은 수의 요청이나 복잡한 쿼리를 적극적으로 처리하고 있음을 나타낼 수 있습니다. 반면, 지속적으로 낮은 CPU 사용량은 리소스 활용도가 낮다는 것을 의미할 수 있습니다. 이는 시스템의 다른 곳에서 비효율성이나 병목 현상이 있음을 의미할 수 있습니다.

MySQL CPU 사용량을 확인하는 방법

top 명령 사용

top 명령은 Linux 및 기타 Unix 계열 운영 체제(예: CentOS)에서 사용할 수 있는 일반 도구입니다. 모든 프로세스의 CPU 사용량을 포함하여 시스템의 현재 상태에 대한 동적 실시간 보기를 제공합니다.

1. 터미널을 엽니다.
2. top 명령을 실행합니다.

목록에서 mysql 프로세스를 찾아보세요. %CPU 열에는 MySQL 인스턴스의 현재 CPU 사용량이 표시됩니다.

SHOW STATUS 명령 사용

MySQL의 SHOW STATUS 명령은 다양한 서버 상태 정보를 가져오는 강력한 도구입니다. CPU 사용량을 직접적으로 표시하지는 않지만 다양한 카운터와 상태 표시기를 통해 CPU 부하를 유추할 수 있습니다.
CLI 또는 MySQL 클라이언트를 통해 MySQL 서버에 액세스하세요.
SHOW STATUS: like 'Threads_%' 명령을 실행하세요.

서버 상태 및 변수 목록에서 thread_running 및 thread_connected는 CPU 사용량을 측정하는 데 가장 중요한 두 가지 지표입니다.

• Threads_running – 휴면 상태가 아닌 스레드 수를 표시합니다. 여기서 숫자가 높다는 것은 CPU 부하가 높다는 의미일 수 있습니다.
• Threads_connected – MySQL 서버에 열려 있는 총 연결 수를 표시합니다. 이 숫자가 threads_running보다 훨씬 높으면 많은 연결이 유휴 상태이거나 요청을 처리하기 위해 사용 가능한 CPU 리소스를 기다리고 있음을 의미할 수 있습니다.

SHOW PROCESSLIST 명령 사용

SHOW PROCESSLIST 명령은 MySQL 서버 내 모든 활성 스레드의 실시간 스냅샷을 제공합니다.

1. CLI 또는 MySQL 클라이언트를 통해 MySQL 서버에 액세스하세요.
2. SHOW PROCESSLIST를 실행합니다. 명령.

출력에는 각 스레드에 대한 여러 데이터 열이 포함됩니다. 다음 사항에 주의하세요:

• 명령 열 – 형성되는 작업 유형을 나타냅니다. 장기 실행 쿼리, 특히 CPU 집약적 쿼리를 여기에서 식별할 수 있습니다.
• 시간 열 – 명령이 실행된 시간(초)을 표시합니다. 시간 열의 값이 높은 스레드는 CPU 로드에 기여할 수 있습니다.

성능 스키마 사용

보다 심층적인 분석을 위해 MySQL의 성능 스키마를 사용할 수 있습니다. 서버 이벤트에 대한 자세한 정보를 제공하며 쿼리를 통해 스레드별 또는 쿼리별 CPU 사용량을 분석할 수 있습니다.

성능 스키마를 활성화하려면(아직 활성화되지 않은 경우):

1. my.cnf 또는 my.ini라는 MySQL 구성 파일을 찾습니다.
2. 다음 항목을 추가하거나 수정하세요performance_schema=ON.
3. 이 변경 사항을 적용하려면 MySQL 서버를 다시 시작하세요.

performance_schema.threads 테이블을 performance_schema.events_statements_summary_by_thread_by_event_name 테이블과 조인하는 쿼리를 실행합니다. 이 쿼리는 서버에서 실행되는 다양한 스레드 및 쿼리와 관련된 CPU 사용량에 대한 자세한 통찰력을 제공합니다.

외부 모니터링 도구 사용

PMM(Percona Monitoring and Management) 및 Releem을 포함한 다양한 외부 모니터링 솔루션은 CPU 사용률과 같은 MySQL의 성능 지표에 대한 심층 분석을 제공합니다.

이러한 도구는 직관적인 인터페이스를 통해 CPU 및 관련 지표에 대한 포괄적인 개요를 제공하므로 추세와 문제를 훨씬 쉽게 파악할 수 있습니다.

MySQL의 높은 CPU 사용량 진단

MySQL 데이터베이스 내의 높은 CPU 사용량은 종속 애플리케이션의 성능과 신뢰성에 영향을 미칩니다. 이 문제를 효과적으로 진단하려면 MySQL의 운영 메커니즘, 쿼리 실행 프로세스 및 광범위한 시스템 환경을 철저히 조사하는 것이 중요합니다.

1. Inefficient Queries

One of the most common causes of high CPU usage is inefficient or complex SQL queries. Queries that lack proper indexing or involve full table scans can cause the database engine to consume excessive CPU cycles. This inefficiency arises because the database has to read more data than necessary, process each row individually, and perform complex calculations without the aid of indexes.

• Suboptimal Index Usage – Without indexes, MySQL has to perform full table scans, which are highly CPU-intensive. Indexes should be strategically created based on query patterns and frequently accessed columns.
• Complex Joins – Queries involving multiple joins, especially on large tables or those lacking appropriate indexes, can lead to high CPU utilization. Each join operation can exponentially increase the amount of data processed.
• Aggregation Functions – SUM(), COUNT(), AVG()) over large datasets, without proper indexing forces MySQL to scan and process large volumes of data.

You can configure the Slow Query Log (enable and then set your preferred long query threshold) or use Releem Query Analytics to identify which specific queries are taking too long to finish executing. These are the queries that need your attention.

2. High Concurrency

MySQL is designed to handle multiple connections and transactions simultaneously. However, as the level of concurrency increases, so does the complexity of managing these simultaneous connections. A separate thread handles each active connection. Managing a large number of threads requires more CPU resources, not just for the execution of queries but also for the overhead of context switching between threads.

3. Lock Contention

Lock contention occurs when multiple transactions attempt to access the same data at the same time, leading to a scenario where they must wait for each other to release locks before proceeding. When transactions compete for locks on rows or tables, the database engine spends additional CPU cycles managing these locks.

You can determine if lock contention is occurring by looking at the information_schema.innodb_lock_waits table. Use the following query to identify transactions that are being locked:

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;

Long-running transactions can also provide insights into the level of contention within your database. Use the SHOW ENGINE INNODB STATUS command to view a list of all open transactions from newest to oldest. Inspect the oldest transactions to get an idea of how long they are running, which tables and rows are involved, and what statements are involved.

4. Poorly Configured MySQL Server

MySQL's performance is highly dependent on its configuration. Parameters that are not optimized for your workload or hardware can lead to inefficient CPU usage. Some key parameters include:

• Inappropriate Buffer Pool Size – The InnoDB buffer pool reduces disk I/O by caching data and indexes in memory. An incorrectly sized buffer pool can cause frequent disk reads. Because the system handles disk I/O operations, MySQL CPU usage is increased.
• Thread Cache Size – If the thread cache is too small, MySQL may spend extra CPU cycles creating and destroying threads for each new connection rather than reusing existing threads.

5. Schema Design Issues

The physical design of the database, including table structures and data types, can also affect CPU efficiency. If you use larger-than-necessary data types (like BIGINT over INT), MySQL processes more data than needed, consuming more CPU resources.

Normalization is a database design technique used to organize tables in a way that reduces data redundancy and improves data integrity. The process involves dividing a database into two or more tables and defining relationships between the tables. While over-normalization can lead to complex joins, under-normalization can result in redundant data processing and larger scans, both of which increase CPU usage.

11 conseils pour atténuer l’utilisation élevée du processeur MySQL

1. Les fonctionnalités de contrôle des ressources de MySQL peuvent aider à empêcher un utilisateur de consommer trop de temps CPU, garantissant ainsi une charge équilibrée entre les applications. Percona a publié l'article à ce sujet.
2. Analysez les modèles de requête et créez des index sur les colonnes fréquemment utilisées dans les clauses WHERE, les conditions JOIN et dans le cadre des instructions ORDER BY et GROUP BY. Méfiez-vous de la surindexation, car cela peut entraîner une augmentation de la surcharge lors des opérations d'écriture.
3. Cette commande vous montre comment la base de données procède pour exécuter une requête spécifique, y compris quels index sont utilisés. Le plan d'exécution peut révéler si une requête aboutit à une analyse complète de la table ou si une requête utilise un index de manière inefficace ou pas du tout. Ces résultats seraient la cible de l'optimisation des requêtes.
4. Réduisez la complexité des jointures, en particulier celles impliquant plusieurs tables ou de grands ensembles de données. Toutes les tables jointes doivent avoir des index appropriés sur les colonnes de jointure. Pensez à dénormaliser votre schéma si certaines jointures ont un impact important sur l'utilisation du processeur.
5. Décomposez les requêtes complexes en requêtes plus simples pour éviter les analyses complètes des tables et réduire l'utilisation des tables temporaires, en particulier celles créées sur le disque. Utilisez les sous-requêtes et les tables dérivées avec précaution.
6. Ajustez la taille du pool de tampons InnoDB pour insérer autant que possible l'ensemble de données de travail en mémoire, réduisant ainsi les E/S du disque et l'utilisation du processeur pour les lectures du disque. La taille du pool de mémoire tampon doit idéalement être définie sur environ 70 à 80 % de la mémoire système disponible.
7. La valeur par défaut de la taille du cache de threads peut être trop faible pour les systèmes soumis à une charge importante ou avec des changements de connexion fréquents. Configurez la taille du cache de threads pour réduire la surcharge liée à la création et à la destruction de threads pour les connexions. Un point de départ peut être de définir la taille du cache de threads en fonction du nombre de connexions simultanées que votre système gère généralement aux heures de pointe.
8. Implémentez le regroupement de connexions dans la couche application pour réduire la surcharge liée aux connexions et déconnexions fréquentes à la base de données. Le regroupement de connexions permet de réutiliser un ensemble de connexions entre plusieurs clients, réduisant ainsi la charge CPU associée à la gestion des connexions.
9. MySQL doit fonctionner sur un serveur avec des ressources dédiées. Le partage de ressources avec d'autres applications gourmandes en CPU peut entraîner des conflits et une utilisation élevée du CPU.
10. Utilisez du matériel qui correspond aux exigences de votre charge de travail. Dans les scénarios liés au processeur, le choix de processeurs dotés d'un nombre de cœurs plus élevé et de vitesses d'horloge plus rapides peut améliorer les performances. Envisagez les disques SSD plutôt que les disques durs pour le stockage afin de réduire la charge du processeur associée aux opérations d'E/S disque.
11. Le schéma de performances de MySQL et le journal des requêtes lentes peuvent être utilisés pour surveiller les opérations de base de données et identifier les modèles d'utilisation élevée du processeur. Ces outils peuvent aider à identifier les requêtes inefficaces et les configurations sous-optimales contribuant à une utilisation élevée du processeur.

Capacités de Releem pour l'optimisation du processeur MySQL

Releem offre une gamme de fonctionnalités pour vous aider à surveiller et gérer l'utilisation de votre processeur :

• Interface intuitive : l'interface conviviale présente les tendances d'utilisation du processeur au fil du temps dans un format visuellement accessible. Il simplifie le processus d'optimisation, vous permettant de prendre facilement des décisions éclairées. Pas besoin d'interpréter manuellement l'utilisation du processeur à partir d'autres variables ou d'utiliser la CLI.
• Surveillance : Releem surveille votre serveur, capturant des paramètres importants et des mesures clés telles que l'utilisation du processeur, la taille du pool de tampons InnoDB, la taille du cache de threads, etc. Cette collecte de données en temps réel fournit des informations inestimables sur la dynamique des performances de votre serveur. Analyse automatique des performances En analysant les chiffres recueillis lors de la surveillance, on identifie les domaines dans lesquels l'efficacité du processeur peut être améliorée.
• Recommandations de configuration : Releem suggère ensuite des recommandations de configuration faciles à appliquer pour optimiser l'utilisation de votre processeur MySQL. Ces recommandations sont personnalisées en fonction des caractéristiques spécifiques de votre serveur et des modèles de charge de travail.
• Query Analytics : fouille dans le journal des requêtes lentes pour identifier et analyser les requêtes susceptibles de monopoliser les ressources du processeur. Fort de ces connaissances, vous pouvez affiner les requêtes, appliquer une indexation appropriée et prendre d'autres mesures correctives.

Si vous souhaitez découvrir ces fonctionnalités, vous pouvez vous inscrire gratuitement pour commencer !

Atas ialah kandungan terperinci Memahami Penggunaan CPU MySQL: Alat dan Teknik. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!