What is best practice for multi-threaded in-memory database?

xiwuping · 2018-08-24 23:10:19

Hi

I have a scientific multi-threaded application, using TSQLRestServerFullMemory. This application has high-frequency read-write (> 100) to the in-memory database every millisecond. Each read-write just involves a couple of records, but up to 8 threads may do the read-write at the same time.

It appears to me - TSQLRestServerFullMemory does NOT handle this situation well. The scientific computation becomes very slow and degrades to the same performance as a single-threaded application.

Is there any tricks in using TSQLRestServerFullMemory in a multi-threaded context? Or, should I use TSQLRestServerFullMemory, at all?

Thank you.
Wux

edwinsn · 2018-08-25 06:15:48

3 thoughts:
- Try ScaleMM and benchmark to see if there are any improvements.
- Try TSQLRestServerDB with in-memory sqlite db (use ":memory:" as the db name).
- Use TSQLDataBase directly with in-memory sqlite db.

Out of curiosity, what kind of program is it?

ab · 2018-08-25 08:36:07

Each TSQLRestServerFullMemory has an internal lock (TSynCriticalSection) for its ORM access, just like any other TSQLRest.
See https://synopse.info/files/html/Synopse … ml#TITL_25
So you are right, basically all requests are serialized. But will be safely executed from the multiple threads...

TSQLRestServerFullMemory is pretty basic, and expects per-ID or per-indexed fields queries. From those keys, it could easily read-write at very high pace.
What you request is 100 request every ms, i.e. 10 micro sec time. For per-ID or per-indexed fields, it should be fine.
But especially if your requests are more complex, the data grows, TSQLRestServerFullMemory may not be fast enough, since it is a basic scheme.

I doubt TSQLDatabase will be faster than 10 micro sec per query (usually SQLite3 is most of the time in the 5 us -> 300 us range, even in :memory:).
You may try it.

What you could do is
1. still use TSQLRestServerFullMemory but create several tables for your application, and access directly each TSQLRestStorageMemory instance (bypassing the main TSQLRestServerFullMemory lock)
2. use a low-level array/record-based in-memory storage (based e.g. on TDynArray/TDynHashed/TSynDictionary).
3. mix the two, using a front-end TSynDictionary cache for most of the reads (TSynDictionary has also a built-in timeout feature, so it is perfect for a cache), then TSQLRestSeverFullMemory for the actual store.
4. change your architecture to a LMAX-like message based ring buffer (see below)

What we do for high-performance process, is usually use a TSynDictionary: it is very fast, will store records so in a very memory-friendly way, and has a lot of features, including JSON or binary serialization.
Of course, it is thread-safe, since it has its own lock too, but since it may be faster than TSQLRest due to its simpler design, it could be a solution.

Last but not least, you may need to refactor your architecture.
For high-performance, we implemented successfully some pattern as used in finance, see e.g. https://martinfowler.com/articles/lmax.html
The TSynQueue class could be used to implement the ring.

xiwuping · 2018-08-25 15:32:49

edwinsn wrote:

3 thoughts:
- Try ScaleMM and benchmark to see if there are any improvements.
- Try TSQLRestServerDB with in-memory sqlite db (use ":memory:" as the db name).
- Use TSQLDataBase directly with in-memory sqlite db.
Out of curiosity, what kind of program is it?

Hi edwinsn - it is a simulation application for academic use.

xiwuping · 2018-08-25 15:36:33

Thank you ab, for this extensive and insightful advice.

I looked into TSynQueue, seems it still relies on some "locking" among threads? Does mORMot provide some "lock-free" circular buffer implementation already? Or you are sure TSynQueue will be good to implement similar pattern as LMAX?

Thank you again.

Last edited by xiwuping (2018-08-25 15:37:21)

ab · 2018-08-25 16:49:20

The idea with TSynQueue is that you push/pop values very fast, in a very small lock.
Time is spend in computation before the push and after the pop, so no need for a lock-free buffer, which would work only with pointers by design, not with full records.

edwinsn · 2018-08-26 02:28:13

@xiwuping,

darkThreading has a lock-free and thread-safe queue implemented using array, not sure if it suits your needs.

https://chapmanworld.com/2018/05/24/a-n … threading/

dcoun · 2021-11-28 10:57:31

ab wrote:

1. still use TSQLRestServerFullMemory but create several tables for your application, and access directly each TSQLRestStorageMemory instance (bypassing the main TSQLRestServerFullMemory lock)

How can this be achieved?

mORMot Open Source

#1 2018-08-24 23:10:19

What is best practice for multi-threaded in-memory database?

#2 2018-08-25 06:15:48

Re: What is best practice for multi-threaded in-memory database?

#3 2018-08-25 08:36:07

Re: What is best practice for multi-threaded in-memory database?

#4 2018-08-25 15:32:49

Re: What is best practice for multi-threaded in-memory database?

#5 2018-08-25 15:36:33

Re: What is best practice for multi-threaded in-memory database?

#6 2018-08-25 16:49:20

Re: What is best practice for multi-threaded in-memory database?

#7 2018-08-26 02:28:13

Re: What is best practice for multi-threaded in-memory database?

#8 2021-11-28 10:57:31

Re: What is best practice for multi-threaded in-memory database?

Board footer