Building database infrastructure for benchmarking\u00a0<\/strong><\/p>\nWe have used “oltp_common.lua” script to create database infrastructure for benchmarking InnoDB and MyRocks, We have not tuned both InnoDB and MyRocks variables for performance. The script below creates database for benchmarking:<\/p>\n
[root@localhost sysbench]# \r\n[root@localhost sysbench]# sysbench bulk_insert.lua --threads=1 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --mysql-storage-engine=rocksdb prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nCreating table 'sbtest1'...\r\n[root@localhost sysbench]# \r\n<\/pre>\nBenchmarking bulk INSERT performance on InnoDB and MyRocks<\/strong><\/span><\/p>\nBenchmarking OLTP insert on InnoDB using “oltp_insert.lua”\u00a0<\/em><\/p>\nScript to create data (5M records) for benchmarking OLTP INSERT performance:<\/p>\n
[root@localhost sysbench]# sysbench oltp_insert.lua --threads=100 --time=180 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\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<\/pre>\nMariaDB [test]> 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: 5404891\r\n Avg_row_length: 265\r\n Data_length: 1433403392\r\n Max_data_length: 0\r\n Index_length: 157024256\r\n Data_free: 4194304\r\n Auto_increment: 5696281\r\n Create_time: 2018-06-03 12:48:12\r\n Update_time: 2018-06-03 12:52:03\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.000 sec)\r\n\r\nERROR: No query specified\r\n\r\nMariaDB [test]> \r\n<\/pre>\nScript for benchmarking InnoDB OLTP INSERT performance:<\/p>\n
[root@localhost sysbench]# sysbench oltp_insert.lua --threads=100 --time=180 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 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: 0\r\n write: 696280\r\n other: 0\r\n total: 696280\r\n transactions: 696280 (3866.32 per sec.)\r\n queries: 696280 (3866.32 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.0872s\r\n total number of events: 696280\r\n\r\nLatency (ms):\r\n min: 0.62\r\n avg: 25.85\r\n max: 358.63\r\n 95th percentile: 81.48\r\n sum: 17998504.11\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 6962.8000\/57.61\r\n execution time (avg\/stddev): 179.9850\/0.04\r\n<\/pre>\nWhat we look for seriously in this benchmarking is QPS (queries per seconds) , In the test above it is 3866 QPS<\/p>\n
Benchmarking MyRocks INSERT performance using Sysbench 1.0.14:\u00a0<\/em><\/p>\nThe steps are same, except for explicitly mentioning the storage engine RocksDB in sysbench scripts: “–mysql-storage-engine=rocksdb<\/em>”<\/p>\nScript for benchmarking OLTP insert on MyRocks using “oltp_insert.lua” \u00a0:\u00a0<\/em><\/p>\n[root@localhost sysbench]# sysbench oltp_insert.lua --threads=100 --time=180 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --mysql-storage-engine=rocksdb prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\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<\/pre>\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: 992949774\r\n Max_data_length: 0\r\n Index_length: 38739880\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.007 sec)\r\n\r\nERROR: No query specified\r\n\r\nMariaDB [test]> \r\n<\/pre>\nScript for benchmarking RocksDB OLTP INSERT performance:<\/em><\/p>\n[root@localhost sysbench]# sysbench oltp_insert.lua --threads=100 --time=180 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --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: 0\r\n write: 123049\r\n other: 0\r\n total: 123049\r\n transactions: 123049 (683.37 per sec.)\r\n queries: 123049 (683.37 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.0618s\r\n total number of events: 123049\r\n\r\nLatency (ms):\r\n min: 1.03\r\n avg: 146.30\r\n max: 1675.09\r\n 95th percentile: 308.84\r\n sum: 18001689.44\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 1230.4900\/26.26\r\n execution time (avg\/stddev): 180.0169\/0.02\r\n\r\n[root@localhost sysbench]# \r\n<\/pre>\nResult: OLTP INSERT performance for RocksDB is 683 QPS (queries per second)<\/em><\/p>\nThe graphical representation of more interesting performance benchmarking results on multiple bulk INSERT transactions scenarios:<\/strong><\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/06\/OLTP-INSERT-PERFORMANCE-INNODB-MYROCKS.png\")
<\/p>\n
Conclusion\u00a0<\/strong><\/p>\nOLTP INSERT performance benchmarking clearly proves InnoDB is almost 6X faster than MyRocks. So MyRocks is not recommended for bulk INSERT transactions !<\/p>\n
OLTP WRITE only transactions performance benchmarking for InnoDB and MyRocks<\/span>\u00a0<\/strong><\/span><\/p>\nOLTP WRITE only performance benchmarking for InnoDB using “oltp_write_only.lua”<\/em><\/p>\nThe steps remains same for “oltp_write_only.lua” script like “oltp_insert.lua” mentioned above, So we are directly copying the results of benchmarking without explaining details again:<\/p>\n
[root@localhost sysbench]# sysbench oltp_write_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_write_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 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: 0\r\n write: 14529\r\n other: 7265\r\n total: 21794\r\n transactions: 3632 (355.03 per sec.)\r\n queries: 21794 (2130.37 per sec.)\r\n ignored errors: 1 (0.10 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 10.2285s\r\n total number of events: 3632\r\n\r\nLatency (ms):\r\n min: 1.88\r\n avg: 277.61\r\n max: 2701.56\r\n 95th percentile: 977.74\r\n sum: 1008267.12\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 36.3200\/4.36\r\n execution time (avg\/stddev): 10.0827\/0.09\r\n<\/pre>\nResult : 2130 QPS (queries per second)<\/strong><\/p>\nOLTP WRITE only performance benchmarking for RocksDB using “oltp_write_only.lua”<\/em><\/p>\n[root@localhost sysbench]# sysbench oltp_write_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --mysql-storage-engine=rocksdb prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_write_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --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: 0\r\n write: 25191\r\n other: 12596\r\n total: 37787\r\n transactions: 6296 (625.73 per sec.)\r\n queries: 37787 (3755.49 per sec.)\r\n ignored errors: 4 (0.40 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 10.0603s\r\n total number of events: 6296\r\n\r\nLatency (ms):\r\n min: 1.39\r\n avg: 159.29\r\n max: 3620.58\r\n 95th percentile: 846.57\r\n sum: 1002895.84\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 62.9600\/25.26\r\n execution time (avg\/stddev): 10.0290\/0.02\r\n<\/pre>\nResult : 3755 QPS (queries per second)<\/strong><\/p>\nThe graphical representation of more interesting performance benchmarking results on multiple WRITE only transactions scenarios:<\/strong><\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/06\/InnoDB_MyRocks_Write_only_OLTP.png\")
<\/p>\n
Conclusion\u00a0<\/span><\/strong><\/p>\nMyRocks OLTP write only performance is almost 2X compared to InnoDB, So MyRocks is definitely an preferred option for high performance and scalable writes, Thanks to LSM data structure \u00a0!<\/p>\n
OLTP READ-WRITE performance benchmarking using Sysbench lua script “oltp_read_write.lua”<\/strong><\/span><\/p>\nThe lua scripts below create data for OLTP READ-WRITE performance benchmarking:<\/p>\n
Benchmarking OLTP READ-WRITE performance for InnoDB:<\/em><\/p>\n[root@localhost sysbench]# sysbench oltp_read_write.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_read_write.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 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: 15652\r\n write: 4472\r\n other: 2236\r\n total: 22360\r\n transactions: 1118 (105.96 per sec.)\r\n queries: 22360 (2119.20 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: 10.5301s\r\n total number of events: 1118\r\n\r\nLatency (ms):\r\n min: 15.71\r\n avg: 922.19\r\n max: 4973.09\r\n 95th percentile: 2009.23\r\n sum: 1031006.57\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 11.1800\/1.68\r\n execution time (avg\/stddev): 10.3101\/0.13\r\n\r\n[root@localhost sysbench]# \r\n<\/pre>\nResult: 2119 QPS (queries per second)\u00a0<\/strong><\/p>\nBenchmarking OLTP READ-WRITE performance for RocksDB:<\/em><\/p>\n[root@localhost sysbench]# sysbench oltp_read_write.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --mysql-storage-engine=rocksdb prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_read_write.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --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: 26964\r\n write: 7628\r\n other: 3827\r\n total: 38419\r\n transactions: 1901 (182.46 per sec.)\r\n queries: 38419 (3687.46 per sec.)\r\n ignored errors: 25 (2.40 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 10.4153s\r\n total number of events: 1901\r\n\r\nLatency (ms):\r\n min: 11.23\r\n avg: 540.87\r\n max: 3480.91\r\n 95th percentile: 1352.03\r\n sum: 1028196.02\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 19.0100\/2.25\r\n execution time (avg\/stddev): 10.2820\/0.10\r\n\r\n[root@localhost sysbench]# \r\n<\/pre>\nResult: 3687 QPS (queries per second)\u00a0<\/strong><\/p>\nThe graphical representation of more interesting performance benchmarking results on multiple READ-WRITE transactions scenarios:<\/strong><\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/06\/OLTP-READ-WRITE-PERF.png\")
<\/p>\n
Conclusion \u00a0<\/strong><\/p>\nOLTP READ-WRITE I\/O operations benchmarking results confirm MyRocks is the definite choice, May be these result vary more if we invest in tuning the InnoDB and MyRocks for performance.<\/p>\n
Benchmarking OLTP READ ONLY operations using Sysbench oltp_read_only.lua script\u00a0<\/strong><\/span><\/p>\nOLTP READ ONLY transactions performance\u00a0<\/i>benchmarking for InnoDB:<\/i><\/span><\/span><\/p>\n[root@localhost sysbench]# sysbench oltp_read_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_read_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 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: 51072\r\n write: 0\r\n other: 7296\r\n total: 58368\r\n transactions: 3648 (352.59 per sec.)\r\n queries: 58368 (5641.45 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: 10.3436s\r\n total number of events: 3648\r\n\r\nLatency (ms):\r\n min: 1.10\r\n avg: 274.41\r\n max: 2863.46\r\n 95th percentile: 733.00\r\n sum: 1001047.53\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 36.4800\/6.57\r\n execution time (avg\/stddev): 10.0105\/0.05\r\n<\/pre>\nResult: 5641 QPS (queries per second)<\/strong><\/p>\nOLTP READ ONLY transactions performance\u00a0<\/i>benchmarking for RocksDB:<\/i><\/span><\/span><\/p>\n[root@localhost sysbench]# sysbench oltp_read_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --mysql-storage-engine=rocksdb prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_read_only.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --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: 26362\r\n write: 0\r\n other: 3766\r\n total: 30128\r\n transactions: 1883 (182.77 per sec.)\r\n queries: 30128 (2924.32 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: 10.2983s\r\n total number of events: 1883\r\n\r\nLatency (ms):\r\n min: 6.20\r\n avg: 540.16\r\n max: 5258.04\r\n 95th percentile: 2045.74\r\n sum: 1017118.10\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 18.8300\/2.29\r\n execution time (avg\/stddev): 10.1712\/0.08\r\n\r\n<\/pre>\nResult: 2924 QPS (queries per second)<\/strong><\/p>\nThe graphical representation of more interesting performance benchmarking results on multiple READ only transactions scenarios:<\/strong><\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/06\/OLTP-READ-ONLY-INNODB-MYROCKS.png\")
<\/p>\n
Conclusion<\/strong><\/p>\nInnoDB works great if it is OLTP READ only transactions, So we can continue recommending customers to use InnoDB for read intensive database operations.<\/p>\n
Benchmarking OLTP DELETE ONLY operations using Sysbench oltp_delete.lua script\u00a0<\/strong><\/span><\/p>\nOLTP DELETE ONLY transactions performance\u00a0<\/i>benchmarking for InnoDB:<\/i><\/span><\/span><\/p>\n[root@localhost sysbench]# sysbench oltp_delete.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_delete.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 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: 0\r\n write: 21659\r\n other: 4464\r\n total: 26123\r\n transactions: 26123 (2521.93 per sec.)\r\n queries: 26123 (2521.93 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: 10.3568s\r\n total number of events: 26123\r\n\r\nLatency (ms):\r\n min: 0.04\r\n avg: 38.08\r\n max: 2679.06\r\n 95th percentile: 116.80\r\n sum: 994654.43\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 261.2300\/46.27\r\n execution time (avg\/stddev): 9.9465\/0.18\r\n\r\n[root@localhost sysbench]# \r\n<\/pre>\nResult: 2521 QPS (queries per second)<\/strong><\/p>\nOLTP DELETE ONLY transactions performance\u00a0<\/i>benchmarking for RocksDB:<\/i><\/span><\/span><\/p>\n[root@localhost sysbench]# sysbench oltp_delete.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --mysql-storage-engine=rocksdb prepare \r\nsysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 5000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n[root@localhost sysbench]# sysbench oltp_delete.lua --threads=100 --table-size=5000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/lib\/mysql\/mysql.sock --mysql-user=root --mysql-password=MyPassword2018 --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: 0\r\n write: 7094\r\n other: 421\r\n total: 7515\r\n transactions: 7515 (746.81 per sec.)\r\n queries: 7515 (746.81 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: 10.0613s\r\n total number of events: 7515\r\n\r\nLatency (ms):\r\n min: 0.43\r\n avg: 133.42\r\n max: 1666.25\r\n 95th percentile: 502.20\r\n sum: 1002663.49\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 75.1500\/15.50\r\n execution time (avg\/stddev): 10.0266\/0.02\r\n<\/pre>\nResult: 746 QPS (queries per second)<\/strong><\/p>\nThe graphical representation of more interesting performance benchmarking results on multiple DELETE only transactions scenarios:<\/strong><\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/06\/InnoDB-MyRocks-QPS.png\")
<\/p>\n
Conclusion\u00a0<\/strong><\/p>\nInnoDB is almost 3X faster than MyRocks in OLTP DELETE operations.<\/p>\n