Siusic Dot Com

I believe one of the areas MySQL server need to improve when competing with other database software is that it needs to provide an easy way to release not used space of the innodb file. MS SQL server is far more superior in that regards.

In MySQL the default setting is to have only one innodb file doesn’t matter how many databases you create on the server.

That is a pitfall. When you create more databases or your databases gets bigger the innodb file will grow. In production environment moving database, adding and dropping table, inserting and deleting data are common operations. MySQL server will increase the innodb file size when you add innodb tables and insert data however it won’t release the space allocated when you drop the tables or delete the data. It doesn’t provide you a command to shrink the database either. In MS SQL server you can easily release the space not used at the tail of the database file by simply running a dbcc shrinkdatabase command. In MySQL server if you want to shrink the innodb file you essentially need to do a database backup using mysqldump and restore it. For large database in a production environment that will something impossible to do.

So in production environment you will have to watching the innodb file closely and don’t let it grow out of control. One way to prevent innodb data keep growing is to change the default setting so that every table has their own innodb table space. This basically solve the problem that innodb doesn’t release non-used space when a table is dropped because with that setting turn on the innodb file for the table is deleted when you drop an innodb table. To turn on innodb file per table setting, just add innodb-file-per-table=ON to the config file. The existing tables will not be affecting by this setting though. The new innodb tables will have their own innodb file when you turn this setting on.

What if you never drop the table? If you keep inserting data to the table but once a while you delete some old data, innodb file will still keep growing. MySQL server is clever enough to reuse the empty space in the innodb data file only when empty space is not fragmented and usually deleting old data will leave fragment empty space in data file. I ever consult with MySL support about how to solve this problem. Here is what they say

If you are deleting most of a table at one time and leaving very few rows, it would be better to create a new table with the rows you want to keep and drop the old table. Or, you could schedule maintenance to do OPTIMIZE TABLE on the relatively small table left over after the large DELETE.

I have tried the OPTIMIZE command. It took very long time to finish for big tables that are over 10G it doesn’t seem to be a workable solution in production environment.

If you’re deleting rows based on date, and the rows have a sequential PK, fragmentation should be less of an issue as you’ll be deleting large chunks of sequential rows. If you have a non-sequential PK, on the other hand, fragmentation could be a pretty significant problem in that situation.

So if you are lucky your table has a sequential PK like auto increment number. You can delete old data based on date and MySQL server can reuse the empty space so that innodb data file will not keep growing.

Related Posts:

MySQL vs SQL Server in Data File Management

How to Separate Data and Indexes of a Table into Different Files

How to Solve Problem That Excel Data Imported Into SQL Server Via DTS Became Null

What is Fragmentation and How to Defragment a Table Being Used Consistently?

New Info on Solving The Mystery of Blog Traffic