I have read a lot of posts which explain that the only solution to stop the growth of an ever increasing ibdata1 file is to:
- Take a dump of all databases
- Set
innodb_file_per_tablein themysqldsection of my.cnf - Clean the data directory (except mysql)
- Restart the MySQL Server and load the dumps using mysqldump.
However my problem starts once I have done all these steps. I still see a scenario where the size of my ibdata1 file increases. The scenario is explained below:
I start MySQL Server 5.5 with a clean data directory (only the mysql directory is present), the size of the ibdata1 file reads 18 MB. Next steps follow:
- I have the
innodb_file_per_tableentry already mentioned under the mysqld section of my.cnf. - Now I load some tables from mysql dump files (all of them have InnoDB storage engine specified) using mysqldump. I observe that ibdata1 size has not changed and separate ibd files have been created which is expected according to the
innodb_file_per_tablesetting - I add a new column say,
ColumnA (int, NOT NULL, UNSIGNED, DEFAULT 0)to a table, say Table A. The storage engine is InnoDB and contains 9 million records. So far so good, the size of ibdata1 file remains the same (18 MB) - I now update the values in
ColumnAusing the following query:update TableA set ColumnA = crc32(someothervarcharcolumnfromthistable); - I observe that during the update the size of ibdata1 consistently grows and grows past 300 MB.
Why is this happening? Does this mean I have to do the cleanup-restart-restore activity mentioned at the beginning every 'x' months, or am I missing something?
Edit 1: This question is not a duplicate of this question answered by RolandoMySQLDBA. Reasons follow:
- It says what ibdata1 stores in general. However it does not clarify when all precautions are taken against its increase, why would it still do so when performing operations on a particular table.
- Though it is very informative, however it outlines the corrective measures that can be taken, does not highlight preventive measures post the activity is done.
In case it is not clear from my question, I wanted to get the opinion of experts on preventive measures once I have set innodb_file_per_table for ibdata1 not to grow.