Memory leaks when using db connections within a for loop

Question

I am trying to continuously query Postgres Database within an infinite for loop. However, it seems that the queries are not closing, the garbage collector not working probably and I face memory allocation problems.

The script design is as following:

In func main() I declare a global db connection. Then execute func checkCross(...) which contains the for loop which with each iteration executes the func importPrices(...) which in it's turn import rows from the database.

import (
    "database/sql"
    _"github.com/lib/pq"
    "runtime"
    )


var db *sql.DB

func main() {
    var err error

    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+" dbname=%s password=%s ", host, port, user, dbname, password)

    //connection
    db, err = sql.Open("postgres", psqlInfo)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    checkCross("EUR_USD")

    ...

func checkCross(...)

   func checkCross(instrument string) {

    for i := 1; i < 4; i++ {
            rawData, _ := importPrices(instrument)

            var mem runtime.MemStats
            runtime.ReadMemStats(&mem)
            fmt.Printf("alloc [%v] \t heapAlloc [%v] \n", mem.Alloc, mem.HeapAlloc)

            ...

        }

func importPrices(...)

func importPrices(instrument string) ([]float64, []time.Time) {

    var price_ float64
    var date_ time.Time

    price := make([]float64, 10000000)
    date := make([]time.Time, 10000000)

    queryCommand := fmt.Sprintf("SELECT * FROM table where instrument = '%[1]v' ORDER BY 1 ASC;", instrument)

    rows, err := db.Query(queryCommand)
    if err != nil {
        log.Fatal(err)
    }


    //scan from db
    z := 0
    for rows.Next() {
        err := rows.Scan(&date_, &price_)
        if err != nil {
            log.Fatal(err)
        }
        price[z] = price_

        date[z] = date_

        z+=1
    }

    price = price[:z] 
    date = date[:z] 


    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }

    rows.Close()
    return price, date

Output

alloc [321664064]        heapAlloc [321664064] 
alloc [641723264]        heapAlloc [641723264] 
alloc [961781984]        heapAlloc [961781984]

Can you please guide me where is the issue in my code or approach? I've read some articles and they recommend to use rows.Close() within the for loop instead of defer rows.Close() but this solution didn't work.

Answer 1

You're allocating two slices of 10M elements each (float64 is 8 bytes and time.Time is bigger) on each call to importPrices.

Sure, most of those slices eventually become garbage (supposedly, you overwrite some variables with those two slices returned from importPrices), but that garbage will naturally float until the Go runtime powering your program decides to collect it. Until that happens, the memory consumption will grow.

It's crucial to understand that the two "standard" implementations of the language (of which you supposedly is using one) feature scanning GC which is working in parallel with the main program flow. In other words, when the program loses the last live reference to an allocated memory block, nothing happens in that very moment with regard to the memory that block occupies—it'll have to wait until the GC will find it and then collect. The exact moment in time when this will happen is mostly unpredictable; Go features complex dynamic algorithm to pace the GC in a way it strikes some balance between the amount of CPU cycles spent on collecting the garbage and letting the memory consumption grow.

As to the problem at hand, the usual approach is to simply reuse the allocated memory between iterations. That is, allocate your slices up front before calling importPrices, pass them there, and then reset them to the zero length before passing them in again.