I am getting too many open connections when the connection is kept alive

Question

I have a simple Go file with a purpose of returning a json response of a client's information. When I am benchmarking this script with apache benchmark, and the requests are kept alive

ab -t 10s -kc 1000 http://127.0.0.1:8080/clients/show/1

But when the requests are not kep alive, I do not suffer from this panic

ab -t 10s -c 1000 http://127.0.0.1:8080/clients/show/1

error:

2018/10/26 03:26:42 http: panic serving 127.0.0.1:44800: Error 1040: Too many connections goroutine 220522 [running]: net/http.(*conn).serve.func1(0xc001779e00)

My code:

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/gorilla/mux"
    "net/http"
    "runtime"
)

type Client struct {
    ID         int    `json:"id"`
    UserID     int    `json:"user_id"`
    Name       string `json:"name"`
    Telephone  string `json:"telephone"`
    Email      string    `json:"email"`
    Category   sql.NullString `json:"string"`
    Notes      string `json:"notes"`
    Additional sql.NullString `json:"additional"`
    CreatedAt  sql.NullString `json:"created_at"`
    UpdatedAt  sql.NullString `json:"updated_at"`
    DeletedAt  sql.NullString `json:"deleted_at"`
}

func main() {
    runtime.GOMAXPROCS(runtime.NumCPU())
    r := mux.NewRouter()

    r.HandleFunc("/clients/show/{id}", showClient).Methods("GET")

    http.ListenAndServe(":8080", r)
}

func handler(w http.ResponseWriter, r *http.Request) {
    fmt.Fprintf(w, "Hello World!")
}

func showClient(w http.ResponseWriter, r *http.Request) {

    db, err := sql.Open("mysql", "root@tcp(127.0.0.1:3306)/crm")

    if err != nil {
        panic(err.Error())
    }

    var client Client

    // Execute the query
    err = db.QueryRow("SELECT * FROM clients where id = ?", 1).Scan(
        &client.ID,
        &client.UserID,
        &client.Name,
        &client.Telephone,
        &client.Email,
        &client.Category,
        &client.Notes,
        &client.Additional,
        &client.CreatedAt,
        &client.UpdatedAt,
        &client.DeletedAt,
    )

    if err != nil {
        panic(err.Error())
    }

    db.Close()

    json.NewEncoder(w).Encode(client)
}

Can someone please explain why this happens at such a low concurrent requests rate and what the correct way to fix this is.

When you call `sql.Open`, you are getting a pool of connections that should be re-used. You should open it before starting your server and let your handlers reference the one pool. — Gavin, Oct 26 '18 at 04:51
https://golang.org/pkg/database/sql/#Open > The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB. — Berkant İpek, Oct 26 '18 at 05:08

score 0 · Accepted Answer · edited May 04 '21 at 09:22

There are two problems, the program use of sockets and the server configuration

Each connection probably requires 2 sockets, one to the database and one to the http client. Moving the sql.Open() to a different scope so it is not repeatedly called would help with the database sockets. For the http server, the defaults are actually quite low - close connections if there are more than 2 unused keep alives

Additionally, your server probably needs modification

As this answer discusses, the default connections per second for Linux are quite conservative, and a small simple Go server could well overwhelm it.

Tune the server in line with the above answer and that should help.

I am getting too many open connections when the connection is kept alive

1 Answers1