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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
Pictorial Representation of InnoDB Architecture
What can make ibdata1 grow suddenly?
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
STEP 03: Run
STEP 04: Drop all databases except the
STEP 08: Login to mysql
STEP 09: At mysql prompt, run
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 !!!