RocksDB和Innodb引擎性能PK胜负难料？

SEian.G

发布于 2021-04-15 14:37:28

3.9K0

发布于 2021-04-15 14:37:28

迫于线上环境存储空间的问题，最近针对Rocksdb引擎做了一些预研测试，本文主要对比MyRocks引擎和Innodb引擎以及压缩模式下的Innodb引擎的在性能方面的一些差异对比，分别从读写，只读，只写等场景下压测的结果对比；关于rocksdb引擎的介绍，本文不做详细介绍；废话不多说了，我们先看一下如何来安装rocksdb引擎;

安装rocksdb引擎

安装rocksdb引擎需要的依赖包

percona-server-client-8.0.19-10.1.el7.x86_64.rpm
percona-server-devel-8.0.19-10.1.el7.x86_64.rpm
percona-server-rocksdb-8.0.19-10.1.el7.x86_64.rpm
percona-server-server-8.0.19-10.1.el7.x86_64.rpm
percona-server-shared-8.0.19-10.1.el7.x86_64.rpm
percona-server-shared-compat-8.0.19-10.1.el7.x86_64.rpm

# ps-admin --enable-rocksdb -u root -S /data/mysql_6306/tmp/mysql.sock
Checking if RocksDB plugin is available for installation ...
INFO: ha_rocksdb.so library for RocksDB found at /usr/lib64/mysql/plugin/ha_rocksdb.so.
 
Checking RocksDB engine plugin status...
INFO: RocksDB engine plugin is not installed.
 
Installing RocksDB engine...
INFO: Successfully installed RocksDB engine plugin.

ROCKSDB引擎安装好之后，可以连接到时候上查看，如下所示：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                             | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| ROCKSDB            | YES     | RocksDB storage engine                                                     | YES          | YES  | YES        |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | 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)

下面针对rocksdb和innodb引擎做几个简单测试：

mysql> create table tab1(id int primary key auto_increment,name varchar(30),age int,memo varchar(60))engine=innodb;
Query OK, 0 rows affected (0.01 sec)
 
mysql> create table tab2(id int primary key auto_increment,name varchar(30),age int,memo varchar(60))engine=rocksdb;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show create table tab1\G
*************************** 1. row ***************************
       Table: tab1
Create Table: CREATE TABLE `tab1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `memo` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql> show create table tab2\G
*************************** 1. row ***************************
       Table: tab2
Create Table: CREATE TABLE `tab2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `memo` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
mysql> insert into tab1 values(null,'张三',23,'测试用例'),(null,'李四',40,'测试innodb'),(null,'万物',29,'案例测试'),(null,'小王',45,'测试用例');    
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> insert into tab2 values(null,'张三',23,'测试用例'),(null,'李四',40,'测试innodb'),(null,'万物',29,'案例测试'),(null,'小王',45,'测试用例'); 
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
查看一下数据文件的大小:
[root@hb30-dbs-mysql-124-186 dbtest]# ll
total 84
-rw-r----- 1 mysql mysql 114688 Feb 22 14:06 tab1.ibd
-rw-r----- 1 mysql mysql   4015 Feb 22 14:00 tab2_384.sdi
  
[root@hb30-dbs-mysql-124-186 dbtest]# du -sh *
80K     tab1.ibd
4.0K    tab2_384.sdi

下面来测试一下同样数据量的rocksdb引擎和innodb引擎在数据查询方面的性能影响：

Innodb引擎表：
mysql> show create table sbtest2\G
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `k` int unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=7498230 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)
 
mysql> select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1021222 |
+----------+
1 row in set (0.96 sec)
 
Rocksdb引擎表：
mysql> show create table sbtest2\G
*************************** 1. row ***************************
       Table: sbtest2
Create Table: CREATE TABLE `sbtest2` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `k` int unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
) ENGINE=ROCKSDB AUTO_INCREMENT=8107475 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)
 
mysql> select count(*) from sbtest2;
+----------+
| count(*) |
+----------+
|  1022178 |
+----------+
1 row in set (50.38 sec)

从上面的结果看，Innodb引擎表执行count(*)操作，时间是0.96s，而rocksdb引擎表执行count(*)操作，时间是50.38s，那么是不是说rocksdb引擎在性能方面不如innodb引擎呢？如果这样想的话，就有点太武断了；

下面针对rocksdb和innodb引擎做一些性能方面的压力测试，分别从读写、只读、只写三个方面进行测试对比；

本次测试环境说明如下：

OS：CentOS 7.8 数据库版本：Percona 8.0.19 CPU & Mem：8C8G 数据量：10*1000000 压测工具：sysbench

上面测试生成的物理文件大小，如下：

innodb表单表大小240M，innodb压缩后的大小为164M，rocksdb引擎表的大小为8K
innodb表：
240M sbtest1.ibd
 
Innodb压缩后：
164M sbtest1.ibd
 
Rocksdb引擎表：
8.0K sbtest1_406.sdi

下面是具体的压测结果：

OLTP 读写测试

小结：从读写测试结果看，16个并发以下，rocksdb引擎的TPS和QPS要比innodb不压缩和innodb压缩的性能要好，超过16个并发，rocksdb引擎的TPS和QPS在逐渐下降，而innodb引擎在逐渐提高，非压缩模式下48个并发线程以后，趋于平稳，但超过48线程，rocksdb性能下降较明显。同时，在24线程以上，rocksdb的平均响应时间要比innodb高出不少。