o-az/sqlite-60k-rps.md

## sqlite-60k-rps.md

      
    Raw
  

              sqlite-60k-rps.md
            
          
Configuring PRAGMAs. We need to send the following PRAGMA commands right after opening the connection:

PRAGMA journal_mode = WAL;

enables write-ahead log so that your reads do not block writes and vice-versa.

PRAGMA busy_timeout = 5000;

sqlite will wait 5 seconds to obtain a lock before returning SQLITE_BUSY errors, which will significantly reduce them.

PRAGMA synchronous = NORMAL;

sqlite will sync less frequently and be more performant, still safe to use because of the enabled WAL mode.

PRAGMA cache_size = -20000;

negative number means kilobytes, in this case 20MB of memory for cache.

PRAGMA foreign_keys = true;

because of historical reasons foreign keys are disabled by default, we should manually enable them.

PRAGMA temp_store = memory;

moves temporary tables from disk into RAM, speeds up performance a lot.

Do NOT use cache=shared! Some tutorials recommend configuring it, but this is how you get nasty SQLITE_BUSY errors. It is disabled by default, so you don't have to do anything extra.


Use immediate transactions
If you know that transaction can possibly do a write, always use BEGIN IMMEDIATE or you can a get SQLITE_BUSY error. Check your framework,  you should be able to set this at the connection level.


Open two connection pools
Another trick is to open 2 connection pools, one for reads only and another for reads/writes. Set the connection limit of write pool to 1, and the connection limit of the read pool to some reasonably high number, e.g. number of your CPU cores.


Bonus: how I configure sqlite with Go
Here is the code in go I use to configure the sqlite connections:


func SQLiteDbString(file string, readonly bool) string {

connectionParams := make(url.Values)
connectionParams.Add("_journal_mode", "WAL")
connectionParams.Add("_busy_timeout", "5000")
connectionParams.Add("_synchronous", "NORMAL")
connectionParams.Add("_cache_size", "-20000")
connectionParams.Add("_foreign_keys", "true")
if readonly {
connectionParams.Add("mode", "ro")
} else {
connectionParams.Add("_txlock", "IMMEDIATE")
connectionParams.Add("mode", "rwc")
}

return "file:" + file + "?" + connectionParams.Encode()
}

func OpenSqliteDatabase(file string, readonly bool) (*sql.DB, error) {

dbString := SQLiteDbString(file, readonly)
db, err := sql .Open("sqlite3", dbString)

pragmasToSet := []string{
"temp_store=memory",
}

for _, pragma := range pragmasToSet {
_, err = db.Exec("PRAGMA " + pragma + ";")
if err != nil {
return nil, err
}
}

if readonly {
db.SetMaxOpenConns(max(4, runtime.NumCPU()))
} else {
db.SetMaxOpenConns(1)
}

return db, nil
}