I’ve been getting paged regularly in the last week to alert me to the fact that the site is (once again) slowing down.
Bah, I hate when that happens.
Anyway, I’ve been looking through logs and checking the systems self diagnosis and it would appear as though, on occasions, the database has been spammed with queries looking for diaries updated in the last x days (where x < 7). This could very easily be real usage of the system, since once one query collapses, the whole darn lot goes on strike – the idea with MySQL being that its fast enough that it doesn't NEED to deal with lots of users concurrently… We could argue about whether that methodology is really workable in a real situation until we get sick, but the point is, I think I have pinpointed where the problem is, and hopefully have made a tweak to the db parameters to optimize that area of the system…
Certainly, so far, my slow-queries log has no queries logged to it any more, whereas it was regularly getting certain queries (Matt will know the ones I mean if I say it had an IN clause) logged as taking 3 seconds or more to complete.
Hopefully, that tweak was all we needed. But if not, I shall keep my eye on my pager and see what comes up next 🙂