INNOBB data files builds up

[peterlaursen]

I have noticed the each time I'm doing some experimenting with INNODB tables my INNODB data-file (ibdata1) grows. When I delete some DB and tables the file doesn't shrink. I simply think it is the way INNOBD behaves with MySQL. it “claims” diskspace when needed but does not free it when it is not needed.

Now the file here with me is about 200 MB and the data in it is just about 30 MB. Not a real problem but …

However is there any way to “shrink” that file except than dumping the DB, deleting the bases, stopping the MySQLserver, deleting the datafile, restarting the server and importing the dumps ? And could that be implemented with SQLyog ?

[peterlaursen]

there is the solution to use the

innodb_file_per_table

– parameter in the my.ini file (can be set from “mySQL Administrator too). That will create an ibd-file for each table – nicely organized in folders with the name of respective databases. Just as with MYISAM tables.

The reason for the file “build up” was that I had created several copies of the DB's involved in my experiment. Now the respective folders are deleted when I drop the obsolete DB's.

But that of course will only take effect with tables created after the change in my.ini

[CalEvans]

QUOTE (peterlaursen @ May 9 2005, 10:09 PM)

I have noticed the each time I'm doing some experimenting with INNODB tables my INNODB data-file (ibdata1) grows.  When I delete some DB and tables the file doesn't shrink.  I simply think it is the way INNOBD behaves with MySQL.  it “claims” diskspace when needed but does not free it when it is not needed. Now the file here with me is about 200 MB and the data in it is just about 30 MB.  Not a real problem but … However is there any way to “shrink” that file except than dumping the DB, deleting the bases, stopping the MySQLserver, deleting the datafile, restarting the server and importing the dumps ?  And could that be implemented with SQLyog ?

QUOTE

I have noticed the each time I'm doing some experimenting with InnoDB tables my InnoDB data-file (ibdata1) grows.  When I delete some DB and tables the file doesn't shrink.  I simply think it is the way InnoDB  behaves with MySQL.  it “claims” disk space when needed but does not free it when it is not needed.

This is not a bug it's a feature. 🙂

You can solve this by turn off the 'autoextend' feature in InnoDB. But I've been working with InnoDB tables for some time now and can say the feature is great. no it doesn't shrink the tables when you are deleting data but when you compare it to Oracle or M$SQL which both just claim the space up front, it's a pretty good option. No major RDBMS that use the table space concept actually release blocks that are not being used. (That I know of)

If you do not specify autoextend then Innodb will allocate the entire table space you specify.

If you are worried about it eating up your entire HD then don't use autoextend and specify only a single table space that is the size you need it. You can always add more as you need them. (Or as they fill up and your app stops working.)

All-in-all, a few GB of disk space is a small price to pay for what InnoDB gives us. (IMHO, etc.)

=C=

[peterlaursen]

>> CalEvans

I'm happy with the “innodb_file_per_table” -option !

[Al_123]

Thanks for the tip. Using the innodb_file_per_table feature sounds good in the future. But for the present, I have 98GB file that is comprised mostly of empty space. How do Shrink the file?

[peterlaursen]

it might take some time … 😀

… but you can export your data, disable InnoDB, delete the InnoDB files, enable InnoDB with the per_table option and import again! I think it is the only way to be considered safe.

You might also simply wait to do this until you upgrade the server to a newer version (4.x>5.0 or 5.0>5.1. Then enable innodb_pr_table before importing data!

[sergius]

QUOTE(peterlaursen @ May 9 2005, 02:09 PM) [snapback]5381[/snapback]

I have noticed the each time I'm doing some experimenting with INNODB tables my INNODB data-file (ibdata1) grows. When I delete some DB and tables the file doesn't shrink. I simply think it is the way INNOBD behaves with MySQL. it “claims” diskspace when needed but does not free it when it is not needed.

Now the file here with me is about 200 MB and the data in it is just about 30 MB. Not a real problem but …

However is there any way to “shrink” that file except than dumping the DB, deleting the bases, stopping the MySQLserver, deleting the datafile, restarting the server and importing the dumps ? And could that be implemented with SQLyog ?

Could you suggest what to do when the data file grows to fill all the disk space?

[peterlaursen]

A few times (when I think that InnoDB has been too greedy) I have been doing this:

1) exporting data

2) dropping all InnoDB databases

3) disabling innodb in configuration

4) restarting server

5) deleting InnoDB files

6) enabling innodb in configuration again

7) restarting server

8) importing data

But using the innodb_file_per_table option is easier.

Each table will then have its own file.

And you can do the above series of operations (export .. drop .. delete file ..import) for a table or database at a time. And without the need to stop the server.

[Author]

Posts