MySQL too many connections

Getting Help Code Review
1

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

2

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

3

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

4

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.

5

@ganesh_salunkhe,

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

6

Hello @ganesh_salunkhe sir, I was unable to fetch data of column of database using golang map. Please help me.

8

that means answer the question accurately and completely asked if you want help

Always post the EXACT message text, COPY and PASTE it for accuracy.
Your interpretation of what you think it said is never accurate enough to answer.

Go has awesome error messages, it tells you EXACTLY which line of code, completely answer someones follow-up questions if you really want help quickly.

You should always use a connection pooling implementation and close connections immediately when you are done with them. Let the connection pool that is thoroughly debugged and tested deal with the connection issues for you.

DB, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s?timeout=10s", username, password, host, dbname))
if err != nil {
	os.Exit(2)
}
defer DB.close()

// do processing here
/* if you need to do more than fits in a function easily, then use a goroutine and a waitGroup to to process a query and its results. Returning a connection, resultset, etc is almost always a really bad idea. */

if you need to do processing on a query do it in a closure around the DB before the current scope expires.

Just like Java, letting connections leak outside the method/function they are created in is a recipe for disaster. Same with anything that holds onto that connection like a ResultSet.

Learning how to read for comprehension and accurately response to questions is more important than knowing obscure syntax of some language of of the month.