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