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

 

[OliverA]

concurrency

Concurrency can be a tricky thing. If SQLite processes request efficiently enough sequentially, concurrency is not really an issue (depending on workload/environment). In my own testing, SQLite (using it via jRDC2) was not the bottleneck, but my own gigabit-speed LAN was.

Running jRDC2 on a bench computer consisting of a Celeron, 4GB RAM and 64GB (32?) SSD, simulating 30 clients (not running on the server) hammering the server with inserts for 60 seconds, I was able to create over 14K records. Each record contained a JPG of ~ 30KB. Without the images, 10 clients hammering the server were able to create 90K records (albeit small records). So whatever concurrency may be missing from SQLite may not be an issue depending on the type of workload it is asked to take care of.

Using MySQL (and pooling), 15 clients hammering with no PICs created 78K records. 20 clients hammering with pictures created over 12K records. H2 as a backend (using pooling), 15 clients created 15K records with pictures and 83K records without pictures.

 

[LucaMs]

was not the bottleneck

I was not referring to any bottlenecks, but to the risk of accessing the same data by multiple users without the necessary blocking.

 

[OliverA]

but to the risk of accessing the same data by multiple users without the necessary blocking.

Reading is not an issue, and writing was solved with WAL mode

 

[Indic Software]

SQLite is a very powerful database engine which is used by many servers.

We had used a RDBMS (in distant past) called ValentinaDB SQLite Server.

And it is working faultlessly even today!

 

[j_o_h_n]

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

View attachment 113105

WAL On

View attachment 113106

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

View attachment 113156

Those numbers for sqlite look insanely good to me particularly since writing requires exclusive control.
It also tells me how little I know about the capacity of databases
I'm using postgresql as the backend for a jserver that probably will never receive more than a few requests per second if it gets busy
and I was worrying about how it would cope!
BTW I would love to see you expand this to other databases especially postgres and mysql if possible (off topic I know feel free to ignore)

 

[tchart]

Those numbers for sqlite look insanely good to me particularly since writing requires exclusive control.
It also tells me how little I know about the capacity of databases
I'm using postgresql as the backend for a jserver that probably will never receive more than a few requests per second if it gets busy
and I was worrying about how it would cope!
BTW I would love to see you expand this to other databases especially postgres and mysql if possible (off topic I know feel free to ignore)

@j_o_h_n exactly what I was trying to show. A lot of developers will just instinctively use a full blown RDBMS for a small application. It just complicates things and is usually not required.

I've been using enterprise databases for 20+ years (Oracle, SQL Server, Postgres etc). I'm even certified for SQL Server but it's not my go to for most applications.

In most cases there is no advantage in going for a full blown RDBMS and it generally will land you with more admin overhead for little gain. This is especially the case if you are not familiar with configuring and administering these databases.

As also mentioned there are some other options besides SQLite, like H2, which offer an easier pathway to a full blown RDBMS through compatibility modes. This can make scaling an app much easier if you find you need to.

But for any of my apps I have not hit any road blocks with SQLite - and I can tell you for experience that my customers appreciate not having to deploy yet another database when they install my apps.

 

[ilan]

@tchart thank you very much for making those tests. i just accidentally saw this thread. i guess that if you include a member with @ he does not get any notification about it.

i also made some tests and found out that SQLite is really powerful especially if u have a lot of entries you want to perform and use

BeginTransaction

running 1000 inserts in a loop would take about 8 seconds but doing it like this:

    SQL1.BeginTransaction
    Try
        'block of statements like:
        For i = 1 To 1000
            SQL1.ExecNonQuery("INSERT INTO table1 VALUES(...)
        Next
        SQL1.TransactionSuccessful
    Catch
        Log(LastException.Message)
        SQL1.RollBack 'no changes will be made
    End Try

took me few ms.

i think for a small-middle eCommerce site SQLite can be a reliable choice. although some limitations did make me think again if i should use it or not.

like the much lower dataType i can use and the fact that you cannot make 2 calls to the db at the same time so each call blocks the db (as far as i have understoud)
but again i am working on a ecommerce site so it should be fine enough.