SQLite 3 not releasing memory in Golang

Question

I'm having issues getting Go to play nicely with SQLite. I fixed 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 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 it 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:

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, and 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?

Answer 1

I'm unable to reproduce your results. It uses about 100 MiB of memory.

$ go version
go version devel +7ab3adc146c9 Sun Oct 19 10:33:50 2014 -0700 linux/amd64
$ sqlite3 --version
3.8.2 2013-12-06 14:53:30 27392118af4c38c5203a04b8013e1afdb1cebd0d
$ go get -v github.com/mattn/go-sqlite3
github.com/mattn/go-sqlite3 (download)
github.com/mattn/go-sqlite3
$ go run simple.go
0
32768
65536
<SNIP>
9928704
9961472
9994240
$

A runtime.MemStats records statistics about the Go memory allocator. It does not include memory managed by SQLite. For example, at the end of the program,

var ms runtime.MemStats
runtime.ReadMemStats(&ms)
fmt.Println(
    ms.Alloc,      // bytes allocated and still in use
    ms.TotalAlloc, // bytes allocated (even if freed)
    ms.Sys,        // bytes obtained from system (sum of XxxSys below)
    ms.Mallocs,    // number of mallocs
    ms.Frees,      // number of frees
)

Output:

12161440 7953059928 18757880 160014535 159826250

And it also works on Go 1.4 Beta 1

$ go version
go version go1.4beta1 linux/amd64

Answer 2

This has been discussed on GitHub at length. I'm posting here to help

https://github.com/mattn/go-sqlite3/issues/157

Solution was found, issue had to do with this statement:

_, err := tx.Stmt(queries["user"]).Exec(user["id"], user["username"], user["password"]); 

this is throwing away the rows and the code following never calls Close().

This version of the test program works correctly

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
    "log"
)

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",
        }

        rows, err := tx.Stmt(queries["user"]).Exec(user["id"], user["username"], user["password"])
        if err != nil {
            log.Fatal(err)
        }
        // CLOSE ROWS HERE!
        rows.Close()

        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()
}