Diskspace Problem When Modifying a Large InnoDB Table in MySQL… and how to solve it !

Recently, somebody requested some help as he was trying to modify the structure of a large table. The requested modification was related in changing a fixed length string to a variable length string column.

If you don’t know it yet, MySQL supports multiple algorithms when altering a table (DDL):

• COPY: operations are performed on a copy
• INPLACE: operations may rebuild the table inplace (no copy)
• INSTANT: operations only change the metadata in the data dictionnary

The operation that interests us today needs to copy the full table. And this was the problem for the user as the MySQL Datadir could not store twice the amount of that large table.

In this blog post I will explain a method that can be used to solve this problem. There are also others method like a logical dump and restore for example, but in this article, we will work with tablespaces.

The user explained that he had plenty of storage on his system but not in the volume mounted for MySQL datadir.

The Problem

To simulate the problem, I used the airport-db sample database and we will modify the booking table that is +/- 5G on disk:

# ls -lh airportdb/booking.ibd 
-rw-r-----. 1 mysql mysql 5.1G Feb 14 20:03 airportdb/booking.ibd

I’ve created a volume for MySQL that is 9.8G:

Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/vg02-mysql      9.8G  7.5G  1.9G  80% /var/lib/mysql

As you can see, we have only 1.9G free that won’t be enough to copy our large table. If we try to modify the table booking to change the seat column from CHAR(4) to VARCHAR(10), we will see the following error:

mysql> ALTER TABLE booking MODIFY seat VARCHAR(10);
ERROR 1114 (HY000): The table '#sql-e0a4_8' is full

In MySQL Error Log we can also see the following messages:

2022-02-14T14:10:23.958456Z 8 [ERROR] [MY-012639] [InnoDB] Write to
   file ./airportdb/#sql-e0a4_8.ibd failed at offset 1927282688,
   1048576 bytes should have been written, only 12288 were written.    
   Operating system error number 28. Check that your OS and file
   system support files of this size.
   Check also that the disk is not full or a disk quota exceeded.
2022-02-14T14:10:23.958474Z 8 [ERROR] [MY-012640] [InnoDB] Error   
   number 28 means 'No space left on device'
2022-02-14T14:10:23.958492Z 8 [Warning] [MY-012145] [InnoDB] Error
   while writing 4194304 zeroes to ./airportdb/#sql-e0a4_8.ibd
   starting at offset 1925185536
2022-02-14T14:10:24.053932Z 8 [ERROR] [MY-013132] [Server] The table 
   '#sql-e0a4_8' is full!

The Solution

With InnoDB, it’s possible to use different types of tablespaces:

• system tablespace
• file-per-table tablespace
• general tablespace

File-per-table tablespace have the possibility to be stored on a different place, but this can only be defined at table’s creation. See Creating Tables Externally in the MySQL Manual.

So, this is not an option for us. We will then create a new general tablespace on a different directory (or volume). And then modify the large table while adding it in that new tablespace.

When done, if needed we can put back the table space as it was in the MySQL data directory.

First we need to modify my.cnf to specify where InnoDB will be able to also find tablespace by adding the following line under [mysqld]section:

innodb_directories=/var/lib/mysql2

We then need to restart mysqld:

mysql> restart;

And we can verify that the change was applied successfully:

mysql> select @@innodb_directories;
+----------------------+
| @@innodb_directories |
+----------------------+
| /var/lib/mysql2      |
+----------------------+
1 row in set (0.00 sec)

Now we will create the new tablespace that will be stored in this new directory:

mysql> CREATE TABLESPACE `new_tablespace` 
       ADD DATAFILE '/var/lib/mysql2/nts.ibd' ENGINE=INNODB;

We can now modify the large table and at the same time move it to its new tablespace:

mysql> ALTER TABLE booking MODIFY seat VARCHAR(10), 
       TABLESPACE new_tablespace;

/var/lib/mysql2 is a different directory (or a mount point) where there is enough space to have the copy of the table (that will be the final table after the ALTER).

While the ALTER statement is running, you can also get its progression using the following query:

mysql> SELECT stmt.THREAD_ID, stmt.SQL_TEXT, stage.EVENT_NAME 
       AS State, stage.WORK_COMPLETED, stage.WORK_ESTIMATED,                          
       lpad(CONCAT(ROUND(100*stage.WORK_COMPLETED/
        stage.WORK_ESTIMATED, 2),"%"),10," ")
       AS COMPLETED_AT,
       lpad(format_pico_time(stmt.TIMER_WAIT), 10, " ") 
       AS STARTED_AGO,
       lpad(format_pico_time(stmt.TIMER_WAIT/
          ROUND(100*stage.WORK_COMPLETED/
          stage.WORK_ESTIMATED,2)*100), 10, " ")
       AS ESTIMATED_FULL_TIME,              
       lpad(format_pico_time((stmt.TIMER_WAIT/
          ROUND(100*stage.WORK_COMPLETED/
          stage.WORK_ESTIMATED,2)*100)-stmt.TIMER_WAIT), 10, " ")
        AS ESTIMATED_REMAINING_TIME,
       current_allocated MEMORY
       FROM performance_schema.events_statements_current stmt
       INNER JOIN sys.memory_by_thread_by_current_bytes mt
       ON mt.thread_id = stmt.thread_id
       INNER JOIN performance_schema.events_stages_current stage
       ON stage.THREAD_ID = stmt.THREAD_ID\G
*************************** 1. row ***************************
               THREAD_ID: 51
                SQL_TEXT: ALTER TABLE booking MODIFY seat
                          VARCHAR(10), TABLESPACE new_tablespace
                   State: stage/sql/copy to tmp table
          WORK_COMPLETED: 46393043
          WORK_ESTIMATED: 54185083
            COMPLETED_AT:     85.62%
             STARTED_AGO:     1.31 h
     ESTIMATED_FULL_TIME:     1.52 h
ESTIMATED_REMAINING_TIME:  13.15 min
                  MEMORY: 2.39 MiB

Now we can see in the new folder the size of the tablespace:

# ls -lh mysql2
total 7.4G
-rw-r-----. 1 mysql mysql 7.4G Feb 14 22:12 nts.ibd

We can also see that there is more space in the MySQL datadir as the previous .idb file of this table is gone:

Filesystem                  Size  Used Avail Use% Mounted on
/dev/mapper/vg02-mysql      9.8G  1.5G  7.9G  16% /var/lib/mysql

Now that the schema is changed as we desired, we still have the option to put it back in the original MySQL datadir if we have enough diskspace:

mysql> ALTER TABLE booking TABLESPACE=innodb_file_per_table;

Now the table (more specifically the tablespace) is back to its initial location:

# ls -lh mysql/airportdb/booking.ibd 
-rw-r-----. 1 mysql mysql 5.3G Feb 14 22:34 mysql/airportdb/booking.ibd

# ls -lh mysql2/
total 7.4G
-rw-r-----. 1 mysql mysql 7.4G Feb 14 22:34 nts.ibd

The new general tablespace we used on a different disk still exists and consume unnecessary diskspace, we can delete it (from MySQL !):

mysql> SELECT it.*, PATH 
       FROM INFORMATION_SCHEMA.INNODB_TABLESPACES it 
       JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF itb 
       ON itb.SPACE=it.SPACE WHERE it.NAME='new_tablespace'\G
*************************** 1. row ***************************
          SPACE: 63
           NAME: new_tablespace
           FLAG: 18432
     ROW_FORMAT: Any
      PAGE_SIZE: 16384
  ZIP_PAGE_SIZE: 0
     SPACE_TYPE: General
  FS_BLOCK_SIZE: 4096
      FILE_SIZE: 7860125696
 ALLOCATED_SIZE: 7860129792
AUTOEXTEND_SIZE: 0
 SERVER_VERSION: 8.0.26
  SPACE_VERSION: 1
     ENCRYPTION: N
          STATE: normal
           PATH: /var/lib/mysql2/nts.ibd

Before deleting it we verify that there are no tables using it (we use the value of SPACE from the query above, 63):

mysql> SELECT * FROM innodb_tables WHERE space=63;
Empty set (0.0009 sec)

As there are none, we can just drop it and reclaim its disk space:

mysql> DROP TABLESPACE new_tablespace;
Query OK, 0 rows affected (0.3385 sec)

Conclusion

In MySQL 8.0 with some InnoDB tablespace management, it’s possible to deal temporary or not with disk space issue. The same technique can be of course used to put some tables on dedicated disk to split IOPS for example.

Enjoy MySQL 8.0 !