What is the ibdata1 file in my /var/lib/mysql directory?
Solution 1
The file ibdata1
is the system tablespace for the InnoDB infrastructure.
It contains several classes for information vital for InnoDB
- Table Data Pages
- Table Index Pages
- Data Dictionary
- MVCC Control Data
- Undo Space
- Rollback Segments
- Double Write Buffer (Pages Written in the Background to avoid OS caching)
- Insert Buffer (Changes to Secondary Indexes)
Please note ibdata1's place in the InnoDB Universe (on Right Side)
You can separate Data and Index Pages from ibdata1
by enabling innodb_file_per_table
. This will cause any newly created InnoDB table to store data and index pages in an external .ibd
file.
Example
-
datadir is
/var/lib/mysql
-
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;
, creates/var/lib/mysql/mydb/mytable.frm
-
innodb_file_per_table
enabled, Data/Index Pages Stored in/var/lib/mysql/mydb/mytable.ibd
-
innodb_file_per_table
disabled, Data/Index Pages Stored inibdata1
-
No matter where the InnoDB table is stored, InnoDB's functionality requires looking for table metadata and storing and retrieving MVCC info to support ACID compliance and Transaction Isolation.
Here are my past articles on separating table data and indexes from ibdata1
-
Oct 29, 2010
: My Original Post in StackOverflow -
Nov 26, 2011
: ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full -
Feb 03, 2012
: Scheduled optimization of tables in MySQL InnoDB -
Mar 25, 2012
: Why does InnoDB store all databases in one file? -
Apr 01, 2012
: Is innodb_file_per_table advisable?
WHAT TO DO NEXT
You can continue having ibdata1
stored everything, but that makes doing LVM snapshots real drudgery (my personal opinion).
You need to use my StackOverflow post and shrink that file permanently.
Please run this query:
SELECT
((POWER(1024,3)*94 - InnoDBDiskDataAndIndexes))/POWER(1024,3) SpaceToReclaim
FROM
(SELECT SUM(data_length+index_length) InnoDBDiskDataAndIndexes
FROM information_schema.tables WHERE engine='InnoDB') A;
This will tell how much wasted space can be reclaimed after applying the InnoDB Cleanup.
Solution 2
That file is ibdata1
, not ibdatal
and it contains all of your InnoDB databases. If you delete it, you lose all your data.
For some ideas on how to deal with it, see How to shrink/purge ibdata1 file in MySQL.
Solution 3
If you use innodb as MySQL engine by default will store all your databases into ibdata1. Also there are log files ib_logfile0 and ib_logfile1. Do not delete those files.
Related videos on Youtube
James
Updated on September 18, 2022Comments
-
James over 1 year
Logging in to my Webmin control panel, I noticed that virtually all of my disk space is full. I searched for the ten largest files/ directories on my system and found that a file called ibdata1 is taking up around 94GB of space. It resides in my /var/lib/mysql directory.
What does ibdata1 do? Am I safe to remove it? My assumption is that it's a dump of some kind, but that's just a wild guess.
-
James about 11 yearsThanks for your input. My disk quota has since filled completely - before I act on any of the advice in your posts, will I need free space? I note that your original post on SO mentions about doing an SQL dump, but this would presumably create a ~90GB file with nowhere to go.
-
Isaac about 10 yearsIt returns 91.25350952148438 for me as
SpaceToReclaim
. Is this in megabytes? percent? bytes? -
Frank almost 6 yearsWhat is delete that file on my server?
-
anupsabraham almost 5 yearsI know this is too old. But for anyone coming here with the issue, you need to replace the number
94
with whatever is the size of youribdata1
file in GB and theSpaceToReclaim
will give you the size in GB.