Realize DB connection pool with golang

at first

I ended up talking about connection pools using GCP. The error "Maximum number of connections has exceeded 100" occurs. It is a solution that I found by investigating why the number of connections increases.

environment

Cloud: GCP Web server Vue / CloudRun API server Golang / CloudRun DB server MySQL / CloudSQL

Cause

Every time I hit the API, a new connection is made between the API server and the DB server.

Turned out.

In other words, the connection could not be reused.

Countermeasures

Use the following methods in database.sql to set the connection pool. Also, it seems that ORM can also be used for connection pooling.

Method name Explanation
func (db *DB) SetMaxOpenConns(n int) Set the maximum number of connections. Set n to a value of 0 or less, and the number of connections is unlimited.
func (db *DB) SetMaxIdleConns(n int) Set the maximum number of connections in the connection pool.
func (db *DB) SetConnMaxLifetime(d time.Duration) Set the time when the connection can be reused. By setting d to a value of 0 or less, it can be reused forever.

Implemented as follows.

DBconnection


func NewLocalDBConnection() error {
	/* ===== connect datebase ===== */
	// user
	user := os.Getenv("MYSQL_USER") 
	// password
	password := os.Getenv("MYSQL_PASSWORD") 
	// connection database
	database := os.Getenv("MYSQL_DATABASE") 
	// connection host
	host := "localhost" 
	// connection port
	port := "3306" 

	var err error
	DB, err = setupDB("mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?parseTime=true", user, password, host, port, database))
	if err != nil {
		return fmt.Errorf("sql.Open: %v", err)
	}

	return err
}

//this function is a function for connection pooling
func setupDB(Driver string, dsn string) (*sql.DB, error) {
	db, err := sql.Open(Driver, dsn)
	if err != nil {
		return nil, err
	}
	//Set the maximum number of connections in the connection pool.
	db.SetMaxIdleConns(100)
	//Set the maximum number of connections. Set n to a value of 0 or less, and the number of connections is unlimited.
	db.SetMaxOpenConns(100)
	//Set the time when the connection can be reused. By setting d to a value of 0 or less, it can be reused forever.
	db.SetConnMaxLifetime(100 * time.Second)

	return db, err
}

Recommended Posts

Realize DB connection pool with golang
Realize PHP / Python generator with Golang / Ruby
Getting Started with Golang 1
Getting Started with Golang 3
Call bash with golang
Getting Started with Golang 4
Operate Db2 container with Go
Set connection timeout with boto3
Getting Started with PKI with Golang ―― 4
UDP simultaneous connection with Python
Connection pooling with Python + MySQL