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:

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 !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

2 Comments

  1. I tried a simpler way to move the tablespace and solve that problem without the need to restart MySQL as below:
    1- Create the following table:
    create table recover_ibd
    (id int primary key, name char(40) null)
    engine=innodb;

    2- Insert some sample records in the table:
    insert into recover_ibd values (1,’temp’),(2,’test’),(3,’third’);
    Query OK, 3 rows affected (0.00 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    3- Lock the table for read to move its tablespace away:
    flush tabe recover_ibd with read lock;

    4- On another session, move the ibd file to another directory and create a soft link for it in the original directory:
    $ cd /data/restore/
    $ ls -lh
    total 116K
    -rw-r—– 1 mysql mysql 65 Feb 24 02:25 db.opt
    -rw-r—– 1 mysql mysql 422 Feb 24 02:33 recover_ibd.cfg
    -rw-r—– 1 mysql mysql 8.4K Feb 24 02:27 recover_ibd.frm
    -rw-r—– 1 mysql mysql 96K Feb 24 02:30 recover_ibd.ibd
    $ mv recover_ibd.ibd /tmp/
    $ ln -s /tmp/recover_ibd.ibd .
    $ ls -lh
    total 20K
    -rw-r—– 1 mysql mysql 65 Feb 24 02:25 db.opt
    -rw-r—– 1 mysql mysql 422 Feb 24 02:33 recover_ibd.cfg
    -rw-r—– 1 mysql mysql 8.4K Feb 24 02:27 recover_ibd.frm
    lrwxrwxrwx 1 root root 20 Feb 24 02:34 recover_ibd.ibd -> /tmp/recover_ibd.ibd

    5- Now the tablespace has been moved to another disk. We can confirm the accessibility of the data:
    unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    select * from recover_ibd;
    +—-+——-+
    | id | name |
    +—-+——-+
    | 1 | temp |
    | 2 | test |
    | 3 | third |
    +—-+——-+
    3 rows in set (0.00 sec)

    6- The table is accessible, now lets modify the table structure:
    alter table recover_ibd modify name varchar(40) null;
    Query OK, 3 rows affected (0.04 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    show create table recover_ibd;
    +————-+——————————————————————————————————————————————————+
    | Table | Create Table |
    +————-+——————————————————————————————————————————————————+
    | recover_ibd | CREATE TABLE `recover_ibd` (
    `id` int(11) NOT NULL,
    `name` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +————-+——————————————————————————————————————————————————+
    1 row in set (0.00 sec)

    7- Check tablespace again:
    $ ls -lh
    total 64K
    -rw-r—– 1 mysql mysql 65 Feb 24 02:25 db.opt
    -rw-r—– 1 mysql mysql 8.4K Feb 24 02:37 recover_ibd.frm
    -rw-r—– 1 mysql mysql 96K Feb 24 02:37 recover_ibd.ibd

    We can see that the softlink was deleted and the table with the new structure was copied.

    Note: we will need to delete the file we moved before doing the operation (i.e. /tmp/recover_ibd.ibd) as the ALTER will just replace the softlink

    • I would always prefer to not play with soft links (that are not always supported), I would prefer the use of pt-online-schema-change with –data-dir option if using tablespaces is not an option.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.