dqdl6469
dqdl6469
2014-10-19 22:48

SQLite 3没有在Golang中释放内存

已采纳

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

3条回答

  • dongping1922 dongping1922 7年前
    点赞 评论 复制链接分享
  • dongxie45083 dongxie45083 7年前

    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
    
    点赞 评论 复制链接分享
  • doucao8982 doucao8982 7年前

    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()
    }
    
    点赞 评论 复制链接分享

相关推荐