On dim, 2003-02-09 at 19:23, Brion Vibber wrote:
I've managed to catch it in the act; attached are two snapshots of "SHOW PROCESSLIST" taken about 45 seconds apart. A whooole bunch of threads are stuck on the user_newtalk query in "Statistics" state; after about a minute they fall apart and there are a bunch of cur queries in "Closing tables" and "Opening tables".
The only information I've dragged up so far on the 'statistics' state is this thread: http://forums.devshed.com/archive/4/2002/11/4/46232
Apparently it's something to do with joins... or indexes... or finding which indexes which are sometimes relevant to joins... Anyway, I'm not sure how much benefit an index has on a table with 75 rows and only constant SELECTs, 99% of which are checking for values that *don't* appear in the table. I've dumped the indexes on user_newtalk for now, see what that does...
Personally, I doubt that it is at all related to user_newtalk. If you check the slow query log, you will notice that we have queries that take up to
1000 seconds. While such a killer query runs, my guess is that everything else
slows down. Since user_newtalk is queried on every pageview (including recent changes etc., and for anons and non-anons), it's bound to show up in the slow query log more often than other queries. Even then, it is predictably the fastest among the slow queries. Similarly, simple queries like looking up a user by ID occasionally show up in slow query log, probably again due to general slowdowns of the server.
Is there a way to specify how long a query can take? Most queries are not critical, and maybe we should just timeout after 20 seconds and show an error message (which, technically, should *never* come up, as queries really shouldn't take that long).
Regards,
Erik