List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 6 2000 1:21am
Subject:Re: How MYSQL drives Hard Drive
View as plain text  
Hi!

>>>>> "Scott" == Scott Hess <scott@stripped> writes:

Scott> On Thu, Mar 02, 2000 at 07:27:30PM +0200, Michael Widenius wrote:
>> >>>>> "Peter" == Peter Zaitsev <pz@stripped> writes:
Peter> Other important problem is DATA fragmentation. Well this
Peter> should be the mostly FS problem but I do not think it is
Peter> solved much in EXT2 for example.  I have about 20.000 of
Peter> which most are continualy grothin simultaneosely. In
Peter> this case standart space allocation mechanisms will produce
Peter> a hard drive fragmentation if I'm not mistaken.
>> 
Peter> The nice thing would be allowing to specify with create
Peter> table something like "groth chunk" which may decrease
Peter> fragmentation.
>> 
>> The problem with the above is mainly that this would confuse
>> myisamchk a lot.

Scott> Why?  Couldn't it just be allocated as empty space, as if you added
Scott> a record and then deleted it?  Even if myisamchk truncated it away,
Scott> that's no big deal (because you don't really run myisamchk all that
Scott> often).

If I remember correctly. the main problem was that when we preallocated
a lot of files, isamchk had to do a lot of extra work to check
the 'empty' space for possible rows;  Nothing really serious but at
that time it was more than it was worth...

>> (The MyISAM table format has actually reserved space for relocation
>> information so it wouldn't be that hard to fix this..)

Scott> The similar thing I would like to see is a parameter which indicates how
Scott> much space you're willing to waste to prevent fragmentation.  If the
Scott> percent of wasted space were lower than the parameter and there was
Scott> no single piece of contiguous space where mysql could write a record,
Scott> it would then just write the record to the end of the file rather than
Scott> 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> On a small/personal system, you might set this to 0% to invoke the current
Scott> space-saving behaviour.  On a beefy RAID array with 3-4x as much space
Scott> as you need for the actual data, you might set this to 100%, wasting
Scott> space in the interests of reducing fragmentation.

Scott> [This is the place we're in on one production system.  We needed to
Scott> RAID 6 fast drives to get the seek performance we needed, but we
Scott> really didn't need 100Gig of disk space.  It's just hard to buy
Scott> smaller drives these days...]

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