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
