May 262013
 
Article MySQL

Ever since the first version of the MySQL, DBAs working with this DBMS have found, at some point, this issue: the ibdata1 file keeps growing indefinitely, until it occupies an excessive amount of the available disk space.

This article explains some possible actions that can be taken to eliminate or at least reduce the magnitude of this annoying problem.

Introduction

Every table in a MySQL database uses a type of storage engine. The most commonly used are MyISAM and InnoDB.

Data in a MyISAM table are stored as independent files, usually under a directory “/var/lib/mysql/DATABASE-NAME”. However, data in InnoDB tables are all stored in a single file “/var/lib/mysql/ibdata1”. If the database keeps a high volume of data, this file can become very big. Besides, when a single table or even a whole database is deleted, the ibdata1 file does not shrink.

To fix this issue, two possible actions can be taken:

  • Change to MyISAM the storage engine of those tables that are using InnoDB, and disable the InnoDB engine in the configuration of MySQL

or else:

  • Make a full dump of all the databases, restart the server and recover the backup. Optionally, the server can be reconfigured to store each InnoDB table in a different file, thus avoiding that the same issue happens again

In any of both cases, we can first get an idea of the situation, by running a sentence that lists the number of tables in the databases making use of each of these storage engines:

If the output of the sentece above shows that some tables in our database are using InnoDB, we can then issue another query to get the detail of the schemas and table names using this storage engine:

Method 1: Convert tables from InnoDB to MySAM

Unless we want to keep InnoDB for performance or other reasons, it is possible to completely disable this kind of storage engine. To do that, we can convert each table using InnoDB to MyISAM with a command like this:

once there are no more InnoDB tables in our database, edit the “my.cnf” configuration file (in a linux system, this file is usually located in the path /etc/mysql/my.cnf), and include inside the section “[mysqld]” two directives:

As InnoDB is the default storage engine since MySQL 5.5, the second directive is required to set a different default storage engine.

Note: For versions of MySQL 5.4 and below, there was only one directive required to disable InnoDB:

Then, restart the database:

We can check that innodb has been disabled issuing the command:

and verifying that the output is:

After the database has restarted, we can proceed to remove the file “ibdata1” and the transaction log files “ib_logfile0”, “ib_logfile1”

Method 2: Dump the databases, restart the database and recover the dump

  1. Make a backup (using mysqldump) of all databases in a file data.sql
  2. Drop all databases except mysql and information_schema
    Besides, a copy of the files of the mysql schema can be done, running as root the commands:
  3. Optionally, the procedure explained here can be followed to backup the user privileges.
  4. Connect to mysql and execute the command SET GLOBAL innodb_fast_shutdown = 0; This completes the execution of any transaction that could be pending in the transaction log files ib_logfile0, ib_logfile1
  5. Stop MySQL (/etc/init.d/mysql stop)
  6. Add the following directives to the mysql configuration file /etc/my.cnf (or my.ini on Windows)

    (Note: The values shown for the parameters innodb_log_file_size and innodb_bufer_pool_size are only examples. In all cases, the value set for innodb_log_file_size must be 25% of innodb_buffer_pool_size.)
    The parameter innodb_file_per_table tells mysql to create independent files for ech InnoDB table, avoiding the problem with the growth of the ibdata1 to happen again.
  7. Delete the files ibdata* and ib_logfile*.
  8. Start MySQL (/etc/init.d/mysql start). This will create a new ibdata1 file (with a default size of 10MB) and ib_logfile0ib_logfile1 (1 GB each).
  9. Import the dump file data.sql created in step 1

References

 Posted by at 7:46 am

 Leave a Reply

(required)

(required)