WordPress向MySQL发送大量SLEEP查询致数据库连接耗尽求助

Hey, let's break this down step by step since you're still learning Apache/MySQL configs—this is a tricky one but totally debuggable!

First: Verify if unlogged Apache requests exist #

It’s absolutely possible for requests to bypass access_log—here’s how to check:

• Check Apache’s error log (error_log): This log often captures traffic that access_log misses, like internal redirects, 5xx errors, or module-level requests that fail before reaching the logging stage. Look for repeated entries around the time your server choked.
• Audit your Apache log configuration: Open your httpd.conf (or site-specific config file) and check for rules that exclude requests from logging. For example, you might see something like:

  SetEnvIf Request_URI "^/favicon.ico$" dontlog
  CustomLog logs/access.log combined env=!dontlog
  

  This skips logging for specific paths. Double-check if any rules could be hiding traffic that’s spawning database connections.
• Use tcpdump to cross-reference traffic: On your Apache server, run two concurrent captures to match MySQL traffic with HTTP requests:

  # Capture MySQL traffic to RDS
  tcpdump -i any host [YOUR_RDS_IP] and port 3306 -w mysql_traffic.pcap
  # Capture HTTP/HTTPS traffic
  tcpdump -i any port 80 or 443 -w http_traffic.pcap
  

  After letting it run for a few minutes, analyze the captures. If you see MySQL connections that don’t have a corresponding HTTP request, you’ll know the issue is coming from something outside Apache’s web request pipeline.

Second: Dig into those mysterious MySQL SLEEP connections #

The NULL entries in SHOW PROCESSLIST usually mean the client (Apache/PHP) disconnected without properly closing the database connection. Let’s narrow this down:

• Check for persistent database connections: In your PHP config (php.ini), look for mysql.allow_persistent and mysqli.allow_persistent—if these are set to On, persistent connections might be piling up without being reused or closed. Temporarily set them to Off, restart Apache, and see if the connection count stabilizes.
• Inspect W3 Total Cache closely: Even if its SLEEP code seems hard to trigger, try disabling the plugin entirely for 15-30 minutes. Cache plugins often run background tasks (like cache pruning or preloading) that can leave hanging connections, especially if there’s a bug in how they handle MySQL connections.
• Get more details from MySQL: Instead of just SHOW PROCESSLIST, run this query to see extended connection metadata:

  SELECT id, user, host, db, command, time, info 
  FROM INFORMATION_SCHEMA.PROCESSLIST 
  WHERE command = 'Sleep';
  

  Look for patterns—do all these connections target the same database? Are they all from the same Apache server port? This can hint at which plugin/script is responsible.

Third: Rule out background/timed tasks #

Most WordPress sites rely on tasks that don’t come through Apache’s web interface—and these won’t show up in access_log:

• Check system-level cron jobs: Run crontab -l (use crontab -u apache -l to check the Apache user’s crontab) to see if any PHP scripts are running on a schedule. These scripts connect directly to MySQL without touching Apache.
• Audit WordPress WP-Cron: In your WordPress admin, go to Tools > Site Health > Info > Cron Scheduled Events to look for abnormal or frequent tasks. You can also use WP CLI (if installed) with wp cron list to get a clean overview. Misconfigured cron jobs can spawn hundreds of hanging database connections.

Fourth: Hunt for connection leaks in code #

If none of the above fix it, you might have a plugin that’s leaking connections (opening them but never closing them, especially during errors):

• Enable detailed PHP error logging: Update php.ini to set error_reporting = E_ALL and log_errors = On. Uncaught exceptions can leave connections open, and the error log might point you to the problematic plugin.
• Track which processes hold MySQL connections: On your Apache server, run:

  lsof -i :3306 | grep ESTABLISHED
  

  Note the PIDs of processes with active MySQL connections. If the PID belongs to an Apache child process, it’s tied to a web request—if it’s a standalone PHP process, it’s a background task.

Start with the tcpdump and cron checks first—those are the most likely culprits when access_log shows no traffic but MySQL is overwhelmed.

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