Aargh, this is frustrating - I only seem to get 5 minutes to look before I'm rushing off somewhere else.
I'd tend to agree with Erik that the user_newtalk is a symptom.
I'd just got looking at the log when I'm dragged away, but a few things that may help... - it's not the volume of queries, it's often the query that starts the backing up. So look at the first query in the log, and the first queries after a period of time running OK. Two that I saw this way are:
SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchindex WHERE cur_id=si_page AND (MATCH (si_text) AGAINST ('math characters') AND (cur_is_redirect=0) ) AND (cur_namespace=0) LIMIT 0, 20;
and
SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchindex WHERE cur_id=si_page AND (MATCH (si_text) AGAINST ('math characters') AND (cur_is_redirect=0) ) AND (cur_namespace=0) LIMIT 0, 20;
You can also usually spot locking problems by seeing a whole batch of slow queries that finish around the same time - look for the query causing the locking problem..
Other things I've spotted, upping the table cache is a good idea - dropping the index from user_newtalk probably will make little difference if the table is so small, but shouldn't help (stranger things have happened though)
And now I really have to run!!
----- Original Message ----- From: "Erik Moeller" erik_moeller@gmx.de To: wikitech-l@wikipedia.org Sent: Monday, February 10, 2003 5:28 PM Subject: Re: [Wikitech-l] Slow query log
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!
_______________________________________________ Wikitech-l mailing list Wikitech-l@wikipedia.org http://www.wikipedia.org/mailman/listinfo/wikitech-l