INNODB storage engine - reclaim space

Hello everyone,

Currently we are using MySQL 5.0.51a version and also using the INNODB storage engine. Now if we drop a table from the tablespace, can you reclaim that space.

In Oracle one can Coalesce a tablespace after a large table has been dropped from a tablespace. Thus the system reclaims the space. I understand currently at least with the version that we are on that when we drop a table from INNODB, that the data is dropped and reusable but that the tablespace does not shrink down in size after the table has beed dropped. Is there any way to do this, except taking a export and dropping the whole schema and reimporting it back into a new tablespace?

Thoughts or advice here? Also is there any other commands or ways to do this in either 5.1 or 5.5 and what about 5.6 Beta Test. Also is there actually a 3rd Storage Engine (yet unknown name) on the future as well, or a change to INNODB that would allow this?

Thanks in advance...

Penn

Note: that it is our intent here shortly to Upgrade our shop to Enterprise edition 5.5 Now it looks like one can Coalesce a tablespace within that version, is that true? Are there any bugs or how exactly does it work?

Thanks...

Not unless you enabled innodb_file_per_table before you created your tables (it's off by default).


I'm not aware of any plans for a 3rd storage engine, although it's not something I pay a lot of attention to. There is an open feature request (http://bugs.mysql.com/bug.php?id=1341) to add this feature to InnoDB; don't hold your breath though, it's already been open for 9 years. You can set innodb_file_per_table, but of course that isn't retroactive.


I don't know

You could do a DB migration as part of the 5.5 maintenance window. Depending on data size, it could be a non starter, but that would be the time to get to file_per_table.

I'd want a sql dump anyway besides a binary dump before a major update.

Hey @ByGoneYrs,
In InnoDB there is a single tablespace by default and all data is stored in only one file. The innodb_data_file_path parameter defines the file. In order to reclaim space on InnoDB storage engine, you pretty much have to first export all and re-import:
1. First, of course, backup all your InnoDB tables and export.
2. Change the setting in my.ini/my.cnf as innodb_file_per_table file. There are two options you can try:
a. One option provided by InnoDB is having separate files per table, where the data and indexes of each table can be stored in a separate file using a global variable innodb_file_per_table.
b. Anothe r option is having a fixed tablespace size, which is done by removing autoextend and setting the tablespace size to an extrapolated value. When the limit is reached, you need to clean data.
3. Restart your MySQL server and import the reconfigured tables.
Each of the tables should have its own tablespace and it should be able to can shrink when data is deleted.
Note: There is another option, which is moving all tables, or even whole databases, that contain non-crucial data to MyISAM.
It does require some fiddling, but I hope this helps you a bit
Ronny

This will cause more problems than it is worth on top of going from transactional to non-transactional storage engine and a much worse locking mechanism.

Mysql 5.6(beta) expands innodb-file-per-table to allow you to put each table file in its own directory(could be different disk). This will allow much more flexibility with innodb and is a long waited for enhancement. You could prepare for this by going file per table in advance.

I'm also excited to play with InnoDB full text search in MySQL 5.6. I have a lot of MyISAM tables due to this fact..

I also sat in on a presentation on using Sphinx during Percona Live NYC last week, and I plan on learning, testing, benchmarking, and possibly integrating that into our forums.