Last modified: 2010-05-15 15:28:23 UTC
Sequences like this for recentchanges happen several
times an hour:
DB Time Query or State
-- ---- ----------
enwiki 239 UPDATE recentchanges SET
rc_this_oldid=6638303 WHERE rc_namespace=0 AND
enwiki 205 UPDATE recentchanges SET
rc_this_oldid=6638305 WHERE rc_namespace=0 AND
enwiki 204 UPDATE recentchanges SET
rc_this_oldid=6638306 WHERE rc_namespace=6 AND
rc_title='ZeroG.jpg' AND rc_timestamp='20040623185548'
enwiki 190 UPDATE recentchanges SET
rc_this_oldid=6638307 WHERE rc_namespace=0 AND
enwiki 189 UPDATE recentchanges SET
rc_this_oldid=6638308 WHERE rc_namespace=0 AND
enwiki 184 UPDATE recentchanges SET
rc_this_oldid=6638309 WHERE rc_namespace=6 AND
enwiki 170 UPDATE recentchanges SET
rc_this_oldid=6638313 WHERE rc_namespace=6 AND
enwiki 170 UPDATE recentchanges SET
rc_this_oldid=6638314 WHERE rc_namespace=0 AND
Notice the timestamps, for a time before any existing
recentchanges records. Show innodb status indicates
that they are waiting for a lock on the rc_timestamp
index, presumably all waiting for the first block,
since all are before any entries.
A. Check the timestamp to see if it is earlier than
the recentchanges purge time limit. If it is, don't
update: if the record still exists it is going to be
purged soon anyway.
This would be less pleasant for low traffic wikis,
where purges may be relatively infrequent and the
records without the information would stay around for
B. Unnecessary if A is done, better overall.
If the timestamp is before the purge threshold, just
do the update. If not:
Use the rc_timestamp index and search for any entries
with the timestamp from the planned update, limit 1.
If no results, there's no work to do and no write
Do the search on a DB slave. Two possible wrong
result cases for a slave:
1. New record or edit. The timestamp will be within
the purge threshold so this code path will never
2. Record just purged from the master, still
replicating. This will show a hit and a need to
update the record. The master will get an unnecessary
update, just as it does now. But far less of them.
Isn't this due to the infrequent, large, slow purges locking things up? Would it be better to just do smaller, more frequent purges?
Purging from a cron job or jobs in small bites off peak would be ideal for the
bigger databases. No need to do them in connection with an end user operation
which could happen at a high load time. delete low priority where timestamp <
threshold (for mysql 4.0.0 and later: order by rc_id) limit 20 at fairly
frequent intervals might be a reasonable approach. I expect that to be OK with
end user things at peak times as well, just not as nice to have the load then
when we don't care about prompt purging.
These queries are the longest running ones on the server at the time they
happen. That seems to argue against them waiting for the purge. But something is
causing them to wait and I don't know what it is. Want a copy of the log with
show processlist and show innodb status for some of these to see if you can see
what's causing them? I look but haven't found it yet, other than that
rc_timestamp index being involved. It might be the delete having happened and
something in the database engine keeping a lock for a while. Maybe.
In any case, there's no point in even trying most updates with timestamps this
old because they are to records which are well before the purge threshold. Just
wastes some master disk/CPU time without any benefit to try it. Quick check on
the slave and then discard the potential update if the slave doesn't have it is
the way to go for any which are less recent than the purge threshold. I'm
curious about the cause of the delay but since the updates aren't necessary
anyway I can happily forget that curiosity this time.
Cron jobs are a solution for wikipedia.org, but not for other cases,
where web space providers might not offer a way to schedule cron jobs.
Definitely agreed. This sort of thing is one of the cases where the way
Wikimedia and other big projects work best is different from how smaller and
shared hosting places need to work.
A update on these: it's not only older records which are
affected, it's all. The problem is a lock on the rc_timestamp
index which is being used to find the rc_id value so the
record can be updated. rc_timestamp is more unique than the
combination of rc_namespace and rc_title (about half the
number of matches). Asking a slave to look up the rc_id value
and then updating via the rc_id will reduce the demand for the
Please at least implement at least a configurable time threshold, if not the
existence check, for 1.4. These rc_old_id updates are by far the most frequent
cause of save delays, happening many times a day and it's completely unnecessary
to try to update RC records several weeks ago when we know that RC entries are
kept for little more than a week.
assumed fixed or at least different in 1.5.