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

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

Scott> On Wed, Mar 08, 2000 at 06:58:50PM +0200, Michael Widenius wrote:
>> >>>>> "Scott" == Scott Hess <scott@stripped> writes:
Scott> I wouldn't _quite_ be willing to waste disk space by simply
Scott> not reusing deleted space until you optimize the table.  That
Scott> would fix the data fragmentation problem, but in my experience,
Scott> the tables with enough fragmentation to hurt performance also
Scott> take a long time to optimize in the first place (which is what
Scott> makes them painful - if they were quick to optimize, you'd just
Scott> optimize them more frequently to fix the 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> The table in question was a mere 90M, with 33M worth of index.  The
Scott> index was on an integer (unfortunately not unique), and there were
Scott> about 3.4M rows.  It took ~20 minutes to optimize even during off
Scott> hours, causing lots of timeouts in web clients and the like.
Scott> Unfortunately, this table was hit _hard_, and had a large number of
Scott> updates (perhaps 60% of the queries to the table were updates), so
Scott> it tended to need optimized frequently (once a week).

By using the method used by isamchk (sorting, instead of inserting
indexes one by one), the time for the above should be about 1-3 minutes.

Scott> [In case anyone is paying attention, this is the table I mentioned
Scott> a month or two ago, where it had three integer columns, a timestamp
Scott> column, and a blob column, where the blob columns was _always_ null,
Scott> and no other column was ever null.  I had expected that the table
Scott> would never fragment in that case, but it still seemed to.]

Why do you have the blob column?

>> (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...

Scott> Just to be clear - deframenting a table in stages would be _great_,
Scott> but that doesn't mean it would be easy :-).

Scott> In the table I mentioned above, even a 5 minute optimize would not
Scott> be acceptable.  The only reason we ever optimized it was because
Scott> we absolutely had to.  The nice thing about a staged defragment is
Scott> that you could potentially defragment a table even during peak usage.
Scott> You'd never _want_ to do that, but right now you simplay can't, even if
Scott> it's an emergency.

We will probably look at partial fragmentation in the future, but not
real soon.

By the way, is the table of type ISAM or MyISAM;  You should get much
less fragmentation with MyISAM...

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