I'm having issues getting Go to play nicely with SQLite, I did it in the past without problems but it has been some time and I can't remember what I did to get it to work properly. I'm using Go along with the mattn/go-sqlite3 package to process and insert a lot of data into an SQLite database but somehow Go always ends up eating all my RAM until if finally exits with an error status code. Just to make sure I had isolated the memory starvation problem to SQLite I wrote the following simple program to test it out:
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/mattn/go-sqlite3"
)
func main() {
db, err := sql.Open("sqlite3", "./test.db"); if err != nil {
log.Fatal(err)
}; defer db.Close()
ddl := `
PRAGMA automatic_index = ON;
PRAGMA cache_size = 32768;
PRAGMA cache_spill = OFF;
PRAGMA foreign_keys = ON;
PRAGMA journal_size_limit = 67110000;
PRAGMA locking_mode = NORMAL;
PRAGMA page_size = 4096;
PRAGMA recursive_triggers = ON;
PRAGMA secure_delete = ON;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA journal_mode = WAL;
PRAGMA wal_autocheckpoint = 16384;
CREATE TABLE IF NOT EXISTS "user" (
"id" TEXT,
"username" TEXT,
"password" TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS "id" ON "user" ("id");
`
_, err = db.Exec(ddl); if err != nil {
log.Fatal(err)
}
queries := map[string]*sql.Stmt{}
queries["user"], _ = db.Prepare(`INSERT OR REPLACE INTO "user" VALUES (?, ?, ?);`); if err != nil {
log.Fatal(err)
}; defer queries["user"].Close()
tx, err := db.Begin(); if err != nil {
log.Fatal(err)
}
for i := 0; i < 10000000; i++ {
user := map[string]string{
"id": string(i),
"username": "foo",
"password": "bar",
}
_, err := tx.Stmt(queries["user"]).Exec(user["id"], user["username"], user["password"]); if err != nil {
log.Fatal(err)
}
if i % 32768 == 0 {
tx.Commit()
db.Exec(`PRAGMA shrink_memory;`)
tx, err = db.Begin(); if err != nil {
log.Fatal(err)
}
fmt.Println(i)
}
}
tx.Commit()
}
When I run the above code, Go eats more than 100 MiB of memory every second without every releasing any, after a minute or so it ends up consuming 6/7 GiB and then the process gets killed. I've tried variations with and without defining the SQLite PRAGMAs but with no luck.
According to the defined PRAGMAs, SQLite should never use more than 128 MiB of RAM.
Have I made any mistake or is there something wrong with either mattn/go-sqlite3 or Go GC?
Profiling with davecheney/profile as per these instructions yields this not so helpful output:
alix@900X4C:~/Go/src$ go tool pprof --text ./test /tmp/profile102098478/mem.pprof
Adjusting heap profiles for 1-in-4096 sampling rate
Total: 0.0 MB
0.0 100.0% 100.0% 0.0 100.0% runtime.allocm
0.0 0.0% 100.0% 0.0 100.0% database/sql.(*DB).Exec
0.0 0.0% 100.0% 0.0 100.0% database/sql.(*DB).conn
0.0 0.0% 100.0% 0.0 100.0% database/sql.(*DB).exec
0.0 0.0% 100.0% 0.0 100.0% github.com/mattn/go-sqlite3.(*SQLiteDriver).Open
0.0 0.0% 100.0% 0.0 100.0% github.com/mattn/go-sqlite3._Cfunc_sqlite3_threadsafe
0.0 0.0% 100.0% 0.0 100.0% main.main
0.0 0.0% 100.0% 0.0 100.0% runtime.cgocall
0.0 0.0% 100.0% 0.0 100.0% runtime.gosched0
0.0 0.0% 100.0% 0.0 100.0% runtime.main
0.0 0.0% 100.0% 0.0 100.0% runtime.newextram
This was just for 1000000 iterations and memory still grows like there's no tomorow.
I also tried the same code on two MacBook Pros, both running the latest version of Go from brew (1.3.1), in one of them the memory usage topped 10 GiB and the other averaged 2 GiB of RAM consumption. This looks like an odd behavior, what can I do to trace down the discrepancies and fix the memory hogging?
