Searchindex update

The searchindex update job is database-intensive and slows down all slaves when running, to the point that search must be turned off for them. So, all slaves have been set not to replicate updates to the searchindex table. Here's how to handle searchindex updates.

Ariel is set to start the rebuild using a cron job.

1. Ariel updating. When you notice Ariel processing searchindex updates, edit CommonSettings.php and take it out of load sharing, then switch to a shell on Ariel and type /home/wikipedia/bin/foreachwikidb localhost "alter table searchindex disable keys;". This disables key updates for the searchindex tables on all wikis, making the update process complete within a few hours. But no search index means fulltext search is so slow as to be completely imposible, so, must be no load until the indexes are turned on again. Note: In December before MediaWiki 1.4 you should not disable the keys. Ariel now has trouble handling the load of the index rebuild when it's receiving most queries.
2. When ariel has finished updating the indexes (you can check on zwinger by typing ps aux and looking for the master and sub-jobs) type this on a shell on it: /home/wikipedia/bin/foreachwikidb localhost "alter table searchindex enable keys;". This causes the indexes to be turned back on and rebuilt. Once this operation is complete Ariel can be put back into load sharing. It takes about two hours while Ariel is under early morning load. In December before MediaWiki 1.4 you should not do either this or the preceding disable step.
3. For each slave in turn:
   1. Take it out of load sharing, switch to a shell on it and type /home/wikipedia/bin/foreachwikidb localhost "drop table searchindex;" (takes a couple of minutes) then /home/wikipedia/bin/foreachwikidb localhost "load table searchindex from master;" (takes about four hours on Bacon) and finally /home/wikipedia/bin/foreachwikidb localhost "optimize table searchindex;"
      1. The backup-only apache slaves and Albert normally have a low value for key_buffer_size, perhaps 16MB. You can significantly speed up the process by using show status then set global key_buffer_size=200*1024*1024;. Replace 200 with whatever value seems prudent from the cache size shown as available in top. You'll need to be a MySQL SUPER/root user to do this. Once the searchindex updating is done you should set it back to the value shown in the show status report, to free up the RAM for Apache.
   2. If time is available you can speed up search and other operations on this slave by typing /home/wikipedia/bin/foreachwikidb localhost "alter table cur engine=InnoDB; alter table recentchanges engine=InnoDB; alter table watchlist engine=InnoDB;". This takes 1-2 hours on Bacon.
   3. Then put the slave back in load sharing and move on to the next.

When the database servers on the slaves are reniced to -5 the slave can be unresponsive or slow during the rebuild steps, particularly for large wikis like en, de and fr. You can accept this or renice 0 during the operations if you prefer. Renice requires Linux root capability.

SQL permissions

If you see an error like this

<tt>ERROR 1188 at line 1: Error from master: 'select command denied to user: 'repl@ikhaldun' for table 'search'

switch to the MySQL master and as a SUPER/root MySQL user type:

grant select on *.* to 'repl'@'khaldun';

grant select on *.* to 'repl'@'ikhaldun';

Replace khaldun with the name of the server you're working on.