Sqlite concurrent writing performance

I'm writing a website with Golang and Sqlite3, and I expect around 1000 concurrent writings per second for a few minutes each day, so I did the following test (ignore error checking to look cleaner):

t1 := time.Now()
tx, _ := db.Begin()
stmt, _ := tx.Prepare("insert into foo(stuff) values(?)")
defer stmt.Close()

for i := 0; i < 1000; i++ {
	_, _ = stmt.Exec(strconv.Itoa(i) + " - ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789,./;'[]-=<>?:()*&^%$#@!~`")
}

tx.Commit()
t2 := time.Now()
log.Println("Writing time: ", t2.Sub(t1))

And the writing time is about 0.1 second. Then I modified the loop to:

for i := 0; i < 1000; i++ {
	go func(stmt *sql.Stmt, i int) {
		_, err = stmt.Exec(strconv.Itoa(i) + " - ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789,./;'[]-=<>?:()*&^%$#@!~`")
		if err != nil {
			log.Fatal(err)
		}
	}(stmt, i)
}

This gives me holy 46.2 seconds! I run it many times and every time is beyond 40 seconds! Sometimes even over a minute! Since Golang handles each user concurrently, does it mean I have to switch database in order to make the webpage working? Thanks!

I recently evaluated SQLite3 performance in Go myself for a network application and learned that it needs a bit of setup before it even remotely usable.

Turn on the Write-Ahead Logging

You need to use WAL PRAGMA journal_mode=WAL. That's mainly why you get such a bad performance. With WAL I can do 10000 concurent writes without transactions in a matter of seconds. Within transaction it will be lightning fast.

Disable connections pool

I use mattn/go-sqlite3 and it opens a database with SQLITE_OPEN_FULLMUTEX flag. It means that every SQLite call will be guarded with a lock. Everything will be serialized. And that's actually what you want with SQLite. The problem with Go in this situation is that you will get random errors that tell you that the database is locked. And the reason why is because of the way the sql/DB works inside. Inside it manages pool of connections for you, so it will open multiple SQLite connections and you don't want to do that. To solve this I had to, basically, disable the pool. Call db.SetMaxOpenConns(1) and it will work. Even on very high loads with tens of thousands of concurent reads and writes it works without a problem.

Other solution might be to use SQLITE_OPEN_NOMUTEX to run SQLite in multi-threaded mode and let it manage that for you. But SQLite doesn't really work in multi-threaded apps. Reads can happen in parallel but only one write at a time. You will get occasional busy errors which are completely normal for SQLite but will require you to do something with them - you probably don't want to stop a write operation completely when that happens. That's why most of the time people work with SQLite either synchronously or by sending calls to a separate thread just for the SQLite.

I tested the write performance on go1.18 to see if parallelism works

 

Out of Box

I used 3 golang threads incrementing different integer columns of the same record

Parallelism Conclusions:

• Read code 5 percentage 2.5%
• Write code 5 percentage 518% (waiting 5x in between attempts)
• Write throughput: 2,514 writes per second

code 5 is “database is locked (5) (SQLITE_BUSY)”

A few years ago on Node.js the driver crashes with only concurrency, not parallelism, unless I serialized the writes, ie. write concurrency = 1

 

Serialized Writes

With golang is used github.com/haraldrudell/parl.NewModerator(1, context.Background()), ie. serialized writes:

Serialized results:

• read code 5: 0.005%
• write code 5: 0.02%
• 3,032 writes per second (+20%)

Reads are not serialized, but they are held up by writes in the same thread. Writes seems to be 208x more expensive than reads.

Serializing writes in golang increases write performance by 20%

 

PRAGMA journal_mode

Enabling sqlDB.Exec("PRAGMA journal_mode = WAL")
(from default: journalMode: delete)

increases write performance to 18,329/s, ie. another 6x

• code 5 goes to 0

 

Multiple Processes

Using 3 processes x 3 threads with writes serialized per process lowers write throughput by about 5% and raises code 5 up to 200%. Good news is that file locking works without errors macOS 12.3.1 apfs