Last modified: 2010-05-15 15:28:23 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 730 - Recentchanges old_id updates have long waits
Recentchanges old_id updates have long waits
Product: MediaWiki
Classification: Unclassified
Special pages (Other open bugs)
All All
: Normal normal with 1 vote (vote)
: ---
Assigned To: Nobody - You can work on this!
Depends on:
  Show dependency treegraph
Reported: 2004-10-17 03:03 UTC by Jamesday
Modified: 2010-05-15 15:28 UTC (History)
0 users

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Description Jamesday 2004-10-17 03:03:46 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 
rc_title='Legend_tripping' AND 
enwiki  205 UPDATE recentchanges SET 
rc_this_oldid=6638305 WHERE rc_namespace=0 AND 
rc_title='Gholamhossein_Mosahab' 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 
rc_title='Australian_hornet' AND 
enwiki  189 UPDATE recentchanges SET 
rc_this_oldid=6638308 WHERE rc_namespace=0 AND 
rc_title='Nima_Yooshij' AND 
enwiki  184 UPDATE recentchanges SET 
rc_this_oldid=6638309 WHERE rc_namespace=6 AND 
rc_title='As15-86-11603.jpg' AND 
enwiki  170 UPDATE recentchanges SET 
rc_this_oldid=6638313 WHERE rc_namespace=6 AND 
rc_title='Apollo15missionpatch.png' AND 
enwiki  170 UPDATE recentchanges SET 
rc_this_oldid=6638314 WHERE rc_namespace=0 AND 
rc_title='City_of_Sydney' 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.

Suggested change:

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 
a while.

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 
lock needed.

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.
Comment 1 Brion Vibber 2004-10-17 04:50:12 UTC
Isn't this due to the infrequent, large, slow purges locking things up? Would it be better to just do smaller, more frequent purges?
Comment 2 Jamesday 2004-10-17 16:59:51 UTC
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.
Comment 3 JeLuF 2004-10-17 17:27:30 UTC
Cron jobs are a solution for, but not for other cases,
where web space providers might not offer a way to schedule cron jobs.
Comment 4 Jamesday 2004-10-17 17:30:49 UTC
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.
Comment 5 Jamesday 2004-10-17 22:12:05 UTC
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 
rc_timestamp index.
Comment 6 Jamesday 2004-12-12 20:52:09 UTC
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.
Comment 7 Jamesday 2005-11-12 23:17:58 UTC
assumed fixed or at least different in 1.5.

Note You need to log in before you can comment on or make changes to this bug.