mysql directory?

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.

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)
Click Here to see a Pictorial Representation

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 in ibdata1

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.

edited Oct 12 at 10:56

dawud
4,3512925

answered Mar 13 at 18:07

RolandoMySQLDBA
8,42711340

Thanks 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. – James Mar 13 at 22:21

mysqldump will only the logical representation of the data pages, not the indexes. You will need another disk mount, perhaps a remote server, to dump the data. – RolandoMySQLDBA Mar 14 at 3:18

출처 - http://serverfault.com/questions/487159/what-is-the-ibdata1-file-in-my-var-lib-mysql-directory

출처 - http://www.scribd.com/doc/31337494/XtraDB-InnoDB-internals-in-drawing

출처 - http://intomysql.blogspot.kr/2010/12/innodb_9912.html

저작자표시 (새창열림)