Why is the ibdata1 file continuously growing in MySQL?

What is stored in ibdata1?

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)

 

ibdata1’s place in the InnoDB Universe (on Right Side)

What is stored in ibdata1?

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.

 

How Figure out how much space you can reclaim by shrinking ibdata file?

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.

 

InnoDB Infrastructure Cleanup

To shrink ibdata1 once and for all you must do the following:

  1. Dump (e.g., with mysqldump) all databases into a .sql text file (SQLData.sql is used below)
  2. Drop all databases (except for mysql and information_schema)NOTE : As a precaution, please run this script to make absolutely sure you have all user grants in place:
    mkdir /var/lib/mysql_grants
    cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
    chown -R mysql:mysql /var/lib/mysql_grants
  3. Login to mysql and run SET GLOBAL innodb_fast_shutdown = 0; (This will completely flush all remaining transactional changes from ib_logfile0 and ib_logfile1)
  4. Shutdown MySQL (service mysql stop)
  5. Add the following lines to /etc/my.cnf (or my.ini on Windows)
    [mysqld]
    innodb_file_per_table
    innodb_flush_method=O_DIRECT
    innodb_log_file_size=1G
    innodb_buffer_pool_size=4G

    Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

    Also: innodb_flush_method=O_DIRECT is not available on Windows

  6. Delete ibdata* and ib_logfile*, Optionally, you can remove all folders in /var/lib/mysql, except /var/lib/mysql/mysql.
  7. Start MySQL (This will recreate ibdata1 [10MB by default] and ib_logfile0 and ib_logfile1 at 1G each).
  8. Import SQLData.sql

Now, ibdata1 will still grow but only contain table metadata because each InnoDB table will exist outside of ibdata1. ibdata1 will no longer contain InnoDB data and indexes for other tables.

For example, suppose you have an InnoDB table named mydb.mytable. If you look in /var/lib/mysql/mydb, you will see two files representing the table:

  • mytable.frm (Storage Engine Header)
  • mytable.ibd (Table Data and Indexes)

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

 

Add a Comment

Your email address will not be published. Required fields are marked *