Bug #50017 ibdata file size never decreases
Submitted: 31 Dec 2009 22:14 Modified: 1 Feb 2010 17:16
Reporter: Kevin Benton Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:All OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb

[31 Dec 2009 22:14] Kevin Benton 
Description:
InnoDB never allows for the ability to decrease the size of the ibdata file set when large data sets are removed from the database.  For example, we are choosing to change to one-file-per-table.  Simply doing an alter table should move the data out of ibdata, but it doesn't deallocate the data.  That makes it nearly impossible to switch completely from one table for all data to one-file-per-table without having at lest 1.2 times the space available as is what's needed to store the data.  In my case, I'm trying to move from a set of data that's 70GB in a 90GB ibdata file to a one-file-per-table method but I don't want to have to add another 100GB to the file system just so I can make this change.

How to repeat:
See description

Suggested fix:
See description
[1 Jan 2010 17:16] Valeriy Kravchuk 
What you describe is a known and documented InnoDB behavior, by design. 

Moreover, AFAIR other RDBMSes that use a concept of "tablespace" (in Oracle terms) does NOT make tablespace smaller just because you freed some (or most, or even all) extents in its "data file"(s). This always require some administrative actions, not always SQL-based (at least this is true for Oracle and IBM Informix).

So, what exact new feature(s) are you suggesting to implement? Shrink shared tablespace when you drop tables there, just because you now use innodb_file_per_table? And then, when data dictionary will need to grow, allocate it back? Or, maybe, you can suggest some SQL statement to "optimize" shared tablespace? Please, be more specific.

In any case it will be a new feature request, not a bug report.
[2 Feb 2010 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".