We are are an vendor neutral and independent open source database technologies consulting, support and remote DBA services provider with several customers using MySQL GA, MySQL Enterprise, InnoDB/XtraDB, Percona Server, MariaDB, MyRocks and ClickHouse. The customers continue doing business with us for being an technology / vendor agnostic company. We spend considerable amount of time doing research on open source database technologies, Often customers ask us about the performance comparison between InnoDB and MyRocks so we decided to write this post to compare performance between InnoDB (MySQL 8.0) and MyRocks (MariaDB 10.3.7) using Sysbench 1.0.14:
Software Infrastructure we have used for this benchmarking :
MySQL 8.0 / InnoDB (no custom tuning done for performance)
[root@localhost sysbench]# cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core) [root@localhost sysbench]# mysql> select version(); -------------- select version() -------------- +-----------+ | version() | +-----------+ | 8.0.11 | +-----------+ 1 row in set (0.01 sec)
MariaDB 10.3.7 / MyRocks (no custom tuning done for performance)
[root@localhost sysbench]# cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)
[root@localhost sysbench]#
MariaDB [(none)]> select version();
+----------------+
| version() |
+----------------+
| 10.3.7-MariaDB |
+----------------+
1 row in set (0.008 sec)
MariaDB [(none)]> show engines\G;
*************************** 1. row ***************************
Engine: ROCKSDB
Support: YES
Comment: RocksDB storage engine
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MRG_MyISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: Aria
Support: YES
Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, foreign keys and encryption for tables
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: SEQUENCE
Support: YES
Comment: Generated tables filled with sequential values
Transactions: YES
XA: NO
Savepoints: YES
9 rows in set (0.000 sec)
ERROR: No query specified
Benchmarking InnoDB using Sysbench 1.0.14
Using Sysbench to create data in both InnoDB and MyRocks:
InnoDB
[root@localhost sysbench]# sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=1 --rand-type=uniform --db-driver=mysql --mysql-db=test --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password=MyPassword@2018 prepare
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 5000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
mysql> show table status like 'sbtest1'\G;
*************************** 1. row ***************************
Name: sbtest1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 4823512
Avg_row_length: 211
Data_length: 1019215872
Max_data_length: 0
Index_length: 0
Data_free: 3145728
Auto_increment: 5000001
Create_time: 2018-06-01 13:31:20
Update_time: 2018-06-01 13:31:20
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.09 sec)
ERROR:
No query specified
RocksDB
** We have explicitly mentioned “–mysql_storage_engine=rocksdb ” with Sysbench script
[root@localhost sysbench]# sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=1 --rand-type=uniform --db-driver=mysql --mysql-db=test --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password=MyPassword@2018 --mysql_storage_engine=rocksdb prepare
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 5000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[root@localhost sysbench]#
MariaDB [test]> show table status like 'sbtest1'\G;
*************************** 1. row ***************************
Name: sbtest1
Engine: ROCKSDB
Version: 10
Row_format: Fixed
Rows: 5000000
Avg_row_length: 198
Data_length: 993119895
Max_data_length: 0
Index_length: 44910893
Data_free: 0
Auto_increment: 5000001
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.009 sec)
ERROR: No query specified
Benchmarking InnoDB
[root@localhost sysbench]# sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --time=180 --rand-type=uniform --db-driver=mysql --mysql-db=test --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password=MyPassword@2018 run
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 839690
write: 0
other: 0
total: 839690
transactions: 839690 (4660.85 per sec.)
queries: 839690 (4660.85 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 180.1567s
total number of events: 839690
Latency (ms):
min: 0.05
avg: 21.43
max: 1777.14
95th percentile: 61.08
sum: 17998514.31
Threads fairness:
events (avg/stddev): 8396.9000/100.67
execution time (avg/stddev): 179.9851/0.04
Benchmarking MyRocks
[root@localhost sysbench]# sysbench /usr/share/sysbench/oltp_point_select.lua --table-size=5000000 --threads=100 --rand-type=uniform --time=180 --db-driver=mysql --mysql-db=test --mysql-socket=/var/lib/mysql/mysql.sock --mysql-user=root --mysql-password=MyPassword@2018 --mysql_storage_engine=rocksdb run
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 100
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 250051
write: 0
other: 0
total: 250051
transactions: 250051 (1383.08 per sec.)
queries: 250051 (1383.08 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 180.7783s
total number of events: 250051
Latency (ms):
min: 0.32
avg: 72.00
max: 2368.31
95th percentile: 267.41
sum: 18003094.85
Threads fairness:
events (avg/stddev): 2500.5100/129.62
execution time (avg/stddev): 180.0309/0.17
How can we summarize the benchmarking of InnoDB (MySQL 8.0) and MyRocks (MariaDB 10.3.7) ?
Conclusion
InnoDB (MySQL 8.0) performance results are impressive compared to MyRocks (MariaDB 10.3.7), We love MyRocks and confident about their advancements in performance and scalability going forward.. This benchmarking is not conducted to prove MySQL better, We love technology (not biased to any business / individuals) and passionate about conducting research on database performance.
