We had some problems enabling search because inserts into a table with a
full-text index is quite slow. Switching PACK_KEYS off seemed to help a
bit, although that may have been illusory. Despite having PACK_KEYS off,
during last peak period the edit rate on the wiki again exceeded the
maximum insert rate on suda, just as it had done in the previous two
days. This causes editing on the wiki in question to apparently cease, a
runaway increase in open mysql connections, and eventually complete loss
of service if search updates are not switched off.
A more efficient alternative was obviously necessary, so I wrote a
script to perform the day's search updates during the off-peak periods.
It scans the recentchanges table for pages to update, starting from a
timestamp saved to disk by the previous run. It obtains a write lock on
searchindex and a read lock on cur, and then updates the table for some
specified period of time (currently 20 seconds). It then releases its
locks and allows any queued edits from the wiki to complete, before
locking again and continuing.
Locking tables like this improves the performance of bulk inserts,
according to the MySQL manual. The read lock on cur is required because
an unlocked table cannot be accessed while another table is locked.
The script is in CVS at maintenance/updateSearchIndex.php, and should
work on non-Wikimedia installations without modification.
-- Tim Starling
Show replies by date