It seems MyRocks is going to become a hot topic in April 2017. Previously (
here and
there) I tried to compare its performance and scalability vs InnoDB from MySQL 5.7.17 using test case from famous bug
#68079. It's an interesting case that took a lot of efforts from Oracle to make InnoDB scale properly, and InnoDB (on my QuadCore box at least, others reported different results on other hardware in comments) still outperformed MyRocks. But maybe it's corner case that is not a big deal in general?
Earlier this month I decided to give MyRocks another chance and try it with "industry-standard" benchmarks, like those provided by
sysbench tool. At the same time, I studied the impact of
adaptive hash indexing (AHI) on InnoDB (for the reason i am not yet ready to share), along the lines of this
great post by Peter Zaitsev. The study is not yet complete, and I am not yet sure that it makes sense to continue doing it on my ages old QuadCore box with Fedora 25, but in the process I've got one interesting and repeatable result that I'd like to share in any case.
For that study I decided to use recent
sysbench 1.1.x, so I had to build it from source to begin with. I did the following:
[openxs@fc23 git]$ git clone https://github.com/akopytov/sysbench.git
but then during
./configure run I've got a small problem:
...
checking for pkg-config... yes
checking for xxd... no
configure: error: "xxd is required to build sysbench (usually comes with the vim package)"
So, I had to install
vim package:
[openxs@fc23 sysbench]$ sudo yum install vim
...
Installed:
gpm-libs.x86_64 1.20.7-9.fc24 vim-common.x86_64 2:8.0.386-1.fc25
vim-enhanced.x86_64 2:8.0.386-1.fc25 vim-filesystem.x86_64 2:8.0.386-1.fc25
Complete!
and then build and installation process (with all defaults and MariaDB software provided by Fedora present) completed without any problem, and I've ended up with nice new
sysbench version:
[openxs@fc23 sysbench]$ /usr/local/bin/sysbench --version
sysbench 1.1.0-2343e4b
[openxs@fc23 sysbench]$ ls /usr/local/share/sysbench/
bulk_insert.lua oltp_point_select.lua oltp_update_non_index.lua tests
oltp_common.lua oltp_read_only.lua oltp_write_only.lua
oltp_delete.lua oltp_read_write.lua select_random_points.lua
oltp_insert.lua oltp_update_index.lua select_random_ranges.lua
As I use all default settings for both MyRocks and InnoDB, I decided to start testing with the
oltp_point_select.lua simplest test and table size that does NOT fit into the default 128M of buffer pool in InnoDB case:
[openxs@fc23 sysbench]$ sysbench /usr/local/share/sysbench/oltp_point_select.lua --report-interval=1 --oltp-table-size=1000000 --max-time=0 --oltp-read-only=off --max-requests=0 --num-threads=1 --rand-type=uniform --db-driver=mysql --mysql-db=test --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password= prepare
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
invalid option: --oltp-table-size=1000000
Note that good old command lines copied from older
sysbench versions verbatim may NOT work any more in 1.1.x. Some options changed, now the names are shorter:
[openxs@fc23 sysbench]$ sysbench /usr/local/share/sysbench/oltp_point_select.lua help
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
oltp_point_select.lua options:
--distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1]
--sum_ranges=N Number of SELECT SUM() queries per transaction [1]
--skip_trx[=on|off] Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
--secondary[=on|off] Use a secondary index in place of the PRIMARY KEY [off]
--create_secondary[=on|off] Create a secondary index in addition to the PRIMARY KEY [on]
--index_updates=N Number of UPDATE index queries per transaction [1]
--range_size=N Range size for range SELECT queries [100]
--auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
--delete_inserts=N Number of DELETE/INSERT combination per transaction [1]
--tables=N Number of tables [1]
--mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
--non_index_updates=N Number of UPDATE non-index queries per transaction [1]
--table_size=N Number of rows per table [10000]
--pgsql_variant=STRING Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
--simple_ranges=N Number of simple range SELECT queries per transaction [1]
--order_ranges=N Number of SELECT ORDER BY queries per transaction [1]
--range_selects[=on|off] Enable/disable all range SELECT queries [on]
--point_selects=N Number of point SELECT queries per transaction [10]
I've ended up creating the table like this for InnoDB case:
[openxs@fc23 sysbench]$ sysbench /usr/local/share/sysbench/oltp_point_select.lua --report-interval=1 --table-size=1000000 --num-threads=1 --rand-type=uniform --db-driver=mysql --mysql-db=test --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password= prepare
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'
to end up with the following table:
mysql> show table status like 'sbtest1'\G
*************************** 1. row ***************************
Name: sbtest1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 986400
Avg_row_length: 228
Data_length: 225132544
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 1000001
Create_time: 2017-03-02 16:18:57
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
For MyRocks I also had to specify storage engine explicitly:
[openxs@fc23 fb56]$ sysbench /usr/local/share/sysbench/oltp_point_select.lua --table-size=1000000 --threads=1 --rand-type=uniform --db-driver=mysql --mysql-db=test --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password= --mysql_storage_engine=rocksdb prepare
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
to end up with the following table:
mysql> show table status like 'sbtest1'\G
*************************** 1. row ***************************
Name: sbtest1
Engine: ROCKSDB
Version: 10
Row_format: Fixed
Rows: 1000000
Avg_row_length: 198
Data_length: 198545349
Max_data_length: 0
Index_length: 16009534
Data_free: 0
Auto_increment: 1000001
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
Note that in case of InnoDB I've used MySQL 5.7.17 from Oracle, and MyRocks was built from this commit using my usual
cmake options:
[openxs@fc23 mysql-5.6]$ git log -1
commit 01c386be8b02e6469b934c063aefdf8403844d99
Author: Herman Lee <herman@fb.com>
Date: Wed Mar 1 18:14:25 2017 -0800
[openxs@fc23 mysql-5.6]$ cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DWITH_SSL=system -DWITH_ZLIB=bundled -DMYSQL_MAINTAINER_MODE=0 -DWITH_EMBEDDED_SERVER=OFF -DENABLED_LOCAL_INFILE=1 -DENABLE_DTRACE=0 -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/fb56
I've run the tests for InnoDB with adaptive hash indexing set to ON (by default) and OFF (changed at run time), and then for MyRocks, using 1, 2, 4, 8, 16, 32 and 64 (all cases but InnoDB with AHI ON) concurrent threads, with
sysbench command line like this to run the test for 60 seconds (note new options syntax of
sysbench 1.x:
--time,
--threads etc):
[openxs@fc23 fb56]$ sysbench /usr/local/share/sysbench/oltp_point_select.lua --table-size=1000000 --time=60 --threads=1 --rand-type=uniform --db-driver=mysql --mysql-db=test --mysql-socket=/tmp/mysql.sock --mysql-user=root run
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 821511
write: 0
other: 0
total: 821511
transactions: 821511 (13691.77 per sec.)
queries: 821511 (13691.77 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0003s
total number of events: 821511
Latency (ms):
min: 0.06
avg: 0.07
max: 1.11
95th percentile: 0.08
sum: 59537.46
Threads fairness:
events (avg/stddev): 821511.0000/0.00
execution time (avg/stddev): 59.5375/0.00
and then summarized the results into the following chart:
One day I'll share raw results, as a gist or somehow else, but for now let me summarize my findings as of March 3, 2017:
- MyRocks really rocks with this oltp_point_select.lua --table-size=1000000 test of sysbench 1.1.0! With default settings of server variables it outperformed InnoDB from MySQL 5.7.17 at all number of threads tested, from 1 to 64, and proved good scalability on up to 64 threads on my QuadCore box. I've got more than 45K QPS starting from 4 threads.
- InnoDB with disabled AHI is somewhat faster for this test than with enabled AHI, highest result was almost 44K QPS with AHI OFF on 4 threads.
- It seems my QuadCore is not relevant any more for serious benchmarks, as for quite a some time people use 8 to 18 cores per socket etc and start with 200K QPS with 8 threads already.
