As always we write code and as soon as the dust settles in, our code seems to fade away into nothingness and you forget about it right until the moment the code decides to slap you in the face by grinding your server to an halt. After a quick inspection, the server load tells it's story. You'll find a few processes eating away most IO, Mem and Proc resources.
Somewhere during your second inspection, your memory starts firing up and while your minds kicks in you notice that small perl/php script meant to get a rewrite. It creates all those speedy-aggregates, in order to show some nice statistics. The sentence, "it's only temporary" flashes around your braincells while you try to gain some measure of control back over your servers.
Armed with a dozen websites and books about patterns, tdd, agile and refactoring you fire up your favorite editor (vim) and drop back in your chair. You browse through your code, it all looks and feels good. It's definetly your coding style and according to GIT, the code hasn't changed for over a year. It once ran smooth and fast, what happened today? Any upgrades? Any downgrades? You check all the logs, the filesystem and data providers. It's all green lights around you, it definetly must be something within your code ...
After some more digging and poking around in the code, it's all clear. The code works properly for, it can't be the code. It must be the next logical thing, it must be our data. After some preliminary testing, you start to see that fetching the data grinds the server to an halt.
Sniffing about with "EXPLAIN Query, SHOW FULL PROCESSLIST, KILL QUERY" show the cause. MySQL performs a nasty and unneeded filesort. While reading the MySQL manual you learn a hard lesson. Even though you're a man, read the fucking manual!
MySQL always performs an ORDER BY while using a GROUP BY statement. It's possible to make the pesky filesort go away by manually applying an ORDER BY NULL clause to your Queries.
In order to solve my problem I spent about an hour digging around in my own code and an hour orso to find the culprits. In order to come to my path for enlightment I made heavy use of tools like vmstat, iostat, bash, cut, grep, ps, top, uptime, awk, ls, find, time, mysql (with EXPLAIN, KILL QUERY, SHOW FULL PROCESSLIST), head, tail and Vim. Without 'em I wouldn't have had a clue! Learn the tools to make your job easy! I know I'll also start to look into how to better apply syslog, load monitoring, ltrace, strace, patch and dbg. It's worth it!