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.
Cloud: GCP Web server Vue / CloudRun API server Golang / CloudRun DB server MySQL / CloudSQL
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.
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