SQLITE_CONFIG_MULTITHREAD needed for connection pool?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLITE_CONFIG_MULTITHREAD needed for connection pool?

Deon Brewis
The model we use for SQLITE is to use a connection pool (connections opened via sqlite3_open_v2).

We have many threads, but only one thread can use a connection at a time - a thread takes it out of the pool, and when done, returns it to the pool.

The only exception to this is calls to: sqlite3_interrupt(), which can be called from any thread.


Under that model, do we need to pass SQLITE_CONFIG_MULTITHREAD ?

- Deon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_CONFIG_MULTITHREAD needed for connection pool?

Keith Medcalf

You never *need* to change SQLITE_CONFIG_MULTITHREAD.  The default mode is "serialized", which means that if you "accidentally" violate the serial entrance requirements of a connection (by accessing it simultaneously on multiple threads) that all hell will not break loose.  This is achieved by putting a mutex on the database connection to ensure that it is only being actively used in one thread at a time.

If you change the threading mode to MULTITHREADED nothing changes EXCEPT that the mutex to ensure that all hell does not break loose is not used.  This may save "a wee bit" of processing time each time you enter the library since the mutex does not need to be obtained.  Whether this is noticable by your application or not depends.  For example, if you run queries that each take '2 million ticks', then the 2 tick savings (per call) are not noticeable.  On the other hand, if you perform lots of '3 tick' calls into the library, then saving '2 ticks' on each call is significant.

If you change the default threading mode to multithreaded and all hell breaks loose (but it does not break loose in the default serialized setting), then you are inadvertantly violating the rules.  Like everything, it is a trade off, in this case between safety and performance.

So, in theory you *may* dispense with serialized default and use the multithreaded mode -- that choice is up to you.  But it is not (ever) required.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Deon Brewis
>Sent: Tuesday, 27 March, 2018 14:55
>To: SQLite mailing list
>Subject: [sqlite] SQLITE_CONFIG_MULTITHREAD needed for connection
>pool?
>
>The model we use for SQLITE is to use a connection pool (connections
>opened via sqlite3_open_v2).
>
>We have many threads, but only one thread can use a connection at a
>time - a thread takes it out of the pool, and when done, returns it
>to the pool.
>
>The only exception to this is calls to: sqlite3_interrupt(), which
>can be called from any thread.
>
>
>Under that model, do we need to pass SQLITE_CONFIG_MULTITHREAD ?
>
>- Deon
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_CONFIG_MULTITHREAD needed for connection pool?

Olivier Mascia
In reply to this post by Deon Brewis
> Le 27 mars 2018 à 22:55, Deon Brewis <[hidden email]> a écrit :
>
> The model we use for SQLITE is to use a connection pool (connections opened via sqlite3_open_v2).
>
> We have many threads, but only one thread can use a connection at a time - a thread takes it out of the pool, and when done, returns it to the pool.

Been there, changed plans.  In our case we profiled no net benefit from such a pooling compared to have each thread which need a connection to actually open it, use it and then close it.  It looks like you really need a text heavy / complex schema (taking some noticeable time to parse upon each new attachment) and a very good connection pool mechanism (not adding too much contention when obtaining a connection from the pool or returning it) to really see benefit from this more complicated scheme.

Our model which is conceptually equivalent to running N processes instead of N threads, guarantees us to be able to use sqlite3_config(SQLITE_CONFIG_MULTITHREAD) at application startup, or compile sqlite with SQLITE_THREADSAFE=2, or yet use SQLITE_OPEN_NOMUTEX on attachment themselves, all three being equivalent means to get SQLITE_THREADSAFE=2 mode) to lower (a very small yet) unneeded contention inside sqlite, since we take care of never ever share any attachment (and child objects) between threads, *not even reuse one later in another thread*.

> The only exception to this is calls to: sqlite3_interrupt(), which can be called from any thread.
>
> Under that model, do we need to pass SQLITE_CONFIG_MULTITHREAD ?

If you ask wether it is required, no it isn't.

If you ask wether you may consider using that mode, I'd say maybe, but there are two risks I wouldn't take.

1) It all depends on wether SQLite might have, directly or indirectly, any kind of thread-local storage on behalf of an opened attachment.  If yes (and I don't know it for sure) you would end up involuntary breaking the contract between you and SQLite and would risk all sorts of horrible things.

2) The slightest occasional mistake with the connection pool could get two of your threads share a same attachment.

With your connection pooling model, I would strongly suggest that you keep SQLITE_THREADSAFE=1 which should be the default unless you use a specifically compiled version of SQLite. Assuming your schema is complex (which would justify the pooling mechanism to gain on schema parsing at each connection), most of your queries are probably too, and the minimalist contention that the normal threading mode will imply is probably very hard to measure in real life.

If you're not sure of the default behaviour of your SQLite instance you link with (if not compiled by you), you may want to pass SQLITE_OPEN_FULLMUTEX on each sqlite3_open_v2 calls to ensure serialized mode is in effect.

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

--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLITE_CONFIG_MULTITHREAD needed for connection pool?

Jens Alfke-2


> On Mar 27, 2018, at 2:37 PM, Olivier Mascia <[hidden email]> wrote:
>
> Been there, changed plans.  In our case we profiled no net benefit from such a pooling compared to have each thread which need a connection to actually open it, use it and then close it.  It looks like you really need a text heavy / complex schema (taking some noticeable time to parse upon each new attachment) and a very good connection pool mechanism (not adding too much contention when obtaining a connection from the pool or returning it) to really see benefit from this more complicated scheme.


(I think by "attachment" you mean a sqlite3* connection?)

Does your program run in any low-memory environments? In my use case (mobile devices) I'm more worried about the RAM consumed by each SQLite connection's cache. (No, we can't use shared-cache mode.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users