Background

During database operations by DBA, you always meet the following problem. For example, the InnoDB storage engine's shared tablespace file "ibdata1" increases gradually and even makes the disk full, resulting in an influence on the service. The following analyzes two issues:

• Why does the "ibdata1" file increase?
• "ibdata1" file does not necessarily increase synchronously between the master and the slave in the same cluster.

Failure Phenomenon

The usage rate of one customer's RDS instance disk rose sharply from 30% to 100%. From troubleshooting, you can find that the "ibdata1" file rises sharply from 32MB by default to 50GB within 30 minutes, finally resulting in full occupation of the disk.

Analysis of cause

Storage content in "ibdata1" file

"ibdata1" is a file used for building "InnoDB" system table space, and the following information is stored in the file:

• Data dictionary
• Double write buffer
• Insert buffer
• Rollback segments
• UNDO space
• Foreign key constraint system tables

Of which, the "rollback segment" and "undo space" are the main causes for the size increase of the "ibdata1" file.

Description of related parameters:

• innodb_data_file_path: specifies "InnoDB system tablespace" file. For example, "ibdata1:32M:autoextend, ibdata1" is the file name; 32M is the file's initial size; "autoextend" indicates the file may be automatically extended.
• innodb_data_home_dir: specifies "InnoDB system tablespace" file's storage path.
• innodb_autoextend_increment: specifies "InnoDB system tablespace" file's automated extension increment.
• innodb_file_per_table: this parameter controls data in InnoDB table, which should be stored in the system table space (ibdata1) or independent table space (.ibd file).

Cause for increase of "ibdata1" file

• Cause 1: Store data using "InnoDB" shared tablespace

  Parameter "innodb_file_per_table" controls the "innodb" engine, which should adopt shared table space or independent table space for storage.

  If the parameter "innodb_file_per_table" is OFF, the "innodb" adopts shared tablespace for storage. Aside from the contents above, the created table's data and index are also stored in "ibdata1" file. Thus, with an increase in the table number and data volume, the "ibdata1" file is extended gradually as well.

  If the parameter "innodb_file_per_table" is ON, the "innodb" adopts independent table space for storage. Newly created table's data and index are stored in their respective "ibd" files. "ibdata1" file only stores the contents above. Usually, the file size does not increase remarkably.

  In this case, the "ibdata1" file is extended synchronously between the master and the slave.

• Cause 2: UNDO LOG pileup

  InnoDB realizes MVCC (multi-version concurrency control) via "undo log" and "redo log" for recording and updating actions. innoDB's "undo log" is stored in ibdata1 shared table space. "undo log" pileup is the main cause for the size increase of the "ibdata1" file. Size increase of "ibdata1" file usually occurs on the master rather than the slave that adopts single-thread synchronization.

  Frequent scenarios leading to "undo log" pileup:

  1. The transactions fail to be submitted or rolled back for a long time, resulting in the "undo log" pileup.

When Transaction A modifies a row of data, InnoDB stores Transaction A's visible data of the old version into an "undo log". At this moment, if Transaction B plans to modify this row of data, InnoDB again stores Transaction B's visible data of the old version into the "undo log" , and so on. If this piece of data is modified by "N" transactions at present, the innoDB stores N copies of undo information of historical version. These "undo" information is right stored in the "ibdata1" file. That becomes one of the main causes for the size increase of the "ibdata1" file. Frequent scenarios: "load data" action, massive concurrent transactions, and failure to submit or roll back old transaction for a long time.

2. Endeavors to ensure consistency of the read request MVCCs in a large transaction lead to the "undo log" pileup.

SQL for reading massive data in the transaction fails to be submitted or rolled back for a long time. During the period from transaction start to transaction submission/rollback, the "undo log" generated from all modifications to the table data cannot be purged, ensuring the consistency of MVCC read versions. Thus, it may result in the pileup and the size increase of the "ibdata1" file. That is one of the main causes for the size increase of the "ibdata1" file. Frequent scenario: "--single-transaction" added in the logic backup of the "mysqldump", and other transactions' massive updating actions to the table in the backup.

Solution

How to bring down the size of the extended "ibdata1" file in the current system? Refer to the following steps for disposal:

• First, purchase a new RDS instance.
• Ensure the parameter "innodb_file_per_table=ON" in the new instance, indicating data in the InnoDB table are stored in independent table space.
• Configure the DTS task, migrate the data from original RDS instance to new RDS instance.
• Modify the program access database's configuration. And, use a new RDS instance.

Conclusions & Suggestions

From the analysis above, we are clear that various factors may cause the size increase of the "ibdata1" file. How can we avoid the risk of size increase of the "ibdata1" file?

1. Make sure that in the program codes for accessing the database, there are paired enabling and disabling actions to every transaction, avoiding the occurrence of a large transaction.
2. When importing the data by batch with "LOAD DATA", you should control the imported data volume per time, avoiding one-off importing of oversize data volume.
3. When executing the "mysqldump" action, avoid adding "--single-transaction" configuration/