Preventing internal table fragmentation?

[Nicolas Turgeon]

Hi everyone,

I'm using a large MyISAM table in which I'm doing a lot of INSERTs and DELETEs, so that internal fragmentation quickly degrades write performance (I went from 300 writes per second to less than 50). I can't run OPTIMIZE TABLE because of the downtime it causes.

It looks like a static (fixed-length) table (without VARCHARs and the like) avoids most of these fragmentation problems. Now, I can't use a static table, but would there be a way to simulate this even with dynamic tables, by wasting some disk space and allocating space for a row in configurable block sizes slightly larger than my typical row length?

For example, my records average 500 bytes, but could (in theory) grow to several kilobytes, although they almost never do in practice. If I could set the table to allocate space in chunks of, say, 700 bytes, I would not have fragmentation 99% of the time, and even if I did, with fixed blocks of 700 bytes, it would not grow out of control.

Anybody knows if there's a way of doing this?

Thanks!

-Nicolas