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\u00a0Sysbench 1.0.14:<\/p>\n

Software Infrastructure we have used for this benchmarking :\u00a0<\/strong><\/p>\n

MySQL 8.0 \/ InnoDB \u00a0(no custom tuning done for performance<\/strong>)<\/em><\/p>\n

[root@localhost sysbench]# cat \/etc\/redhat-release \r\nCentOS Linux release 7.3.1611 (Core) \r\n[root@localhost sysbench]# \r\n\r\nmysql> select version();\r\n--------------\r\nselect version()\r\n--------------\r\n\r\n+-----------+\r\n| version() |\r\n+-----------+\r\n| 8.0.11    |\r\n+-----------+\r\n1 row in set (0.01 sec)\r\n\r\n<\/pre>\n
MariaDB 10.3.7 \/ MyRocks\u00a0(no custom tuning done for performance<\/strong>)<\/em><\/p>\n
[root@localhost sysbench]# cat \/etc\/redhat-release \r\nCentOS Linux release 7.3.1611 (Core) \r\n[root@localhost sysbench]# \r\n\r\nMariaDB [(none)]> select version();\r\n+----------------+\r\n| version()      |\r\n+----------------+\r\n| 10.3.7-MariaDB |\r\n+----------------+\r\n1 row in set (0.008 sec)\r\n\r\n\r\nMariaDB [(none)]> show engines\\G;\r\n*************************** 1. row ***************************\r\n      Engine: ROCKSDB\r\n     Support: YES\r\n     Comment: RocksDB storage engine\r\nTransactions: YES\r\n          XA: YES\r\n  Savepoints: YES\r\n*************************** 2. row ***************************\r\n      Engine: CSV\r\n     Support: YES\r\n     Comment: CSV storage engine\r\nTransactions: NO\r\n          XA: NO\r\n  Savepoints: NO\r\n*************************** 3. row ***************************\r\n      Engine: MRG_MyISAM\r\n     Support: YES\r\n     Comment: Collection of identical MyISAM tables\r\nTransactions: NO\r\n          XA: NO\r\n  Savepoints: NO\r\n*************************** 4. row ***************************\r\n      Engine: MEMORY\r\n     Support: YES\r\n     Comment: Hash based, stored in memory, useful for temporary tables\r\nTransactions: NO\r\n          XA: NO\r\n  Savepoints: NO\r\n*************************** 5. row ***************************\r\n      Engine: Aria\r\n     Support: YES\r\n     Comment: Crash-safe tables with MyISAM heritage\r\nTransactions: NO\r\n          XA: NO\r\n  Savepoints: NO\r\n*************************** 6. row ***************************\r\n      Engine: MyISAM\r\n     Support: YES\r\n     Comment: MyISAM storage engine\r\nTransactions: NO\r\n          XA: NO\r\n  Savepoints: NO\r\n*************************** 7. row ***************************\r\n      Engine: PERFORMANCE_SCHEMA\r\n     Support: YES\r\n     Comment: Performance Schema\r\nTransactions: NO\r\n          XA: NO\r\n  Savepoints: NO\r\n*************************** 8. row ***************************\r\n      Engine: InnoDB\r\n     Support: DEFAULT\r\n     Comment: Supports transactions, row-level locking, foreign keys and encryption for tables\r\nTransactions: YES\r\n          XA: YES\r\n  Savepoints: YES\r\n*************************** 9. row ***************************\r\n      Engine: SEQUENCE\r\n     Support: YES\r\n     Comment: Generated tables filled with sequential values\r\nTransactions: YES\r\n          XA: NO\r\n  Savepoints: YES\r\n9 rows in set (0.000 sec)\r\n\r\nERROR: No query specified\r\n<\/pre>\n
Benchmarking \u00a0InnoDB using\u00a0Sysbench 1.0.14<\/strong><\/p>\n
Using Sysbench to create data in both InnoDB and MyRocks:<\/p>\n
InnoDB<\/em><\/strong><\/p>\n
[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\r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n\r\n\r\nmysql> show table status like 'sbtest1'\\G;\r\n*************************** 1. row ***************************\r\n           Name: sbtest1\r\n         Engine: InnoDB\r\n        Version: 10\r\n     Row_format: Dynamic\r\n           Rows: 4823512\r\n Avg_row_length: 211\r\n    Data_length: 1019215872\r\nMax_data_length: 0\r\n   Index_length: 0\r\n      Data_free: 3145728\r\n Auto_increment: 5000001\r\n    Create_time: 2018-06-01 13:31:20\r\n    Update_time: 2018-06-01 13:31:20\r\n     Check_time: NULL\r\n      Collation: utf8mb4_0900_ai_ci\r\n       Checksum: NULL\r\n Create_options: \r\n        Comment: \r\n1 row in set (0.09 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\n<\/pre>\n
RocksDB\u00a0<\/em><\/strong><\/p>\n
** We have explicitly mentioned \u00a0“–mysql_storage_engine=rocksdb<\/em> ” with Sysbench script<\/p>\n
[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\r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n[root@localhost sysbench]# \r\n\r\nMariaDB [test]> show table status like 'sbtest1'\\G;\r\n*************************** 1. row ***************************\r\n            Name: sbtest1\r\n          Engine: ROCKSDB\r\n         Version: 10\r\n      Row_format: Fixed\r\n            Rows: 5000000\r\n  Avg_row_length: 198\r\n     Data_length: 993119895\r\n Max_data_length: 0\r\n    Index_length: 44910893\r\n       Data_free: 0\r\n  Auto_increment: 5000001\r\n     Create_time: NULL\r\n     Update_time: NULL\r\n      Check_time: NULL\r\n       Collation: latin1_swedish_ci\r\n        Checksum: NULL\r\n  Create_options: \r\n         Comment: \r\nMax_index_length: 0\r\n       Temporary: N\r\n1 row in set (0.009 sec)\r\n\r\nERROR: No query specified\r\n\r\n<\/pre>\n
Benchmarking InnoDB\u00a0<\/em><\/strong><\/p>\n
[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 \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n    queries performed:\r\n        read:                            839690\r\n        write:                           0\r\n        other:                           0\r\n        total:                           839690\r\n    transactions:                        839690 (4660.85 per sec.)\r\n    queries:                             839690 (4660.85 per sec.)\r\n    ignored errors:                      0      (0.00 per sec.)\r\n    reconnects:                          0      (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n    total time:                          180.1567s\r\n    total number of events:              839690\r\n\r\nLatency (ms):\r\n         min:                                    0.05\r\n         avg:                                   21.43\r\n         max:                                 1777.14\r\n         95th percentile:                       61.08\r\n         sum:                             17998514.31\r\n\r\nThreads fairness:\r\n    events (avg\/stddev):           8396.9000\/100.67\r\n    execution time (avg\/stddev):   179.9851\/0.04\r\n<\/pre>\n
Benchmarking MyRocks\u00a0<\/em><\/strong><\/p>\n
[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  \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n    queries performed:\r\n        read:                            250051\r\n        write:                           0\r\n        other:                           0\r\n        total:                           250051\r\n    transactions:                        250051 (1383.08 per sec.)\r\n    queries:                             250051 (1383.08 per sec.)\r\n    ignored errors:                      0      (0.00 per sec.)\r\n    reconnects:                          0      (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n    total time:                          180.7783s\r\n    total number of events:              250051\r\n\r\nLatency (ms):\r\n         min:                                    0.32\r\n         avg:                                   72.00\r\n         max:                                 2368.31\r\n         95th percentile:                      267.41\r\n         sum:                             18003094.85\r\n\r\nThreads fairness:\r\n    events (avg\/stddev):           2500.5100\/129.62\r\n    execution time (avg\/stddev):   180.0309\/0.17\r\n<\/pre>\n
How can we summarize the benchmarking of InnoDB (MySQL 8.0) and MyRocks (MariaDB 10.3.7) ?\u00a0<\/strong><\/p>\n
<\/p>\n
 <\/p>\n
Conclusion\u00a0<\/strong><\/p>\n
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 \/\u00a0individuals)<\/strong>\u00a0and passionate about conducting research on database performance.\u00a0<\/strong><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/a><\/p>\n<\/div>","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[131,2],"tags":[134,133,132,106],"yoast_head":"\n