MySQL CPU占用率达280%求排查优化方案，附my.cnf配置

Hey there, let's dig into why your MySQL process is churning through 280% of your CPU—way higher than it should be. First, let's start with the my.cnf config you shared, then walk through actionable steps to diagnose and fix this.

Your Current my.cnf Config #

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 127.0.0.1
thread_stack = 192K
thread_cache_size = 32
myisam-recover-options = BACKUP
query_cache_limit = 128M
log_error = /var/log/mysql/error.log
expire_logs...

Step 1: Identify the Culprit Queries #

High CPU almost always ties back to inefficient or runaway queries. Here's how to find them:

• Run SHOW FULL PROCESSLIST; in MySQL to see active queries. Look for queries with a long Time value, or repeated queries that are running over and over.
• Enable slow query logging to capture problematic queries long-term. Add these lines to your my.cnf under [mysqld] (adjust paths as needed):

  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/slow_queries.log
  long_query_time = 2  # Log queries taking >2 seconds
  log_queries_not_using_indexes = 1  # Catch queries doing full table scans
  

  Restart MySQL, then check the slow log after a few minutes—this will tell you exactly which queries are draining CPU.

Step 2: Fix Index Issues #

Missing or unused indexes force MySQL to do full table scans, which are CPU-heavy.

• For each slow query from the log, run EXPLAIN [your query]; to analyze its execution plan. Look for:
  • type: ALL (means full table scan)
  • key: NULL (no index being used)
• Add targeted indexes for the columns your queries filter, join, or sort on. For example:

  CREATE INDEX idx_users_email ON users(email);
  

Step 3: Tune Problematic my.cnf Settings #

Looking at your config, a few settings might be contributing:

• Query Cache: If you're using MySQL 5.7 or older (query cache was removed in 8.0), your query_cache_limit = 128M is way too large. Large cache limits lead to frequent cache invalidation, which wastes CPU. Try:

  query_cache_size = 64M
  query_cache_limit = 8M
  

  If your query cache hit rate is low (check with SHOW GLOBAL STATUS LIKE 'Qcache_hits'; vs Qcache_inserts;), consider disabling it entirely with query_cache_type = 0—it often does more harm than good in high-concurrency environments.
• MyISAM Usage: Your config includes myisam-recover-options, which suggests you might still be using MyISAM tables. MyISAM uses table-level locks and is terrible for concurrency; convert critical tables to InnoDB with:

  ALTER TABLE your_table_name ENGINE=InnoDB;
  

• Thread Cache: thread_cache_size = 32 is reasonable for most setups, but if your server has very few concurrent connections, you could lower it to 8-16 to reduce overhead.

Step 4: Check System-Level Concurrency #

• Run SHOW GLOBAL STATUS LIKE 'Threads_running';—if this number is much higher than your server's CPU core count (e.g., 10+ threads on a 4-core server), you're overwhelming MySQL with concurrent work. Focus on optimizing queries instead of increasing max_connections (more connections = more CPU overhead).
• Verify no other processes on the server are competing for CPU—use top or htop to confirm MySQL is the only process using high CPU.

Step 5: Additional Checks #

• Scheduled Jobs: Are there cron jobs or scripts running bulk updates, reports, or imports during peak hours? Move these to off-peak times, or optimize their SQL to be more efficient.
• MySQL Version: If you're running an older version (e.g., 5.6 or earlier), upgrade to a stable, supported version (like 5.7.x or 8.0.x)—many CPU-related bugs are fixed in newer releases.

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