wordpress mysql cpu 100%？

When a WordPress site causes MySQL CPU usage to hit 100%, it’s usually due to inefficient database queries, poor hosting resources, or bad plugins/themes. Here’s how you can troubleshoot and fix the issue:

🔍 Common Causes of MySQL CPU Spikes in WordPress

1. Poorly written plugins or themes
2. Unoptimized or excessive database queries
3. Lack of caching
4. Large databases without indexing
5. Brute-force or DDoS attacks
6. Too many active users or bots hitting the site
7. Cron jobs running too frequently
8. Outdated WordPress/MySQL versions

✅ Step-by-Step Fix for MySQL 100% CPU Usage

1. Check Slow Queries (Enable Slow Query Log)

This will help identify which queries are taking too long.

Enable Slow Query Log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Then check the log file (/var/log/mysql/slow.log) for problem queries.

You can analyze the output using tools like mysqldumpslow or pt-query-digest.

2. Optimize Database Tables

Use a plugin like WP-Optimize or run this SQL command:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;

Also, delete unnecessary data like spam comments, post revisions, and transient options.

3. Install a Caching Plugin

Use a full-page caching plugin like:

• WP Super Cache
• W3 Total Cache
• LiteSpeed Cache

This dramatically reduces database load by serving cached pages instead of querying the DB every time.

4. Limit Post Revisions & Autosave Frequency

Add these to your wp-config.php:

define('WP_POST_REVISIONS', 3); // Limit revisions per post
define('AUTOSAVE_INTERVAL', 300); // Autosave every 5 minutes

Then clean up old revisions:

DELETE FROM wp_posts WHERE post_type = "revision";

5. Use Indexes on Frequently Queried Columns

If you have custom tables or queries, make sure that columns used in WHERE, JOIN, or ORDER BY clauses are indexed.

Example:

ALTER TABLE wp_mytable ADD INDEX idx_column (column_name);

6. Check for Bad Plugins

Some plugins cause heavy query loads. To test:

• Deactivate all plugins.
• Check if CPU usage drops.
• Reactivate them one by one to find the culprit.

Plugins like Query Monitor can help track down expensive queries.

7. Rate-Limit Bots & Crawlers

Bad bots or scrapers can overload your server.

Use:

• Wordfence or iThemes Security to block malicious bots.
• Set rules in .htaccess or Nginx config to rate-limit requests.

8. Upgrade Hosting Plan / Use Better Stack

If you’re on shared hosting or low-end VPS:

• Upgrade to a better plan with more CPU/RAM.
• Consider managed WordPress hosting (e.g., Kinsta, WP Engine).
• Use a CDN like Cloudflare.

9. Tune MySQL Configuration

Review and optimize my.cnf or my.ini. Example settings:

[mysqld]
query_cache_type = 1
query_cache_size = 64M
innodb_buffer_pool_size = 256M
tmp_table_size = 64M
max_connections = 100

Use tools like MySQLTuner to get recommendations.

10. Monitor Server Resources

Use tools like:

• htop
• iotop
• New Relic
• Netdata

To monitor CPU, memory, and disk I/O in real-time.

🛠 Bonus: Kill Long-Running Queries

To kill a problematic query:

SHOW FULL PROCESSLIST;

Find the Id of the long-running query and kill it:

KILL <process_id>;

Summary Checklist

If you want me to help further, please provide:

• Your hosting environment (shared, VPS, cloud?)
• Any recent changes (plugins, traffic spikes)?
• Sample slow queries from logs?

Let me know!