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
--
+++ GMX - Mail, Messaging & more
http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!