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 ?

Please have a look at http://dba.stackexchange.com/questions/8982/is-there-any-best-way-to-reduce-the-size-of-ibdata-in-mysql — Abdul Manaf, Apr 22 '13 at 10:52
Please goto the link in my answer from Oct 29, 2010 and carry out all the steps in it. — RolandoMySQLDBA, Apr 22 '13 at 11:52

score 1 · Accepted Answer · edited May 23 '17 at 12:40

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

mysql ibdata1 file corrupted or increased unusually

1 Answers1