Quantcast

Best settings for concurency

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Best settings for concurency

Max Terentiev
Hi,

I developing app server with sqlite as storage engine.

Sqlite have many settings combinations affecting concurrency please help me
to chose right settings for my task:

1. SQLITE_THREADSAFE 1 (serialized) vs SQLITE_THREADSAFE 2 (multithreaded)

My app server will execute each client request in their own thread. Should
I use Shared Connection and SQL_THREADSAFE 1 or per thread connections +
SQLITE_THREADSAFE 2 ? I expect about 100-200 requests per second (80% of them is simple SELECT queries).
Requests will be handled by threads pool, so only 50-70 threads should be always working.

2. Shared Cache + Read Uncommitted + busy timeout vs WAL mode

What is better for multithreaded connections ? My app server needs this operations:

- Simple SELECT queries returning 100-500 records. 80% of requests
- Bulk INSERT or DELETE records, 10000 records per transaction (several millions records total). 15% of requests.
- INSERT/DELETE/UPDATE of single record. 5% of requests
- CREATE TABLE, ALTER TABLE, DROP TABLE - rare requests.

If WAL mode is better - should I use Read Uncommitted with WAL mode for better concurrency ?

3. Should I use locking_mode = EXCLUSIVE for best performance ? Database will be accessed only from single process.

4. BEGIN DEFFERED vs BEGIN IMMEDIATE vs BEGIN EXCLUSIVE for writing transactions.

Should I use BEGIN EXCLUSIVE for writing transactions ? If BEGIN EXCLUSIVE success does
it guarantee no SQLITE_BUSY/SQLITE_LOCKED errors for nested sqlite3_step and COMMIT call ?

Thanks for help and excuse me for bad English.

---
With best regards, Max Terentiev.
Business Software Products.
AMS Development Team.
[hidden email]


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

Re: Best settings for concurency

Simon Slavin-3

On 25 Mar 2017, at 7:14pm, Max Terentiev <[hidden email]> wrote:

> Sqlite have many settings combinations affecting concurrency please help me
> to chose right settings for my task

1. Leave SQLITE_THREADSAFE to the default setting.

2. Leave shared cache and uncommitted reads to the default settings.

Use WAL mode.  This is a one-time setting which is stored in the database.  Once you’ve set it every connection which opens the database automatically knows that it’s in WAL mode.

Set a timeout of at least thirty seconds.  You have to set this with every connection to the database (simplest to do it immediately after the sqlite3_open() command).

3. Leave locking mode to the default setting.

4. Use BEGIN without adding anything, which defaults to BEGIN DEFERRED.

I’m sure you’ve noticed a pattern here.  The default settings are pretty good for the way you want to use SQLite.  Try the above.  If you get a problem post again and we’ll try to figure out what to change.

> - Bulk INSERT or DELETE records, 10000 records per transaction (several millions records total). 15% of requests.

Depending on how complicated your schema is, and the performance of your storage system, this may be a problem, an exception to the above.  If inserting 10000 records in one transaction locks the database for too long, you might want to do them in batches of 1000, sleeping for a few milliseconds between blocks.  But it’s likely that you will have no problem with far longer blocks.

> Thanks for help and excuse me for bad English.

Always fun when people post this.  The people who think to post it write English better than I do.

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

Re: Best settings for concurency

Keith Medcalf
In reply to this post by Max Terentiev

On Saturday, 25 March, 2017 13:15, Max Terentiev <[hidden email]> wrote:

> I developing app server with sqlite as storage engine.
 
> Sqlite have many settings combinations affecting concurrency please help
> me to chose right settings for my task:
 
> 1. SQLITE_THREADSAFE 1 (serialized) vs SQLITE_THREADSAFE 2 (multithreaded)

> My app server will execute each client request in their own thread. Should
> I use Shared Connection and SQL_THREADSAFE 1 or per thread connections +
> SQLITE_THREADSAFE 2 ? I expect about 100-200 requests per second (80% of
> them is simple SELECT queries).
> Requests will be handled by threads pool, so only 50-70 threads should be
> always working.

THREADSAFE=1 (serialized) ensures that the same *CONNECTION* (or things derived from the same connection, such as statements) do not simultaneously make calls into the SQLite engine code at the same time from different threads, thus preventing internal corruption of the process.  If you yourself ensure that a given *CONNECTION* and all objects derived from that connection are only ever accessed from a single thread, then THREADSAFE=2 removes the mutex checking (overhead) that is used to ensure that CONNECTION associated state data is not corrupted by simultaneous entry on multiple threads.

In other words, if you have a connection per thread, and do not access connections (and statements derived from that connection) except in the thread in which the connection was created (*EVER*), then you can use THREADSAFE=2.  If you are sharing a connection object across threads then you need THREADSAFE=1 in order to ensure that the connection object (and derivatives thereof such as statements) are not simultaneously accessed from multiple threads concurrently.

Note that if you have one connection, and prepare two statements, those statements cannot be used simultaneously from different threads since they refer to the same global state (the connection).  In the case of THREADSAFE=1 the access will be serialized by mutexes to prevent you from corruption.  If you do that with THREADSAFE=2 then AHWBL (All Hell Will Break Loose).
 
> 2. Shared Cache + Read Uncommitted + busy timeout vs WAL mode

> What is better for multithreaded connections ? My app server needs this
> operations:
 
> - Simple SELECT queries returning 100-500 records. 80% of requests
> - Bulk INSERT or DELETE records, 10000 records per transaction (several
> millions records total). 15% of requests.
> - INSERT/DELETE/UPDATE of single record. 5% of requests
> - CREATE TABLE, ALTER TABLE, DROP TABLE - rare requests.

SHARED CACHE is designed for resource constrained systems.  Unless you are resource constrained do not use it.  Buy more resources instead.

READ UNCOMMITTED permits one connection to "see" data that has not been committed in another transaction.  Have you read
https://www.sqlite.org/isolation.html
 
> If WAL mode is better - should I use Read Uncommitted with WAL mode for
> better concurrency ?

No.  Read Uncommitted has no effect on concurrency.

If you use WAL, you can simultaneously have ONE connection writing and ANY number of simultaneous readers.  Without WAL mode you can have *either* ONE connection writing *OR* multiple connections reading.  WAL also has Repeatable Read isolation for transactions which only read the database.

> 3. Should I use locking_mode = EXCLUSIVE for best performance ? Database
> will be accessed only from single process.

No.  You said you have multiple threads.  That means that if you have a connection per thread (as you ought to have) then when one thread acquires an EXLUSIVE lock all other threads will be locked out.  The purpose of EXCLUSIVE locking is to *eliminate* concurrency.

> 4. BEGIN DEFFERED vs BEGIN IMMEDIATE vs BEGIN EXCLUSIVE for writing
> transactions.

If you know you are going to be doing an update of the database, start the transaction with BEGIN IMMEDIATE.  If you are reading, then start the transaction with BEGIN.  BEGIN EXCLUSIVE does exactly what it says and grants that connection EXCLUSIVE access to the database for reading or writing to the exclusion of all other connections (and threads, since you have one connection per thread).

> Should I use BEGIN EXCLUSIVE for writing transactions ? If BEGIN EXCLUSIVE
> success does it guarantee no SQLITE_BUSY/SQLITE_LOCKED errors for nested sqlite3_step
> and COMMIT call ?

No and Yes.  BEGIN IMMEDIATE does the same thing for transactions in which you KNOW you will be updating the database, without locking out other connections (threads).
 
> Thanks for help and excuse me for bad English.



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