List:General Discussion« Previous MessageNext Message »
From:George Larson Date:October 4 2010 11:34am
Subject:Re: Ineffective OPTIMIZE TABLES
View as plain text  
Aha!  You are precisely correct.  Thank you!

On 3 October 2010 21:16, Dan Nelson <dnelson@stripped> wrote:
> In the last episode (Oct 03), George Larson said:
>> I have an InnoDB table with a 'Data_length' of 114688 and 'Data_free'
>> of '3896508416'.  If I'm correctly understanding what I've been
>> reading, those are good conditions to optimize the table.  I
>> understand the part where it maps to 'ALTER' for InnoDB.  However,
>> nothing I do seems to affect anything.  Whether using 'OPTIMIZE' or
>> doing the 'ALTER' myself, there is no apparent difference.  I've done
>> the 'FLUSH TABLES' for good measure and the results of 'SHOW TABLE
>> STATUS' are unchanged.
>>
>> I have this same thing happening on multiple tables, I just picked
>> this particular one as an example.
>
> Are you using innodb_file_per_table=on ?  If you aren't, then you are using
> the "shared tablespace" model, and you cannot recover unused space without
> dumping all your tables, deleting the ib_data* files, and restoring.
>
> MySQL 5.5.5 has finally switched the default to innodb_file_per_table=on, but
> if you are running any older version, you will need to set that value in
> your config file.
>
> http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html
>
> --
>        Dan Nelson
>        dnelson@stripped
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
Thread
Ineffective OPTIMIZE TABLESGeorge Larson3 Oct
  • Re: Ineffective OPTIMIZE TABLESDan Nelson4 Oct
    • Re: Ineffective OPTIMIZE TABLESGeorge Larson4 Oct