Prototyping a persistent-memory-native MySQL Storage Engine Leo #MySQL #Database #Transaction #PersistentMemory

Prototyping a persistent-memory-native MySQL Storage Engine Leo April 22, 2020 Shohei Matsuura YAHOO! JAPAN Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved.

Agenda 1. Overview 2. Quick Glance at Leo 3. Transaction Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 2

1. Overview YAHOO! JAPAN Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 3

What is Leo? ■ PMEM*1-native MySQL Storage Engine with Transaction Feature ✓ Lock Granularity: Tuple-level ✓ Isolation Levels: Read-committed(Default)/Repeatable Read ✓ Crash Recovery: Transaction Log Based ✓ Data Files & Transaction Log Files mmaped to mysqld/Leo with libpmem*2 mysqld Leo Storage Engine Insert Delete Select Update Commit Rollback mmap with libpmem PMEM Data Files (Table/Index Data) Transaction Log Files *1 PMEM: Persistent Memory *2 libpmem: low-level library for PMEM programming within PMDK https://pmem.io/pmdk/ Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 4

Background: PMEM Characteristics • Byte-addressable Memory with Data Persistency • Have intermediate characteristics between DRAM and (NVMe) SSDs in terms of performance and capacity ➢ Positioning of PMEM • Non-volatility • Higher I/O performance than SSD • Higher capacity than DRAM Performance Higher Capacity Larger Volatile DRAM Non-volatile Persistent Memory SSD Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 5

2. Quick Glance at Leo, how it is working? YAHOO! JAPAN Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 6

Glance of Leo as a MySQL Storage Engine ■ Leo Recognized as a Storage Engine mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | LEO | YES | Leo Storage Engine | YES | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.00 sec) Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 7

Glance of Leo as a MySQL Storage Engine Table Creation mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> create table t1(c1 int primary key, c2 char(4), c3 varchar(64), c4 int) engine=leo; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ 1 row in set (0.00 sec) Insert Operation mysql> insert into t1 values(0,'aaaa','bbb',1); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(1,'cccc','c',2); Query OK, 1 row affected (0.00 sec) mysql> insert into t1(c1,c4) values(2,3); Query OK, 1 row affected (0.00 sec) Select Operation mysql> select * from t1; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 0 | aaaa | bbb | 1 | | 1 | cccc | c | 2 | | 2 | NULL | NULL | 3 | +----+------+------+------+ 3 rows in set (0.00 sec) mysql> select avg(c1) from t1; +---------+ | avg(c1) | +---------+ | 1.0000 | +---------+ 1 row in set (0.00 sec) mysql> select * from t1 where c2 like 'a%'; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 0 | aaaa | bbb | 1 | +----+------+------+------+ 1 row in set (0.00 sec) Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 8

Glance of Leo as a MySQL Storage Engine Delete Operation mysql> select * from t1; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 0 | aaaa | bbb | 1 | | 1 | cccc | c | 2 | | 2 | NULL | NULL | 3 | +----+------+------+------+ 3 rows in set (0.00 sec) mysql> delete from t1 where c1=0; Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 1 | cccc | c | 2 | | 2 | NULL | NULL | 3 | +----+------+------+------+ 2 rows in set (0.00 sec) Update Operation mysql> select * from t1; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 1 | cccc | c | 2 | | 2 | NULL | NULL | 3 | +----+------+------+------+ 2 rows in set (0.00 sec) mysql> update t1 set c4=c4+1; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from t1; +----+------+------+------+ | c1 | c2 | c3 | c4 | +----+------+------+------+ | 1 | cccc | c | 3 | | 2 | NULL | NULL | 4 | +----+------+------+------+ 2 rows in set (0.00 sec) Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 9

3. Transaction YAHOO! JAPAN Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 10

Leo Transaction Control
■ Preventing uncommitted transaction seen by another transaction
Insert tuples to a table in TX A, and check that
they are not visible to another transaction TX
B until the TX A commits.
TX A
TX B
4 Tuple Insertion
to the Table T1
Read the Table T1
COMMIT
Read the Table T1
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table t1(c1 char(4), c2 char(4)) engine=leo;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('aaaa','aaaa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values('bbbb','bbbb');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values('cccc','cccc');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values('dddd','dddd');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| dddd | dddd |
+------+------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| dddd | dddd |
+------+------+
4 rows in set (0.01 sec)
TX Not Committed
TX Committed
After the tuple insertion, read the table by
another TX.
➔ Inserted Tuples Not visible! As expected!
mysql> select * from t1;
Empty set (0.00 sec)
Read by another TX after the commit. Now the tuples
are visible to another TX.
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| dddd | dddd |
+------+------+
4 rows in set (0.00 sec)
Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 11

Leo Transaction Control
■ Preventing uncommitted transaction seen by another transaction
Update tuples in TX A, and check that the
updated tuples are not visible to another
transaction TX B until the TX A commits.
TX A
TX B
1 Tuple Update in
Table T1
Read the Table T1
COMMIT
Read the Table T1
mysql> update t1 set c1='eeee' where c1='dddd';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
Updated Tuple
Not Visible to
Another TX!
TX Not Committed
Updated Tuple Now
Visible to Another
TX After the
Commit!
TX Committed
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| dddd | dddd |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 12

Leo Transaction Control
■ Leo's Lock Granularity: Tuple-level
Update 2 different tuples from 2 transactions,
and check that they don't block each other.
TX A
TX B
Update a tuple
(c1=bbbb) in T1
Update a tuple
(c1=cccc) in T1
They don't block each other,
and can commit TX
independently
COMMIT
COMMIT
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
mysql> update t1 set c1='pppp' where c1='bbbb';
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| pppp | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
Not Blocking the
other!
Changes not
visible to another
TX before the
commit!
Changes now
visible to each
other after the
commit!
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
mysql> update t1 set c1='qqqq' where c1='cccc';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| qqqq | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 13

Leo Transaction Control
■ Leo's Lock Granularity: Tuple-level
Update the "SAME" tuple from 2 transactions,
and check that one of them is blocked by the
other.
TX A
TX B
Update a tuple
(c1=aaaa) in T1
Update a tuple
(c1=aaaa) in T1
One of them is blocked
because they do interfere
the other.
COMMIT
COMMIT
mysql> update t1 set c1='xxxx' where c1='aaaa';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
| xxxx | aaaa |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
| xxxx | aaaa |
+------+------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
| xxxx | aaaa |
+------+------+
4 rows in set (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
| xxxx | aaaa |
+------+------+
4 rows in set (0.00 sec)
Tuple Lock
Granted!
Holding
TX for
1min
Commit &
Tuple Lock
Released!
The final outcome is
visible to the both,
after their commit!
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
| xxxx | aaaa |
+------+------+
4 rows in set (0.00 sec)
mysql> update t1 set c1='yyyy' where c1='aaaa';
Query OK, 1 row affected (1 min 2.42 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
| yyyy | aaaa |
+------+------+
4 rows in set (0.00 sec)
Now TX B
can proceed!
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| pppp | bbbb |
| qqqq | cccc |
| eeee | dddd |
| yyyy | aaaa |
+------+------+
4 rows in set (0.00 sec)
Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 14

Leo Transaction Control
■ Transaction Rollback
Delete a Tuple, Rollback the TX, and check that
the delete is cancelled.
TX A
TX B
Delete a tuple
(c1=eeee) in T1
Read the Table T1
Rollback
Read the Table T1
Read the Table T1
mysql> delete from t1 where c1='eeee';
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
+------+------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
Delete Operation is
Cancelled with
Rollback!
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.01 sec)
mysql> select * from t1;
+------+------+
| c1 | c2 |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
| cccc | cccc |
| eeee | dddd |
+------+------+
4 rows in set (0.00 sec)
Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved. 15

Thank you! YAHOO! JAPAN Copyright (C) 2020 Yahoo Japan Corporation. All Rights Reserved.