WordPress服务器配置求助：月20万访问致MySQL CPU占用过高

Hey folks, let's dig into that MySQL CPU overload issue you're hitting with your WordPress setup targeting 200K monthly visitors. As someone who's helped lots of teams scale their WP sites, here's my breakdown of what to check and fix step by step:

First, let's align on your setup:

• Apache Server: 8GB RAM / 25GB + 200GB storage / Ubuntu 16.04
• Second Server: Presumably running MySQL (since you mentioned two servers total)
• Core Issue: MySQL CPU spikes to 149% during user traffic, using InnoDB engine
• Partial Configs Provided: Snippets from apache2.conf (Timeout 40, KeepAlive...) and mysql.cnf

1. Diagnose MySQL's Bottlenecks First #

MySQL CPU almost always spikes due to inefficient queries or misconfigured engine settings. Let's start here:

Enable & Analyze Slow Query Logs #

This is the fastest way to find the culprits. Update your mysql.cnf with these lines:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # Log queries taking longer than 2 seconds
log_queries_not_using_indexes = 1  # Catch unindexed queries (big WP culprit!)

Restart MySQL, let it run during peak traffic, then parse the log with:

mysqldumpslow /var/log/mysql/slow.log

Look for repeated, slow WordPress queries—often from poorly written plugin/theme custom WP_Query calls or missing indexes on custom fields.

Tune InnoDB Key Parameters #

For a server with 8GB RAM (assuming MySQL is isolated on its own server), adjust these critical settings in mysql.cnf:

• innodb_buffer_pool_size = 5G: Allocate ~70% of RAM to InnoDB's cache (this reduces disk I/O drastically)
• innodb_log_file_size = 512M: Larger log files reduce checkpointing overhead (you'll need to stop MySQL, delete old ib_logfile0/ib_logfile1, then restart)
• innodb_flush_log_at_trx_commit = 2: If you don't need strict ACID compliance, this cuts down disk write pressure
• max_connections = 150: Avoid setting this too high—WordPress rarely needs more than 200, and excess connections waste memory
• query_cache_type = 0 & query_cache_size = 0: Disable query cache—it's counterproductive for InnoDB in high-concurrency setups (WordPress has better caching options anyway)

2. Optimize Apache to Reduce Unnecessary Database Hits #

Your Apache config snippets hint at room for improvement here:

Switch to MPM Event Mode #

Ubuntu 16.04 defaults to MPM Prefork, which is memory-heavy. Switch to Event mode for better concurrency:

sudo a2dismod mpm_prefork
sudo a2enmod mpm_event
sudo systemctl restart apache2

Tune MPM Event Parameters #

Edit /etc/apache2/mods-available/mpm_event.conf to match your 8GB RAM:

<IfModule mpm_event_module>
    StartServers             2
    MinSpareThreads          25
    MaxSpareThreads          75
    ThreadLimit              64
    ThreadsPerChild          25
    MaxRequestWorkers        150
    MaxConnectionsPerChild   10000
</IfModule>

This prevents Apache from hogging memory that MySQL needs.

Refine KeepAlive Settings #

Adjust these in apache2.conf to balance connection reuse and resource waste:

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 5

Short timeouts free up connections faster during peak traffic.

3. WordPress-Specific Optimizations to Cut Database Load #

WordPress is often the source of unnecessary DB calls—fix these and you'll see immediate relief:

Enable Object Caching #

Install a plugin like Redis Object Cache (or Memcached) to cache frequent DB queries (like post metadata, user data) in memory. This cuts down repeated hits to MySQL for the same data.

Clean Up & Optimize the Database #

• Use WP-Optimize to delete transients, spam comments, old drafts, and optimize table structures.
• Check for plugins/themes using unindexed custom fields—add indexes to those columns via phpMyAdmin or WP-CLI.

Implement Page Caching #

Serve static cached pages to most visitors so they never hit the database. Plugins like WP Rocket or Cache Enabler work great, or you could set up Varnish in front of Apache for even better performance.

4. Architecture Tweak (Leverage Your Two Servers) #

Since you have two servers, split the workload to isolate resources:

• Run Apache + WordPress on one server (your existing 8GB box)
• Move MySQL to the second server, allocating most of its RAM to innodb_buffer_pool_size (e.g., 5GB if it's also 8GB)
  This way, Apache and MySQL don't fight for CPU/RAM, which is a common cause of spikes.

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