Trying to Troubleshoot extremely high MySQL CPU Usage
MySQL CPU usage was spiking upwards of 1000%. Load average was around 50-60. I could not SSH into the machine though, not immediately.
Since I could not actually get into the machine I had it restarted. Just as soon as the machine came back up MySQL CPU usage jumped right back up to 1000%.
Once I was able to finally shut MySQL down I had to discover _why_ the load was so ridiculously high.
MySQL slow query log showed that it was a WordPress plugin that was making a SHIT TON of queries, and MySQL could not keep up. The plugin in question was the WP Security & Audit Log. The plugin logs activity on a WordPress sites, failed logins, successful logins, new user accounts, etc. The queries in question were all related to failed logins. Checking the Apache access logs showed me that there was a massive spike in users attempting to brute force passwords. Thankfully none of them got through.
Gathered a list of offending IPs and blocked them. Also changed all user passwords, just in case. Also disabled the plugin until a resolution can be found and an alternative put in place.
A few things here..
- MySQL is probably not tuned well, and should be tuned for this particular site.
- The audit plugin makes too many unnecessary queries and I do not think it is meant to handle the amount of traffic the site was getting. I really should look into that further.
- Our Nagios configuration does not monitor traffic spikes on websites. If it did I could have caught this _before_ it become so bad.