List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 8 2000 4:58pm
Subject:Re: How MYSQL drives Hard Drive
View as plain text  
Hi!

>>>>> "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
Scott> fragmentation!).

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
different story...

Regards,
Monty
Thread
Hardware RAIDPeter Zaitsev1 Mar
  • Re: Hardware RAIDTonu Samuel1 Mar
  • How MYSQL drives Hard DrivePeter Zaitsev2 Mar
    • How MYSQL drives Hard DriveMichael Widenius3 Mar
      • Re: How MYSQL drives Hard DriveScott Hess3 Mar
        • Re: How MYSQL drives Hard Drivesinisa4 Mar
          • Re: How MYSQL drives Hard DriveScott Hess6 Mar
            • Re: How MYSQL drives Hard Drivesinisa6 Mar
        • Re: How MYSQL drives Hard DriveMichael Widenius6 Mar
          • Re: How MYSQL drives Hard DriveScott Hess6 Mar
            • Re: How MYSQL drives Hard DriveMichael Widenius8 Mar
              • Re: How MYSQL drives Hard DriveScott Hess9 Mar
                • Re: How MYSQL drives Hard DriveMichael Widenius9 Mar
                  • Re: How MYSQL drives Hard DriveScott Hess9 Mar
                    • Re: How MYSQL drives Hard DriveMichael Widenius9 Mar
          • Re: How MYSQL drives Hard DrivePeter Zaitsev9 Mar
            • Re: How MYSQL drives Hard DriveMichael Widenius9 Mar
            • Re: How MYSQL drives Hard DriveScott Hess9 Mar
  • Re: How MYSQL drives Hard DrivePeter Zaitsev3 Mar
    • Re: How MYSQL drives Hard DriveMichael Widenius3 Mar
Re: How MYSQL drives Hard DriveEd Carp3 Mar