List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 4 2010 1:16am
Subject:Re: Ineffective OPTIMIZE TABLES
View as plain text  
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.

	Dan Nelson
Ineffective OPTIMIZE TABLESGeorge Larson3 Oct
  • Re: Ineffective OPTIMIZE TABLESDan Nelson4 Oct
    • Re: Ineffective OPTIMIZE TABLESGeorge Larson4 Oct