Ineffective OPTIMIZE TABLES

Hello all.

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.

I'm confused. :-/ Would somebody please explain what is going on here?

Thanks!
G

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Ineffective OPTIMIZE TABLES

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@allantgroup.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Ineffective OPTIMIZE TABLES

Aha! You are precisely correct. Thank you!

On 3 October 2010 21:16, Dan Nelson 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'. =A0If I'm correctly understanding what I've been
>> reading, those are good conditions to optimize the table. =A0I
>> understand the part where it maps to 'ALTER' for InnoDB. =A0However,
>> nothing I do seems to affect anything. =A0Whether using 'OPTIMIZE' or
>> doing the 'ALTER' myself, there is no apparent difference. =A0I'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=3Don ? =A0If you aren't, then you are=
using
> the "shared tablespace" model, and you cannot recover unused space withou=
t
> 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=3Do=
n, 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
>
> --
> =A0 =A0 =A0 =A0Dan Nelson
> =A0 =A0 =A0 =A0dnelson@allantgroup.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dgeorge.g.lars=
on@gmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg