SQLite threading optimization (SQLITE_CONFIG_MULTITHREAD)

[tawmaz]

We are currently using threading mode SQLITE_CONFIG_SERIALIZED which enables all SQLite mutexes and also using a mutex m_connection_mutex for each access. This will severely limit multi-thread performance as only one thread can access the database at a time. We should switch to SQLITE_CONFIG_MULTITHREAD, make sure each thread maintains it's own database connection, and not use our own mutex.

https://www.sqlite.org/threadsafe.html

[andyoknen]

@tawmaz

At the moment , the connection scheme is as follows:

• The main thread has a connection to the main database for reading and writing, and all work is done in one thread within the cs_main mutex.
• Each RPC thread has its own read-only connection to the database and cannot process multiple requests at the same time.

Also, the connection to the database itself has an internal mutex inside beginTransaction for situations if developers accidentally use a single connection in different threads. When used correctly, the internal mutex should not affect performance.

I will also note that I made attempts to switch to SQLITE_CONFIG_MULTITHREAD and did not notice a significant performance gain, but did not do tests.

[tawmaz]

Some additional tuning will likely be required to see optimal benefit. For example, if threads are not blocking each other with database accesses, we should be able to do the same work with fewer number of threads, which frees up memory as well as OS resources needed to pause and resume threads.

[andyoknen]

@tawmaz With these changes https://github.com/pocketnetteam/pocketnet.core/pull/171, I think we could see if SQLITE_CONFIG_MULTITHREAD affects performance.

[tawmaz]

I am not sure if it is related, but I am seeing very slow sync performance on one of my nodes. From bench logging it appears SQLite is very slow. The system has a fast NVMe SSD drive. Here are some of the samples from ProcessBlock:

2022-04-04T04:00:34Z CreateNewBlock() packages: 376.61ms (0 packages, 0 updated descendants), validity: 0.07ms (total 376.68ms)
2022-04-04T04:00:34Z --- ProcessBlock: 9e4cd5963882677732dba9434890778a9b7889f5c345ccdd315b9637af5e2595
2022-04-04T04:00:34Z  -- Lock cs_main: 0.00ms (0.000ms/txin)
2022-04-04T04:00:34Z  -- Check block: 0.34ms (0.068ms/txin)
2022-04-04T04:00:34Z  -- Social check block: 0.16ms (0.031ms/txin)
2022-04-04T04:00:34Z  -- Accept LeveDb: 0.18ms (0.037ms/txin)
2022-04-04T04:00:34Z  -- Accept SQLite: 283.81ms (56.762ms/txin)
2022-04-04T04:00:34Z --- ProcessBlock: 87d73d43567cf3bd89fc8c971684e8b4a723940c7eb7f8b1cb1160a8aff2050d
2022-04-04T04:00:34Z  -- Lock cs_main: 0.00ms (0.000ms/txin)
2022-04-04T04:00:34Z  -- Check block: 0.17ms (0.003ms/txin)
2022-04-04T04:00:34Z  -- Social check block: 0.51ms (0.008ms/txin)
2022-04-04T04:00:34Z  -- Accept LeveDb: 0.21ms (0.003ms/txin)
2022-04-04T04:00:46Z  -- Accept SQLite: 12198.45ms (196.749ms/txin)

[andyoknen]

I am not sure if it is related, but I am seeing very slow sync performance on one of my nodes. From bench logging it appears SQLite is very slow. The system has a fast NVMe SSD drive. Here are some of the samples from ProcessBlock:

Actual version?