2

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:

  1. Take a dump of all databases
  2. Set innodb_file_per_table in the mysqld section of my.cnf
  3. Clean the data directory (except mysql)
  4. 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:

  1. I have the innodb_file_per_table entry already mentioned under the mysqld section of my.cnf.
  2. 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_table setting
  3. 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)
  4. I now update the values in ColumnA using the following query: update TableA set ColumnA = crc32(someothervarcharcolumnfromthistable);
  5. 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:

  1. 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.
  2. 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.

  • @RolandoMySQLDBA Please see edit as to why this question is not a duplicate. – Cik Jan 15 '15 at 17:09
  • 1
    What do you mean with "preventive" measures? The ibdata1 file can grow large but I'm sure any decent server can handle a file of a few GBs, let alone 300 MB. What is the problem with having one large file that is used by the DBMS for several reasons? – ypercubeᵀᴹ Jan 15 '15 at 17:19
  • In my post, I specifically mentioned the Undo Space (where the most uncontrolled growth can happen). I also make reference to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace (percona.com/blog/2010/06/10/…). I also pointed out the three reasons for the ibdata1 growth. 1) Lots of Transactional Changes, 2) Very Long Transactions, 3) Lagging Purge Thread. – RolandoMySQLDBA Jan 15 '15 at 17:47
  • 1
    Please also note that the posted answer has two links from me. I have consistently stated the ibdata1 will grow no matter what. So, it's either do the cleanup of InnoDB or live with the growth. – RolandoMySQLDBA Jan 15 '15 at 18:08
  • 1
    You mentioned posts about 2TB ibdata1. I have those as well (dba.stackexchange.com/a/68326/877, dba.stackexchange.com/a/21195/877). In those posts, innodb_file_per_table was disabled. You don't have to worry about such a situation. Your question says the type of UPDATEs you are running. That will demand UNDO growth. It simply must happen because of the possibility of rollbacks and MVCC for transaction isolation. Sorry for being redundant, but there is nothing you can do about stemming ibdata1's growth. – RolandoMySQLDBA Jan 15 '15 at 18:17
0

Lets try to understand whats stored inside ibdata1: When you have innodb_file_per_table enabled, the tables are stored in their own tablespace but the shared tablespace is still used to store other InnoDB’s internal data: * data dictionary * change buffer * doublewrite buffer * undo logs

since there is trancations, rollbacks that support repeatable read that will make ibdata1 files grow even when you update any innodb table.

reference: http://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

ibdata1 grows exponentially when innodb_file_per_table is configured

https://serverfault.com/questions/272146/why-ibdata1-is-still-growing-when-i-have-used-innodb-file-per-table-in-mysql-5-1

Not the answer you're looking for? Browse other questions tagged or ask your own question.