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.
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
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!
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
We then need to 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 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)
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 !