64GB内存CentOS7服务器MySQL占用90%CPU求优化方案

First, let's break down the key issues in your current my.cnf configuration and suggest targeted fixes, then add extra steps to get that CPU load under control.

Immediate Configuration Fixes #

Let's go through each problematic setting one by one:

1. Disable the Query Cache #

The query cache is deprecated in MySQL 5.7+ and causes significant overhead, especially with write-heavy workloads (like WordPress, which does frequent writes for comments, post updates, and transients). Your current settings enable it, which is likely contributing to CPU spikes:

query_cache_type = 0
query_cache_size = 0
query_cache_limit = 0

This completely disables the query cache, eliminating that unnecessary overhead.

2. Reduce Temporary Table Sizes #

Your tmp_table_size and max_heap_table_size are set to 256M—way too large. Large temporary tables can spill to disk (slowing operations) and consume excessive memory if multiple are created at once. Drop these to a more reasonable value:

tmp_table_size = 64M
max_heap_table_size = 64M

If you still see tmp tables spilling to disk (check with SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';), you can adjust slightly, but 64M is a safe starting point.

3. Lower Max Connections #

You've set max_connections = 500, but your traffic (2-3 sites with 1k daily visitors) only needs around 50-100 concurrent connections max. Higher values force MySQL to allocate resources for idle connections, wasting CPU and memory:

max_connections = 100

Monitor connection usage with SHOW GLOBAL STATUS LIKE 'Threads_connected'; later to fine-tune this if needed.

4. Adjust InnoDB Log File Size #

Your innodb-log-file-size = 5G is far too large. While larger log files reduce checkpointing frequency, they drastically increase recovery time if MySQL crashes, and don't provide meaningful benefits for your workload. A more optimal size is 1G:

innodb-log-file-size = 1G

Important: To change this, stop MySQL, delete the old log files (ib_logfile0 and ib_logfile1 in your datadir), then restart MySQL.

5. Optimize InnoDB Flush Settings #

innodb-flush-log-at-trx-commit = 1 is ACID-compliant but causes heavy write I/O. For most WordPress sites, setting it to 2 is a safe balance—it reduces write overhead (lowering CPU) while only risking up to 1 second of data loss if the server crashes unexpectedly:

innodb-flush-log-at-trx-commit = 2

6. Reduce InnoDB Buffer Pool Instances #

You have innodb-buffer-pool-instances = 40 for a 40G buffer pool. While each instance gets the minimum recommended 1G, having this many instances adds unnecessary management overhead. Aim for 1 instance per 2-4GB of buffer pool:

innodb-buffer-pool-instances = 10

7. Disable Performance Schema (If Not Used) #

If you're not actively using the Performance Schema for monitoring, disabling it will save CPU cycles:

performance_schema = OFF

8. Tweak Thread Cache Size #

With lower max connections, you don't need 50 in the thread cache. A value of 20 is more than sufficient:

thread_cache_size = 20

Updated my.cnf Snippet #

Here's how your optimized [mysqld] section would look (keeping non-problematic settings):

[mysql]
# CLIENT #
port = 3306
socket = /home/mysql/mysql.sock

[mysqld]
local-infile = 0
performance_schema = OFF

# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /home/mysql/mysql.sock
pid-file = /home/mysql/mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000

# DATA STORAGE #
datadir = /home/mysql/

# BINARY LOGGING #
log-bin = /home/mysql/mysql-bin
expire-logs-days = 2
sync-binlog = 1

# CACHES AND LIMITS #
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 0
max_connections = 100
thread_cache_size = 20
open-files-limit = 65535
table_definition_cache = 4096
table_open_cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 40G
innodb-buffer-pool-instances = 10
join_buffer_size = 2M

# LOGGING #
log-error = /home/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /home/mysql/mysql-slow.log

Note: I enabled the slow query log (slow-query-log = 1)—this is critical for identifying long-running queries that are killing your CPU.

Additional Steps to Reduce MySQL Load #

Beyond configuration tweaks, these WordPress-specific fixes will make a huge difference:

• Enable Object Caching: Install a plugin like Redis Object Cache or Memcached to offload frequent database queries to memory. This drastically reduces the number of MySQL requests.
• Optimize WordPress Databases: Use plugins like WP-Optimize to clean up transients, spam comments, and optimize table structures. Run OPTIMIZE TABLE on all WordPress tables periodically.
• Use a Page Caching Plugin: Plugins like WP Rocket or W3 Total Cache serve static versions of pages, eliminating database hits entirely for most visitors.
• Analyze Slow Queries: After enabling the slow query log, review it with mysqldumpslow to find long-running queries. Look for missing indexes or inefficient queries from themes/plugins—add indexes or replace problematic plugins as needed.
• Monitor MySQL Process List: Run SHOW FULL PROCESSLIST; during high load to see what queries are running. Kill stuck or long-running queries temporarily and fix their root cause.

Final Notes #

After applying these changes, restart MySQL and monitor your CPU load for a few hours. You should see a significant drop in MySQL's CPU usage. If issues persist, the slow query log will point you directly to the queries causing problems.

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