SetMaxOpenConns（）无法解决“错误1040：连接过多”

Question

I am using golang with Mysql. I am testing load on server using apache benchmark tool. I am getting too many connections error. I read this post . So, I added SetMaxOpenConns(100) in my code. Still I am getting this too many connections error.

I am doing the following query

ab -n 1000 -k -c 20 -p post.txt -T application/x-www-form-urlencoded http://localhost:8084/abcd

Note: post.txt file contains array of 35 ids(type integer). This is my main function:

db, err := models.NewDB("root:@/rules")
    if err != nil {
        panic(err)
    }

    db.SetMaxOpenConns(100)
    http.Handle("/abcd", getReq(db))

    log.Fatal(http.ListenAndServe(":8084", nil))

I am querying this function in go routine for all the ids.

func getRuleforProduct(db *sql.DB, id int) map[int]string {
    m := make(map[int]string)
    var res string
    err := db.QueryRow("select rules from table where product_id = ?", id).Scan(&res)
    checkError(err)
    m[id] = res
    return m
}

How to solve this issue, even if request per second is less. I want the code to work for atleast 20 concurrent request.

Answer 1

First you should check how many open connections does your MySQL server allow with SHOW VARIABLES LIKE 'max_connections'. Default is 151 but if it's less than 100 then your program obviously tries to open too many connections.

But even if it's more than 100 you could still easily get that error. MySQL's max_connections is a global variable that affects the whole server. If your program uses other databases on same server they will also count towards your max_connections limit. Or similarly if you have other programs using your database server you can also run out of connections. In that case you have to use smaller values for SetMaxOpenConns() or increase the max_connections variable.

Also consider setting db.SetMaxIdleConns() To a value lower than db.SetMaxOpenConns(). Otherwise you might have 100 (or whatever value you gave to SetMaxOpenConns) idle connections to your database server using up your connection pool.