On ven, 2003-01-31 at 17:15, Erik Moeller wrote:
- Composite indexes and index use in general.... If I
understand correctly, this means that when we do a query with multiple comparisons, e.g. WHERE foo=bar AND baz=bat, only one index is used.
Might want to check into that, yes...
There are also a couple of tables with no indexes (including ARCHIVE, which may cause Special:Undelete to create high server load) and some unexplainable ones (site_stats has an index, but only one row).
Archive is very rarely used. But, it probably should have an index stuck on the namespace & title fields at least.
We really need to clean up our indexes. I can't help much with this without server access because I don't know if the table structures have been altered.
They should all be the same...
- The HANDLER command. HANDLER, which works with both InnoDB and MyISAM,
provides low-level access to tables *without locking*. You get reads, but you don't get consistency, which may cause problems when you're dealing with critical, heavily modified data.
Now you're scaring me. :)
- Upgrading to MySQL4. MySQL4 supports query caching (also subqueries,
but I haven't looked into that), which means that heavily queried pages will load faster. When someone mentioned query caching, a lot of other people made comments like "query caching rocks", so I assume it provides quite some performance benefits in practice.
I'm planning to do that this weekend. (Weekend nights are relatively low traffic. Relatively. ;)
- Tuning my.cnf. According to one IRC resident, upping table_cache can
greatly increase performance with lots of UPDATEs. If we create new indexes, we may also need to raise index_cache (there's a formula to calculate your index cache efficiency, currently we are at 99.75%, which is pretty good).
Attached are current my.cnf file and SHOW STATUS and SHOW VARIABLES output. More tweaking suggestions are welcome.
- Caching our article index. I have mentioned this idea before, I think:
We could just keep our entire article index in memory *persistently* (currently the linkcache is filled for each request), which should make link lookup almost instant. There are several ways to accomplish this:
I'm still dubious about consistency, but if someone can figure it out, great!
- We could use session variables for the purpose. There is supposed to be
an option to keep session stuff in shared memory. This might help with other caching problems as well.
It's been suggested that cookies/session vars be used for user prefs.
- We could just put a database on a ramdisk for this purpose.
MySQL has a 'HEAP' table type which is in-memory; we wouldn't have to set up a separate ramdisk. It's not persistent, so we'd either have to pre-fill the whole thing or fall back to checking 'cur' on demand.
-- brion vibber (brion @ pobox.com)