MySQL CPU占用70-80%致网站加载慢，求技术协助（附配置日志）

First off, let's break down your scenario: you're running an 8-core/32GB CentOS 6.9 server with cPanel/CloudLinux, but MySQL is eating up 70-80% of your CPU while barely touching your available memory (~5% usage). Unsurprisingly, this is killing your site performance during high traffic. Let's walk through the most likely fixes and checks to get this sorted.

Key Observations & Common Root Causes #

High CPU + low memory usage in MySQL almost always points to one of these issues:

• Underconfigured MySQL memory settings: Your server has plenty of RAM, but MySQL isn't allowed to use it—so it's relying on CPU-intensive disk operations instead of caching.
• Poorly optimized queries: Slow, unindexed, or overly complex queries force MySQL to work overtime with CPU instead of leveraging cached data.
• cPanel/CloudLinux resource limits: Per-user LVE (Lightweight Virtual Environment) limits might be throttling MySQL processes in unexpected ways.

Step-by-Step Fixes & Checks #

1. Tune MySQL Configuration (my.cnf) for Your Server Specs #

Since you have 32GB of RAM, MySQL should be using a significant portion of that for caching. Here's a starting point for your my.cnf (adjust based on your actual workload):

[mysqld]
# Basic Settings
user = mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0

# InnoDB Tuning (Critical for Memory Usage)
innodb_buffer_pool_size = 16G  # ~50% of total RAM (leaves room for cPanel/other services)
innodb_log_file_size = 2G      # 25% of buffer pool size (max 4G)
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# Query Cache (Valid for older MySQL versions; deprecated in 8.0+)
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 4M

# Connection & Thread Settings
max_connections = 200  # Adjust based on concurrent users; cPanel often sets this too high
wait_timeout = 60
interactive_timeout = 60
table_open_cache = 4096
table_definition_cache = 4096

# CPU Optimization
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 8M

Why this works: The innodb_buffer_pool_size is the biggest win here—it caches table data and indexes, so MySQL doesn't have to hit the disk (a CPU-heavy operation) as often.

2. Hunt Down Slow/Unoptimized Queries #

High CPU is almost always tied to bad queries. Here's how to find them:

• Enable the slow query log in my.cnf:

  slow_query_log = 1
  slow_query_log_file = /var/log/mysql-slow.log
  long_query_time = 2  # Log queries taking >2 seconds
  log_queries_not_using_indexes = 1
  

• Restart MySQL, let it run during high traffic, then analyze the log with mysqldumpslow:

  mysqldumpslow -s t /var/log/mysql-slow.log
  

• Look for queries with:
  • Using filesort or Using temporary in EXPLAIN output (signs of missing indexes)
  • Large LIMIT clauses without proper sorting/indexing
  • Unnecessary SELECT * (fetching more data than needed)
  • Missing joins on indexed columns

Quick fix: Run EXPLAIN on slow queries to identify missing indexes, then add them. For example:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
-- If customer_id isn't indexed, add it:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

3. cPanel/CloudLinux Specific Tweaks #

• Check LVE Limits: Use lveinfo or the cPanel CloudLinux Manager to ensure no user is hitting CPU/memory limits that throttle MySQL.
• Update MySQL: Use WHM > Software > MySQL Upgrade to install the latest stable version—older releases have performance bottlenecks.
• Disable Unused Plugins: In WHM > SQL Services > MySQL Configuration, turn off any unnecessary plugins (like MySQL Enterprise Monitor) that might be wasting resources.

4. Server-Level Checks #

• Verify Disk I/O: Slow disks can force MySQL to use more CPU waiting for data. Run iostat -x 1 5—if %util is consistently over 90%, consider upgrading to SSDs.
• Check for CPU Throttling: Use htop to confirm no other processes are competing for CPU (though MySQL is clearly the main culprit here).
• Plan OS Migration: CentOS 6.9 is end-of-life—while you can apply remaining updates, prioritize moving to AlmaLinux/Rocky Linux or CentOS 7/8 soon for better performance and security.

Verification Steps #

After making changes:

1. Restart MySQL with service mysql restart
2. Monitor CPU usage with htop or mysqladmin status during high traffic
3. Check memory usage with free -h—you should see MySQL using 16GB+ of RAM
4. Re-run mysqltuner for updated recommendations:

   mysqltuner
   

Tuning MySQL is iterative—start with the buffer pool and slow query log, then adjust based on real-world performance metrics.

内容的提问来源于stack exchange，提问作者Miguel