I have created a mysql connection in Go, where I set:
SetMaxIdleConns to 10
SetMaxOpenConns to 200
SetConnMaxLifetime to 15 * time.Second
After running script, I observed that no insert query is running after 200 connections.
func GetDbh(conf goini.SectionMap) (db *sql.DB) {
db, err := sql.Open("mysql", dbUser+":"+dbPass+"@tcp("+dbServer+":3306)/"+dbName)
if err != nil {
fmt.Println("Exit. Cannot make connection with host: '" + dbServer + "' user: '" + dbUser + "' pass: '" + dbPass + "' database '" + dbName + "'")
return nil
}
db.SetMaxIdleConns(10)
db.SetMaxOpenConns(200)
db.SetConnMaxLifetime(15 * time.Second)
return db
}
MariaDB [test]> show status where `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 201 |
+-------------------+-------+
Below code insert data into mysql
package main
import (
dbh "../lib/dbutils"
)
func main() {
dbm := dbh.GetDbh(conf)
.
.
for loop <which runs more than 500 times> {
.
. <here is some code which calculate date, id, email and salary>
.
query_update_insert := "INSERT INTO employee(id,date,email,salary) values("+strconv.FormatInt(id,10)+",'"+mdate+"','"+email+"',"+strconv.FormatInt(salary,10)+") ON DUPLICATE KEY UPDATE salary=VALUES(salary);"
fmt.Println("Insert(employee) Query:"+query_update_insert)
_, err := dbm.Query(query_update_insert)
if err != nil {
fmt.Println("Insert Error: "+err.Error())
}
}
dbm.Close()
}
If I reduce SetConnMaxLifetime to Nanosecond or less than 5 seconds it gives following error:
Insert Error: Error 1040: Too many connections
Insert(employee) Query:INSERT INTO employee(id,date,email,salary) values(416,'2019-09-24','naresh_t@sharekhan.com',60000) ON DUPLICATE KEY UPDATE salary=VALUES(salary);
Insert Error: Error 1040: Too many connections
Insert(employee) Query:INSERT INTO employee(id,date,email,salary) values(416,'2019-09-24','madanlal@sharekhan.com',40000) ON DUPLICATE KEY UPDATE salary=VALUES(salary);
All insert queries should run properly.