Author: bugzilla_wikipedia_org.to.jamesd
Description:
The code which runs this query needs to have use index(name_title_timestamp)
added. The MySQL query optimiser sometimes selects the namespace key, which is
fatally slow on old. Which is chosen seemed unpredictable, varying at different
times on the same server and different servers.
SELECT old_user, old_user_text,
user_real_name, MAX(old_timestamp) as timestamp
FROM old LEFT JOIN user ON old.old_user = user.user_id
WHERE old.old_namespace = 0 AND old.old_title = "August_10"
AND old.old_user != 82928 GROUP BY old.old_user
ORDER BY timestamp DESC;
+----------+-------------------+----------------+----------------+
old_user | old_user_text | user_real_name | timestamp |
+----------+-------------------+----------------+----------------+
176 | Mark | 20040907073128 | |
95144 | Krin | 20040824000930 | |
62 | Maveric149 | 20040812041236 | |
As a partial protection against this I've set max_seeks_for_key=10000 on the
Wikimedia servers. This should make this and similar cases prefer the index.
This query is also added to servmon/querybane for auto-kill if it causes other
queries to back up.
Version: 1.3.x
Severity: normal