I have recently started with an advanced MySQL book, and though I'm far
from through with it (still missing replication, among others), it got
me thinking on how to improve our DB server setup. I have some ideas
that I would like to share with you. Note that I didn't run any tests to
prove they're wise ;-)
It might be of use to add a single harddrive to each DB slaves, and use
it exclusively for temporary tables. That way, I/O won't have to wait
for other things, and many queries that don't use temporary tables will
run faster. Some (used) IDE drive might do.
From what I can see on meta, at least bacon has a RAID that can be
fitted with a battery. This will not only improve data safety, it will
also speed up writes, as a disk write can already be handeled as written
even if it only in the RAID write cache.
After a quick look at the indices (indexes?) on the MySQL tables, these
seem fine, although there are quite a lot of them. Once the new database
schema is in place, we might want to look into index usage again. If we
could rewrite some queries, we might be able to get rid of one or the
other index, which will not only save disk space, but RAM as well,
reducing the chances of Linux swapping indexes to the disk.
Also, we currently leave the choice which index to use entirely up to
MySQL, as far as I can tell. Enforcing the use of one index, depending
on the query of course, might prevent strange side effects if MySQL
choses to use the wrong index which would result in a slow query.
I take it that we have a slow query log. Do we also have a "slow page"
log from within MediaWiki? That might give us more insight into what
pages really s**k performance-wise.
There's more, but I'll stop for now. If I'm talking BS here, just tell
me ;-)
Magnus