Understanding and Testing Space Requirements for MySQL Online DDL Operations

1 Problem Background

The customer reported an error when executing alter table table_name engine=innodb; due to insufficient space. Inspection of the MySQL tmpdir revealed it pointed to /tmp , a small directory. Changing tmpdir to the data directory and re‑running the ALTER succeeded, but the story continues.

2 Information Interpretation

According to the official MySQL documentation, Online DDL operations require sufficient space in three areas:

Temporary Log File

During Online DDL, a temporary log records concurrent DML. Its maximum size is controlled by innodb_online_alter_log_max_size . If the operation is long and many DML rows are modified, the log may exceed this limit, triggering DB_ONLINE_LOG_TOO_BIG and rolling back uncommitted DML.

Temporary Sort File

For Online DDL that rebuilds a table (e.g., index creation), temporary sort files are written to MySQL's temporary directory ( tmpdir ). If /tmp is small, the operation can fail because the sort file cannot be accommodated.

Intermediate Table File

Online DDL that rebuilds a table creates a temporary intermediate table in the same directory as the original table. The intermediate file may require space equal to the original table size and is prefixed with #sql-ib during the operation.

3 Preparation

Based on the documentation, the following test environment is set up:

1. Create a test database

create database my_test;

2. Limit the data directory size

# Create a 600M disk image
dd if=/dev/zero of=/root/test.img bs=60M count=10
# Attach the image
losetup /dev/loop0 /root/test.img
# Format the device
mkfs.ext3 /dev/loop0
# Mount to limit space
mount -t ext3 /dev/loop0 /opt/mysql/data/3310/my_test
# Change ownership to MySQL user
chown -R mysql.mysql /opt/mysql/data/3310/my_test

3. Create a test table

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Insert data

./mysql_random_data_load -h127.0.0.1 -P3310 -uuniverse_op -p'xxx' --max-threads=8 my_test student 1500000

5. Reduce /tmp size

mount -o remount,size=1M tmpfs /tmp

6. Reduce other parameters

set sort_buffer_size=128*1024;
set tmp_table_size=128*1024;

4 Scenario Test

Running the following command shows a failure due to insufficient temporary space:

mysql> alter table student add index idx_name (name);
ERROR 1878 (HY000): Temporary file write failure.

After expanding /tmp and re‑executing the ALTER , the operation succeeds:

[root@localhost ~]# mount -o remount,size=500M tmpfs /tmp
mysql> alter table student add index(name);
Query OK, 0 rows affected (4.92 sec)

Observing the data directory shows increased space usage; further data insertion is recommended until usage exceeds ~50%.

When attempting to change the storage engine with insufficient space, MySQL returns:

mysql> alter table student engine=innodb;
ERROR 1114 (HY000): The table 'student' is full

5 Problem Summary

Before Online DDL, check innodb_online_alter_log_max_size and adjust if necessary; a larger value may increase DML replay time after the operation.

Set tmpdir to a reasonable location during instance installation; it cannot be changed dynamically in production.

Monitor disk space proactively; operations like OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB also require extra space and may fail if space is low.

Reference

[1] innodb-online-ddl-space-requirements: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-space-requirements.html