We have a virtualized LAMP+memcached+nginx setup which, after some tweaking a few months ago, has been performing well enough until recently (3-4 weeks ago).
During peak time, the server becomes nearly unreachable, usually with nginx giving a 500 -- which despite my original assessment does not seem to be timeout-related, as this often happens within a second or two. Looking at our munin graphs, MySQL appears to be the culprit, but I can't understand why exactly.
Before this started happening, queries/second would rise around peak time as expected, eventually reach a manageable peak and die down a few hours later. Now, although the peak isn't any higher than it used to be, performance tanks as soon as the peak is reached, as reflected by the throughput and queries/second metrics.
CPU is at least 50% idle, there are several gigs of memory still unused, and there is no swapping or I/O contention to speak of. Slow MySQL queries tops out at about 0.01-0.02 per second, and there are rarely more than 15 threads running at the same time. The one thing I did note was a slight (2-4K blocks/second) rise in writes to the MySQL disk coinciding with each of these episodes, which I have to believe is related although I cannot see directly what the connection is.
mysqltuner suggested a few memory-related changes, mainly the following:
query_cache_limit (> 4M, or use smaller result sets) sort_buffer_size (> 32M) read_rnd_buffer_size (> 32M) innodb_buffer_pool_size (>= 9G)
I am having trouble assessing what impact these changes might have, if any, or what the main issue could be given that there is no obvious contention that I can spot. The main bulk of the database is some 10 million user records and related data, about 150k of which are accessed daily and some 1.3 million monthly.
Any advice on how to proceed is appreciated.
APPENDIX: It appears nginx is often failing with "24: Too many open files", including when attempting to talk to Apache. ulimit says the file handle limit for nginx is 1024 -- is this too little?
APPENDIX 2/ANSWER: Well, embarrassing as it may be, I found the answer to my own issue, and it's related to the appendix above.
Nginx was hitting the limit for concurrently open file handles, which was set to 1024. As a result, many of the requests headed for Apache were never delivered, which caused a natural drop in MySQL throughput -- not because MySQL was underperforming, but because fewer requests ever made it that far. It doesn't quite explain the increased disk activity, but it may be that this activity always existed even before the problems emerged -- sadly, the performance graphs don't go back that far.
Anyway, I added "worker_rlimit_nofile 2048;" to nginx.conf, which overrides what ulimit thinks about max file handles, and performance has been restored. If someone else ever comes across this question, well, here's the answer. ;)
(For the record, I can't /actually/ answer my own question because this question is too new.)