[Advice Needed] What is a proper way to query concurrently?

[zzxgzgz]

Hi Devs,

First I'd like to thank you for make this amazing tool, it made my life much easier when interacting with SQLite3 in Go.

I'm doing some experiement on SQLite3 and trying to get some performance data, using some simple code I wrote: https://github.com/zzxgzgz/SQLite_Multithreading_Go

The code tries to do the following thing:

1. Create a job dispatcher, and${numberOfCores} * 2 workers.
2. Open the db ${numberOfCores} * 2 time, and save the connections for later use.
3. Create a simple table people that has id as primary key, firstName and lastName as the data columns and have them indexed.
4. Generate some random names for each people.
5. Insert these people into the DB, in a sequential manner, in order to prevent problems caused by multiple writers.
6. Prepare the select statements using the db connections, one statement per connection, and try to read all rows from the DB using a query SELECT id, firstname, lastname FROM people WHERE firstname = ? AND lastname = ? using all the prepared statement concurrently.

To my surprise, concurrent read is much slower than sequential write:

# ./main
2022/03/11 13:25:04 Hello world!
2022/03/11 13:25:04 All 112 workers are running, now you may dispatch jobs.
2022/03/11 13:25:19 Gernated 20971520 people!
2022/03/11 13:31:55 Inserting 20971520 people took 6m35.770298756s
^[[A^[[A^[[C2022/03/11 15:19:35 To query 20971520 people, it took time: 1h47m40.500086021s

The .db file is ~1.1Gb in size, if that matters.

I'm wondering if this read performance is normal, or I am doing it wrong? What is some good rules to follow for concurrent reads?

Thank you very much for your time and help.

[rittneje]

Note that you need to prefix your connection string with file: for the various options to be interpreted properly. Also, you should not call sql.Open once per worker, as sql.DB itself represents a pool, not an individual connection.

My general recommendation is to make two pools (as in two sql.DBs), one with mode=ro and one with mode=rw. Use wal mode (_journal_mode=wal), which will allow reads to happen concurrently with writes. Do not use shared cache mode. Throttle the read/write pool to a single connection using SetMaxOpenConns, as SQLite doesn't support multiple concurrent writers anyway. The read-only pool should be throttled as per your application requirements and system constraints. The read/write pool should also use BEGIN IMMEDITATE when starting transactions (_txlock=immediate), to avoid certain issues that can result in "database is locked" errors.

[zzxgzgz]

Note that you need to prefix your connection string with file: for the various options to be interpreted properly. Also, you should not call sql.Open once per worker, as sql.DB itself represents a pool, not an individual connection.

My general recommendation is to make two pools (as in two sql.DBs), one with mode=ro and one with mode=rw. Use wal mode (_journal_mode=wal), which will allow reads to happen concurrently with writes. Do not use shared cache mode. Throttle the read/write pool to a single connection using SetMaxOpenConns, as SQLite doesn't support multiple concurrent writers anyway. The read-only pool should be throttled as per your application requirements and system constraints. The read/write pool should also use BEGIN IMMEDITATE when starting transactions (_txlock=immediate), to avoid certain issues that can result in "database is locked" errors.

Thank you for your precious insights and advice! Let me try to make the change according to them and see how it performs.

Thanks again.

[zzxgzgz]

I tried to update the code according to the advice above, and tested it again, and here is the result:

root@fw0016589:/home/user/src/github.com/zzxgzgz/SQLite_Multithreading_Go# ./main
2022/03/16 14:41:39 Hello world!
2022/03/16 14:41:39 All 112 workers are running, now you may dispatch jobs.
2022/03/16 14:41:54 Gernated 20971520 people!
2022/03/16 14:50:48 Inserting 20971520 people took 8m54.187231461s
2022/03/16 16:42:39 To query 20971520 people, it took time: 1h51m50.105310237s

It actually got slower somehow.

I have another question @rittneje, you mentioned to also throttle the read-only pool, do you have any recommandations about the max open connections for it? In my update code, I set it to the ${numberOfCores} * 2, is this the right way to do it?

Also, I'm still trying to understand why the read performance is so bad, could you please point me into the right direction?

I ran this perftest provided by Bedrock, in this post it mentioned that they are able to achieve 4 million QPS in a single server. I ran the same test, and the results I got from it is pretty good:

Thu Mar 17 10:30:58 PDT 2022  Starting: -vms unix-excl -locking_mode NORMAL (./perftest, /home/user/src/github.com/Expensify/Bedrock/perftest/test.db)
./perftest -csv -numa -numastats -mmap -linear -vms unix-excl -locking_mode NORMAL -testSeconds 60 -maxNumThreads 256 -dbFilename /home/user/src/github.com/Expensify/Bedrock/perftest/test.db
Enabling NUMA awareness:
numa_available=0
numa_max_node=1
numa_pagesize=4096
numa_num_configured_cpus=56
numa_num_task_cpus=56
numa_num_task_nodes=2
numThreads, maxQPS, maxQPSpT
1, 46024, 46024
2, 91274, 45637
3, 137404, 45801.3
4, 180705, 45176.2
5, 225147, 45029.4
6, 272936, 45489.3
7, 314279, 44897
8, 364338, 45542.2
9, 405119, 45013.2
10, 448852, 44885.2
11, 494748, 44977.1
12, 537469, 44789.1
13, 591314, 45485.7
14, 637572, 45540.9
15, 678796, 45253.1
16, 730028, 45626.8
17, 770238, 45308.1
18, 815726, 45318.1
19, 858698, 45194.6
20, 907344, 45367.2
21, 951636, 45316
22, 994060, 45184.5
23, 1041419, 45279.1
24, 1083378, 45140.8
25, 1128111, 45124.4
26, 1169421, 44977.7
27, 1216605, 45059.4
28, 1257847, 44923.1
29, 1260620, 43469.7
30, 1266371, 42212.4
31, 1268080, 40905.8
32, 1266702, 39584.4
33, 1275697, 38657.5
34, 1285441, 37807.1
35, 1279162, 36547.5
36, 1285150, 35698.6

It is able to achieve a max QPS of over 1 million. I'm trying to do something similar here with this library. Is it a way to do it?

Thank you for your time and help.

[rittneje]

One thing I will suggest is running pragma wal_checkpoint after committing the insert transaction, as that will merge the WAL file back into the main database file, which will make subsequent reads faster.

Also, right now you are spawning one goroutine per job. Instead, I would suggest cutting out the extra goroutine in the middle, and have the worker routines directly pull from the job channel. This will stop your program from spawning millions of goroutines. https://github.com/zzxgzgz/SQLite_Multithreading_Go/blob/5eebf73f8b5b9ab09981b37456c72349983be2d1/worker_pool/woker_pool.go#L97-L107

[zzxgzgz]

One thing I will suggest is running pragma wal_checkpoint after committing the insert transaction, as that will merge the WAL file back into the main database file, which will make subsequent reads faster.

Also, right now you are spawning one goroutine per job. Instead, I would suggest cutting out the extra goroutine in the middle, and have the worker routines directly pull from the job channel. This will stop your program from spawning millions of goroutines. https://github.com/zzxgzgz/SQLite_Multithreading_Go/blob/5eebf73f8b5b9ab09981b37456c72349983be2d1/worker_pool/woker_pool.go#L97-L107

Thank you again for your advice.

One more question: What is the difference between *sql.Stmt.Query() and *sql.Db.Query()? Do they have the same/similar performance? For reading the database, which is is preferred?

Thank you.

[rittneje]

A *sql.Stmt is a prepared statement, which is prepared ("compiled") once, and can be reused multiple times. Meanwhile, *sql.Db.Query is a one-shot query. Note that for SQLite specifically, *sql.Db will ultimately create a prepared statement anyway, it just gets automatically disposed of once the query completes. (This is not true for all SQL dialects.)

If you are going to run the same query a lot, then using *sql.Stmt will provide some performance benefits, as the underlying database engine will only have to parse the SQL to bytecode once (per connection). If however you will only be running a query once, or generally infrequently, then *sql.Db.Query will be simpler to use.

Note that parameter values are not part of the prepared statement. For example, you could prepare the query SELECT * FROM MyTable WHERE Id = ? and then run it multiples with different values for the bound parameter each time.

Also note that if you create a prepared statement via *sql.Db.Prepare, and you then need to use it within a transaction, it will need to be "re-prepared" via *sql.Tx.Stmt.

[joefrancia]

Is there any downside to long-lived prepared statements with this driver? I have a number of server-side apps where I prepare a bunch of statements during start up and keep them around for the entire life of the app, many of which are used tens or hundreds of thousands of times daily. I haven't noticed any issues doing this, so it's more just curiosity about best practices

[rittneje]

In SQLite, a prepared statement will require some amount of (C-allocated) memory in order to store the "compiled" statement. In addition, because of the way Go's database/sql package works, it will lazily create a prepared statement per connection as needed. Consequently, the larger your sql.DB connection pool grows, the more memory it will need for all the prepared statement copies. For this and other reasons, it is recommended to configure your sql.DB pool via SetConnMaxIdleTime, SetConnMaxLifetime, SetMaxIdleConns, and SetMaxOpenConns.

[zzxgzgz]

So I found a post online, and the OP was also trying to do some performance testing using SQLite and this library, here is the link. I tried that code, and I was able to get ~ 100k query/second.

./main
SQLite start
insert span= 62 read span= 107 avg read= 0.0107

10 million rows were inserted in 62 seconds and read in 107 seconds, so the qps is 10,000,000 / 107 = 93457.94.

I realized that what I'm trying to achieve is not neccessarily concurrent read, I'm more interested in increasing the database's QPS.

[zzxgzgz]

So I found a post online, and the OP was also trying to do some performance testing using SQLite and this library, here is the link. I tried that code, and I was able to get ~ 100k query/second.

./main
SQLite start
insert span= 62 read span= 107 avg read= 0.0107

10 million rows were inserted in 62 seconds and read in 107 seconds, so the qps is 10,000,000 / 107 = 93457.94.

I realized that what I'm trying to achieve is not neccessarily concurrent read, I'm more interested in increasing the database's QPS.

I just realized that the above test results are achieved using the memory mode, which is not my intent. I removed the :momery: but I didn't remove the mode=memory when I had the test.

After making the change and retesting it, I go got the following results:

./main
SQLite start
insert span= 60 read span= 217 avg read= 0.0217

It took more than twice as long when it is not using the memory mode, which brings the qps to 10,000,000 / 217 = 46082.94 qps.

This is a huge difference compared with the Bedrock test. I wonder if there is any way to achieve a similar level of performance using this library?

Another observation I have is, writing into the database seems faster than reading from it. This is counter intuitive to me, I always thought that writing takes more time than reading. Is this the normal case for SQLite?

Thank you.

[iwind]

So I found a post online, and the OP was also trying to do some performance testing using SQLite and this library, here is the link. I tried that code, and I was able to get ~ 100k query/second.

./main
SQLite start
insert span= 62 read span= 107 avg read= 0.0107

10 million rows were inserted in 62 seconds and read in 107 seconds, so the qps is 10,000,000 / 107 = 93457.94. I realized that what I'm trying to achieve is not neccessarily concurrent read, I'm more interested in increasing the database's QPS.

I just realized that the above test results are achieved using the memory mode, which is not my intent. I removed the :momery: but I didn't remove the mode=memory when I had the test.

After making the change and retesting it, I go got the following results:

./main
SQLite start
insert span= 60 read span= 217 avg read= 0.0217

It took more than twice as long when it is not using the memory mode, which brings the qps to 10,000,000 / 217 = 46082.94 qps.

This is a huge difference compared with the Bedrock test. I wonder if there is any way to achieve a similar level of performance using this library?

Another observation I have is, writing into the database seems faster than reading from it. This is counter intuitive to me, I always thought that writing takes more time than reading. Is this the normal case for SQLite?

Thank you.

the article you mentioned says: we’re just about to roll out an entirely new generation of self-hosted hardware with the following basic specs: 1TB of DDR4 RAM 3TB of NVME SSD storage 192 physical 2.7GHz cores (384 with hyperthreading)

Did you run the test under the same condition?