Understanding MySQL DDL Space Requirements

This blog post will explore MySQL’s file storage behavior during DDL operations, particularly with InnoDB tables. Using a practical example, we’ll discuss where and how MySQL stores data during different types of DDL operations.

Setting the Stage: Table Structure

Consider a table sbtest1 with 30 million rows, created as follows:

This table is actively used in DML operations (e.g., inserts, updates, and deletes), as shown by sysbench:

Example 1: Modifying a Column

Suppose you execute the following DDL operation to modify a column:

This online DDL operation uses the COPY algorithm, creating a temporary table to store the rows during the DDL process. Concurrent DMLs are not allowed during this process.

Observing Files

A temporary file is created in the same directory as the table:

The file #sql-3461b9_13.ibd is the intermediate table created to perform the COPY operation.

Example 2: Performing a NULL Rebuild operation

Another operation is to rebuild the table in place, which is a very common operation to defragment the table:

This operation creates a temporary file for the rebuild process:

The temporary file #sql-ib1068-957745685.ibd  is an intermediate table file created during the rebuild, requiring space proportional to the table’s size plus its indexes.

Example 3: Adding an Index

Adding an index with the following DDL allows concurrent DMLs:

Temporary files for this online DDL are stored in the directory specified by the --tmpdir option. These files might not appear in standard directory listings but can be observed using tools like lsof.

Observing Temporary Files

Using lsof while the operation runs:

After completion, the files are automatically deleted.

Customizing Temporary File Storage

You can specify a custom directory for temporary files using the innodb_tmpdir variable. For example:

Performing the same index addition:

Temporary files will now appear in the custom directory:

Considerations

If you encounter issues due to insufficient disk space for temporary sort files during online DDL operations, there are a few strategies to mitigate them:

1. Use innodb_tmpdir:

Specify a path for temporary files on another mount with sufficient space:

2. Perform the DDL operation “offline“

You can use options like ALGORITHM=COPY and LOCK=EXCLUSIVE. While these operations are blocking, they eliminate the need for temporary sort files and reduce disk space requirements during the operation. Like in Example 1, a temporary file will be created in the same table directory.

Key Insights

1. Intermediate Table Files: Temporary files are often created during DDL operations, such as modifying columns or changing storage engines. These files typically begin with the #sql-ib prefix.

2. Temporary Sort Files: When adding indexes, MySQL stores temporary files in the directory specified by --tmpdir or innodb_tmpdir.

3. Disk Space Requirements: Operations that involve rebuilding or copying data require additional disk space equal to or similar to the original table size.

Takeaway

Understanding MySQL’s file management during DDL operations helps in planning disk space and locating intermediate files during troubleshooting. For more details, refer to the MySQL Documentation on Online DDL.

Written by

Vinicius Grippa

Writes this blog. Mostly about databases. Boring on purpose.

More about me →