MySQL Online DDL Space Errors: Causes, Fixes, and Prevention

Problem Background

A client reported an "out of space" error when executing alter table table_name engine=innodb;. The tmpdir was set to /tmp, which had limited space. Changing tmpdir to the data directory allowed the ALTER operation to succeed. However, there's more to uncover from this error.

Information Interpretation

According to official documentation, three aspects of space must be sufficient for Online DDL operations:

Temporary Log Files

During Online DDL operations for index creation or table changes, temporary log files record concurrent DML operations. The maximum size is controlled by innodb_online_alter_log_max_size. Long operations with many DML modifications can exceed this value, causing a DB_ONLINE_LOG_TOO_BIG error and rolling back uncommitted DML operations.

Temporary Sort Files

For Online DDL operations that rebuild tables, temporary sort files are written to MySQL's temporary directory ( tmpdir on UNIX systems). A small /tmp directory can lead to Online DDL failure if it can't accommodate these files.

Intermediate Table Files

These files, created in the same directory as the original table and possibly requiring space equal to the original table's size, are named with a #sql-ib prefix and exist only during Online DDL operations.

Prerequisites

Let's conduct tests for temporary sort files and intermediate table files. First, some preparations:

1. Create a test database

The data directory is /opt/mysql/data/3310/my_test.

create database my_test;

‍

2. Restrict data directory size

# Create a 600M disk image file
dd if=/dev/zero of=/root/test.img bs=60M count=10

# Mount the device
losetup /dev/loop0 /root/test.img

# Format the device
mkfs.ext3 /dev/loop0

# Mount as a folder to limit its size to 600M
mount -t ext3 /dev/loop0 /opt/mysql/data/3310/my_test

# Change ownership to the MySQL service 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

Note: The data volume shouldn't be too small—around 30% of the /opt/mysql/data/3310/my_test directory is recommended.

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

‍

5. Modify /tmp size

Here, tmpdir is /tmp. Reduce its size:

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

‍

6. Adjust other parameters

Set sort_buffer_size, tmp_table_size, and max_heap_table_size to smaller values to easily generate disk temporary files. Note: This is not recommended for production environments as it can severely impact performance.

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

‍

Scenario Testing

After logging into the database and executing the following operation, we observed a failed index addition with the error:

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

‍

After increasing the /tmp directory size and re-executing the ALTER operation, it succeeded:

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

‍

If the usage of the /opt/mysql/data/3310/my_test directory is low, continue inserting data until disk space usage exceeds 50%. Then, executing the following operation will result in an error:

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

‍

Problem Summary

To ensure smooth Online DDL operations:

Check innodb_online_alter_log_max_size before operations. While increasing it can help, be aware that high DML activity during DDL can prolong table locking time at the end of the operation. Choose a low-peak business period or consider tools like pt-osc or ghost.

Set tmpdir to a reasonable value during instance installation. Note that this value doesn't support dynamic modification.

Monitor disk space regularly. Avoid using Online DDL operations like optimize table table_name; or alter table table_name engine=innodb; to clean up fragment space when disk space is nearly full, as these operations themselves require additional space.