By Dan


2013-04-23 20:00:33 8 Comments

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 comments

@RolandoMySQLDBA 2013-04-23 20:11:27

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

@Dan 2013-04-23 21:50:00

OK, so those long transactions did grow the ibdata1 file even though the tables are in separate files. Thanks for the confirmation.

@STW 2014-09-05 16:26:20

Very nice! This is also a useful way to convert to using innodb_file_per_table.

@vidyadhar 2015-11-05 17:32:30

@RolandoMySQLDBA can you please explain what is stored in rollback segments and undo space ?

Related Questions

Sponsored Content

1 Answered Questions

Trying to recover a database with just .ibd and .frm files, but no idbata1 file

  • 2016-07-18 01:56:13
  • David Borrink
  • 172 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql ibdata

2 Answered Questions

Unable to fix ROW size too large even with innodb_file_per_table

1 Answered Questions

ibdata1 grows big again after using innodb_file_per_table and having shrunk it

  • 2016-04-07 14:22:50
  • nl-x
  • 330 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql

1 Answered Questions

[SOLVED] How can I *actually* defragment my InnoDB tables?

1 Answered Questions

[SOLVED] innodb_file_per_table option enabled but ibdata1 file still grows up

2 Answered Questions

1 Answered Questions

[SOLVED] Move ibdata1, set innodb_data_file_path

3 Answered Questions

[SOLVED] ibdata1 grows exponentially when innodb_file_per_table is configured

1 Answered Questions

[SOLVED] MySQL InnoDB transactions with lot of delete instructions

1 Answered Questions

[SOLVED] InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

  • 2012-07-20 04:08:51
  • tia
  • 16438 View
  • 8 Score
  • 1 Answer
  • Tags:   mysql innodb

Sponsored Content