Interpretation of new features | MySQL8.0 ALTER TABLE … ALGORITHM=INSTANT

Author: Zhang Na

A member of the DBA team in the Southern District of Aikesheng, responsible for the daily maintenance and troubleshooting of MySQL and TiDB.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

I. Introduction

Before MySQL 8.0.29, adding columns instantly in online DDL operations can only be added to the last column of the table. It is very inconvenient to quickly add columns after a specific column. MySQL 8.0.29 expands the support for ALTER TABLE … ALGORITHM=INSTANT Supported: Users can instantly add columns anywhere in the table, drop columns on the fly, evaluate row size limits as columns are added.

A new row version is created every time a column is added or removed on the fly. MySQL 8.0.29 adds a new column TOTAL_ROW_VERSIONS to the INFORMATION_SCHEMA.INNODB_TABLES table to track the number of row versions, allowing a maximum of 64 row versions per table.

In addition, XtraBackup 8.0.29 will have an episode when backing up MySQL 8.0.29. This episode is related to ALTER TABLE … ALGORITHM=INSTANT. Next, let's experience it one by one.

2. Experience with new features:

First create a table sbtest1 of 20 million:

 sysbench ./oltp_read_write.lua --mysql-host=10.186.61.168  
--mysql-user=root 
--mysql-password='XXXXXX' 
--mysql-port=3388 
--mysql-socket=/data/mysql8.0.29/data/mysqld.sock 
--mysql-db=test_a --tables=1 
--table-size=20000000 
--report-interval=2 
--threads=10 prepare

1. Add columns instantly at any location

The original table structure is as follows:

Immediately add a column anywhere in the table sbtest1: add column k2 after column k:

 mysql> ALTER TABLE sbtest1 ADD COLUMN k2 int(10) AFTER k,ALGORITHM=INSTANT;

It can be seen that adding columns to 20 million tables at any location is completed in seconds.

In versions prior to 8.0.29, only immediate addition of columns is supported in the last column of the table, and immediate addition of columns in any position of the table is not supported, as follows in 8.0.27:

It can be seen that it takes 7 minutes and 22 seconds to add a column to any position through the default ALGORITHM for the same table with 20 million rows, while ALGORITHM=INSTANT only supports adding a column immediately in the last column by default.

2. Instantly delete columns

Since MySQL 8.0.29, ALTER TABLE … ALGORITHM=INSTANT supports deleting a column. Add two columns first, and then delete two columns as follows:

 mysql> ALTER TABLE sbtest1 ADD COLUMN c4 int(10) ,ADD COLUMN c5 int(10),ALGORITHM=INSTANT;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> alter table sbtest1 DROP COLUMN c4,DROP COLUMN c5,ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

In versions before 8.0.29, ALTER TABLE … ALGORITHM=INSTANT does not support deleting a column, otherwise an error message ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation will be reported. Try ALGORITHM=COPY/INPLACE.

Operation error in 8.0.27:

3. Evaluate row size limit when adding columns

Before MySQL 8.0.29, the row size limit was not evaluated when adding columns. However, the row size limit is checked during DML operations that insert and update rows in the table. As of 8.0.29, the row size limit is checked when adding columns. If the limit is exceeded, an error will be reported.

For example we add a column that exceeds the row size limit:

 mysql> ALTER TABLE sbtest1 ADD COLUMN pad6 varchar(4990),ALGORITHM=INSTANT;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

Before 8.0.29, the row size limit will not be evaluated when adding a column. The same varchar(4990) in 8.0.27 below can be added successfully.

In this case, a pit will be buried when the data is updated in the future.

ALTER TABLE ... ALGORITHM=INSTANT creates a new row version after each operation that adds one or more columns, deletes one or more columns, or adds and deletes one or more columns in the same operation.

MySQL 8.0.29 adds a new TOTAL_ROW_VERSIONS column to the INFORMATION_SCHEMA.INNODB_TABLES table to track the number of row versions of a table. This value is incremented each time a column is immediately added or removed. The initial value is 0.

In the above operation, we have performed multiple ALTER TABLE ... ALGORITHM=INSTANT on the table sbtest1. INFORMATION_SCHEMA.INNODB_TABLES The number of row versions for sbtest1 has been recorded.

 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

4. XtraBackup 8.0.29 Backup Community Edition MySQL 8.0.29

XtraBackup 8.0.29 backs up the table with INSTANT ADD/DROP COLUMNS in community edition MySQL 8.0.29, and the following error message will appear.

 [root@node168 ~]# xtrabackup --version
2022-08-02T17:34:33.011020+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
[root@node168 ~]# xtrabackup 
--defaults-file=/data/mysql8.0.29/etc/my.cnf 
--target-dir=/data/backup8029 
--uer=root 
--password='xxxxxxxxx' 
--socket=/data/mysql8.0.29/data/mysqld.sock 
--backup

This is because the InnoDB redo log format has changed for all DML operations to support the new feature of ALTER TABLE … ALGORITHM=INSTANT. The new redo log format introduces a design flaw that causes table data corruption for instant add/drop columns. This bug is said to have been fixed in Percona version MySQL 8.0.29, but is still buggy in the current community version, MySQL 8.0.29.

Since XtraBackup cannot handle the corrupted redo log generated by Community Edition MySQL 8.0.29, if XtraBackup version 8.0.29 detects a table with INSTANT ADD/DROP columns, it will not backup and will generate an error message listing A list of affected tables with instructions for converting them to regular tables.

Therefore, before the backup, you can check whether there is a table with INSTANT ADD/DROP columns through the INFORMATION_SCHEMA.INNODB_TABLES table. If there is, you can perform the optimize table operation first, and then go to the backup.

as follows:

 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1190 | test_a/sbtest1 |   33 |     11 |    64 | Dynamic    |             0 | Single     |            0 |                 27 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.09 sec)
mysql> OPTIMIZE TABLE test_a.sbtest1;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| test_a.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test_a.sbtest1 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (7 min 12.49 sec)

You can then use XtraBackup to back up the Community Edition MySQL 8.0.29.

3. Summary

The new feature of ALTER TABLE … ALGORITHM=INSTANT can add one or more columns in any position of the table, and can quickly delete a column, which greatly improves the efficiency of online DDL.