How to set the maximum number of connections and the maximum number of idle connections in golang combined with mysql

The maximum number of connections and the maximum number of idle connections are defined in database/sql in the golang standard library.

The SQL driver package used in the example to connect to MySQL is github.com/go-sql-driver/mysql.

The interface for setting the maximum number of connections is

func (db *DB) SetMaxOpenConns(n int)

Setting the connection The maximum number of connections that MySQL can open.

If n <= 0, it means there is no limit on the number of open connections.

The default is 0, which means there is no limit on the number of connections.

Another parameter related to the number of connections is MaxIdleConns, which represents the maximum number of idle connections.

If MaxIdleConns is greater than 0 and greater than MaxOpenConns, then MaxIdleConns will be adjusted to be equal to MaxOpenConns, and the excess connections will be closed when there are excess connections.

The interface for setting the maximum number of idle connections is:

func (db *DB) SetMaxIdleConns(n int)

When n<=0, the idle connection pool will not be used, that is, when the idle connection pool is not used, the unused connections will will not be stored in it. Therefore, this method does not reuse the connection, and a new connection needs to be re-established every time a SQL statement is executed. <=0时，空闲连接池不会被使用，即不使用空闲连接池时，未使用的连接将不会被存入其中。因此，这种方法不会重复利用连接，每次执行SQL语句都需要重新建立新的连接。

The default maximum number of idle connections is 2:
const defaultMaxIdleConns = 2

Regarding the relationship between open connections and idle connections, please add:

Opened connection = connection in use (inuse) connection in idle state (idle)

Let’s test and verify the maximum number of connections and the maximum number of idle connections.

1. Maximum number of connections test

First set the maximum number of open connections to 1, then open 20 goroutines, each goroutine executes the sql statement, and prints the connection id of the connection used to execute the sql . Observe the execution of other Goroutines that need to execute SQL when executing time-consuming SQL statements occupying connections.

The example code is as follows:

package main

import (
        "database/sql"
        "log"

        _ "github.com/go-sql-driver/mysql"
)

var DB *sql.DB
var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"

func Init() {
        var err error
        DB, err = sql.Open("mysql", dataBase)
        if err != nil {
                log.Fatalln("open db fail:", err)
        }

        DB.SetMaxOpenConns(1)

        err = DB.Ping()
        if err != nil {
                log.Fatalln("ping db fail:", err)
        }
}

func main() {
        Init()
        
        //开启20个goroutine
        for i:=0; i < 20; i++ {
                go one_worker(i)
        }
        
        select {
        }

}

func one_worker(i int) {
        var connection_id int
        err := DB.QueryRow("select CONNECTION_ID()").Scan(&connection_id)
        if err != nil {
                log.Println("query connection id failed:", err)
                return
        }

        log.Println("worker:", i, ", connection id:", connection_id)

        var result int
        err = DB.QueryRow("select sleep(10)").Scan(&result)
        if err != nil {
                log.Println("query sleep connection id faild:", err)
                return
        }

}

output

2019/10/02 18:14:25 worker: 2, connection id: 55
2019/ 10/02 18:14:25 worker: 17 , connection id: 55
2019/10/02 18:14:25 worker: 11 , connection id: 55
2019/10/02 18:14:35 worker: 3 , connection id: 55
2019/10/02 18:14:45 worker: 0 , connection id: 55
2019/10/02 18:14:45 worker: 4 , connection id: 55
2019/10/02 18:14:45 worker: 5 , connection id: 55
2019/10/02 18:15:05 worker: 7 , connection id: 55
2019/10/02 18:15:25 worker: 15 , connection id: 55
2019/10/02 18:15:25 worker: 6 , connection id: 55
2019/10/02 18:15:35 worker: 13 , connection id: 55
2019/10/02 18:15:45 worker: 19 , connection id: 55
2019/10/02 18:15:45 worker: 10 , connection id: 55
2019/10/02 18:15:45 worker: 12 , connection id: 55
2019/10/02 18:15:55 worker: 14 , connection id: 55
2019/10/02 18:16 :15 worker: 8 , connection id: 55
2019/10/02 18:16:35 worker: 18 , connection id: 55
2019/10/02 18:16:35 worker: 1 , connection id : 55
2019/10/02 18:17:05 worker: 16 , connection id: 55
2019/10/02 18:17:35 worker: 9 , connection id: 55

Use show processlist to view the connection

mysql> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
| 20 | root | localhost       | NULL | Query   |    0 | starting   | show processlist |
| 55 | root | localhost:59518 | NULL | Query   |    5 | User sleep | select sleep(10) |
+----+------+-----------------+------+---------+------+------------+------------------+
2 rows in set (0.00 sec)

Use netstat to view the connection

netstat -an | grep 3306
tcp4       0      0  127.0.0.1.3306         127.0.0.1.59518        ESTABLISHED
tcp4       0      0  127.0.0.1.59518        127.0.0.1.3306         ESTABLISHED
tcp46      0      0  *.3306                 *.*                    LISTEN

As you can see from the results, 20 goroutines take turns using the same connection (connection id is 55) to execute sql statements.

Other goroutines will enter the blocking state when the connection is occupied. No other goroutine can use the connection until the connection is used up.

Even if multiple goroutines are executing SQL, multiple connections are not created.

Therefore, the maximum number of connections setting takes effect.

Some readers may ask, if they have not seen the maximum number of idle connections set, what is the maximum number of space connections at this time?

As mentioned before, the default maximum number of idle connections is 2.

Let’s test the maximum number of space connections.

2. Maximum number of idle connections test

In the following example, set the maximum number of connections to 1 and the maximum number of idle connections to 0.

And execute a SQL every 3 seconds statement.

The code is as follows:

package main

import (
        "database/sql"
        "log"
        "time"

        _ "github.com/go-sql-driver/mysql"

)

var DB *sql.DB
var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"

func mysqlInit() {
        var err error
        DB, err = sql.Open("mysql", dataBase)
        if err != nil {
                log.Fatalln("open db fail:", err)
        }

        DB.SetMaxOpenConns(1)
        DB.SetMaxIdleConns(0)

        err = DB.Ping()
        if err != nil {
                log.Fatalln("ping db fail:", err)
        }
}

func main() {
        mysqlInit()

        for {
                execSql()
                time.Sleep(3*time.Second)
        }
}


func execSql() {
        var connection_id int
        err := DB.QueryRow("select CONNECTION_ID()").Scan(&connection_id)
        if err != nil {
                log.Println("query connection id failed:", err)
                return
        }

        log.Println("connection id:", connection_id)
}

output:

2019/10/13 23:06:00 connection id: 26
2019/10/13 23 :06:03 connection id: 27
2019/10/13 23:06:06 connection id: 28
2019/10/13 23:06:09 connection id: 29
2019/10/13 23:06:12 connection id: 30
2019/10/13 23:06:15 connection id: 31
2019/10/13 23:06:18 connection id: 32
2019/10/ 13 23:06:21 connection id: 33
2019/10/13 23:06:24 connection id: 34
2019/10/13 23:06:27 connection id: 35
2019/10 /13 23:06:30 connection id: 36
2019/10/13 23:06:33 connection id: 37
2019/10/13 23:06:36 connection id: 38

As can be seen from the results, the connection id used is different each time SQL is executed.

Set the maximum number of idle connections to 0. After each execution of SQL, the connection will not be put into the idle connection pool, but will be closed. The next time SQL is executed, a new connection will be re-established.

The above is the detailed content of How to set the maximum number of connections and the maximum number of idle connections in golang combined with mysql. For more information, please follow other related articles on the PHP Chinese website!