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.

Using `rows.Close()` is fine when you have a single exit point to your function, otherwise `defer rows.Close()` is better to make sure it doesn't get missed. You should [profile](https://golang.org/pkg/runtime/pprof/) your program to figure out where the memory is going. This could be perfectly normal. — Marc, Dec 29 '19 at 12:42
Why are you preallocating 10000000 length slices? Are you actually loading that many rows every time this function is called? Are you aware that `date = date[:z]` just changes the window into the underlying array that the slice reveals, and **does not** free the extra elements for garbage collection? — Adrian, Dec 30 '19 at 04:36
@Adrian I am loading more than 1M rows so instead of dynamically allocating the resources I decided to preallocate with a constant number (I've noticed this approach is faster). The ```date = date[:z]``` part is to free the unused resources. However, as suggested in the answer below, at the end of the loop I used ```date = nil``` and ```price = nil```, but this step didn't help neither. — Kingindanord, Dec 30 '19 at 10:50
It's faster but it wastes memory. As I noted, `date = date[:z]` doesn't free anything. The underlying slice is still there so those elements cannot be garbage collected. Have you considered preallocating the actual number of rows returned by your query instead of an arbitrary constant? — Adrian, Dec 30 '19 at 13:44
@Adrian yes but that means I have to make an additional query to the database, I didn't find yet a way to get the rows count before scanning them. — Kingindanord, Dec 30 '19 at 13:58

score 3 · Accepted Answer · answered Dec 29 '19 at 18:56

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.

thanks for the explanation. Allocating the slices before calling the function didn't affect the memory allocation. Calling ```runtime.GC() ``` at the end of the for loop did the trick somehow though I am not sure if it has negative effects. — Kingindanord, Dec 29 '19 at 22:59
Don't ever call `runtime.GC()` in production code: it's intended for debugging purposes. Again, if you face (what you think is) an uncontrollable memory growth you either have a bug in your code or in the Go runtime or its stdlib. The latter is highly unlikely for the—I hope—apparent reasons unless, of course, you're using a bleeding edge version of Go. Since we do not access to the full code, we're unable to assess whether you've implemented slice reusing correctly or not. And of course memory churn can also happen in other places of the code. _Profiling_ is the only answer to this "riddle". — kostix, Dec 30 '19 at 11:58

Memory leaks when using db connections within a for loop

1 Answers1