make ibdata1 file smaller

sfullman

Hi,

I use relational databases with foreign keys using innodb tables in mysql 4.x and 5.x. One thing I /think/ is true about the file innodb data is stored in (ibdata1 on my Linux server) is that it is assigned a large size initially - cause I notice it doesn't change in size even though innodb table data is being added. Am I mistaken on this?

In other words, the size in bytes on my ibdata1 file is 1,344,274,432 and it never seems to change even when I insert data in those tables.

My thought was, can I cut it down to maybe half that size? There is NO WAY I need a gig of innodb space for a long time and I think it might run faster.

Thank you for your advise and experience here.

Samuel

MarkR

I'm not aware that you can make the innodb tablespace file smaller. You can add new ones though (under some circumstances).

Anyway, the default setting makes it only 10Mb, but it grows as you add new tables.

In your development server, try creating a new innodb tablespace file, by shutting down, deleting the current one (you'll lose all your data of course - you should drop all innodb tables before doing this) and setting something like

innodb_data_file_path=ibdata1:50M:autoextend

Then you should end up with a 50Mb tablespace.

Now restore a copy of your production data into this database, and see how big it becomes.

By default, mysql will create a small tablespace and gradually grow it - if it' 1Gb, that's either because the data really take up that much space, or because at some point in the past they did.

The only way I am aware of shrinking an unused innodb tablespace is to drop all innodb tables, remove it, recreate it and reload them all from a dump - this is clearly not ideal on production.

Another option is to use per-table tablespaces. this can be turned on in my.cnf, but only affects new tables, so in order to use it you'd need to do a dump, delete all your innodb tables, delete your innodb tablespace file, restart and restore.

per-table-tablespaces can't shrink either during deletes, but at least you can drop the table (or ALTER the table, which rebuilds it)

Mark