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:

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=30000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `sbtest1` (

`id` int NOT NULL AUTO_INCREMENT,

`k` int NOT NULL DEFAULT '0',

`c` char(120) NOT NULL DEFAULT '',

`pad` char(60) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

KEY `k_1` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=30000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

[ 22s ] thds: 10 tps: 157.96 qps: 3136.24 (r/w/o: 2188.47/631.85/315.92) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00

1	[ 22s ] thds: 10 tps: 157.96 qps: 3136.24 (r/w/o: 2188.47/631.85/315.92) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00

Example 1: Modifying a Column

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

ALTER TABLE sbtest1 MODIFY COLUMN k BIGINT NOT NULL DEFAULT 0;

1	ALTER TABLE sbtest1 MODIFY COLUMN k BIGINT NOT NULL DEFAULT 0;

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:

-rw-r-----. 1 user vgrippa 7.0G Nov 19 19:07  sbtest1.ibd
-rw-r-----. 1 user vgrippa 4.8G Nov 19 19:53 '#sql-3461b9_13.ibd'

1 2	-rw-r-----. 1 user vgrippa 7.0G Nov 19 19:07 sbtest1.ibd -rw-r-----. 1 user vgrippa 4.8G Nov 19 19:53 '#sql-3461b9_13.ibd'

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:

ALTER TABLE sbtest1 ENGINE=InnoDB;

1	ALTER TABLE sbtest1 ENGINE=InnoDB;

This operation creates a temporary file for the rebuild process:

-rw-r-----. 1 user vgrippa  27M Nov 19 20:15 '#sql-ib1068-957745685.ibd'
-rw-r-----. 1 user vgrippa 7.1G Nov 19 20:15  sbtest1.ibd

1 2	-rw-r-----. 1 user vgrippa 27M Nov 19 20:15 '#sql-ib1068-957745685.ibd' -rw-r-----. 1 user vgrippa 7.1G Nov 19 20:15 sbtest1.ibd

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:

ALTER TABLE sbtest1 ADD INDEX idx_new_k (k);

1	ALTER TABLE sbtest1 ADD INDEX idx_new_k (k);

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:

mysqld  3432889 user   94u      REG    253,8      65536   32908240 /home/user/sandboxes/msb_8_0_30/tmp/ibZzJNo7 (deleted)

1	mysqld 3432889 user 94u REG 253,8 65536 32908240 /home/user/sandboxes/msb_8_0_30/tmp/ibZzJNo7 (deleted)

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:

SET GLOBAL innodb_tmpdir = '/path/to/custom/tmpdir';

1	SET GLOBAL innodb_tmpdir = '/path/to/custom/tmpdir';

Performing the same index addition:

ALTER TABLE sbtest1 ADD INDEX idx_new_k (k);

1	ALTER TABLE sbtest1 ADD INDEX idx_new_k (k);

Temporary files will now appear in the custom directory:

mysqld  3432889 user   15u      REG    253,8    7651328   /path/to/custom/tmpdir/ibmcFP3W (deleted)

1	mysqld 3432889 user 15u REG 253,8 7651328 /path/to/custom/tmpdir/ibmcFP3W (deleted)

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:

SET GLOBAL innodb_tmpdir = '/path/to/large/disk';

1	SET GLOBAL innodb_tmpdir = '/path/to/large/disk';

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.