8

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

4

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

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.