In my earlier blog (here) I gave a high level introduction of INSTANT ADD/DROP COLUMNS feature which was introduced in 8.0.29. Now in this blog I will go into low level details of design and its working.
Before we go into more details, let’s talk about the on-disk row in InnoDB as it is very crucial to understand it before understanding INSTANT ADD/DROP COLUMN design and its working.
InnoDB Row Formats and Rows on Disk
From a very high level view, InnoDB rows (Row1, Row2 … RowN) on disk looks like following:
where ROW METADATA is the information kept on each row which helps to interpret the row once it is read. This Metadata also keeps the pointer (shown above) to the next record on page as records are stored in a linked list.
NOTE: Although in above depiction, the next record always looks on right side of the previous record, physically on disk the next record could be anywhere on the page and its offset is stored in previous record to maintain the list.
There are 4 row formats named REDUNDANT, COMPACT, DYNAMIC and COMPRESSED supported in InnoDB. And each one of them follow above scheme. Let’s go more into details of ROW METADATA.
ADDITIONAL METADATA
This is used to keep some additional metadata eg : Length of the variable length columns, null bitmap for COMPACT/DYNAMIC row formats etc.
ROW HEADER
In REDUNDANT row format ROW HEADER is 6 bytes in length whereas in COMPACT/DYNAMIC/COMPRESSED it is of 5 bytes in length.
This is used to keep information like Next Record Offset on page etc. Along with these information it also keeps 4 bits of INFO BITS which is of importance for our discussion.
Instant Metadata on Rows on Disk
In all the row formats, INFO_BITS are of 4 bits. These info bits represent different state of a ROW (eg : delete marked etc.). The second bit (x), shown in above figure, was free which I used to indicate if this row has a row version. Let’s call it INSTANT BIT.
Q : When will a row in a table have a version?
A : If the table hasn’t gone through any INSTANT ADD/DROP COLUMN operation, then this table metadata version is always 0. And there is no need to have any version on any row of this table. For this table, on every row, this bit in the record header will be 0 indicating this row belongs to table metadata version 0.
Once the table has gone through one or more INSTANT ADD/DROP COLUMN, table metadata version must have been bumped up. In this table, each row which is inserted (updated) will have this bit set and the row version explicitly stamped on it. See below:
NOTE : If INSTANT BIT is not set in INFO_BITS, there will be no 1 byte version stored on row.
Instant Metadata in Data Dictionary for Columns
New additional metadata for column is introduced
| New Metadata | Description |
|---|---|
| VERSION_ADDED | The table metadata version in which this column was added. |
| VERSION_DROPPED | The table metadata version in which this column was dropped. |
Interpreting a Row during Fetch
With the above two metadata information in hand, it is easy to determine which columns have their values present on disk in this row.
To interpret a row, following rules can be applied:
- Ignore all the columns value with VERSION_DROPPED > 0
- Use default value for columns with VERSION_ADDED > ROW_VERSION
Let’s see it with an example.
Scenario
-- T1 [c1, c2, c3, c4]
CREATE TABLE t1 (C1 CHAR(10), C2 CHAR(10), C3 CHAR(10), C4 CHAR(10));
-- Insert a row R1
INSERT INTO t1 VALUES ("r1c1", "r1c2", "r1c3", "r1c4");
-- INSTANT DROP C3 (version 1). Table will be T1 [c1, c2, c4]
ALTER TABLE t1 DROP COLUMN C3, ALGORITHM=INSTANT;
-- INSTANT ADD C5 (Version 2). Table will be T1 [c1, c2, c4, c5]
ALTER TABLE t1 ADD COLUMN C5 CHAR(10) DEFAULT "C5d", ALGORITHM=INSTANT;
-- FETCH Row R1
SELECT * from t1;
Expectations
We shall not see the value of C3 in fetch result though it’s there on disk in R1
We shall see the value of C5 in fetch result though it is not there on disk in R1
Depiction
+----------------------------------+
| Columns' Metadata in DD |
+---------+------------------------+
| Version | C1 | C2 | C3 | C4 | C5 |
+---------+------------------------+
| 0 | E | E | E | E | - | +---------------------------+
+---------+------------------------+ | Default value of C5 in DD |
| 1 | E | E | ID | E | - | +---------------------------+
+---------+------------------------+ | C5d |
| 2 | E | E | - | E | IA | +---------------------------+
+---------+------------------------+ |
E => Existing Column |
ID => INSTANT Dropped Column |
IA => INSTANT Added Column |
|
+----------------+-----+-----+-----+-----+ |
| V0 row on disk | Vc1 | Vc2 | Vc3 | Vc4 | |
+----------------+-----+-----+-----+-----+ |
| | x | +-----------+
| | x | |
V V V V
+-----------------+------+------+------+------+
| Row fetched | Vc1 | Vc2 | Vc4 | C5d |
+-----------------+------+------+------+------+
In above :
| Column | VERSION ADDED | VERSION DROPPED |
+------------+---------------+-----------------+
| C1 | 0 | 0 |
+------------+---------------+-----------------+
| C2 | 0 | 0 |
+------------+---------------+-----------------+
| C3 | 0 | 1 |
+------------+---------------+-----------------+
| C4 | 0 | 0 |
+------------+---------------+-----------------+
| C5 | 2 | 0 |
+------------+---------------+-----------------+
In action
Let’s see it all in action
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t1 VALUES ("r1c1", "r1c2", "r1c3", "r1c4");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1;
+------+------+------+------+
| C1 | C2 | C3 | C4 |
+------+------+------+------+
| r1c1 | r1c2 | r1c3 | r1c4 |
+------+------+------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE t1 ADD COLUMN C5 CHAR(10) DEFAULT "c5_def", ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4", "r2c5");
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t1;
+------+------+------+------+--------+
| C1 | C2 | C3 | C4 | C5 |
+------+------+------+------+--------+
| r1c1 | r1c2 | r1c3 | r1c4 | c5_def |
| r2c1 | r2c2 | r2c3 | r2c4 | r2c5 |
+------+------+------+------+--------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE t1 DROP COLUMN C3, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+------+------+------+--------+
| C1 | C2 | C4 | C5 |
+------+------+------+--------+
| r1c1 | r1c2 | r1c4 | c5_def |
| r2c1 | r2c2 | r2c4 | r2c5 |
+------+------+------+--------+
2 rows in set (0.00 sec)
So the exepctations are met!
On disk rows and DD column Metadata changes with INSTANT ADD/DROP
Now let’s see how the rows are stored on disk as and when INSTANT ADD/DROPs are done on a table.
INSERT INTO t1 values ("r1c1", "r1c2", "r1c3", "r1c4");
| On Disk Row(s) | ||||
|---|---|---|---|---|
| ADDITIONAL METADATA | VERSION | ROW_HEADER | ROW DATA | |
| INFO_BITS | … | |||
| … |
–NA– |
X 0 X X | … | r1c1r1c2r1c3r1c4 |
| Columns’ metadata in DD | ||
|---|---|---|
| Column Name | VERSION_ADDED | VERSION_DROPPED |
| C1 | 0 | 0 |
| C2 | 0 | 0 |
| C3 | 0 | 0 |
| C4 | 0 | 0 |
INSERT INTO t1 values ("r2c1", "r2c2", "r2c3", "r2c4", "r2c5");
| On Disk Row(s) | ||||
|---|---|---|---|---|
| ADDITIONAL METADATA | VERSION | ROW_HEADER | ROW DATA | |
| INFO_BITS | … | |||
| … |
–NA– |
X 0 X X | … | r1c1r1c2r1c3r1c4 |
| … |
1 |
X 1 X X | … | r2c1r2c2r2c3r2c4r2c5 |
| Columns’ metadata in DD | ||
|---|---|---|
| Column Name | VERSION_ADDED | VERSION_DROPPED |
| C1 | 0 | 0 |
| C2 | 0 | 0 |
| C3 | 0 | 0 |
| C4 | 0 | 0 |
| C5 | 1 | 0 |
INSERT INTO t1 values ("r3c1", "r3c2", "r3c4", "r3c5");
| On Disk Row(s) | ||||
|---|---|---|---|---|
| ADDITIONAL METADATA | VERSION | ROW_HEADER | ROW DATA | |
| INFO_BITS | … | |||
| … |
–NA– |
X 0 X X | … | r1c1r1c2r1c3r1c4 |
| … |
1 |
X 1 X X | … | r2c1r2c2r2c3r2c4r2c5 |
| … |
2 |
X 1 X X | … | r3c1r3c2r3c4r3c5 |
| Columns’ metadata in DD | ||
|---|---|---|
| Column Name | VERSION_ADDED | VERSION_DROPPED |
| C1 | 0 | 0 |
| C2 | 0 | 0 |
| C3 | 0 | 2 |
| C4 | 0 | 0 |
| C5 | 1 | 0 |
Cool ! So all this looks so straight forward.
Q : How about the scenarios where Columns are added and dropped from a random position. And new rows are inserted/existing ones are updated. How’s that handled?
A : Right, this needs a bit more complex example and lot more detailed explanation of steps how thing are handled in memory and on disk. I would be explaining that in details in my next blog (3rd Blog in the series). Stay tuned!
Consideration for upgrade
As it was mentioned in earlier blog that before this feature, in MySQL 8.0.12, we already had an INSTANT ADD COLUMN feature in which columns could be added only as last column in the table. So any upgrade from earlier releases which might have tables which have gone through INSTANT ADD, shall be handled correctly. We have following scenarios while upgrading to version >= MySQL 8.0.29 :
- Table getting upgraded from a version V where V < MySQL 8.0.12.
- These tables don’t have any INSTANT ADD column.
- Any new INSTANT ADD/DROP done on these tables, would create a table metadata version 1.
- These rows are correctly handling during Read/Write operations.
- Tables getting upgraded from a version V where MySQL 8.0.12 < V < MySQL 8.0.29
- These tables may have INSTANT ADD COLUMN executed and might have rows which may/may not have values for INSTANTLY ADDED columns.
- Thus we may have following 4 kind of possible rows on a table which has gone through an upgrade.
- Inserted before any INSTANT ADD was done in previous implementation.
- Inserted after INSTANT ADD done in previous implementation but before upgrade.
- Inserted after INSTANT ADD done in previous implementation but after upgrade and before any instant ADD/DROP done in current implemenation.
- Inserted after INSTANT ADD/DROP done in current implementation.
- All these rows are correctly handled during Read/Write operations.
Detailed explanation of how upgrade works for table with INSTANT ADD columns and how these different kind of rows on disk are handled would need yet another blog. Stay tuned !
Thanks for using MySQL !