MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 29 1999 9:54pm
Subject:Re: to much fragmentation
View as plain text  
>>>>> "Ed" == Ed Carp <erc@stripped> writes:

Ed> Is this why MySQL 3.22.21 eats up all my CPU when searching a 50K row
Ed> indexed table?  Almost no deletes, but lots and lots of adds.  Is
Ed> there
Ed> something I can do about the CPU utilization?
>> If you don't have any deletes and updates, the table should not be
>> that fragmented.
>> You can check this by doing 'isamchk -dv table_name'
>> (You don't have to take down mysqld for this)

Ed> isamchk -dv news gives me:

Ed> ISAM file:     news
Ed> Isam-version:  2
Ed> Creation time: 1999-03-16 23:59:29
Ed> Data records:             53715  Deleted blocks:              0
Ed> Datafile  Parts:          54305  Deleted data:                0
Ed> Datafile pointer (bytes):      4  Keyfile pointer (bytes):     3
Ed> Recordlength:              1032
Ed> Record format: Packed

Ed> table description:
Ed> Key Start Len Index   Type                       Root  Blocksize    Rec/key

Ed> Interesting that it doens't list a key!  Maybe that's my problem, huh?  Duh!

This means that you don't have any keys defined for news.

The file has 54305 parts and 53715 records, which means that its not
very fragmented.

>> Why do you think MySQL is CPU and not disk bound in the above case?

Ed> Because the disk light doesn't flash more than once every few seconds, but
Ed> top says it's sucking up the CPU time like there's no tomorrow :)

Doesn't sound completely right.

What kind of query are you using ?
What does EXPLAIN say for the query ?

You can easily check if this is NISAM problem by checking if
'isamchk tablename' gets slower over time.


to much fragmentationDavid Johnson26 Jun
  • Re: to much fragmentationSasha Pachev27 Jun
  • Re: to much fragmentationScott Hess28 Jun
    • Re: to much fragmentationVivek Khera28 Jun
    • Re: to much fragmentationMichael Widenius29 Jun
      • Re: to much fragmentationEd Carp29 Jun
        • Re: to much fragmentationSasha Pachev29 Jun
        • Re: to much fragmentationMichael Widenius29 Jun
          • Authentication methodsRoger Smith29 Jun
            • Authentication methodsJani Tolonen1 Jul
        • Re: to much fragmentationScott Hess29 Jun
      • Re: to much fragmentationEd Carp29 Jun
        • Re: to much fragmentationMichael Widenius30 Jun
      • Re: to much fragmentationT├Ánu Samuel29 Jun
  • Re: to much fragmentationScott Hess28 Jun
Re: to much fragmentationFred Lindberg28 Jun