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