1

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:

Application performance graph

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:

Database throughput graph

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?

  • Brute-force bots visit Wordpress sites regularly and hammer your login script for 20 minutes at a time. It might be worth checking to see if that's happening to you. – Ladadadada Sep 27 '16 at 15:42
0

Before getting into something application specific, I would start with the basics. It doesn't take long or much effort.

Start with some of the basic/generic server diagnostics stuff. top/iostat/atop/htop/iotop/free/df/vmstat, etc... during this time. Report back with what you find. My personal preference is atop for many things (it also runs in the background and records a log kind of like sysstat). Look at memory, swap, disk IO, load, etc... everything.

Then look at logs like the kernel log (or dmesg output) and syslog messages.

Then look at mysql and your web services when all else fails to reveal anything.

0

Do you have everything in one machine? Are you sure your server(s) are not swapping? During the problem can you check with top and friends if your workload is CPU / Memory / IO bound?

Do you collect this data? If not start installing systat (sar and friends) so you can check what happened system wide after the problem happens.

Then enable the slow logging on both PHP and MySQL:

1) https://stackoverflow.com/questions/8560579/how-to-monitor-slow-php-processes

2) https://stackoverflow.com/questions/11606972/how-can-i-enable-slow-query-log-on-my-server

Having this data you can pinpoint the eventual problem or have a better understanding of what is happening and target better your monitoring or adapt your setup ecc.

0

In addition to the other tools mentioned, I'd suggest using 'mytop' to see what mysql is doing when it slows down. It is very top-like and shows stats like queries/sec as well as actively running queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.