Re: Preventing internal table fragmentation?

[James Day]

One possibility to consider is InnoDB - it'll cache the writes to the data pages and do log writes which are mostly sequential, cutting out a lot of your random disk load. Particularly helpful if you have a lot of records which tend to be updated many times within a short period, since the same dirty page might be updated many times, with only one random write at the end. Choice of a primary key (clustered index) which matches your typical query is important for performance with InnoDB. You'll need to choose just how far you want InnoDB to go on the ACID guarantees front - it has many options for you to tweak between maximum speed and maximum safety.

Sticking with MyISAM, your approach makes sense. Might be worth trying a split record, with the most varying field(s) or only those above a certain size threshold, in a different table which gets heavily fragmented but might be small enough for you to optimize it. Still involves you doing the work in your application, though.

Personally, I'd give the InnoDB approach a try to see if it can do the job without the extra work. Easy enough to try it and see what happens. Even InnoDB does benefit from defragmentation from time to time, though - if you're able to find a chance to do it. I usually can, with tables up to the few gigabytes range. For the bigger ones, not a chance - people notice.

If none of this works, one other option is a master-slave setup. Take the slave offline periodically and optimize it, start replication again and let it catch up. Then switch to having it as master and optimize the former master now it's out of service. Not the most convenient method but it gets the job done and you get a backup server for some types of disaster recovery as part of the package.

If uptime is critical and you have many gigabytes of data you might also consider the time it takes MyISAM and InnoDB to repair tables after a crash. InnoDB looks better and better for this as the amount of data you have, and the time for a MyISAM repair, grows. InnoDB repairs are also more likely to succeed first time than MyISAM, in my experience.