Understanding MySQL CPU Usage: Tools and Techniques-Mysql Tutorial-php.cn

Understanding MySQL CPU Usage: Tools and Techniques

What is CPU Usage in MySQL?

CPU usage in MySQL refers to the proportion of a computer's processor resources that the MySQL server utilizes to execute operations, including processing queries, managing database transactions, and maintaining the database system. In essence, it measures how much of the CPU's computational capacity is dedicated to running MySQL tasks at any given time.

When MySQL CPU usage is very high this can indicate that your database is actively processing a large number of requests or complex queries. On the other hand, consistently low CPU usage might suggest you are underutilizing resources. This can mean there are inefficiencies or bottlenecks elsewhere in the system.

How to Check MySQL CPU Usage

Using the top Command

The top command is a generic tool available in Linux and other Unix-like operating systems (like CentOS). It provides a dynamic real-time view of your system's current state, including the CPU usage of all processes.

Open your terminal.
Execute the top command.

Look for the mysql process in the list. The %CPU column will show the current CPU usage of your instance of MySQL.

Using the SHOW STATUS Command

MySQL's SHOW STATUS command is a powerful tool for fetching a wide array of server status information. Although it doesn't directly show CPU usage, it allows you to infer CPU load through various counters and status indicators.
Access your MySQL server through the CLI or a MySQL client.
Run the SHOW STATUS: like 'Threads_%' command.

From the list of server statuses and variables, threads_running and threads_connected are the two most important indicators for gauging CPU usage:

Threads_running – Shows the number of threads that are not sleeping. High numbers here can indicate a high CPU load.
Threads_connected – This shows the total number of open connections to the MySQL server. When this number is significantly higher than threads_running, it might suggest that many connections are idle or waiting for available CPU resources to process their requests.

Using the SHOW PROCESSLIST Command

The SHOW PROCESSLIST command provides a real-time snapshot of all active threads within your MySQL server.

Access your MySQL server via the CLI or through a MySQL client.
Execute the SHOW PROCESSLIST; command.

The output includes several columns of data for each thread. Pay close attention to the following:

Command Column – Indicates the type of operation being formed. Long-running queries, especially CPU-intensive queries, can be identified here.
Time Column – Shows how long (in seconds) the command has been running. Threads with a high value in the Time column may be contributing to CPU load.

Using Performance Schema

For a more in-depth analysis, MySQL's Performance Schema can be used. It provides detailed information on server events and can be queried to analyze CPU usage per thread or per query.

To enable Performance Schema (if not already enabled):

Locate your MySQL configuration file, named my.cnf or my.ini.
Add or modify the following entry performance_schema=ON.
Restart your MySQL server to apply this change.

Execute a query that joins the performance_schema.threads table with the performance_schema.events_statements_summary_by_thread_by_event_name table. This query will provide detailed insights into the CPU usage associated with different threads and queries executed by the server.

Use External Monitoring Tools

A range of external monitoring solutions, including Percona Monitoring and Management (PMM) and Releem, offer in-depth analysis of MySQL's performance metrics, such as CPU utilization.

These tools provide a comprehensive overview of CPU and associated metrics through an intuitive interface, making it far easier to spot trends and issues.

Diagnosing High CPU Usage in MySQL

High CPU usage within MySQL databases impacts the performance and dependability of your dependent applications. To effectively diagnose this issue, it is key to thoroughly examine MySQL's operational mechanics, query execution processes, and the broader system environment.

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;

Copy after login

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.

높은 MySQL CPU 사용량을 완화하기 위한 11가지 팁

MySQL의 리소스 제어 기능은 단일 사용자가 너무 많은 CPU 시간을 소비하는 것을 방지하여 애플리케이션 전체에 균형 잡힌 로드를 보장하는 데 도움이 됩니다. 페르코나가 이에 대한 기사를 게재했습니다.
쿼리 패턴을 분석하고 WHERE 절, JOIN 조건, ORDER BY 및 GROUP BY 문의 일부로 자주 사용되는 열에 대한 인덱스를 생성합니다. 쓰기 작업 중 오버헤드가 증가할 수 있으므로 과도한 인덱싱에 주의하세요.
이 명령은 사용되는 인덱스를 포함하여 데이터베이스가 특정 쿼리를 실행하는 방법을 보여줍니다. 실행 계획을 통해 쿼리 결과가 전체 테이블 스캔인지 또는 쿼리가 인덱스를 비효율적으로 사용하는지 또는 전혀 사용하지 않는지 확인할 수 있습니다. 이러한 결과는 쿼리 최적화의 대상이 됩니다.
특히 여러 테이블이나 대규모 데이터세트와 관련된 조인의 복잡성을 줄입니다. 조인된 모든 테이블에는 조인 열에 대한 적절한 인덱스가 있어야 합니다. 특정 조인이 CPU 사용량에 큰 영향을 미치는 경우 스키마를 비정규화하는 것이 좋습니다.
복잡한 쿼리를 간단한 쿼리로 나누어 전체 테이블 스캔을 방지하고 특히 디스크에 생성된 임시 테이블 사용량을 줄이세요. 하위 쿼리와 파생 테이블을 신중하게 사용하세요.
InnoDB 버퍼 풀 크기를 조정하여 작업 데이터 세트를 메모리에 최대한 맞춰서 디스크 I/O 및 디스크 읽기에 대한 CPU 사용량을 줄입니다. 버퍼 풀 크기는 사용 가능한 시스템 메모리의 약 70-80%로 설정하는 것이 이상적입니다.
스레드 캐시 크기의 기본값은 부하가 높거나 연결 교체가 빈번한 시스템의 경우 너무 낮을 수 있습니다. 연결을 위한 스레드 생성 및 삭제에 따른 오버헤드를 줄이도록 스레드 캐시 크기를 구성합니다. 시작점은 스레드 캐시 크기를 시스템이 일반적으로 사용량이 많을 때 처리하는 동시 연결 수로 설정하는 것입니다.
애플리케이션 계층에 연결 풀링을 구현하여 데이터베이스에 대한 빈번한 연결 및 연결 끊김으로 인한 오버헤드를 줄입니다. 연결 풀링을 사용하면 여러 클라이언트 간에 일련의 연결을 재사용하여 연결 관리와 관련된 CPU 로드를 줄일 수 있습니다.
MySQL은 전용 리소스가 있는 서버에서 실행되어야 합니다. CPU를 많이 사용하는 다른 애플리케이션과 리소스를 공유하면 경합이 발생하고 CPU 사용량이 높아질 수 있습니다.
워크로드 요구 사항에 맞는 하드웨어를 사용하세요. CPU 바인딩 시나리오에서는 코어 수가 많고 클럭 속도가 빠른 프로세서를 선택하면 성능이 향상될 수 있습니다. 디스크 I/O 작업과 관련된 CPU 부하를 줄이기 위해 저장용으로 HDD보다 SSD를 고려하세요.
MySQL의 성능 스키마와 느린 쿼리 로그를 사용하여 데이터베이스 작업을 모니터링하고 높은 CPU 사용 패턴을 식별할 수 있습니다. 이러한 도구는 높은 CPU 사용량을 초래하는 비효율적인 쿼리와 최적이 아닌 구성을 찾아내는 데 도움이 될 수 있습니다.

MySQL CPU 최적화를 위한 Releem의 기능

Releem은 CPU 사용량을 모니터링하고 관리하는 데 도움이 되는 다양한 기능을 제공합니다.

직관적인 인터페이스: 사용자 친화적인 인터페이스는 시각적으로 접근 가능한 형식으로 시간 경과에 따른 CPU 사용량 추세를 보여줍니다. 최적화 프로세스를 단순화하여 정보에 입각한 결정을 쉽게 내릴 수 있습니다. 다른 변수의 CPU 사용량을 수동으로 해석하거나 CLI를 사용할 필요가 없습니다.
모니터링: Releem은 서버를 모니터링하여 CPU 사용량, InnoDB 버퍼 풀 크기, 스레드 캐시 크기 등과 같은 중요한 매개변수 및 주요 지표를 캡처합니다. 이 실시간 데이터 수집은 서버의 성능 역학에 대한 귀중한 통찰력을 제공합니다. 자동 성능 분석 모니터링 중에 수집된 수치를 분석하여 CPU 효율성을 향상시킬 수 있는 영역을 찾아냅니다.
구성 권장 사항: 그런 다음 Releem은 MySQL CPU 사용량을 최적화하기 위해 적용하기 쉬운 구성 권장 사항을 제안합니다. 이러한 권장 사항은 서버의 특정 특성과 작업 부하 패턴에 맞게 맞춤화되었습니다.
쿼리 분석: 느린 쿼리 로그를 자세히 분석하여 CPU 리소스를 많이 차지할 수 있는 쿼리를 식별하고 분석합니다. 이러한 지식을 갖추고 있으면 쿼리를 세부적으로 조정하고, 적절한 색인을 적용하고, 기타 수정 조치를 취할 수 있습니다.

이러한 기능을 확인하고 싶다면 무료로 등록하여 시작해 보세요!

The above is the detailed content of Understanding MySQL CPU Usage: Tools and Techniques. For more information, please follow other related articles on the PHP Chinese website!