SoFunction
Updated on 2025-04-09

Perfectly solve the problem of the CPU soaring in MySQL database server

Let's take a look at what routines are there

1. Positioning issues

  • Monitoring with tools: Check the CPU occupied by MySQL processes through system monitoring tools (such as top, htop, vmstat, etc. under Linux). You can also use MySQL's own performance monitoring tools, such asSHOW PROCESSLISTCheck the SQL statements currently being executed and find queries that have a long execution time or consume a lot of resources.
SHOW PROCESSLIST;
  • View slow query log: Turn on the slow query log, which can record SQL statements whose execution time exceeds the specified threshold. By analyzing the slow query log, you can find slow queries that may cause the CPU to soar.
-- Check whether the slow query log is on
SHOW VARIABLES LIKE 'slow_query_log';
-- Turn on slow query log
SET GLOBAL slow_query_log = 'ON';
-- Set the slow query time threshold（unit：Second）
SET GLOBAL long_query_time = 1;

2. Optimize SQL query

  • Optimize query statements: Optimize slow query statements to avoid inefficient operations such as complex subqueries and full table scanning. For example, convert subqueries into join queries and use indexes reasonably to improve query efficiency.
-- Original query：Using subquery
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'China');
-- After optimization：Use connection query
SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE  = 'China';
  • Add appropriate index: Add indexes based on query conditions and frequently sorted and grouped fields, but be careful to avoid creating too many indexes, because indexes will increase the overhead of write operations.
-- for customers Table of country Field index
CREATE INDEX idx_country ON customers (country);

3. Adjust MySQL configuration parameters

  • Adjust the buffer pool size：innodb_buffer_pool_sizeParameters control the buffer pool size of the InnoDB storage engine. Appropriately increasing this parameter can reduce disk I/O and reduce CPU usage.
[mysqld]
innodb_buffer_pool_size = 2G
  • Adjust thread pool parameters: If the MySQL version supports thread pool, you can adjust the relevant parameters of the thread pool, such asthread_pool_sizeTo optimize thread management and reduce the overhead of CPU context switching.
[mysqld]
thread_pool_size = 64

4. Optimize the database architecture

  • Table partition: For large tables, you can consider using table partitioning technology to store data in different partitions to improve query efficiency.
-- Create a table partitioned by scope
CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
  • Vertical split and horizontal split: If there are too many fields in the table, vertical splitting can be performed to separate infrequently used fields into other tables; if the amount of data in the table is too large, horizontal splitting can be performed to disperse the data into multiple tables.

5. Check hardware resources

  • Increase CPU resources: If the server has insufficient CPU cores or low performance, you can consider upgrading the CPU or increasing the number of CPU cores of the server.
  • Check disk I/O: High CPU usage may be due to disk I/O bottlenecks. You can check disk I/O using tools such as iostat under Linux. If disk I/O is too high, consider using faster disks such as SSDs or optimizing disk configuration.

6. Deal with lock competition issues

  • Analyze lock waiting situation:useSHOW ENGINE INNODB STATUSCheck the status information of the InnoDB storage engine and analyze whether there is a lock waiting situation.
SHOW ENGINE INNODB STATUS;
  • Optimize transactions: Try to shorten the execution time of transactions and avoid holding locks for a long time. Large transactions can be split into multiple small transactions, reducing the lock holding time.

Let’s take a look at a case scenario.

Case scenario analysis

The case background is like this: In the e-commerce business system, the database uses MySQL to store product information, order information, user information, etc. Recently, the operation department has reported that the system has been slow to respond, especially during the promotions from 8 to 10 p.m. every day, the system is almost stuck, and after monitoring, it was found that the CPU usage of MySQL servers soared to nearly 100%.

Problem troubleshooting process

  • Use system monitoring tools: Operations and maintenance personnel use Linux systemtopThe command checks the system process and finds that the MySQL process occupies a large amount of CPU resources.
  • View MySQL execution:implementSHOW PROCESSLISTCommand, it is found that there are a large number of query statements in execution state, one of which appears very frequently. This statement is used to query the detailed information of a popular product and related user comments.
SELECT p.*, c.comment_content 
FROM products p 
JOIN comments c ON p.product_id = c.product_id 
WHERE p.product_id = 12345 
ORDER BY c.comment_time DESC;
  • Analyze slow query logs: After turning on the slow query log, it was found that the execution time of the query statement exceeded 5 seconds, which was a slow query.

Analysis of the cause of the problem

  • Missing index：productsTable andcommentsTable in join fieldproduct_idThe index is not created on it, resulting in a full table scan when executing the connection query, which increases the burden on the CPU.
  • Too large data volume：commentsA large amount of user comment information is stored in the table. When performing sorting operations, a large amount of data needs to be compared and sorted, which further consumes CPU resources.

Solution

  • Add an index:forproductsTable andcommentsTable ofproduct_idAdd indexes in the field, andcommentsTable ofcomment_timeAdd indexes to fields to improve sorting efficiency.
-- for products Table of product_id Field index
CREATE INDEX idx_products_product_id ON products (product_id);
-- for comments Table of product_id Field index
CREATE INDEX idx_comments_product_id ON comments (product_id);
-- for comments Table of comment_time Field index
CREATE INDEX idx_comments_comment_time ON comments (comment_time);
  • Optimize query statements: Considering that users may only care about the latest comments, you can add them in the query statementLIMITClause, reduces the amount of data that needs to be sorted and returned.
SELECT p.*, c.comment_content 
FROM products p 
JOIN comments c ON p.product_id = c.product_id 
WHERE p.product_id = 12345 
ORDER BY c.comment_time DESC 
LIMIT 10;
  • Adjust MySQL configuration parameters: Increase appropriatelyinnodb_buffer_pool_sizeParameters to improve cache hit rate, reduce disk I/O operations, and thus reduce CPU usage.
[mysqld]
innodb_buffer_pool_size = 4G
  • Clean up data regularly:rightcommentsSome old comment data in the table that users don’t care about are regularly cleaned to reduce the amount of data in the table and improve query efficiency.

Implementation effect

After the above optimization measures, the CPU usage rate of MySQL server was monitored again during the promotion and found that it was stable at around 30% - 40%, the system response speed was significantly improved, and the user experience was greatly improved.

at last

The above is the detailed content that perfectly solves the problem of CPU soaring in MySQL database server. For more information about the soaring in MySQL CPU, please follow my other related articles!