Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Please have a look at dba.stackexchange.com/questions/8982/… –  Abdul Manaf Apr 22 '13 at 10:52
    
1) Please run SELECT VERSION();. 2) Do you have innodb_file_per_table enabled (Please run SHOW VARAIBLES LIKE 'innodb_file_per_table';. –  RolandoMySQLDBA Apr 22 '13 at 11:25
    
INNODB_FILE_PER_TABLE IS NOT ENABLED.. –  chows Apr 22 '13 at 11:47
    
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

1 Answer 1

up vote 1 down vote accepted

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.

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:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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