Garbage collecting/trimming ibdata1

I recently tried to run

INSERT INTO general_log SELECT * FROM mysql.general_log;

but that failed a few hours in because I ran out of disk space.
'SELECT COUNT(*) FROM general_log' returns 0, yet ibdata1 is still
49GB (started at 3GB before the INSERT; the source mysql.general_log,
a CSV table, was initially 43GB). I tried TRUNCATE then DROP on
general_log, then restarted mysqld, to no avail.

From Googling, the only thing that appears remotely relevant to
garbage collection is OPTIMIZE TABLE, but I'm not sure how to apply it
in this case (now that the table has been dropped). How do I reclaim
my disk space? Thanks in advance.
--
Yang Zhang
http://www.mit.edu/~y_z/

--
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: Garbage collecting/trimming ibdata1

--0016e6d644861c555e0480535c28
Content-Type: text/plain; charset=ISO-8859-1

Your innodb data file just auto-extended until you either reached its max or
ran out of disk space if you had no max.

The only way I know to reduce it is to dump all the innodb tables, drop the
innodb data file and logs (and drop the innodb tables if you're using
file-per-table), restart mysql, let it rebuild the innodb files, and reload
the innodb tables from the dump file.

On Wed, Feb 24, 2010 at 12:59 AM, Yang Zhang wrote:

> I recently tried to run
>
> INSERT INTO general_log SELECT * FROM mysql.general_log;
>
> but that failed a few hours in because I ran out of disk space.
> 'SELECT COUNT(*) FROM general_log' returns 0, yet ibdata1 is still
> 49GB (started at 3GB before the INSERT; the source mysql.general_log,
> a CSV table, was initially 43GB). I tried TRUNCATE then DROP on
> general_log, then restarted mysqld, to no avail.
>
> From Googling, the only thing that appears remotely relevant to
> garbage collection is OPTIMIZE TABLE, but I'm not sure how to apply it
> in this case (now that the table has been dropped). How do I reclaim
> my disk space? Thanks in advance.
> --
> Yang Zhang
> http://www.mit.edu/~y_z/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4435@gmail.com
>
>


--
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

--0016e6d644861c555e0480535c28--