Thanks for the slow query log - I don't have the db in front of me now, but I'll take a look later. It may be worthwhile changing user_newtalk to InnoDb to see what happens. It's currently MyISAM, and if the talk pages are being updated frequently table locking may be a problem here, so row level locking may be more efficient.
Quoting Brion Vibber brion@pobox.com:
Slow query log from Friday morning to present: http://www.wikipedia.org/tools/slowquerylog.gz
I notice more slow queries than you'd expect on user_newtalk. It's a bone-simple query, fully indexed: EXPLAIN SELECT 1 FROM user_newtalk WHERE user_ip='some_ip_address'; +--------------+------+---------------+---------+---------+-------+------+----
---------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+------+---------------+---------+---------+-------+------+----
---------------------+
| user_newtalk | ref | user_ip | user_ip | 40 | const | 1 | where used; Using index | +--------------+------+---------------+---------+---------+-------+------+----
---------------------+
or:
EXPLAIN SELECT 1 FROM user_newtalk WHERE user_id=some_user_id_number; +--------------+------+---------------+---------+---------+-------+------+----
---------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+------+---------------+---------+---------+-------+------+----
---------------------+
| user_newtalk | ref | user_id | user_id | 4 | const | 1 | where used; Using index | +--------------+------+---------------+---------+---------+-------+------+----
---------------------+
One of these two queries happens on *every* page view; the table itself is quite small (a few dozen rows generally), and 99% of the time queries will turn up no rows. It's only altered when a user talk page is changed, or viewed by its owner.
CREATE TABLE `user_newtalk` ( `user_id` int(5) NOT NULL default '0', `user_ip` varchar(40) NOT NULL default '', KEY `user_ip` (`user_ip`), KEY `user_id` (`user_id`) ) TYPE=MyISAM
I'm assuming that the sometime slowness here is a side effect of other things being slow somehow.
-- brion vibber (brion @ pobox.com)