mORMot2 SQLite Engine, MREW lock question

wxinix · 2021-10-17 14:44:33

I understand that the Server Engine is thread-safe, with an internal lock when executing SQL.

This lock seems to be a READ-WRITE lock --- it locks up the DB for both READ, and WRITE. If there are a lot of concurrent READ query coming in from different threads, the performance drops down quickly because of the blocking. I am looking at mormot.orm.sqlite3, line 1778

   result := DB.LockJson(SQL, ReturnedRowCount); // lock and try from cache

My problem is: if I have two threads, each running a separate query to fetch some data from different tables:
- Thead 1: the query execution would take 200 milliseconds
- Thead 2: would have to wait Thread 1 unlock the DB, before running its own query. This means Thread 2 must wait at least 200 milliseconds, which is a waste.

Is it possible to make it a Multi-Read Exclusive Write Lock? This would improve multi-thread READ access to the DB.

Or mORMot already has some existing solution to resolve this concurrent READ access from different threads? Advice appreciated.

Last edited by wxinix (2021-10-17 17:09:34)

ab · 2021-10-17 18:27:45

You have to notice that SQlite3 engine is internally serialized.
https://sqlite.org/threadsafe.html details how it works.
In mORMot we have a single connection, but internal process is serialized with mutexes within SQLite3 itself.

Our lock R/W lock is mainly about the statements resource cache, and the JSON cache.
So it is needed as such.

Even if we made this lock a MREW lock, it would be serialized in SQLite3 itself.

About READ concurrent access, the idea is to keep the writes as short as possible.
I mean, in practice, to use a TRestBatch which is able to insert at more than 900,000 items per second.

A 200ms query is too long for SQLite3, if it is run often.
You should either add some indexes and tune the query plan (mORMot 2 logs the query plan).
Or use separated database instances, e.g. one per client or one per organisation. I have made some services with thousands of individual SQLite3 DBs, each one dedicated to one client, and it worked very well. Just use a connection cache.
Another idea may be to duplicate the data into "report" R/O databases which could be used for the most complex queries, even with a local copy on the client side, with no penalty for the main process. There are efficient DB duplicatation features in mORMot ORM (e.g. master/slave replication).

SQLite3 make databases very easy to create. Much easier than other engines.
Use this feature!

Ensure you have read https://synopse.info/files/html/Synopse … ml#TITL_25
And inspect the logs on production for any potential bottleneck - but no premature optimization!

wxinix · 2021-10-17 20:29:40

@ab thank you very much for the advice.

From the query planning, I saw the index was hit. There is another multi-index (ZID, UnixMinutes, DataType), but the query planner indicates that a simpler index (ZID, UnixMinutes) was selected.

20211017 20242137 DB    mormot.rest.sqlite3.TRestServerDB(047f5ec0) prepared 54us  
SELECT GID,LengthFt,SpeedMph,NumLanes,TraTimeSec
FROM TranscomLinkStatus 
WHERE ZID=? and UnixMinutes>=? and DataType=?  
[{id:3,detail:"SEARCH TranscomLinkStatus USING INDEX IndexTranscomLinkStatusZIDUnixMinutes (ZID=? AND UnixMinutes>?)"}]

And it takes 116.76ms to fetch 104800 rows - is this speed normal?

20211017 20242144 SQL   mormot.rest.sqlite3.TRestServerDB(047f5ec0) 116.76ms returned 104800 rows as 8.7 MB 
SELECT GID,LengthFt,SpeedMph,NumLanes,TraTimeSec
FROM TranscomLinkStatus 
WHERE ZID=:(1): and UnixMinutes>=:(27241444): and DataType=:('S'):

Last edited by wxinix (2021-10-17 20:32:01)

ab · 2021-10-17 21:23:29

It is around 1 million rows per second, so yes I guess this is normal.
Fastest than most SQL databases around for sure.

But 104,800 row seems way to much for me.
Why are you retrieving so much data? Is not paging possible - say per 1000 rows per query, using a LIMIT and OFFSET clause, or a proper manual paging using the index.
Why not just create a database for this particular ZID?

I would create some consolidated dedicated datasets if I could. Or at least use a reporting read/only slave database if you really need so much data at once. Or keep the resulting objects in memory...

wxinix · 2021-10-17 23:37:13

It is a time-series in-memory (mORMot SQLite direct in-mem) DB - the data are from roadway network sensors that report data every 1 minutes - so the data are WRITTEN to the DB every minute

There are 6000 sensors.

Every 1 minute, I need to calculate a statistical measure using the latest 20 minutes data — this query is for for the purpose of retrieving (READ) the lastest 20 minutes data. It is like a 20-minutes rolling window moving forward at 1 minute step in real time.

The database is not a read only DB - it keeps receiving new data every 1 minutes from those 6000 sensors. So I guess “replication” is really not meaningful here?

ab · 2021-10-18 06:52:01

Waiting 100 ms every minute seems just fine to me.

Perhaps I would have not used SQLite3 for this, or at least use it for disk persistence only.
I would have stored the incoming data in a dynamic array of records, and one dynamic array per minute - or the last minute.

mORMot Open Source

#1 2021-10-17 14:44:33

mORMot2 SQLite Engine, MREW lock question

#2 2021-10-17 18:27:45

Re: mORMot2 SQLite Engine, MREW lock question

#3 2021-10-17 20:29:40

Re: mORMot2 SQLite Engine, MREW lock question

#4 2021-10-17 21:23:29

Re: mORMot2 SQLite Engine, MREW lock question

#5 2021-10-17 23:37:13

Re: mORMot2 SQLite Engine, MREW lock question

#6 2021-10-18 06:52:01

Re: mORMot2 SQLite Engine, MREW lock question

Board footer