R: R: Table dbmail_messages is full

This is false.

 

When using innodb_file_per_table “optimize table <table&gt;” gets mapped to “;alter table <table&gt; type=InnoDB” and the later command will issue a create from select that will result in a totally clean table, optimized and will free al the unused space. Try it by yourself, I can assure you it works: I just shrinked a 3GB .idb file to 500MB with this trick.

 

Furthermore, in the unlucky case you didn’;t use the innodb_file_per_table at first (you unlucky!) you can enable it and then later issue an alter table <table&gt; type=InnoDB. It will result in a new .idb file with just that table inside. From that moment on the optimize table trick will perfectly work. Later on when you’ll have done the alter table on every table to take them all out of the single innodb tablespace/file (which won’t decrease when tables will be getting out of it), you can just trash it and manually reclaim all your disk space.

 

From http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html :

 

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

 

See also: http://dev.mysql.com/doc/refman/5.1/en/multiple-tablespaces.html

 

 

mysqloptimize against an innodb table will not reclaim disk space.&nbsp; if anything, you're innodb file will grow. ; In fact it can end up twice as large as when you started.&nbsp; its one of the drawbacks of innodb tables.

if you want to reclaim diskspace change the table type to myisam, then run mysqloptimize against the innodb table then alter the table back to innodb.

Curtis

Andrea Brancatelli wrote:
&gt; I strongly suggest you to use the innodb_file_per_table and to run an
> optimize table dbmail_messages (and not only, also the others) from time
>; to
> time to free some disk space.
&gt;
> ciaociao
>
> -----Messaggio originale-----
> Da: dbmail-bouncesdbmail.org [mailto:dbmail-bouncesdbmail.org] Per conto
&gt; di Sascha Braun
&gt; Inviato: marted́ 31 luglio 2007 15.10
&gt; A: DBMail mailinglist
> Oggetto: Re: [Dbmail] Table dbmail_messages is full
>;
> Is that the value you where asking for?
>;
> innodb_data_file_path = ibdata1:10M:autoextend:max:128M
>
> I am now changing the max value, I hope i recognized everything
> correctly.
>
> Best Regards,
>
> Sascha Braun
&gt;
> Am Dienstag, den 31.07.2007, 16:30 +0400 schrieb alexander benaguev:
>> give us "my.cnf", please
&gt;>
&gt;> alexander
>> _______________________________________________
>> DBmail mailing list
>;> DBmaildbmail.org
>> https://mailman.fastxs.nl/mailman/listinfo/dbmail
&gt;
> _______________________________________________
> DBmail mailing list
>; DBmaildbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
&gt;
> _______________________________________________
> DBmail mailing list
>; DBmaildbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
&gt;


--
Curtis Maurand
Head Honcho
Xyonet Hosting Services
Biddeford, ME 04005
mailto:curtismaurand.com
mailto:cmaurandxyonet.com