MySQL too many connections

Hello,

Greetings !!!

Lately I am facing too many connections for MySQL DB for my golang application.
max_connections is set to 9000.
open files is set to 102400
Below is the code I am using for MySQL DB connection.
I have not closed DB connection anywhere as I am using same connection for each API calls.

var DB *sql.DB = nil
func SetDbConnection() *sql.DB {
	// Establish rules for a connection with the database
	if DB == nil {
		info := config.GetMysqlConfig("") // reading from yaml file
		host := info["host"]
		username := info["username"]
		password := info["password"]
		dbname := info["dbname"]
		DB, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s?timeout=10s", username, password, host, dbname))
		if err != nil {
			fmt.Println(err)
			log.Println(err)
			os.Exit(2)
		}
		DB.SetMaxIdleConns(300)
		fmt.Println("Mysql Connection Done")
	}
	return DB
}

Below are the functions I am using to get data from MySQL.

func GetRow(query string, params []interface{}) map[string]string {
	var err error
	var stmt *sql.Stmt
	stmt, err = DB.Prepare(query)
	if err != nil {
		panic(err)
	}
	singleRow := true
	rows, err2 := stmt.Query(params...)
	stmt.Close()
	if err2 != nil {
		panic(err2)
	}
	val := make(map[string]string)
	index := 0
	results := map[int]map[string]string{}
	cols, _ := rows.Columns()
	str := ""
	for rows.Next() {
		columns := make([]interface{}, len(cols))
		columnPointers := make([]interface{}, len(cols))
		for i := range columns {
			columnPointers[i] = &columns[i]
		}
		if err := rows.Scan(columnPointers...); err != nil {
			panic(err)
		}
		results[index] = map[string]string{}
		for i, colName := range cols {
			val := columnPointers[i].(*interface{})
			val2 := *val
			switch val3 := val2.(type) {
			case int, int8, int16, int32, int64, float32, float64:
				str = fmt.Sprintf("%v", val3)
			case string:
				str = fmt.Sprintf("%s", val3)
			case nil:
				str = ""
			default:
				str = fmt.Sprintf("%s", val3)
			}
			results[index][colName] = str
		}
		if singleRow == true {
			break
		}
		index++
	}
	rows.Close()
	if val, ok := results[0]; ok {
		return val
	}
	return val
}

func GetRows(query string, params []interface{}) map[int]map[string]string {
	var err error
	var stmt *sql.Stmt
	stmt, err = DB.Prepare(query)
	if err != nil {
		panic(err)
	}
	rows, err2 := stmt.Query(params...)
	stmt.Close()
	if err2 != nil {
		panic(err2)
	}
	index := 0
	results := map[int]map[string]string{}
	cols, _ := rows.Columns()
	str := ""
	for rows.Next() {
		columns := make([]interface{}, len(cols))
		columnPointers := make([]interface{}, len(cols))
		for i := range columns {
			columnPointers[i] = &columns[i]
		}
		if err := rows.Scan(columnPointers...); err != nil {
			panic(err)
		}
		results[index] = map[string]string{}
		for i, colName := range cols {
			val := columnPointers[i].(*interface{})
			val2 := *val
			switch val3 := val2.(type) {
			case int, int8, int16, int32, int64, float32, float64:
				str = fmt.Sprintf("%v", val3)
			case string:
				str = fmt.Sprintf("%s", val3)
			case nil:
				str = ""
			default:
				str = fmt.Sprintf("%s", val3)
			}
			results[index][colName] = str
		}
		index++
	}
	rows.Close()
	return results
}

func GetVar(query string, params []interface{}) string {
	var err error
	var stmt *sql.Stmt
	stmt, err = DB.Prepare(query)
	if err != nil {
		panic(err)
	}
	singleRow := true
	rows, err2 := stmt.Query(params...)
	stmt.Close()
	if err2 != nil {
		panic(err)
	}
	index := 0
	results := ""
	cols, _ := rows.Columns()
	str := ""
	for rows.Next() {
		columns := make([]interface{}, len(cols))
		columnPointers := make([]interface{}, len(cols))
		for i := range columns {
			columnPointers[i] = &columns[i]
		}
		if err := rows.Scan(columnPointers...); err != nil {
			panic(err)
		}
		for i := range cols {
			val2 := *columnPointers[i].(*interface{})
			switch val3 := val2.(type) {
			case int, int8, int16, int32, int64, float32, float64:
				str = fmt.Sprintf("%v", val3)
			case string:
				str = fmt.Sprintf("%s", val3)
			case nil:
				str = ""
			default:
				str = fmt.Sprintf("%s", val3)
			}
			results = str
		}
		if singleRow {
			break
		}
		index++
	}
	rows.Close()
	return results
}

Can you advise what could be causing the errors.?

My application has 50-60 API calls per second and average response / processing time is about 2 seconds.

On each API call these is a call to SetDbConnection() however I am checking if connection exists or not and then only connecting to DB.

Please advise.

Edit1 : I am using github.com/go-sql-driver/mysql

Every connection can be “forever”, have a limit session length or closed immediately. You can reuse connections by “pooling”.

https://golang.org/doc/database/manage-connections

Thank you for reply. I understand about SetConnMaxLifetime() . Is there anything else which could be causing connection leaks?

Connection is channel and session is a state. A connection may have multiple sessions. If it as connection problem, you may close connections in a way so it not reach your limit.

@ganesh_salunkhe,

What error message do you get, and at which line of the code?