>>>>> "Scott" == Scott Hess <scott@stripped> writes:
Scott> On Mon, 6 Mar 2000, Michael Widenius wrote:
>> >>>>> "Scott" == Scott Hess <scott@stripped> writes:
>> > The similar thing I would like to see is a parameter which indicates
>> > how much space you're willing to waste to prevent fragmentation. If
>> > the percent of wasted space were lower than the parameter and there
>> > was no single piece of contiguous space where mysql could write a
>> > record, it would then just write the record to the end of the file
>> > rather than breaking it across the available space.
>> MyISAM doesn't hold pointer to blocks of free blocks of different
>> size; It has keeps a link to a free block list. To do the above we
>> would need to add a lot of pointers (16?) to point to blocks of
>> different sizes; Not that hard, but it would break the current MyISAM
>> format :(
Scott> Hmm, I can see how that might mess things up. Since these are disk
Scott> structures, it would be hard to do the obvious fixes, such as keeping
Scott> things in sorted order, or something of the sort. Really, you'd probably
Scott> want the free list to be a BTREE of its own, indexed by size, so that you
Scott> can quickly drill down to correctly-sized free chunks...
I think that just having pointer to blocks of different sizes would
help a lot...
Scott> I wouldn't _quite_ be willing to waste disk space by simply not reusing
Scott> deleted space until you optimize the table. That would fix the data
Scott> fragmentation problem, but in my experience, the tables with enough
Scott> fragmentation to hurt performance also take a long time to optimize in the
Scott> first place (which is what makes them painful - if they were quick to
Scott> optimize, you'd just optimize them more frequently to fix the
How big tables and keys do you have; isamchk is now that slow and the
OPTIMIZE TABLE in next MySQL version will be as fast as isamchk.
Scott> One thing I'd love to see in MYSQL would be the ability to optimize in
Scott> smaller passes than currently. I've been able to segment many of our
Scott> admin scripts by using LIMIT and DELAYED on updates and deletes to keep
Scott> them from impacting other statements too much. Being able to say
Scott> "OPTIMIZE mytable LIMIT 100000" would be a truly powerful tool. Then you
Scott> could afford to optimize tables without taking the system down while doing
Scott> it (I've seen tables that took >20 minutes to optimize while the system is
Scott> still up). Unfortunately, itseems like that would be very hard to
Scott> implement as things currently stand.
If you have more than one key in the table, the difference between
isamchk/optimize is real big. As this will be fixed shortly, this
problem may already be solved...
(Doing optimize in steps isn't that good as it optimize currently
works by making a new copy of the table; Doing this only partly isn't
that easy. Doing a defragmentation on an old table is a completely