MariaDB 10.3 MyRocks vs InnoDB 基本的基准测试，mariadbmyrocks

MariaDB 10.3 MyRocks vs InnoDB 基本的基准测试，mariadbmyrocks

在Facebook官方的发布的比对中是采用的linkbench来比对InnoDB和MyRocks，其中提到在SSD硬盘存储磁盘空间降低至少2倍，

但是写入速度降低了10倍，对于需要大量存储数据的公司来说对存储的要求更持久一些。

Benchmark tests against 3 different instances – MyRocks (compressed), InnoDB (uncompressed), and InnoDB (compressed, 8 KB page size):

MyRocks was 2x smaller than InnoDB (compressed) and 3.5x smaller than InnoDB (uncompressed).
MyRocks also has a 10x lower storage write rate compared to InnoDB.
With SSD database storage, this means less space used and a higher endurance of the storage over time.

压测的环境配置：

MyRocks和InnoDB的内存配置一样，均为1G，VMware的CPU配置为2，内存为3G，数据均存储于SSD硬盘。

压测的数据单表1000w条数据，压测120秒，间隔为10秒，并发50，采用sysbench 1.0.15版本压测 读写：

1.写入数据的时间：

InnoDB：
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest1'...
Inserting 10000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

real    2m29.167s
user    0m12.528s
sys     0m0.780s

MyRocks：
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest1'...
Inserting 10000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

real    4m40.257s
user    0m11.659s
sys     0m1.395s

结论：时间上InnoDB可以更块的写入1000万条记录。

2.磁盘占用：

InnoDB：
# du -sh sbtest/
2.4G    sbtest/

MyRocks:
# du -sh \#rocksdb/
2.2G    #rocksdb/

结论：InnoDB 没有开启压缩，MariaDB下的默认压缩不确定，不过可以看到MyRocks存储引擎占用磁盘空间确实比InnoDB小一些，可能是我的数据量不够大，比对不明显，没有出现官方的倍数降低。

3.CPU IO 内存：

这三部分由于没有搭建监控暂时无法有详细的截图和数据，不过在压测的过程中使用top监控了。

MyRocks的内存利用和IO 要比InnoDB利用的充分。尤其在MyRocks压测时IO基本是满负荷运行，

而InnoDB的IO利用率则30%左右。

4.压测的TPS和QPS数据比对：

InnoDB：
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 50
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 50 tps: 346.28 qps: 6990.73 (r/w/o: 4907.73/1385.43/697.56) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 357.60 qps: 7155.64 (r/w/o: 5009.73/1430.71/715.20) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 371.91 qps: 7435.51 (r/w/o: 5202.95/1488.74/743.82) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 360.59 qps: 7213.65 (r/w/o: 5051.49/1440.97/721.18) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 346.41 qps: 6926.96 (r/w/o: 4848.61/1385.53/692.82) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 371.40 qps: 7428.46 (r/w/o: 5200.27/1485.39/742.80) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 373.90 qps: 7479.30 (r/w/o: 5235.50/1496.00/747.80) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 372.68 qps: 7448.58 (r/w/o: 5212.98/1490.34/745.27) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 373.22 qps: 7466.81 (r/w/o: 5227.41/1492.86/746.53) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 375.38 qps: 7510.09 (r/w/o: 5257.39/1501.94/750.77) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 377.41 qps: 7542.78 (r/w/o: 5278.69/1509.26/754.83) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 376.42 qps: 7538.66 (r/w/o: 5276.95/1508.87/752.84) lat (ms,95%): 173.58 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            617190
        write:                           176340
        other:                           88170
        total:                           881700
    transactions:                        44085  (367.12 per sec.)
    queries:                             881700 (7342.48 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0809s
    total number of events:              44085

Latency (ms):
         min:                                   25.96
         avg:                                  136.14
         max:                                  562.15
         95th percentile:                      183.21
         sum:                              6001865.42

Threads fairness:
    events (avg/stddev):           881.7000/35.76
    execution time (avg/stddev):   120.0373/0.02


MyRocks：
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 50
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 50 tps: 410.99 qps: 8276.79 (r/w/o: 5802.74/1647.06/826.98) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 459.01 qps: 9186.60 (r/w/o: 6431.74/1836.84/918.02) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 483.41 qps: 9664.73 (r/w/o: 6764.19/1933.73/966.81) lat (ms,95%): 134.90 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 508.00 qps: 10167.68 (r/w/o: 7121.46/2030.22/1016.01) lat (ms,95%): 127.81 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 516.00 qps: 10327.58 (r/w/o: 7225.59/2070.00/1032.00) lat (ms,95%): 127.81 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 543.10 qps: 10833.76 (r/w/o: 7582.94/2164.71/1086.11) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 522.89 qps: 10473.58 (r/w/o: 7334.82/2092.88/1045.89) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 540.31 qps: 10807.79 (r/w/o: 7563.84/2163.34/1080.62) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 526.30 qps: 10517.87 (r/w/o: 7360.68/2104.59/1052.60) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 537.30 qps: 10754.61 (r/w/o: 7528.54/2151.48/1074.59) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 529.69 qps: 10596.61 (r/w/o: 7421.47/2115.76/1059.38) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 524.81 qps: 10496.61 (r/w/o: 7346.75/2100.44/1049.42) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            854966
        write:                           244276
        other:                           122138
        total:                           1221380
    transactions:                        61069  (508.65 per sec.)
    queries:                             1221380 (10173.08 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0588s
    total number of events:              61069

Latency (ms):
         min:                                    3.80
         avg:                                   98.28
         max:                                  346.58
         95th percentile:                      130.13
         sum:                              6001571.64

Threads fairness:
    events (avg/stddev):           1221.3800/7.83
    execution time (avg/stddev):   120.0314/0.02

结论：可以看到在配置相同的情况下MyRocks的TPS和QPS，延迟均优于InnoDB。

比对的数据：

               TPS      QPS     Latency_min Latency_avg  Latency_max Latency_95th
	InnoDB  367.12   7342.48   25.96      136.14       562.15      183.21
	MyRocks 508.65   10173.08  3.80       98.28        346.58      130.13

结论：MyRocks是Facebook大力推广的产品，在某些场景下MyRocks确实优于InnoDB。当然Facebook也说了不是取代InnoDB的，而是在某些场景下替换InnoDB。

这里的测试不够充分，多并发下没有完整的数据比对。更加专业的测试可以参考Percona的性能测试，不过这里是使用的TPC-C：

https://en.wikipedia.org/wiki/MyRocks
https://www.percona.com/blog/2018/04/30/a-look-at-myrocks-performance/