MyRocks at Alibaba Cloud

1 .MyRocks at Alibaba Cloud MyRocks at Alibaba Cloud Zhang Yuan Alibaba Cloud ZhangYuan Nov,2018 

2 .About me Yuan Zhang Database engineer Work at Alibaba for 5 years Focus on MySQL & MyRocks Email: zhangyuan.zy@alibaba-inc.com 

3 .Agenda Brief MyRocks introduction Improvements we have made on MyRocks Limitations MyRocks products 

4 .MyRocks = MySQL + RocksDB 

5 . persistent statistic lower write amplification compress MyRocks data read free load replication 

6 .Compression 8X compression 400 300 200 100 0 rocksdb innodb sql text 

7 .Lower Write Amplification lower write amplification 18X Append Only & Sequential Write 

8 .Fast Replication Read Free Replication DELETE 
 unpack_current_row use row image to delete the row unpack_current_row directly, whether the row exist or not scan old row UPDATE
 delete/update use before row image to update the row directly，no need to find the row delete/update 

9 .Fast Load Data rocksdb_bulk_load=1 sql_log_bin=0 load rocksdb_skip_unique_check=1
 rocksdb_commit_in_the_middle=1 

10 .TTL CREATE TABLE t1 ( a BIGINT(20) NOT NULL, ttl_duration=3600 b INT NOT NULL, ts BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (a), ttl_cols=ts KEY kb (b) ) ENGINE=RocksDB COMMENT='ttl_duration=3600;ttl_col=ts;'; 

11 .Statistic Information 

12 .Read penalty bloom filter 

13 .MyRocks at Alibaba Cloud 

14 .MyRocks at Alibaba Cloud Clustering index Optimize auto increment Optimize replace into Optimize truncate Backup POLARDB for MyRocks 

15 .Secondary Clustering Index CREATE TABLE t1(c1 int PRIMARY KEY, c2 int, c3 int, KEY(c2)) ENGINE=RocksDB; SELECT c1,c2 FROM t1 WHERE c2=22; //cover index SELECT c1,c2,c3 FROM t1 WHERE c2=22; //no cover index CREATE TABLE t1(c1 int PRIMARY KEY, c2 int, c3 int, clustering key(c2)) ENGINE=RocksDB; SELECT c1,c2,c3 FROM t1 WHERE c2=22; //cover index Ordinary secondary index Secondary clustering index K: c2, c1 K: c2,c1 V: null V: c3 

16 .Optimize Replace into replace CREATE TABLE t1(c1 int PRIMARY KEY, c2 int) ENGINE=RocksDB; unique key exist INSERT INSERT INTO t1 VALUES (1,1); Yes No When doing replace into statement REPLACE INTO t1 VALUES(1,2); // pk=1 exist, do update update insert REPLACE INTO t1 VALUES(2,2); // pk=2 not exist, do insert replace In MyRocks, We can just do PUT whether pk is exist or not REPLACE INTO t1 VALUES(1,2); REPLACE INTO t1 VALUES(2,2); PUT(1,2) PUT(2,2) PUT(1,1) PUT(1,1) put/insert 

17 .Optimize truncate partitioned table Truncate Normal table is Fast Normal table truncate is recreate table, because drop table if fast Truncate partitioned table is very Slow Use remove_rows to truncate each sub partitioned tables remove_rows call delete/singledelete() for each row Optimize Not fsync at each delete, manually call FlushWAL at last Unfortunately, DeleteRange is still an experimental feature Origin: 1 hour 1000000 rows New: 32 seconds 

18 .Optimize Auto Increment Concurrent write Merge Use merge operator to persist auto increment at each commit, break But Merge operator is not allowed concurrent memtable write concurrent memtable write Skip unique check Optimize auto_increment, the basic idea is that if all auto_increment column is auto generated without specific values, then there is no need to check unique constraint. max_user_specific 6 10 11 12 13 

19 .Xtrabackup 

20 .Limitations 

21 .Limitations Large transactions SET sql_log_bin=0; SET rocksdb_bulk_load=on ALTER TABLE xxx ENGINE=RocksDB Write unprepare policy: write memtable after PUT 

22 .Limitations Collation Unsupported collation on string indexed column test.t1.c2 Use binary collation (binary, latin1_bin, utf8_bin, utf8mb4_bin). SET GLOBAL rocksdb_strict_collation_check=off SET GLOBALrocksdb_strict_collation_exceptions=‘t1’ create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_bin; insert into t1 values(1,'ab'); mysql> explain select c2 from t1 where c2='ab'; +——+-------------+-------+-------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | t1 | const | c2 | c2 | 33 | const | 1 | Using index | +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+ set global rocksdb_strict_collation_check=off create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_general_ci; insert into t1 values(a,'ab'); mysql> explain select c2 from t1 where c2='ab'; +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+ | 1 | SIMPLE | t1 | const | c2 | c2 | 33 | const | 1 | NULL | +----+-------------+------- 

23 .Limitations Gap Lock SELECT * FROM t1 WHERE c1 > 1 and c1 < 10 FOR UPDATE; INSERT INTO t1(c1,c2)VALUES(2,2); show variables like '%gap%'; +----------------------+-----------------------------------+ | Variable_name | Value | +----------------------+-----------------------------------+ | gap_lock_log_file | gaplock.log | | gap_lock_raise_error | OFF | | gap_lock_write_log | OFF | +———————————+-----------------------------------+ 3 rows in set (0.00 sec) 

24 .Limitations OnlineDDL Foreign key Fulltext index JSON 

25 .MyRocks Products 

26 .HyBridDB For MySQL(HTAP) OLTP Combine OLTP and OLAP on one database Support MyRocks client HTAP PB level data OLAP Data Compression proxy Query Optimizer Separation of Storage and Computing Compute Compute master node node master Flexible Expansion slave slave 

27 .POLARDB For MyRocks https://www.percona.com/live/18/sessions/polardb-for-myrocks-make-myrocks-run-on-shared-storage 

28 .Thank You