MySQL Bugs: #1341: InnoDB ibdata1 never shrinks after data is removed

Bug #1341	InnoDB ibdata1 never shrinks after data is removed
Submitted:	18 Sep 2003 14:40	Modified:	30 Nov 2005 20:50
Reporter:	Scott Ellsworth
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	All versions	OS:	Any
Assigned to:	Heikki Tuuri	Target Version:
Triage:	Needs Triage: D5 (Feature request)

Description:
Description: The Innodb table handler does not reclaim space and return it to the OS once
it 
allocates it, and it would be very good if it did so.  A manual step would meet our
needs.

As part of a new product we are developing, we have been loading, manipulating,
exporting, and 
dropping 5G of data in a single database using a dozen tables with the Innodb table type,
and 
after dropping the database in which all this data lives, we see that we do not get the
disk space 
back.  This is no big deal for a meg or two, but when the data is measured in gigabytes,
this can 
be a problem.

By the time we are finished, we will have done this process roughly 60 times, as we need
to 
eventually create roughly 300 GB of manipulated data.

We expect that our clients will want to do the same someday, and will object to the
excess space 
never being returned.

We are using a dozen machines, running MacOS X, RH Linux 8 and 9, and Windows
2k.  All of them are using 4.0.14.

We have noticed that dropping the database, containing 5G of data, does not cause the
ibdata1 file 
to shrink, and thus once a machine has been used for this, it continues to take up a lot
of disk 
space.  This is a serious problem.

If an analyze or optimize SQL command would fix it, we would run one happily.  We do not
need 
the space reclaimed automatically, as long as we can get it back once the load process is
finished.

This is a high priority feature request for us, as the current fix involves privs the
user may not 
have.  It is quite common in the biotech world for different dbas to have different privs
on various 
databases.  Dumping the table data, physically deleting the ibdata1 file, and reloading
can take 
weeks or months for some of our data, and further, it requires unix root privs, as the
ibdata1 file 
cannot be deleted from within mysql.  We do not like telling our dbas to work directly
with the 
filesystem.

I believe this design decision, needs to be looked at again, as it requires end users to
have root 
access to their filesystem to do maintainence.  Auto-shrink may be arguable, in that the
space can 
be used in the future, but when we know we are permenantly reducing the size of a db, we
should 
have a means.  Otherwise, the practical impact on large table users is extreme.

Consider the use case of a db that has grown to 20GB, and which we would like to split
into two 
10G tablespaces.  (We do this in Oracle on occasion.)  With the current design, we would
end up 
with a 10G tablespace on the new machine, and a half unused 20G tablespace on the
original.

How to repeat:
The procedure for one of these 60 load steps:

Create a new database
Create a schema using innodb tables.  Ours has roughly fifty tables, but only a dozen
have data
Load 5G of data - note that this will take two or three days for our usage pattern
Export the data
Drop the database
Note that ibdata1 is still quite large.

Suggested fix:
The best possible fix would be automatic space reclamation once the data are deleted.  It
would 
only be slightly less convenient if this required an "Optimize Table FOOBAR" to cause.

Second best would be getting the space back when an entire table is dropped.

If we have to drop the entire database, I could accept that.  This is, again, a bit of a
pain, but not 
absolutely awful.

The current solution of "dump every database on the server, delete the file, and reload"
is quite 
time consuming and requires filesystem access.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

same as #1287

oops. It was feature request this time, not a bug :)
even though the ticket text was the same...

Hi!

MySQL-4.1.1 partially solves this problem: you can put

innodb_file_per_table

to my.cnf, and then each InnoDB table is plased into its own .ibd file.

But, of course, that .ibd file never shrinks, unless you reorganize the table with

ALTER TABLE ... TYPE=InnoDB;

And the undo logs are still stored in ibdata files, and those files never shrink in 4.1.1
either :).

Regards,

Heikki

This is good news, as this will solve the most common problems I had with the ever growing
ibdata 
files.

Could you add a comment to the manual that alter table type=innodb on a current innodb
table 
will compact the idb files?  The logs are, at least, fixed in size and less of a problem,
if I 
understand correctly.

I still want it all :-), but this is a very big step in the right direction that solves
my most difficult 
problem.

Scott

I would like to add to the list of people who would like to shrink the ibdata1 file
without duming the database and reloading it.  Our server has more than a hundred
databases, each of which is used by different users, none of whom have shell access to
the server.  We have already used the innodb_file_per_table which fixes a lot of the
problem, but we are still stuck with a more than 40 gigabyte ibdata1 file that existed
before we started using the innodb_file_per_table option.  Our dataset has grown
significantly since then, and it would take hours to dump all the data and then restore
it, during which time more than 100 web-based applications would be off-line.

I will stipulate that a one-time dump and reload of the data would fix the problem, but
that's a lot more work and headache than it sounds like for me.  :)

Just a feature request!

I have just suceeded in filling up my hdd, and can't for the life of be figure out how to
get my space back without deleting the 7Gb ibdata1 file. I vote for a fix!

I just have very few datas(about 1000 records) and my ibdata1 is already 300MB!
Doesn't mysql is suppose to be clean and fast?
Can't belive there is a issue like this;(

Changing status to verified as feature request.

Just to add my $0.02, I have just run an ALTER TABLE... ENGINE=InnoDB on all my InnoDB
files and my ibdata1 file went *up* in size by 200Mb... This is on 4.1.12.

Colin,

using the my.cnf option:

innodb_file_per_table

would help in disk space management.

In ALTER TABLE, MySQL rebuilds the whole table. That will temporarily cause more space
usage in ibdata files, and may cause them to extend. The above my.cnf option would remove
that problem.

Regards,

Heikki

Hello, I've followed the recommended instructions for this, but like everyone else I now
have suitably sized small files for every table, and still one giant data file that needs
to be dropped.  What can I do?

Ty,

unfortunately, the only way to shrink ibdata files is to reload the entire database.

Regards,

Heikki

Well i was stupid enough to NOT include the one-file-per-table and then created a a test
schema with lots of stuff in it.. once i was finished testing i dropped it... then
noticed i had about 2gb disk space left.. (was > 30 before).. now how do i get it back?

Can't 'optimize' since schema is gone, can't delete innodb since it contains other
stuff... so the only way is to backup ALL other schmas, tunr on one-file-per-table and
then restore them all, just to get the space of the deleted schma back? that's crazy!
where is my "shrink all"???

Ouch. That's pretty bad, I have an almost-full /var partition, I dropped several unused
MySQL databases but didn't reclaim almost any space!

Bug #45173 was marked as duplicate of this one.

If the only way to shrink the innodb data file is to to a mysqldump, how about 

I copy all my .ibd files, then

ALTER TABLE tbl_name DISCARD TABLESPACE;

then did a full mysqldump, erased the huge innodb data file, then restored from the dump,
then tried to import the .ibd files with

ALTER TABLE tbl_name IMPORT TABLESPACE;