Slow execution of many concurrent threads in RocksDB

[Nosknut]

Info about project:

- I am bench marking a jetty java web server.

- I have mad a REST API that returns a JSON formatted text file containing all the contents of a table with 100 rows and some 30 columns of different datatypes.

- I am currently trying to decide between using InnoDB or RocksDB as my storage engine.

- Using 2 identical tables, one stored in a RocksDB and one in an InnoDB. They both contain 500 000 rows and i am extracting the 100 rows based on an INT id set as primary key.

- The select statement is in a stored procedure, which is identical on both databases.

- I am tracking the time in milliseconds it takes for the querry to execute using this code:

- The REST API  uses the threadpool native to jetty.

- Each thread gets its own connection to the database from a connection pool.

- I have set the max number of threads and connections to 200 and i have confirmed that this works.

- The connection pool is managed by HikariCP

- The results shown below are from a standard MariaDB config (Program Files\MariaDB 10.3\data\my.ini) file.

Size of identical tables in different engines:

- InnoDB: 362Mib

- RocksDB: 236Mib

Code used to track the time executing a query takes:

```

 long millis = System.currentTimeMillis();

preparedQuery.executeQuery();

 long queryTime = System.currentTimeMillis() - millis;

System.out.println("Time spent to execute sql querry without storing it: " + queryTime + " ms");

```

Benchmarks:

(The following results are consistent over several tests)

The log output when using InnoDB when benchmarking 20 concurrent requests:

Throughput of the RESTAPI is now 2975 request/s

The log output when using InnoDB when benchmarking 200 concurrent requests:

Throughput of the RESTAPI is now 2911 request/s

The log output when using RocksDB when benchmarking 20 concurrent requests:

Throughput of the RESTAPI is now 810 request/s

The log output when using RocksDB when benchmarking 200 concurrent requests:

Throughput of the RESTAPI is now 750 request/s

As you can see, RocksDB seems to handle many concurrent reads very poorly, compared to InnoDB wich is consistent throughout most of the process.

I have been browsing the web for days trying to find some reasonable solutions to this, but have come up with nothing as most of the documentation i have found refers to old versions of RocksDB.

- Can someone explain why i see such an extensive preformance reduction when using RocksDB compared to InnoDB?

- Can someone refer me to a propper tutorial about tuning the config of RocksDB for concurrency and high read speeds?

[Matsunobu Yoshinori]

Hi!

> - The results shown below are from a standard MariaDB config (Program Files\MariaDB 10.3\data\my.ini) file.

Were you running perf tests on Windows? If yes, could you test on Linux (newer kernel) where RocksDB was optimized the most?

- Yoshinori

[Mark Callaghan]

Threading for my answer didn't work. Mine is at https://groups.google.com/forum/#!topic/myrocks-dev/WNNw3ZGI-wc

The databases are small, a few hundred MB, but the query response times (~2ms for InnoDB, > 40ms for MyRocks) suggest there is slow IO per query. Are there reads from storage per query and does this server use disks or SSD?

[knut....@evry.com]

This is my.ini file:

[mysqld]
datadir=C:/Program Files/MariaDB 10.3/data
port=3306
innodb_buffer_pool_size=4075M
max_connections = 10000


[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.3/lib/plugin

I did attempt to add many different configs but none of them increased performance by much.