I finally discovered why Bugzilla is sometimes so slow (meaning: not responding for a few minutes). I used to blame it fully on table locking (Bugzilla needs MyISAM for full text indexing). My solution would be to setup replication (avoiding some of the locking). Today I finally discovered that it was actually due to people searching for all bugs they commented upon… exactly the same query I added recently to describeuser.cgi or ‘My Bugs and Patches’ (didn’t notice the query being so slow when I added it).
Initially I removed the query from describeuser.cgi and I changed the apache config to redirect these queries to a page explaining it was slowing down Bugzilla too much. After that I asked in the Bugzilla developers channel if this was fixed after 2.20. Mkanat looked into the problem and the ‘EXPLAIN SELECT‘ + query. He discovered MySQL was using the wrong index. I made MySQL forcefully use the right index. This sped up the query, but it still was slow. A few minutes later mkanat had a patch ready. Testing it gave great results. Initial query ran in 4 minutes, 30 seconds. My ‘force index’ did it in 27 secs. The fix by mkanat made it run in 1 second! This fix will be in the next Bugzilla version (probably 3.0) and on bugzilla.gnome.org. The fix is pretty easy to port to 2.20 although it will not apply cleanly; see bug 57350 if you want the patch.
Anyway, thanks mkanat!