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