mysql ibdata1 file corrupted or increased unusually

Question

We are using MySQL 5.2 version all InnoDB tables.

I have observed that ibdata1 file got increased very unusually like occupied 500 GB in 2 days.

In last 6 months, it had taken only 6 GB though there were no inserts at all. I also deleted the ibdata1, ib_logfile0 and ib_logfile1 and restarted mysql.

It worked fine. but again after 6 months same issue, and the ibdata1 got increased to 550 GB. There are no errors found in error log file.

I would like to know what may be the reason in general ?

Answer 1

Please keep in mind that ibdata1 is the system tablespace. It is home of the InnoDB Infrastructure. Here are the following classes of information stored in ibdata1.

• Table Data Pages (if innodb_file_per_table disabled)
• Table Index Pages (if innodb_file_per_table disabled)
• Data Dictionary (Tablespace IDs, Logical-to-Physical Mapping to Tables)
• Double Write Buffer (Handles Data Redundancy to Support Crash Recovery)
• Insert Buffer (Handles Updates to Secondary Indexes)
• Rollback Segments (Manages Reverse Engineering of Uncommitted Transactions)
• Undo Space (Containers of Information for Rollback Operations)
• Click to See Pictorial Representation of the InnoDB Infrastructure

As you can see, ibdata1 has a lot of moving parts. The fastest growing section of ibdata1 would be the undo space, especially when there are a lot of read transactions and a few write transactions.

Please read me past posts on doing InnoDB Cleanup:

• Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?
• Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
• Mar 25, 2012 : Why does InnoDB store all databases in one file?
• Jun 19, 2012 : whether a big (but not used) ibdata1 slow anything down
• Jan 07, 2013 : Database space doesn't match ibdata1 size