One read/write rest server and multi read-only server?

edwinsn · 2014-06-20 14:08:35

Hello Arnaud,

In my program there is a in-process TSQLRestServerDB server which is responsible for reading and writing the data to sqlite3 db, the clients (in other threads) talk to it via Named Pipes.

But since there are a lot of read operations (in threads) and I feel that the Named Pipe channels are not fast enough, so I am thinking of using additional TSQLRestServerDB instances to read, only read data from the sqlite3 db (no writing, one instance each thread), I believe that'll speed the data reading up.

But my concern is, will there be any side effects with this design? I guess no, but I'd like your comments

Thanks in advance!

Last edited by edwinsn (2014-06-20 14:14:54)

ab · 2014-06-20 14:56:42

If the "clients" are in the same process, you should perhaps not use Named Pipes, but direct call of the TSQLRestServerDB instance.
It will be as fast as possible, and thread-safe.

By all means, NEVER use several TSQLRestServerDB instances to access the same SQlite3 DB file: it would be unsafe, and slower due to lock contention at file level.

edwinsn · 2014-06-20 15:09:50

ab wrote:

If the "clients" are in the same process, you should perhaps not use Named Pipes, but direct call of the TSQLRestServerDB instance. It will be as fast as possible, and thread-safe.

Yes, the clients are in the same process, I didn't know we can directly access the same TSQLRestServerDB instance from multiple threads!

Just to be sure, do you mean, if I have a global var:

unit1
interface
...
var
  gRestSvr: TSQLRestServerDB;//this is a global variable declared in a unit.

...
  //make the writing to the sqlite3 db runs in a single background thread.
  gRestSvr.AcquireWriteMode := amBackgroundThread;

And in a TThread.Execute() I can call cany methods of gRestSvr?

gRestSvr.anyRestServerMethod();// this code is in a thread other than the main thread.

If yes, that'll be awesome! Can you confirm?

Last edited by edwinsn (2014-06-20 15:10:16)

ab · 2014-06-20 15:25:36

Yes, you can use gRestSvr from multiple threads.

Then you can tune the thread affinity using AcquireExecutionMode[] properties.
Note that amBackgroundThread is of little benefit with a SQLite3 database: db process will continue to lock the execution, but execution will take place in a dedicated single thread.
This may be of some benefit for some external database engines (e.g. Oracle or MS SQL via OleDB). But it will make Sqlite3 slightly slower.
The faster would be amUnlocked here. And it will still be thread-safe.

See the regression tests benchmark:

2.9. Multi thread process:
- Create thread pool: 1 assertion passed 1.75ms
- TSQLRestServerDB: 4,822 assertions passed 60.29ms
1=35942/s 2=44898/s 5=45259/s 10=45449/s 30=51354/s 50=41972/s
- TSQLRestClientDB: 4,822 assertions passed 63.44ms
1=38178/s 2=40530/s 5=40621/s 10=40683/s 30=45444/s 50=38084/s
- TSQLRestClientURINamedPipe: 2,412 assertions passed 1.50s
1=1282/s 2=1108/s 5=637/s
- TSQLRestClientURIMessage: 3,222 assertions passed 97.77ms
1=12410/s 2=24417/s 5=19963/s 10=17258/s
- TSQLHttpClientWinHTTP_HTTPAPI: 4,822 assertions passed 590.00ms
1=4245/s 2=4306/s 5=4217/s 10=4086/s 30=4220/s 50=3687/s
- TSQLHttpClientWinSock_WinSock: 4,822 assertions passed 479.00ms
1=8949/s 2=9123/s 5=9120/s 10=8358/s 30=7429/s 50=2972/s
- Locked: 4,822 assertions passed 78.02ms
1=18434/s 2=40518/s 5=40671/s 10=41105/s 30=40845/s 50=38149/s
- Unlocked: 4,822 assertions passed 64.50ms
1=38406/s 2=40563/s 5=40633/s 10=40687/s 30=40895/s 50=38084/s
- Background thread: 4,822 assertions passed 67.92ms
1=37939/s 2=35470/s 5=40563/s 10=36818/s 30=40675/s 50=34834/s
- Main thread: 4,822 assertions passed 67.80ms
1=36202/s 2=37551/s 5=35404/s 10=37700/s 30=41480/s 50=36710/s
Total failed: 0 / 44,211 - Multi thread process PASSED 3.09s

Please take a look at "Thread-safety" in the latest version of the SAD 1.18 pdf.
Everything is explained there, especially the AcquireExecutionMode[] property.

edwinsn · 2014-06-20 15:41:24

Thanks Arnaud, it's so great to know that, TSQLRestServerDB is so much faster than TSQLRestClientURINamedPipe! I'll try it tomorrow!

I'm using the snapshop of the trunk few weeks ago, I'll download the latest version tomorrow!

ab · 2014-06-20 15:50:06

Yes, named pipes are a bit disappointing about speed and stability, in multi-thread process (see above numbers).
The issue is in our implementation, which may be enhanced.
If you have any idea...

In short, HTTP is the best for remote access, and direct in-process execution is to be preferred otherwise.

With the latest version of the code, you have even BATCH methods available on server, for even faster process.

edwinsn · 2014-06-21 04:31:19

@ab, there is something I don't understand, why amUnlocked is still thread-safe? I checked the source code, in amUnlocked mode, there is no criticalSection protection?

Re named pipe improvements, I'm afraid I've not got a level that I can have any good suggestion for it

ab · 2014-06-21 04:58:19

It is thread-safe at the DB level (SQlite3, cache, SynDB...), protected at this level by critical sections (e.g. for SQLite3, cache - see the LockJSON/Lock/UnLock methods) or per-thread connections (e.g. for SynDB).
All other mORMot code (e.g. JSON parsing, and so on) is re-entrant.
As a result, amUnlocked is thread-safe.

But your code has to be thread-safe, if you define custom services as methods or interfaces.

edwinsn · 2014-06-21 07:12:14

ab, I must be doing something wrong - from the above conversation my understanding is that multiple threads can use the **same** TSQLRestServerDB object to do both writing and reading safely. However, I encountered this error:

Project myApp.exe raised exception class $C0000005 with message 'access violation at 0x009854d4: read of address 0x00000000'.

I was running the program in the IDE, and the debugger stopped it at:

function TSQLModel.GetTableIndex(aTable: TSQLRecordClass): integer;
begin
...
    // manual search e.g. if fModel[] is not yet set
    for result := 0 to fTablesMax do
      if Tables[result]=aTable then //Edwin: the breakpoint was here
        exit;

And the stack trace:

mORMot.TSQLModel.GetTableIndex(TSQLMail)
mORMot.TSQLModel.GetTableIndexExisting(TSQLMail)
mORMot.TSQLRest.Add($852D2D0,True,False)

At that breakpoint, I checked the value of fTablesMax and it was: 7602286.

Also:

Watch Name	Value
length(tables)	Access violation at 0040CB04 accessing FFFFFFFD

Should I create a ticket, or am I doing something wrong? I'm using the nightly build downloaded several hours ago, and I'm sure there is only one single instance of TSQLRestServerDB in the program.

edwinsn · 2014-06-21 07:33:18

ab, one more question, it seems that my try...except...end block can't catch the exception - the exception raises only in the DELPHI IDE? And since TSQLRestServerDB doesn't have the LastErrorException properly like TSQLRestClientURI does, is there a way to catch those exceptions? Thanks.

edwinsn · 2014-06-21 08:45:29

Oh! My bad! Sorry!

As per our conversatin, since I changed to TSQLRestServerDB from TSQLRestClientURINamedPipe, and didn't comment out all calls to .Free(), thus causing the global TSQLRestServerDB instanced got freed unexpectedly, and the error I described above is due to accessing a already destroyed object...

Sorry for have wasted a little time of your, Arnaud

Anyways, I still want my try...except...end block to catch all internal exceptions of TSQLRestServerDB.

edwinsn · 2014-06-22 03:34:59

I'd like to report that, it's so much faster!

edwinsn · 2014-06-29 05:31:26

AB, after reading some posts in the forum, I realized TSQLRestClientDB should be used instead of TSQLRestServerDB, since the former will allow me to switch to HTTP protocol in the future without any changes to be made?

If I'm right, may I ask, does TSQLRestClientDB have the same thread-safety?

Thanks and I wish you have a nice weekend

ab · 2014-06-29 06:20:02

Take a look at the regression tests: TSQLRestClientDB is as safe.

See http://synopse.info/forum/viewtopic.php … 160#p11160

mORMot Open Source

#1 2014-06-20 14:08:35

One read/write rest server and multi read-only server?

#2 2014-06-20 14:56:42

Re: One read/write rest server and multi read-only server?

#3 2014-06-20 15:09:50

Re: One read/write rest server and multi read-only server?

#4 2014-06-20 15:25:36

Re: One read/write rest server and multi read-only server?

#5 2014-06-20 15:41:24

Re: One read/write rest server and multi read-only server?

#6 2014-06-20 15:50:06

Re: One read/write rest server and multi read-only server?

#7 2014-06-21 04:31:19

Re: One read/write rest server and multi read-only server?

#8 2014-06-21 04:58:19

Re: One read/write rest server and multi read-only server?

#9 2014-06-21 07:12:14

Re: One read/write rest server and multi read-only server?

#10 2014-06-21 07:33:18

Re: One read/write rest server and multi read-only server?

#11 2014-06-21 08:45:29

Re: One read/write rest server and multi read-only server?

#12 2014-06-22 03:34:59

Re: One read/write rest server and multi read-only server?

#13 2014-06-29 05:31:26

Re: One read/write rest server and multi read-only server?

#14 2014-06-29 06:20:02

Re: One read/write rest server and multi read-only server?

Board footer