OK, I’ve optimized (read, recoded) a BUNCH of queries on the server – some of them in the old Perl code (which really is beginning to show its age now!) and a couple in the new .Net code (which shouldn’t have barfed but oh well you live and learn)…
The slow query log is trapping any queries that take too long to run. So far there haven’t been any since 21:06 (which is errr, 4 and a half hours ago) and they only popped up because I dropped a couple of indexes which caused some slow down.
All the queries are now running a LOT faster, there’s some extra tables for counters and things rather than calculating counts on the fly, and the Top Ten Diaries has been split out into seperate queries to enable MySQL to stand a chance 🙂
All in all, the server seems to be flying along now (and there’s actually STILL room for a LOT of optimization I noticed in my digging around in there) so in theory we should be beginning to see the light at the end of the tunnel.