B4J Tutorial 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 or writing at the same time.

As mentioned in the various threads using SQLite is actually fine for most use cases and alleviates many issues such as DB maintenance, extra services, extra load on your server etc.

You should probably have a read of these threads if you are interested.

https://www.b4x.com/android/forum/threads/would-you-recommend-sqlite.113915/
https://www.b4x.com/android/forum/threads/mysql-vs-sqlite-vs-mongodb.130400/

I use SQLite for many projects including with jServer. As these are web applications I expect multiple concurrent requests which may be reading or writing at the same time. Ive never had any issues with SQLite in any of my projects (even commercial ones).

Just to be 100% sure I put together a quick test using jServer/SQLite and then stress tested it using Web Surge (https://websurge.west-wind.com/).

I created a simple web server with two handlers; one that inserts a row & reads and another that just does a read (all using the same table). I then stress tested this with Web Surge using 100 threads over 60 seconds.

As you can see below there are no failed requests over 10k+ requests. I repeated this several times and can never get any errors. The SQLite database didnt even have WAL turned on initially. With WAL enabled the requests are much quicker and I get over 700 request per second, again with no failures.

I know this is a simplistic test but it does prove the point that unless you are having several thousand requests per second then SQLite will probably be just fine.

WAL Off

WAL On

Another option is to use the H2 database - https://www.h2database.com/html/main.html

H2 is another database that can be used as an embedded database, hybrid (embedded + auto server) or as a TCP based server. It has many advantages over SQLite.

One of the more appealing things is that you can run H2 in modes that are compatible with larger RDBMS systems. For example when I use H2 I use Postgres mode (MODE=PostgreSQL) which means that if I ever get to a point where I need to scale then I can move to Postgres with almost zero effort.

I performed the same test as above but with H2 as the backend (in embedded mode) and as you can see I got over 6 times the requests per second.

H2 Embedded Mode

UPDATE 2022-03-02

@Erel has confirmed there is a limitation with the Xerial JDBC driver that means it will only process queries in series when using a single shared connection.

It may be useful to read this thread; https://www.b4x.com/android/forum/threads/concurrent-execution-with-jserver-and-sqlite.138803/

This means that;

1. Queries are queued and processed one after the other.
2. There is no risk of queries ever clashing if you are using a single connection (because of point #1)
3. It does however mean that queries submitted after a long running query will have to wait.

The work around for this is to use different connections or a connection pool (see the thread above). Be warned though that using multiple connections is fine for read only but you should not use multiple connections for writing.

UPDATE 2022-03-21

A couple of updates.

1. Truncating data and compacting the database speeds SQLite up. Doing this I can squeeze ~1000 transactions per second out of SQLite (reading and writing using the same connection). The performance does degrade with more data in the database.
2. Using a pool manager (eg HikariCP) results in a bit more performance but only by a bit. However as pointed out (here) if you have any potentially long running queries then a pool manager is a good option as it wouldnt block the single connection.

SQLite Compacted with shared/single Connection

SQLite Compacted DB with Hikari CP

 

[tchart]

@ilan FYI

 

[Erel]

Two relevant threads:
1. https://www.b4x.com/android/forum/threads/server-performance-measures.37502/post-238853
2. https://www.b4x.com/android/forum/t...ent-access-to-sqlite-databases.39904/#content

 

[LucaMs]

I like (or need?) to think... little.

SQLite, if I'm not mistaken, was created to manage data on mobile devices, "personal" data.
This is enough for me and knowing many existing DBMS, without running tests, to discard it as a server.

 

[LucaMs]

SQlite has limitations due to the purpose of a simple database that must be incorporated into other applications:

• it does not allow stored procedures
• it does not provide the management of access permissions; delegated to the sw with which it interacts with the db or to the access permissions to the file system
• it has no real concurrency management
• to ensure the consistency of the database file, the file system locks are used and therefore there may be problems if it does not implement them correctly, for example with network file systems (NFS)
• it has no cache for queries (and could not, as it is not a central server process)
• it has no network protocols, as it cannot be used as a stand-alone program. It is possible to use a remote database but only through the operating system's network file system, with hardly acceptable performance
• it does not support important constructs such as "RIGHT JOIN" and "FULL OUTER JOIN"
• it does not support variable subqueries
• it does not support direct writing in Views ("INSTEAD OF" triggers must be used)
• it does not support "FOR EACH STATEMENT" triggers (only "FOR EACH ROW" possibly combined with "INSTEAD OF")
• support for recursive triggers and constraints on external keys must be activated by the programmer (this will be changed from version 3.7)

 

[tchart]

SQLite, if I'm not mistaken, was created to manage data on mobile devices, "personal" data.

Thats actually not correct. Altough it is prevalant on mobile devices nowadays, it predates modern mobile devices and was designed for use on Unix. The fact that its embedded and portable is part of why its so popular.

 

[tchart]

SQlite has limitations due to the purpose of a simple database that must be incorporated into other applications:

That may be true however how many people actually use these features?

I am first to agree that if you need those feature then dont use SQLite.

As per the other threads if you need more functionality there are other embedded options such as H2 which are highly compatible with Postgres and has many of the features you described.

All Im saying is that it is not necessary to use a full blown RDBMS if you only have a small application.

 

[LucaMs]

Thats actually not correct. Altough it is prevalant on mobile devices nowadays, it predates modern mobile devices and was designed for use on Unix. The fact that its embedded and portable is part of why its so popular.

Precisely because I was not quite sure I looked on Wikipedia; consequently, I read the list of shortcomings of SQLite (only) as a server and then reported them.

 

[LucaMs]

That may be true however how many people actually use these features?

I am first to agree that if you need those feature then dont use SQLite.

As per the other threads if you need more functionality there are other embedded options such as H2 which are highly compatible with Postgres and has many of the features you described.

All Im saying is that it is not necessary to use a full blown RDBMS if you only have a small application.

If you want you can also use one or more text files, then.

 

[Erel]

I like (or need?) to think... little.

SQLite, if I'm not mistaken, was created to manage data on mobile devices, "personal" data.
This is enough for me and knowing many existing DBMS, without running tests, to discard it as a server.

If you want you can also use one or more text files, then.

Sorry to say but it is nonsense.
SQLite is a very powerful database engine which is used by many servers.
It is definitely a tool that developers should consider. Like any other tool it has its advantages and disadvantages.

Why do you need to turn each thread into an ongoing argument? No one wants to read all these posts. This is just clutter added to an informative thread.

 

[LucaMs]

Why do you need to turn each thread into an ongoing argument? No one wants to read all these posts

I just answered the thread question.

 

[tchart]

I just answered the thread question.

This was a tutorial/information thread and not a question. The question has already been answered in those other discussions. All I was showing was that using SQLite for concurrent write/read is entirely possible even with high throughput.

 

[LucaMs]

This was a tutorial/information thread and not a question. The question has already been answered in those other discussions. All I was showing was that using SQLite for concurrent write/read is entirely possible even with high throughput.

Yes, you changed the title of the thread, which was:

Should I use SQLite as my web server database?

in a sense a rhetorical question.

I probably should have just commented: "I disagree" or, better, avoid writing anything, both here and elsewhere.

 

[EnriqueGonzalez]

Sqlite has an in memory mode (https://sqlite.org/inmemorydb.html) it would be interesting to see this comparison with it,

I mean for small apps is okey but as the user base grows may be it could shine as a cache system like REDIS.

 

[OliverA]

BTW, even though H2 seems to trounce SQLite in the OPs tests, the tests were performed on the same machine as the server application. One may find that these numbers are meaningless once a network is involved, since the network itself may be saturated before the top numbers of the DBs can be realized.

Unless one or more features that @LucaMs lists are required, SQLite seems to make an excellent embedded database that requires low resources (be it computer or human). Even if something seems to be required in @LucaMs's list, testing would need to be performed to see if that really affects the performance of the user's implementation.

 

[LucaMs]

Unless one or more features that @LucaMs lists are required,

I think the first 3 points are important (especially the second and third)

 

[tchart]

BTW, even though H2 seems to trounce SQLite in the OPs tests, the tests were performed on the same machine as the server application. One may find that these numbers are meaningless once a network is involved, since the network itself may be saturated before the top numbers of the DBs can be realized.

Apologies, I should clarify that the use of H2 for the test was using embedded mode. I will upload the updated project.

Dim driver As String = "org.h2.Driver"
Dim url As String = $"jdbc:h2:${File.DirApp}\report.h2;MODE=PostgreSQL;DATABASE_TO_LOWER=TRUE"$
DB.Initialize2(driver,url,"sa","sa")

As you say all of this is theoretical (best case? worst case?) since there is no network latency. It is very unlikley that any web application would be seeing this many simultaneous request as they would no doubt be spread out and latency would introduce natural delays between requests.

 

[OliverA]

I think the first 3 points are important (especially the second and third)

• it does not allow stored procedures
• it does not provide the management of access permissions; delegated to the sw with which it interacts with the db or to the access permissions to the file system
• it has no real concurrency management

a) I've seen plenty of web projects that do not use any stored procedures. Many write code. Why? To remain more DB backend neutral
b) Many web projects do not use a DBMS's permission system. They use their own tables for user information, user permissions, and role assignments
c) At what point will this affect SQLite's performance?

Yes, the first two points can be very important, if part of an application design is to rely on these features. As to the third point, testing would need to be done if an applications usage scenario would actually benefit from a "proper concurrency management".

 

[tchart]

Even if something seems to be required in @LucaMs's list, testing would need to be performed to see if that really affects the performance of the user's implementation.

Also sometimes you just need to be realistic about how much use your web application will get. One of my customers (a large multi-national) sees a maximum of 12000 transactions per hour for one of their high use web applications - that is only 200 transactions per second - I just checked the stats over a period of 30 days. The average is actually only 28 transactions per second, and 90th percentile is 100 transactions per second (ie 90% of the time there is less than 100 transactions per second).

This is all backed by huge infrastructure in Azure using Azure SQL (this isnt my application BTW, just one of the apps I look after).

So the fact that jServer and SQLite can handle 500, 800 or even 5000 transactions per second is amazing but unrealistic in most real world scenarios.

Merely dismissing SQLite because of lack of features does not mean it is not fit for purpose.

 

[LucaMs]

c) At what point will this affect SQLite's performance?

I was not referring to performances (already a long time ago I read a thread by Erel in this regard and for me that test was more than enough) but to concurrency and the rest (even permissions, certainly yes).