MySQL Bugs: #1287: Innodb database file ibdata1 never shrinks after data is removed

Bug #1287	Innodb database file ibdata1 never shrinks after data is removed
Submitted:	15 Sep 2003 18:54	Modified:	12 Jul 2004 13:51
Reporter:	Scott Ellsworth	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.0.14	OS:	MacOS (MacOS X, Win2k, RH Linux 8 and 9)
Assigned to:		CPU Architecture:	Any

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.

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.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

The fact that InnoDB tablespaces can only grow and never shrink is docummented design decision.

Like with every other storage management system it is arguable if auto-shrink is wise decision as space can be reused in future. 

In any case this issue can be only qualified as Feature Request, but not as Bug.

Where is this documented?  I scanned through "file space usage", but the closest I saw was 
mention of "When you delete data from a table, InnoDB will contract the corresponding B-tree 
indexes." and a warning about rows not being deleted.

This is a real problem when database sizes change, since the ONLY way to fix it is to dump every 
other innodb table in every other database, physically delete the ibdata1 file, and then restore.  
This is not pretty.

This sure seems like a bug to me, even if it was a design decision.  Any case where the user has to 
go through a laborious process involving root privs is a pain.

Scott

InnoDB tablespace configuration is covered at:

http://www.mysql.com/doc/en/InnoDB_start.html

Note that for every file that is part of the tablespace,
you must specify a size. When the tablespace is
initialized for the first time, each file is allocated
to the size specified. The only change in size that
may occur thereafter is that the final file named in
the table specification may grow if it has the
autoextend attribute.

Preallocation of tablespace files makes for better
performance.

By the way, though I am not certain about this, your
initial report seems to imply that you believe that
InnoDB doesn't allocate space until you load data
into your tables. That is not true. It allocates it the
first time the server starts up and initializes the
tablespace.

By the way, it is not necessary to have root privileges to
reconfigure the InnoDB tablespace or remove the InnoDB
tablespace files. Those operations can be done from the
account that is used for running the server.

The documentation you point to does describe the pre-allocation of tables.  It also states what 
happens as tablespace grows.  It is silent on whether tablespace will eventually be recovered and 
returned to the OS.

I was aware that innodb allocates space for its tables at startup, and that preallocation of your 
expected usage is key to decent performance.

That said, if one has usage spikes where tablespace used is double, triple, or two orders of 
magnitude above the normal usage, then innodb will expand to suit.  This is a good feature.  What 
I would like is a means to cause it to decrease again to a size more appropriate for regular usage.

I have logins disabled for the mysql user in the name of security.  I suppose I could allow sudo in 
its name, which does make me less worried about root exploits.

Scott

Since this is likely to end up treated as a freq, I have opened freq 1341 to cover this.  I would still 
like it to be treated as a bug, but I understand your point of view and appreciate the comments.

Scott

We also faced the similar thing at our place.  We are developing a product which supports databases like MS SQL and MySQL.  We are developing an archive feature to reduce backup size of working database.

MS SQL does shrink nicely.  But MySQL (as mentioned by scott in detail) is really a pain for us.  I strongly feel it is a bad design of InnoDB though performance was in your mind.

- Paresh

Hi!

If you use MySQL-4.1.3, define

innodb_file_per_table

in my.cnf, and do OPTIMIZE TABLE, the operating system will get the disk space back. That is because the table is stored in its own .ibd file, and OPTIMIZE will rebuild the whole table to another .ibd file, rename the .ibd file, and delete the old .ibd file.

Regards,

Heikki

Hi,
once the file per table option is enabled, i am still stuck with a large ibdata file.

was this issue resolved with release 5.0?

Cheers
J

Heikki,

That is interesting.  I wonder why that directive isn't set by default then.  Thanks for the information!

Hi experts...

As per the status, this is not a bug. I am facing the same problem.
I am running mysql on solaris 10 box, with mysql version 4.0.
I have few table, the data on 2-3 table can grow upto 500000-600000 entries. But one of my thread is watching the table entries every 5 min, and if found entries greater then the 500000, truncate the extra entries from the table.

The problem is during the process, my ibdata size keep on increasing and ultimately rechaes to 45GB.

My query is when I am deleting the entries, so that I am restricting the table entries to 500000 only, why my data size is not shrinking. I tried to delete entries from the mysql prompt also, but result is still same, lbdata is not reduces even a bit value.

Can you please suggest me the steps or some reference doc which I have to follow before creating my database. Or is it a bug.

Looking forword for a response from your side.

Regards,
Lalit

I delete a database (the only one on this server) and I still have a huge /var/lib/mysql/ibdata1 there. The free space is not recovered.

Can I delete that file through file system?

January 2012 - Wiesbaden Germany - Hi, I did carefully read all the arguments, why this wasting of diskspace is (or should be) NOT a bug. Sorry, after evaluating a lot of databases within the last 20 years, it remains to be a design bug from innodb structure, to generate a 45 Gigabyte index from a total of some 150 megabyte datafiles. The very old DOS/Netware DATAFLEX did it perfectly better.
The benefit in speed compared by myisam needed to be more than 10 : 1 to accept such a growth without a chance of resetting or reshrinking. And that is not true. As I see the first thread in 2003, now IT IS a BUG, that within almost 10 years there has not been written a simple CLI tool to solve that problem.
What I did read above are workarounds around the main problem. Or is it a political issue ?
G. Redlich Wiesbaden Germany

Please see Bug #1341 for the related feature request and updates on what is happening in this area.

This bug was closed as not a bug because it's working as designed, the other one covers the feature request to get it improved, so it's the one that will be most useful to you.

James Day, MySQL Senior Principal Support Engineer, Oracle