MySQL Forums
Forum List  »  Performance

Preventing internal table fragmentation?
Posted by: Nicolas Turgeon
Date: March 31, 2005 10:12AM

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?



Options: ReplyQuote

Written By
Preventing internal table fragmentation?
March 31, 2005 10:12AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.