WordPress+PHP+MariaDB低负载下CPU满负载问题排查求助

Hey there, let’s work through this CPU spike issue on your Ubuntu 18.04 WordPress production server. From what you’ve shared—even 5 concurrent users pushing 4 cores to 100% with 500 errors, plus MySQLTuner flags and abnormal mysqld CPU usage—this is almost certainly tied to misconfigured MySQL settings and underlying database inefficiencies. Let’s break down fixes step by step:

1. Fix MySQL Memory Overcommitment First #

Your MySQLTuner result mentions memory overuse—this is a critical culprit. When MySQL tries to use more memory than the system has available, the OS starts swapping memory to disk, which absolutely kills CPU performance (since the kernel spends most of its time moving data between RAM and disk instead of processing queries).

For your 10GB server, adjust these key parameters in your my.cnf (typically at /etc/mysql/my.cnf or /etc/mysql/mariadb.conf.d/50-server.cnf):

• Set innodb_buffer_pool_size = 4G (this is InnoDB’s main cache; allocate ~40-50% of your total RAM for it, leaving room for the OS, PHP-FPM, and other services)
• Lower key_buffer_size to 64M (only relevant for MyISAM tables, which WordPress rarely uses anymore)
• Ensure max_connections is reasonable—start with 100 (too many connections eat into memory)

After making changes, restart MySQL with sudo systemctl restart mysql and monitor memory usage with htop or free -h to confirm swap isn’t being used heavily.

2. Eliminate Disk-Based Temporary Tables (83% Ratio) #

83% of temporary tables hitting disk means your in-memory temporary table limits are too low, forcing MySQL to write to slow disk storage for query processing—this is a huge CPU drain.

Adjust these settings in my.cnf:

• Set tmp_table_size = 256M and max_heap_table_size = 256M (these values must match; they control the maximum size of in-memory temporary tables)
• Enable slow query logging with a low threshold to catch queries that trigger disk temp tables:

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

Restart MySQL, run your load test again, then analyze the slow log with mysqldumpslow /var/log/mysql/mysql-slow.log to find unindexed queries or poorly optimized WordPress queries (even core WP queries can be inefficient if tables lack proper indexes).

3. Fix Low Table Lock Acquisition Rate (84%) #

An 84% lock acquisition rate means MySQL is waiting on table locks regularly, which stalls query processing and spikes CPU. Here’s how to fix it:

• Convert all WordPress tables to InnoDB: MyISAM uses full table locks, while InnoDB uses row-level locks which are far more efficient. Run this command for your WordPress database (replace wp_db with your actual DB name):

  ALTER TABLE wp_db.wp_commentmeta ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_comments ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_links ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_options ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_postmeta ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_posts ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_terms ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_term_relationships ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_term_taxonomy ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_usermeta ENGINE=InnoDB;
  ALTER TABLE wp_db.wp_users ENGINE=InnoDB;
  

• Check for long-running transactions with SHOW ENGINE INNODB STATUS;—look for the TRANSACTIONS section to find queries holding locks.
• Lower innodb_lock_wait_timeout to 10 (defaults to 50) to prevent stalled queries from holding locks too long.

4. Resolve MySQL Error Log Warnings & Errors #

Your error log has 8 warnings and 3 errors—these could be contributing to instability. First, run a table integrity check to fix any corrupted tables:

mysqlcheck --all-databases -u root -p

If you see corrupted tables, use mysqlcheck --repair --all-databases -u root -p to fix them.

Common warnings/errors to look for:

• InnoDB log file size mismatches (adjust innodb_log_file_size to a reasonable value like 256M—you’ll need to stop MySQL, delete old log files, then restart)
• Permission issues on MySQL directories (ensure mysql user owns /var/lib/mysql and subdirectories)
• Outdated table formats (run ALTER TABLE [table_name] FORCE; to update tables)

5. Quick PHP-FPM Sanity Check #

Even though you disabled plugins, double-check your PHP-FPM configuration (usually at /etc/php/7.2/fpm/pool.d/www.conf) to rule out resource contention:

• Set pm.max_children = 20 (for 4 cores, 20 is a safe starting point—too many children eat memory and cause CPU context switching)
• Set pm.start_servers = 4, pm.min_spare_servers = 2, pm.max_spare_servers = 8
  Restart PHP-FPM with sudo systemctl restart php7.2-fpm after changes.

Final Testing Workflow #

After each change:

1. Restart the relevant service (MySQL/PHP-FPM)
2. Run your 5-users-per-second load test
3. Monitor CPU usage with htop and MySQL metrics with mysqladmin status
4. Check for 500 errors in /var/log/apache2/error.log or /var/log/nginx/error.log (whichever web server you’re using)

Start with memory adjustments first—those will have the biggest immediate impact. Then tackle temp tables and locks, and finally resolve error log issues.

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