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?
Mysql – How tonnodb ibdata1 file grows by 5X even with innodb_file_per_table set
ibdatainnodbMySQL
Related Solutions
I have very bad news for you.
You should not have deleted the ibdata1 file. Here is why:
ibdata1 contains four type of information:
- table metadata
- MVCC data
- data pages (with innodb_file_per_table enabled)
- index pages (with innodb_file_per_table enabled)
Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.
I also have very good news for you along with some bad news.
It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).
To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.
I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.
UPDATE 2011-10-17 06:19 EDT
Here is your original innodb configuration from your question:
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000
Please notice that innodb_log_file_size is there twice. Look carefully...
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000
The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;
. Mystery solved !!!
UPDATE 2011-10-17 11:07 EDT
The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:
110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).
For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.
You should be very happy the mysqldump is two-thirds the size of the data. Why?
You should want to move data and indexes without copying unused space at the same time. What aspects would you have to consider by simply importing a mysqldump?
Aspect #1
InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.
Aspect #2
InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:
- With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
- With innodb_file_per_table enabled, you could go to the individual InnoDB and run of the the following to physically shrink the InnoDB table
mydb.mytable
:OPTIMIZE TABLE mydb.mytable;
ALTER TABLE mydb.mytable ENGINE=InnoDB;
In light of this, you should keep in mind that a mysqldump is a logical representation of the data and indexes. There is no wasted space to be concerned with. You should not have to scp or rsync bloated InnoDB data or logs. Importing a mysqldump into a fresh installation of MySQL should be what you what want.
I have written about this many times:
- MySql - Clean ibdata1 (April 19, 2012)
- How do you remove fragmentation from InnoDB tables? (April 11, 2012)
- Is innodb_file_per_table advisable? (April 01, 2012)
- How to Clean a mysql innodb storage engine (October 29, 2010)
Once you have loaded the test database with the mysqldump, you should run CHECKSUM TABLE
on random tables of various sizes. If all tables have the same checksum value, you could certify as good. Make a disklevel backup of the data on the prod server as well.
You can then do this in production
- shutdown mysql
- hose everything (including ibdata1, ib_logfile0, ib_logfile1) in /var/lib/mysql except for /var/lib/mysql/mysql
- Set
innodb_data_file_path=ibdata1:10M:autoextend
in /etc/my.cnf - restart mysql (this recreates ibdata1, ib_logfile0, ib_logfile1)
- reload mysqldump
Your InnoDB architecture and data will then be lean and mean.
Related Question
- Mysql – InnoDB – High disk write I/O on ibdata1 file and ib_logfile0
- Mysql – ibdata1 grows exponentially when innodb_file_per_table is configured
- Mysql – Trying to recover a database with just .ibd and .frm files, but no idbata1 file
- Mysql – Need correct thesqlfrm command path to restore a database from .ibd and .frm files
- Mysql – How to *actually* defragment the InnoDB tables
- Mysql – innodb_file_per_table option enabled but ibdata1 file still grows up
- Mysql – Unable to fix ROW size too large even with innodb_file_per_table
Best Answer
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?
According to mysqlperformanceblog.com's
Reasons for run-away main Innodb Tablespace
: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
SET GLOBAL innodb_fast_shutdown = 0;
STEP 04: Drop all databases except the
mysql
andinformation_schema
databaseSTEP 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 !!!