On ven, 2003-01-31 at 17:15, Erik Moeller wrote:
1) 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...
2) 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. :)
3) 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. ;)
4) 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.
5) 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)