Last modified: 2010-05-15 15:33:08 UTC
At Wikimedia sites the following page2xml query is now being automatically
killed on sight, after use of it caused a problem on one of the database servers
/* page2xml */ SELECT old_id as id,old_timestamp as timestamp,old_user as
user,old_user_text as user_text,old_comment as comment,old_text as
text,old_flags as flags FROM `old` WHERE old_namespace='4' AND
old_title='whatever' ORDER BY old_timestamp
This query asks for all versions of the article, at about one disk seek per
revision for the current schema, unless the revision is in cache. Worse, it ends
up using the old_title index, so it actually retrieves from disk all revisions
in all namespaces. At the time the server overload caused me to take a look
there were three of these running with run times of 891, 583 and 339 seconds.
The last was killed by the changed querybane rules after 805 seconds.
Adding use_index(name_title_timestamp) improves it significantly and should be
done but it still needs a limit added to it.
The planned schema change, if it stores old articles with article ID as the
first part of the primary key, would make this far more efficient because
adjsacent revisions would be in the same database pages. It would still
potentially retrieve hundreds of thousands of revisions for a popular article so
some limit is still going to be needed even with the new schama. If page2xml is
intended to be a general retrieve an article call, that limit needs to be very
low - tens not hundreds of revisions.
For Wikimedia sites, limit 10 is a good choice at present, assuming the primary
purpose isn't retrieving the article history.
enwiki doesn't have the old_title index, nor has a fresh REL1_4 install.
dewiki has this index.
Should old_title index be dropped? I'm not aware of any queries that use
old_title but not old_namespace.
The old_title index was in some of the wikis - de, fr, pl, probably others, but
not in the new ones I looked at. I'll remove it when practical, since it's not
present in the newer wikis. The query does select the correct index when that
one is removed. Not going to be enough to make it practical to return all old
metadata and text, though.
Obsolete - old is gone now.