Sharing connections between goroutines

[ChristianGerdes]

Hi,

We're building an API using net/http (server) and github.com/gorilla/mux (router) and have some difficulties understanding how to best manage open connections in an efficient way using upper/db for mysql.

I believe the underlying sql/database module manages a pool of connections that each goroutine can use when needed and then release it back to the pool for others to use. However, when running the following code in a stress test, we see some weird behavior.

https://go.dev/doc/database/manage-connections

According to your documentation, we should start a session (open an actual connection) and then specify pool configuration details like max open connections, max idle connection, and connection max idle time which seems a bit weird to do that on an open/active session (connection).

After the session (connection) has been established, it's assigned to the global DB variable and used throughout the application including goroutines.

The weird behavior is that under stress, upper sometimes log slow queries - which is fine. However, each log statement includes information about the session in use. All of our logs state that a given query was slow for the session with id 00001. Given that our max open connections is set to 10, I would expect to see different session id's between 00001-00010. It seems like the same session is always being used and not balanced between a pool of 10-15 connections.

I've tried opening the connection (session) in each router handler (goroutine) but we don't want to open too many connections to the database.

I know that something is wrong with my code and my understanding, but I haven't been able to find any good answers, so... I hope someone can help!

import (
	"fmt"
	"log"
	"net/http"
	"time"

	"github.com/gorilla/mux"
	"github.com/upper/db/v4"
	"github.com/upper/db/v4/adapter/mysql"
)

var DB db.Session

func init() {
	databaseSettings := mysql.ConnectionURL{
		Host:     "host",
		Database: "database",
		User:     "user",
		Password: "password",
	}

	session, err := mysql.Open(databaseSettings) // this actually opens a connection!

	if err != nil {
		log.Fatalln("Cant connect to database")
	}

	session.SetMaxOpenConns(10)
	session.SetMaxIdleConns(5)                   
	session.SetConnMaxIdleTime(time.Minute * 10)

	DB = session
}

func main() {
	router := mux.NewRouter()
	router.HandleFunc("/path", Index).Methods("GET")

	server := &http.Server{
		Handler:      router,
		Addr:         ":8080",
		ReadTimeout:  time.Second * 5,
		WriteTimeout: time.Second * 60 * 2,
	}

	if err := server.ListenAndServe(); err != nil {
		log.Fatalln(err)
	}
}

// This function is called within it's own goroutine
func Index(w http.ResponseWriter, r *http.Request) {
	w.WriteHeader(http.StatusOK)

	// Dont mind the actual query, it's just to show the connection in use
	iterator := DB.SQL().Select("table.column").From("table").Iterator()

	defer iterator.Close()

	// Scan and more etc.

	fmt.Fprintf(w, "OK")
}

[vertazzar]

Hello, I've been using this library for a project for some time now, and currently I'm testing out the v4 version.

This example is not correct, firstly the "Session ID: 00001" is not the connection number *sql.DB, and second to better simulate the workload, you can use mysql SLEEP() function:

package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/http"
	"time"

	"github.com/gorilla/mux"
	"github.com/upper/db/v4"
	"github.com/upper/db/v4/adapter/mysql"
)

var (
	DB  *sql.DB
	DBS db.Session
)

func init() {
	databaseSettings := mysql.ConnectionURL{
		Host:     "host",
		Database: "database",
		User:     "user",
		Password: "password",
	}

	session, err := mysql.Open(databaseSettings) // this actually opens a connection!

	if err != nil {
		log.Fatalln("Cant connect to database", err)
	}

	session.SetMaxOpenConns(10)
	session.SetMaxIdleConns(5)
	session.SetConnMaxIdleTime(time.Minute * 10)
	db.LC().SetLevel(db.LogLevelDebug)

	DBS = session
	DB = session.Driver().(*sql.DB)
}

func main() {
	router := mux.NewRouter()
	router.HandleFunc("/path", Index).Methods("GET")
	server := &http.Server{
		Handler:      router,
		Addr:         ":8080",
		ReadTimeout:  time.Second * 5,
		WriteTimeout: time.Second * 60 * 2,
	}

	if err := server.ListenAndServe(); err != nil {
		log.Fatalln(err)
	}
}

func Index(w http.ResponseWriter, r *http.Request) {
	w.WriteHeader(http.StatusOK)
	println("open connections", DB.Stats().OpenConnections, "In use", DB.Stats().InUse)
	rws, err := DBS.SQL().Query("select sleep(1)")
	if err != nil {
		fmt.Fprintf(w, "error: %v", err)
		return
	}
	defer rws.Close()
	fmt.Fprintf(w, "done")
}

 

then you can use tool "siege" https://github.com/JoeDog/siege to put pressure on the server and you will see in the stdout of the server that the pool is immediately utilized. e.g. showing that there is 10 OpenConnections and 10 InUse.

siege -c 50 http://localhost:8080/path -v --reps=10

As for the things I personally didn't like in v4 are:

The "sqlbuilder" package was marked as internal, and now when you want to access the interface "Tx" you have to do hacks like so:

// copy "Tx" from the sqlbuilder package
type Tx interface {
	// All db.Session methods are available on transaction sessions. They will
	// run on the same transaction.
	db.Session

	Commit() error

	Rollback() error
}

	var (
		err error
		txs []Tx
	)
         // conn is db.Session
	sdb := conn.Driver().(*sql.DB)
                // manually begin the transaction
		stx, err = sdb.Begin()
		if err != nil {
			return err
		}
                // wrap the tx into mysqlTx
		tx, err = mysql.NewTx(stx)
		if err != nil {
			return err
		}
                 // do some work premptively
		_, err = tx.SQL().Exec("SET foreign_key_checks=0")
		if err != nil {
			return err
		}
                // append tx for later use to manually call .Commit() and Rollback() whenever 
		txs = append(txs, tx)

The syntax for committing transactions is very limited. You can accomplish most of the work by using db.Session.Tx(func...) but sometimes you need direct access to control as the given example