I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was reduced but the server's ibdata1 file went crazy. Any ideas?

1 Answer 1


You may find this surprising, but did you know that there are several moving parts to ibdata1? Even with innodb_file_per_table enabled, here the classes of information stored in ibdata1

  • Data Dictionary
  • Double Write Buffer (support data consistency; used for Crash Recovery)
  • Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
  • Rollback Segments
  • Undo Space (where the most uncontrolled growth can happen)

Pictorial Representation of InnoDB Architecture

InnoDB Architecture

What can make ibdata1 grow suddenly?

According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

  • Lots of Transactional Changes
  • Very Long Transactions
  • Lagging Purge Thread

As long as your total InnoDB dataset is relatively small and you would like to shrink ibdata1, you can do the following:

STEP 01: Schedule downtime

STEP 02: mysqldump all databases to /root/MySQLData.sql

mysqldump -uroot -p --all-databases --routines --triggers > /root/MySQLData.sql

STEP 03: Run SET GLOBAL innodb_fast_shutdown = 0;

STEP 04: Drop all databases except the mysql and information_schema database

STEP 05: service mysql stop

STEP 06: rm -f /var/lib/mysql/ib*

STEP 07: service mysql start (recreates ibdata1, ib_logfile0, ib_logfile1)

STEP 08: Login to mysql

STEP 09: At mysql prompt, run mysql> source /root/MySQLData.sql

That's it. I have done this many, many times : Howto: Clean a mysql InnoDB storage engine?

From here, you just have to live with the weird growth due to transactions.

Give it a Try !!!

  • OK, so those long transactions did grow the ibdata1 file even though the tables are in separate files. Thanks for the confirmation.
    – Dan
    Commented Apr 23, 2013 at 21:50
  • Very nice! This is also a useful way to convert to using innodb_file_per_table.
    – STW
    Commented Sep 5, 2014 at 16:26
  • @RolandoMySQLDBA can you please explain what is stored in rollback segments and undo space ?
    – vidyadhar
    Commented Nov 5, 2015 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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