B4J Question concurrent execution with JSERVER and SQLITE

[Quandalle]

I have a web server built with Jserver and Sqlite running on a 4 core processor.
The application uses a single SQL object. A call to sql.ExecQuerySingleResult("PRAGMA journal_mode = wal") has been made, even though there are hardly any updates but basically reads from the database.

The application does searches (free text search with the FTS5 module of SQLITE) on a large database of 10 GB.

Most of the searches (ExecQuery " Select .... " ) are done in a few tens of milliseconds, but some searches require several tens of seconds.

When a long search is running, another browser performing a short search is blocked until the end of the execution of the long search.

Is this normal? Is there a special setting to allow 2 jserver handlers to access the SQLITE database simultaneously?
Is it necessary to create several SQL objects to allow both searches simultaneously?

Thanks for your advice

 

[Erel]

Are you calling BeginTransaction?

Most of the searches (ExecQuery " Select .... " ) are done in a few tens of milliseconds, but some searches require several tens of seconds.

Make sure that the servlet isn't configured as a single threaded servlet.

 

[Quandalle]

Are you calling BeginTransaction?


Make sure that the servlet isn't configured as a single threaded servlet.

The servlet is configured in multithread, and moreover during the execution of the servlet which makes a long SQLITE ExecQuery, I can execute without problem the same servlet in parallel for treatments without access to the SQLITE. However, as soon as there is an access to the database, the query seems to be put on hold until the previous query is finished. I don't use the BeginTransaction (only reads ExecQuery for this servlet)

I followed the recommendations of

[WebApp] Concurrent access to SQLite databases

SQLite databases are very easy to use as they don't require any additional software or configuration. SQLite support for concurrent access is not comparable to server based databases such as MySQL and others. However they can still be perfect for small / medium solutions, especially if there...

www.b4x.com

"- Use a single SQL object that is shared by all classes. Do not use a ConnectionPool with a SQLite database.
"- When you create the database, you must set the log mode to wal "
"This mode allows multiple readers and a single writer to access the database at the same time."

So in Main before the server starts the SQL object is declared and then initialized by an InitializeSQLite. Then all servlets use this SQL object referencing main.sql1 to access the database. But in my case, the queries (read access) seem to be put on hold.

I made a test where in each servlet I create a SQL object, initializeSQLITE , Execquery, and close object . Everything is fine, short queries can be executed during a long query without being put on hold.

So what is the right writing for using a SQLITE database in servlets ?

 

[tchart]

Have a read of this;

jServer & SQLite [Multiple Request Stress Test]

Should I use SQLite as my web server database? This has come up before and last week it came up again in the "Chit Chat" forum. The question has already been answered here before and there are many arguments for and against using SQLite, especially when you have lots of users who may be reading...

www.b4x.com

Sample project handles thousands of read and writes per second with no issues.

 

[Quandalle]

Have a read of this;

jServer & SQLite [Multiple Request Stress Test]

Should I use SQLite as my web server database? This has come up before and last week it came up again in the "Chit Chat" forum. The question has already been answered here before and there are many arguments for and against using SQLite, especially when you have lots of users who may be reading...

www.b4x.com

Sample project handles thousands of read and writes per second with no issues.

I made a simple example with two handlers. One that does a very long ExecQuery, and the other that does a very short ExecQuery on the same SQL object declared in the Main module. If we launch from a browser localhost:5100/long followed by localhost:5100/fast, the fast query is deferred until the end of the execution of the long query.

#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
    #AdditionalJar: sqlite-jdbc-3.32.3.2
#End Region

Sub Process_Globals
    Private srvr As Server
    Dim DB As SQL
End Sub

Sub AppStart (Args() As String)
    DB.InitializeSQLite(File.DirApp, "test.db", True)
    srvr.Initialize("srvr")
    srvr.Port = 5100
    srvr.AddHandler("/long", "LongExecQuery", False)
    srvr.AddHandler("/fast", "FastExecQuery", False)
    srvr.Start
    StartMessageLoop
End Sub

'Handler class
Sub Class_Globals
End Sub

Public Sub Initialize
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Dim limit  As Long = 100000000  ' give about 30 seconds execQuery on my computer
    Dim start As Long = DateTime.now
    Main.DB.ExecQuery($"WITH RECURSIVE r(i) AS (  VALUES(0)  UNION ALL SELECT i FROM r  LIMIT ${limit} ) SELECT i FROM r WHERE i = 1; "$)
    resp.Write($"duration = ${DateTime.Now-start} ms"$ )
End Sub

'Handler class
Sub Class_Globals
End Sub

Public Sub Initialize
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Dim start As Long = DateTime.now
    Main.DB.ExecQuery($"SELECT 1"$)
    resp.Write($"duration = ${DateTime.Now-start} ms"$ )
End Sub

On the other hand if I declare another SQL object in the handler of the FastExecQuery . Then I have a real parallelism. The short query is executed during the long query.

'Handler class
Sub Class_Globals
End Sub
Public Sub Initialize
    
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Dim lDB As SQL
    lDB.InitializeSQLite(File.DirApp, "test.db", True)
    Dim start As Long = DateTime.now
    lDB.ExecQuery($"SELECT 1"$)
    resp.Write($"duration = ${DateTime.Now-start} ms"$ )
End Sub

Also it seems to me that the SQLITE engine queues the read requests, and that there is no concurrent access. I had understood from Erel's posts that SQLITE allows concurrent access with a single SQL object. Is there something I don't understand?

 

[Erel]

You are correct. I've investigated the threads dump of the server project you described (next time please upload the project) and the threads are accessed one by one.
After some digging, I've found that it is a limitation of the jdbc driver: https://github.com/xerial/sqlite-jdbc/issues/369

You should be careful with multiple connections if writing is involved. Make sure to set the journal mode to wal and never write concurrently.
Consider using a different SQL engine if this limits your solution.

 

[Quandalle]

Thanks Erel for your investigation. This limitation of the jdbc driver is a negative point for web solutions with SQLITE, because we do not necessarily benefit from the full capacity of a multiprocessor server. For my application, it does not seem possible to use another database in the short term because I use intensively the Full Text Search functionalities of SQLITE which have a particular syntax.

I will therefore experiment with the following organization for my Web site:
- A SQL object used by all the servlets that do writes
- A set of SQL objects shared by all the servlets that do only reads (management in pool mode)
- The "wal-on" mode activated on all connections

Do you have any particular comments on this organization?

 

[Erel]

It sounds valid though I cannot say for sure.

Consider using a read-write lock. There is such a lock in jBuilderUtils library. Such lock will allow multiple readers to read concurrently. Reading and writing will not happen at the same time.
You need to handle the locks carefully, including cases where there are exceptions.

 

[tchart]

@Quandalle can you confirm you are running your project in release mode? Probably unrelated but running under debug causes queuing also.

 

[Quandalle]

@Quandalle jcan you confirm you are running your project in release mode? Probably unrelated but running under debug causes queuing also.

Of course it is in release mode.
In your example the queries are very short, and we don't see that they are waiting to be executed one after the other. In fact with jetty each servlet runs in parallel in separate threads, but when accessing the database (using the SQL object) the JDBC-SQLITE driver puts on hold if another query is already running.

As Erel pointed out, and as indicated in the list of Xerial Issues the JDBC driver for SQLITE, it is this driver that does not do parallelism, and queues the queries it receives.

A way to have a better parallelism (and to use the multi-cores), as I propose above, is to use several instances of the driver (so in fact several SQL objects) but be careful with the risk if you have several SQL objects in writing.

Hence my proposal to use in "wal-on" mode a single shared SQL object for all servlets that do writes, and a set of shared SQL objects for servlets that do only reads. This works on an example that I have set up and allows to really make parallelism on reads.

For more security Erel suggests to use a lock to avoid that reads and writes can be done simultaneously. I'm not sure if this is necessary because according to the documentation it seems that SQLITE correctly manages this situation of multiple readers and a single writer. But it would be necessary to investigate a little more.

 

[tchart]

@Quandalle thanks. Yes I have replicated the issue using your code.

Just some other comments;

1. In one of my projects I keep my full text search in a separate sqlite database (so I have two DB connections to two different files). This avoids clashes between long running and short running queries.
2. An earlier version of my app used to open a new connection for each query. This was obviously slower than having one connection but each query was isolated. You could use a shared connection for all of your fast reads and a new connection for your full text long queries.
3. You could try connection pooling. I found this to be slower for my use case (multiple fast reads) but it could be useful for you.

 

[tchart]

@Erel @Quandalle

Tested & confirmed - using a connection pool solves the problem.

Using HIkariCP from here; https://www.b4x.com/android/forum/threads/hikaricp-high-performance-connection-pool.88430/

EDIT actually I forgot I did my own wrapper for a newer version of HikariCP - its attached below.

Main

#Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True
    #AdditionalJar: sqlite-jdbc-3.36.0.3_min
#End Region

Sub Process_Globals
    Private srvr As Server
    Dim cp As HikariCP
End Sub

Sub AppStart (Args() As String)
    'SQLiteCP
    Dim url As String = $"jdbc:sqlite:${File.DirApp}\test.db"$
    cp.Initialize("org.sqlite.JDBC",url,Null,Null)
  
    Dim jocp As JavaObject = cp
    jocp.RunMethod("setMaximumPoolSize", Array(100))
      
    srvr.Initialize("srvr")
    srvr.Port = 5100
    srvr.AddHandler("/long", "LongExecQuery", False)
    srvr.AddHandler("/fast", "FastExecQuery", False)
    srvr.Start
    StartMessageLoop
End Sub

Handler

'Handler class
Sub Class_Globals
    Dim DB As SQL
End Sub

Public Sub Initialize
    Dim DB As SQL
    DB = Main.cp.GetConnection
End Sub

Sub Handle(req As ServletRequest, resp As ServletResponse)
    Dim limit  As Long = 100000000  ' give about 30 seconds execQuery on my computer
    Dim start As Long = DateTime.now
    DB.ExecQuery($"WITH RECURSIVE r(i) AS (  VALUES(0)  UNION ALL SELECT i FROM r  LIMIT ${limit} ) SELECT i FROM r WHERE i = 1; "$)
    resp.Write($"duration = ${DateTime.Now-start} ms"$ )
End Sub

 

[Erel]

Connection pool is a good way to manage multiple connections. In the case of SQLite you need to be careful as its threading model is more fragile compared to most other SQL engines.