Archive for April 3rd, 2011
MySQL InnoDB ibdata size
by z3n on Apr.03, 2011, under Tips & Hints
Problem:
MySQL InnoDB ibdata file size never shrinks no matter if you deleted a big database / table or whatever.
Solution:
There is no easy solution for this, according to mysql this is supposed to be a bug, however they never fixed it since, apparently, there’s not many people around deleting big tables / databases.
Looking around, someone suggested that recreating the whole dataset would help fixing the issue, since we won’t be having trash on the innodb ibdata file.
Recreating the whole dataset is a extremely time consuming task, you would need to do a dump of every database, completly delete mysql data folder, create a new one with defaults, and inject the dump file. Just for example, on my dev machine i run about 100 different databases the total size is around 8GB, innodb was 20GB, wasting a lot of space on my precious SSD, so dumping everything, doing backups and injecting the whole data took over 8 hours, which is something nobody can take on a production server.
After some more research I’ve found about a specific configuration switch:
innodb_file_per_table
This is what you should have on your my.cnf or my.ini into the innodb branch. Instead of creating one big innodb ibdata for every single innodb table across all databases, this switch will create a `.ibd` file for each of the the innodb tables. Those files will be stored inside the database folder, so if you eventually have a greatly sized table you just need to drop it and recreate the single table to avoid gigantic innodb .ibd files instead of doing a full database recreation.
If you don’t have time to recreate everything you can just add `innodb_file_per_table` into the config, so new tables will be created using the new scheme.
The only con i can think about this switch is because you will have one extra file that will have to be seeked and will incrase the overhead when doing table joins and so on, although the disk overhead should be minimal, if you have thousands of tables into the same db it might make some difference, if you relay on speed you should do some benchmarks to make sure.
Bug Tracking:
