I work on a WordPress site that gets roughly 100K page views per day. We use Varnish for a caching layer. Recently, we've been running into a problem where, seemingly at random, MySQL performance slows to a halt and brings down our application. Here's a graph from New Relic for an outage that occurred yesterday:
This has happened twice in the past week. The site will run fine for a week without anybody touching the production servers, and then this happens out of nowhere. Usual response time is around 500ms, and here it is peaking at 60K ms(!). The problem is fixed by stopping apache, letting the database cool down, and then starting apache back up.
It's not that more queries are being run, or that a particular slow query is running and causing this problem. If I take any of the 'slow queries' that are being reported by New Relic and run them any other time, they are blazing fast. Here's a graph for MySQL throughput for the same time frame:
I've checked the stats for WordPress hooks and plugins, and everything seems to be normal.
My question is, where should I start looking? Which MySQL settings could be causing this? Is it even likely a MySQL problem, or is it more likely to be an application problem?