How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

Question

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?

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

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 !!!