MySQL InnoDB Storage Engine: Deep Dive - Mydbops

Copyright © 2023, Oracle and/or its affiliates | Confidential: Internal/Restricted/Highly Restricted
MySQL - InnoDB
MyDBOps
June 08, 2024
1

About me
2 Copyright © 2024, Oracle and/or its affiliates
● Mayank Prasad
● M.Tech in Information Technology (IIIT Bangalore)
● Part of MySQL Server Development since 2011
● Part of MySQL-InnoDB Storage Engine Development team since 2017

Safe Harbor Statement
3
The following is intended to outline our general product direction. It is intended
for information purposes only, and may not be incorporated into any contract.
It is not a commitment to deliver any material, code, or functionality, and
should not be relied upon in making purchasing decisions. The development,
release, and timing of any features or functionality described for Oracle’s
products remains at the sole discretion of Oracle.
Copyright © 2024, Oracle and/or its affiliates

4
➡Configurable REDO logs
➡INSTANT ADD/DROP COLUMN
➡Q&A
Agenda

5
➡INSTANT ADD/DROP COLUMN
➡Q&A
Agenda

● REDO Logs are WAL for InnoDB
● To avoid flushing dirty pages (16K) to disk
every time they change.
● An on disk circular buffer
● Snake Metaphor : A snake eating it’s own tail.
Dynamic Configuration of REDOs
6
Introduction
head
head
head
File 1
Log file size

7
Introduction
● Longer REDOs => Less page flushes but Longer recovery. Slower slow shutdown.
● Shorter REDOs => More page flushes but shorter recovery. Performance impact.
● Trial and error to see what suits according to Workload
● Can specify size of REDO Log file (innodb_log_file_size)
● But REDO file size Can’t be changed at run time. A server restart is needed
● There were requests to make it dynamic.

● Still an on disk circular buffer but spanned across
multiple files
● Instead of file size, we talk in terms of capacity
(innodb_redo_log_capacity) in bytes.
● Can be changed at runtime i.e. no server restart.
● Number of files and their size is calculated automatically
8
Solution
head
head
head
File 2
File N
File 1
File …
REDO log capacity

• A new dedicated folder: #innodb_redo in MySQL’s datadir
• InnoDB creates 32 redo log
fi
les
• If more space is needed, space can be reclaimed by deleting old “not needed”
fi
les and create new to be reused for
new REDOs.
• Not needed : Files containing REDOs which are behind checkpoint LSN.
9 Copyright © 2024, Oracle and/or its affiliates
Solution contd

10
• checkpoint_lsn (Innodb_redo_log_checkpoint_lsn) :
• an LSN point up to which all changes to the pages are guaranteed to have already been written and fsynced back to tablespace
fi
les – basically, the still
needed portion of redo log starts here.
• current_lsn (Innodb_redo_log_current_lsn) :
• the last written position in the redo log. That write could still be buffered inside MySQL processes buffer.
•
fl
ushed_to_disk_lsn (Innodb_redo_log_flushed_to_disk_lsn) :
• the last position in the redo log that InnoDB has been
fl
ushed to disk.
Solution contd

11
Solution contd
• When needed, the log files governor will perform some cleanup and some active files that are not needed
anymore will become the new spare ones
• When the background thread is not able to remove a log
fi
le from the left to put it to the right, the user
transaction will get stuck waiting for REDO buffers to be written to disk. DBAs get warning in the error log
notifying them to increase the InnoDB Redo Log Capacity
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file.
Consider increasing innodb_redo_log_capacity.

Observability
12
• The new Redo Log is instrumented in performance_schema in the table innodb_redo_log_files
• This means there are 5 active redo log
fi
les and 27 (32-5) spare ones (_tmp):

13
● Resizing
• Eg : make capacity to 200MB
SET GLOBAL innodb_redo_log_capacity =200*1024*1024;
• Resizing Up
• Instant
• Resizing Down
• Need to wait for existing REDOs to get truncated.
• Happens in background
• Resizing status
mysql> SHOW STATUS LIKE 'Innodb_redo_log_resize_status';
+-------------------------------+------------------+
| Variable_name | Value |
+-------------------------------+------------------+
| Innodb_redo_log_resize_status | Resizing down |
+-------------------------------+------------------+
mysql> SHOW STATUS LIKE 'Innodb_redo_log_resize_status';
+-------------------------------+-------+
+-------------------------------+-------+
| Innodb_redo_log_resize_status | OK |
+-------------------------------+-------+
• Current REDO log capacity
mysql> SHOW STATUS LIKE 'Innodb_redo_log_capacity_resized';
+----------------------------------+-----------+
+----------------------------------+-----------+
| Innodb_redo_log_capacity_resized | 104857600 |
+----------------------------------+-----------+
Observability contd

14
➡INSTANT ADD/DROP COLUMNS
➡Q&A
Agenda

● InnoDB on disk Row Format
INSTANT ADD/DROP Column
15
Introduction
Row Metadata Row Data
• Null bitmap
• Length of var length fields
• Etc.

16
Row Metadata Row Data
Fixed size row Header
Variable size row Header
6 bytes
(REDUNDANT)
5 bytes
(COMPACT/DYNAMIC/COMPRESSED)
Null bitmap*
0 0 0 1 0 1 1 0 0
…
Var length fields lengths
L1
L2
L3
Ln …
InnoDB Row Header
*null bitmap is not present in REDUNDANT row format

17
● Each record header keeps columns specific info.
● Add/Drop column would need each row header to be modified.
● Causes table rebuild
● Bigger the table, more the time it take.
● Costly - time, disk, resource, schedule
● Table lock
● Replication - Master does it. Then slave does the same.
● Big requirement from users/customers
InnoDB Row Format Impact

● Only metadata change.
● No table rebuild.
● No table lock required for long duration
● Saves time/space/resources
● Replication would benefit.
18
Idea behind solution

● MySQL 8.0.12
● ALTER TABLE t1 ADD COLUMN … ALGORITHM=INSTANT;
● Limitations
● Only ADD COLUMN support
● Column can be added only at the end. AFTER/FIRST clause throws error.
● New design
● Can add column at any place.
● Design work for DROP column as well. So column can be dropped from any place.
● ALTER TABLE t1 ADD COLUMN [AFTER/FIRST] … ALGORITHM=INSTANT;
● ALTER TABLE t1 DROP COLUMN … ALGORITHM=INSTANT;
19
Earlier attempt and new design

20
Solution : New design, Row versions
● Introduced “Row versioning”
● Every time a new INSTANT ADD/DROP Done, bump the versions
● Keep information of columns in the version they are ADDed or
DROPped.
● Stamp this version on the row when it is inserted/updated.
● When fetched, rows are transformed (if needed) to current row version.

21
Next Row Offset
Num Columns
HEAP Number
1|2 bytes
offset?
NUM
owned
INFO
BITS
1 bit 16 bits
10 bits
13 bits
4 bits
4 bits
6 bytes header
(REDUNDANT)
Free bit
6 bytes header
Version
8 bits
1 …
Preceding version byte is present
Solution : On disk row format change

22
Next Row Offset
HEAP Number Status
NUM
owned
INFO
BITS
3 bits 16 bits
13 bits
4 bits
4 bits
5 bytes header
(COMPACT/DYNAMIC/COMPRESSED)
Free bit
5 bytes header
Version
8 bits
1 …
Preceding version byte is present
Solution : On disk row format change contd.

23
Row Data
Solution : On disk row format change contd.
● A row with no row version.
● Bit in INFO BITS is 0.
● No version on row on disk
0
● A row with no row version.
● Bit in INFO BITS is 1.
● Version is stamped on row on disk
Row Metadata
Row Data
Row Metadata
Version
1

24
Solution : Transforming rows while fetch
New info on rows on disk
• rec_version
New column metadata
• version_added : The row version in which the column I added (0 if it was present during CREATE)
• version_dropped : The row version in which the column is dropped (0 if it is present currently)
When a record is read, with above informations, it is easy to determine which columns are present in the record.
And while fetching :
• Ignore all the columns value with version_dropped > 0
• Use default value for columns with version_added > rec_version

25
SQL C1 C2 C3 C4 C5 Version
CREATE TABLE Y Y Y Y - 0
ALTER TABLE … DROP C3 Y Y D Y - 1
ALTER TABLE … ADD C5 … DEFAULT C5Default Y Y - Y A 2
Column version_added version_dropped Default value
C1 0 0
C2 0 0
C3 0 1
C4 0 0
C5 2 0 C5Default
On disk R1 in V0 Vc1 Vc2 Vc3 Vc4
Row fetched Vc1 Vc2 Vc4 Vc5
Solution : Transforming rows while fetch
• (nothing stamped) rec_version 0
• Current version is 2.
• C3 was present on disk and to be ignored
• C5 wasn’t present so need to populate
default value
Insert

26
Solution : Flow explanation
No changes to rows on table!
Metadata (eg: Default values)
DML
INSERT
DML
UPDATE
DDL
ALTER TABLE … ADD/DROP COLUMN…
,ALGORITHM=INSTANT;
TABLE
R2’ : Row with no version
R3 : Row with no version
R4 : Row with version 1
R3’ : Row with version 1
Data
Dictionary
Core DD tables
System tables
———————————
Fetch
R1
R2’
R3’
R4
———————————
Row data
Metadata
R1 and R2’ transformed

27
Default ALGORITHM
● For ALTER TABLE … ADD/DROP COLUMN …
● ALGORITHM=INSTANT is default. I.e. if not specified explicitly we try to use INSTANT
algorithm.
● If INSTANT is not possible, it falls back to ALGORITHM=INPLACE/COPY.
● Once OPTIMIZE TABLE and other ALTER TABLE DDLs, which cause tables to rebuild, are
executed, INSTANT metadata is reset.
● Column metadata is reset in DD
● row_version is reset to 0.
● TRUNCATE TABLE resets the INSTANT metadata (no row on disk to interpret).

28
ROW version limit
● Not frequent operations.
● Maximum row versions allowed are 64.
● After that ALGORITHM=INSTANT throws with following error
ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped
instantly. Please use COPY/INPLACE.
● If ALGORITHM=INSTANT is not specified, it falls back to ALGORITHM=COPY automatically.
● As it rebuilds the table, INSTANT Metadata from each row is reset
● Column metadata is reset in DD
● row_version is reset to 0.

29
Compatibility : Upgrade
● Earlier ADD COLUMN has different row format.
● No need to transform any rows on disk during upgrade.
● After upgrade we might have table which has
● Rows INSERTED before earlier INSTANT ADD was done
● Rows INSERTED after earlier INSTANT ADD was done
● Rows INSERTED before new INSTANT ADD/DROP was done
● Rows INSERTED after new INSTANT ADD/DROP was done
● These rows are transformed correctly when fetched and.
● Once table is rebuilt (OPTIMIZE TABLE, ALTER etc.) Instant metadata is cleared.

30
Compatibility : IMPORT/EXPORT
● EXPORT
● We write INSTANT metadata in the cfg file.
● IMPORT
● cfg file is must
● allowed even if target table has only the columns which exists (eg: CREATE TABLE LIKE)
● INSTANT metadata is populated from CFG file. Rows are interpreted correctly.
SOURCE
(INSTANT ADD/DROP)
DESTINATION
(INSTANT ADD/DROP)
ALLOWED REMARK
NO NO YES -
YES NO YES -
NO YES NO -
YES YES YES/NO Allowed iff INSTANT metadata
matches

31
Observability
mysql> CREATE TABLE t1 (c1 INT);
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT N_COLS, INSTANT_COLS, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE
"%t1%";
+--------+--------------+--------------------+
| N_COLS | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+--------+--------------+--------------------+
| 4 | 0 | 0 |
+--------+--------------+--------------------+
1 row in set (0.02 sec)
mysql> SELECT HAS_DEFAULT, DEFAULT_VALUE FROM
INFORMATION_SCHEMA.INNODB_COLUMNS WHERE NAME LIKE "%c1%";
+-------------+------------------------------+
| HAS_DEFAULT | DEFAULT_VALUE |
+-------------+------------------------------+
| 0 | NULL |
+-------------+------------------------------+
mysql> ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 100
FIRST, ALGORITHM=INSTANT;
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT N_COLS, INSTANT_COLS, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE
"%t1%";
+--------+--------------+--------------------+
| N_COLS | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+--------+--------------+--------------------+
| 5 | 0 | 1 |
+--------+--------------+--------------------+
mysql> SELECT HAS_DEFAULT, DEFAULT_VALUE FROM
INFORMATION_SCHEMA.INNODB_COLUMNS WHERE NAME LIKE "%c2%";
+-------------+------------------------------+
| HAS_DEFAULT | DEFAULT_VALUE |
+-------------+------------------------------+
| 1 | 0x3830303030303634 |
+-------------+------------------------------+
1, if column is
added earlier
INSTANT ADD
implementation

32
Example and Limitation
Here is what I tried on my system :
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 8388608 |
+----------+
mysql> alter table t1 add column c4 char(10), algorithm=copy;
mysql> alter table t1 add column c5 char(10), algorithm=instant;

33
Limitations
● No support for COMPRESSED tables (seldom used)
● No support for tables with a full-text index
● No support for table residing in DD Tablespace (Not needed)
● No support for temporary tables
Blog 1 link here
Blog 2 link here

34
Thank you

The Snake Metaphor
36
● A snake which can move from one cage to another connected cage.
● Needed redo log => snake body
● Each redo file => A cage snake moves into.
● When reached to the last cage, cages from left to be move to right side so that snake can
move forward.
● Size of snake shirks when checkpoint_lsn moves forward
● Size of snake increases when more redos are written.

MySQL InnoDB Storage Engine: Deep Dive - Mydbops

Recommended

Recommended

More Related Content

Similar to MySQL InnoDB Storage Engine: Deep Dive - Mydbops

Similar to MySQL InnoDB Storage Engine: Deep Dive - Mydbops (20)

More from Mydbops

More from Mydbops (20)

Recently uploaded

Recently uploaded (20)

MySQL InnoDB Storage Engine: Deep Dive - Mydbops