Sqlite concurrent writing performance

user_5390982 0
Viewed 5813

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!

go sqlite
2 Answers

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

user_16164240
Related