Hexo

coder

0%

Golang Mysql连接池笔记

发表于 更新于 分类于 ，
本文字数： 24k 阅读时长 ≈ 22 分钟

背景：

发现mysql 连接数有大量增长情况，老是报警，分析连接数

Mysql

解释几个Mysql参数

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show status like  'Threads%';

Threads_cached:      mysql 服务器 缓存的线程， 服务对接客户端的连接请求, 计算方法: 8 + (max_connections / 100), 基本默认为9
Threads_connected:   客户端和 mysql服务端连接成功的连接数
Threads_created:     服务端一共创建的连接数、即线程数
Threads_running:     服务端正在运行的线程数

mysql> select @@max_connections;
151 该函数返回的是 mysql服务端最大连接数

mysql> select CONNECTION_ID();
该方法返回的是 连接id 即线程的id，复用同一个线程，也会分配不同的ID

mysql参数官网参数解释

CONNECTION_ID()官网描述

Golang 程序案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
func main(){
		var (
		username     = "root"
		password     = "123456780"
		host         = "localhost"
		port         = "3306"
		dbName       = "saas"
		connTimeOut  = 1000
		readTimeOut  = 1000
		writeTimeOut = 1000
	)
	MaxOpenConn := 15
	MaxIdleConn := 15
	connectStr := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?timeout=%dms&readTimeout=%dms&writeTimeout=%dms&charset=utf8mb4,utf8",
		username, password, host, port, dbName, connTimeOut, readTimeOut, writeTimeOut)
	db, err := sql.Open("mysql", connectStr)
	if err != nil {
		fmt.Println(err)
		return
	}
	db.SetMaxOpenConns(MaxOpenConn)
	db.SetMaxIdleConns(MaxIdleConn)
	job := 1
	for i := 0; i < job; i++ {

		go func(j int, dba *sql.DB) {
			ticker := time.NewTicker(time.Duration(1 * time.Second))
			for true {
				select {
				case <-ticker.C:
					doReadMysql(j, db)
				}
			}
		}(i, db)
    
	}

	select {}
}

func doReadMysql(i int,db *sql.DB) {
	sqlT := "select CONNECTION_ID()"
	query, err := db.Query(sqlT)
	if err != nil {
		fmt.Println("read sql err", err)
		return
	}
	defer query.Close()
	var Id int
	for query.Next() {
		if err := query.Scan(&Id); err != nil {
			fmt.Println("scan sql data err", err)
			continue
		}
	}
	stats := db.Stats()
	fmt.Printf("t:%v\tworke:%v\tId:%v\t%+v\n", time.Now(), i, Id, stats)
}

上述代码的主要逻辑：

  1. 构建连接mysql的参数

    主要参数

    1
    2
    db.SetMaxOpenConns(MaxOpenConn) // 此db和服务端 可以建立的最大连接数
    db.SetMaxIdleConns(MaxIdleConn) // 此db和服务端 之间可以保存的最大空闲连接数

  2. 通过sql.Open 获取到 sql.DB

  3. 并发启动任务

  4. 每隔一秒通过db 查询CONNECTION_ID 获取结果 打印， 且打印db的状态

Case 分析

数据库初始状态

  • Threads_cached = 9
  • Threads_connected = 1
  • Threads_created = 1074
  • Threads_running = 2

case1: MaxOpenConn=5&MaxIdleConn=5&job=5

Mysql结果： 正常连接的数量+5， 缓存线程-5 总创建的连接数不变

  • Threads_cached = 4
  • Threads_connected = 6
  • Threads_created = 1074
  • Threads_running = 2

程序结果：连接id没有变，一共5个 循环使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
t:2022-04-24 21:55:12.124946 +0800 CST m=+1.010643043   worke:0 Id:9544 {MaxOpenConnections:5 OpenConnections:5 InUse:4 Idle:1 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:12.124952 +0800 CST m=+1.010649168   worke:2 Id:9546 {MaxOpenConnections:5 OpenConnections:5 InUse:2 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:12.124951 +0800 CST m=+1.010647501   worke:1 Id:9545 {MaxOpenConnections:5 OpenConnections:5 InUse:3 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:12.124973 +0800 CST m=+1.010670251   worke:3 Id:9543 {MaxOpenConnections:5 OpenConnections:5 InUse:0 Idle:5 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:12.124969 +0800 CST m=+1.010666210   worke:4 Id:9547 {MaxOpenConnections:5 OpenConnections:5 InUse:1 Idle:4 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:13.120617 +0800 CST m=+2.006334543   worke:0 Id:9545 {MaxOpenConnections:5 OpenConnections:5 InUse:4 Idle:1 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:13.120626 +0800 CST m=+2.006343626   worke:1 Id:9544 {MaxOpenConnections:5 OpenConnections:5 InUse:2 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:13.120621 +0800 CST m=+2.006338001   worke:4 Id:9546 {MaxOpenConnections:5 OpenConnections:5 InUse:3 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:13.120679 +0800 CST m=+2.006396876   worke:2 Id:9547 {MaxOpenConnections:5 OpenConnections:5 InUse:1 Idle:4 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:13.120751 +0800 CST m=+2.006468501   worke:3 Id:9543 {MaxOpenConnections:5 OpenConnections:5 InUse:0 Idle:5 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:14.119881 +0800 CST m=+3.005619168   worke:4 Id:9544 {MaxOpenConnections:5 OpenConnections:5 InUse:3 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:14.119889 +0800 CST m=+3.005626626   worke:0 Id:9546 {MaxOpenConnections:5 OpenConnections:5 InUse:3 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:14.119941 +0800 CST m=+3.005679001   worke:1 Id:9545 {MaxOpenConnections:5 OpenConnections:5 InUse:1 Idle:4 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:14.119912 +0800 CST m=+3.005649626   worke:2 Id:9547 {MaxOpenConnections:5 OpenConnections:5 InUse:2 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:14.12009 +0800 CST m=+3.005827876    worke:3 Id:9543 {MaxOpenConnections:5 OpenConnections:5 InUse:0 Idle:5 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:15.119877 +0800 CST m=+4.005635418   worke:2 Id:9547 {MaxOpenConnections:5 OpenConnections:5 InUse:3 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:15.119927 +0800 CST m=+4.005684918   worke:0 Id:9546 {MaxOpenConnections:5 OpenConnections:5 InUse:1 Idle:4 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:15.119883 +0800 CST m=+4.005641043   worke:1 Id:9545 {MaxOpenConnections:5 OpenConnections:5 InUse:2 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:15.119874 +0800 CST m=+4.005632085   worke:4 Id:9544 {MaxOpenConnections:5 OpenConnections:5 InUse:4 Idle:1 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 21:55:15.119931 +0800 CST m=+4.005688918   worke:3 Id:9543 {MaxOpenConnections:5 OpenConnections:5 InUse:0 Idle:5 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}

case2: MaxOpenConn=20&MaxIdleConn=15&job=20

Mysql结果： 正常连接的数量+15， 缓存线程-3 总创建的连接数 新增12

  • Threads_cached = 5
  • Threads_connected = 16
  • Threads_created = 1086
  • Threads_running = 2

程序结果：15个连接Id一直在用，剩下连接ID一直在变，一直在和mysql 服务端做新的连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
t:2022-04-24 22:28:31.541753 +0800 CST m=+23.006227876  worke:18        Id:9566 {MaxOpenConnections:20 OpenConnections:20 InUse:5 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:91 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:31.543484 +0800 CST m=+23.007958042  worke:13        Id:9658 {MaxOpenConnections:20 OpenConnections:19 InUse:4 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:92 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:31.543513 +0800 CST m=+23.007987959  worke:4 Id:9654 {MaxOpenConnections:20 OpenConnections:18 InUse:3 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:94 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:31.543515 +0800 CST m=+23.007989084  worke:1 Id:9655 {MaxOpenConnections:20 OpenConnections:17 InUse:2 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:94 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:31.543543 +0800 CST m=+23.008017876  worke:15        Id:9657 {MaxOpenConnections:20 OpenConnections:16 InUse:1 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:31.543552 +0800 CST m=+23.008026792  worke:17        Id:9656 {MaxOpenConnections:20 OpenConnections:15 InUse:0 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541031 +0800 CST m=+24.005508126  worke:0 Id:9564 {MaxOpenConnections:20 OpenConnections:20 InUse:19 Idle:1 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541066 +0800 CST m=+24.005543834  worke:19        Id:9565 {MaxOpenConnections:20 OpenConnections:20 InUse:18 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541135 +0800 CST m=+24.005612334  worke:9 Id:9557 {MaxOpenConnections:20 OpenConnections:20 InUse:17 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541203 +0800 CST m=+24.005680584  worke:1 Id:9549 {MaxOpenConnections:20 OpenConnections:20 InUse:16 Idle:4 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541253 +0800 CST m=+24.005730542  worke:2 Id:9555 {MaxOpenConnections:20 OpenConnections:20 InUse:15 Idle:5 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541306 +0800 CST m=+24.005783834  worke:10        Id:9556 {MaxOpenConnections:20 OpenConnections:20 InUse:14 Idle:6 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541346 +0800 CST m=+24.005823334  worke:3 Id:9551 {MaxOpenConnections:20 OpenConnections:20 InUse:13 Idle:7 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541403 +0800 CST m=+24.005880209  worke:11        Id:9550 {MaxOpenConnections:20 OpenConnections:20 InUse:12 Idle:8 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.54146 +0800 CST m=+24.005937417   worke:12        Id:9552 {MaxOpenConnections:20 OpenConnections:20 InUse:11 Idle:9 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541504 +0800 CST m=+24.005981334  worke:4 Id:9558 {MaxOpenConnections:20 OpenConnections:20 InUse:10 Idle:10 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541553 +0800 CST m=+24.006030501  worke:6 Id:9561 {MaxOpenConnections:20 OpenConnections:20 InUse:9 Idle:11 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541611 +0800 CST m=+24.006088876  worke:13        Id:9553 {MaxOpenConnections:20 OpenConnections:20 InUse:8 Idle:12 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541663 +0800 CST m=+24.006140126  worke:7 Id:9567 {MaxOpenConnections:20 OpenConnections:20 InUse:7 Idle:13 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541711 +0800 CST m=+24.006188167  worke:17        Id:9566 {MaxOpenConnections:20 OpenConnections:20 InUse:6 Idle:14 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.541761 +0800 CST m=+24.006238917  worke:5 Id:9548 {MaxOpenConnections:20 OpenConnections:20 InUse:5 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:96 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.546732 +0800 CST m=+24.011209292  worke:14        Id:9660 {MaxOpenConnections:20 OpenConnections:19 InUse:4 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:97 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.546817 +0800 CST m=+24.011294167  worke:8 Id:9663 {MaxOpenConnections:20 OpenConnections:18 InUse:3 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:98 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.547108 +0800 CST m=+24.011585584  worke:16        Id:9659 {MaxOpenConnections:20 OpenConnections:17 InUse:2 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:101 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.547118 +0800 CST m=+24.011595251  worke:18        Id:9661 {MaxOpenConnections:20 OpenConnections:16 InUse:1 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:101 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:32.547119 +0800 CST m=+24.011596126  worke:15        Id:9662 {MaxOpenConnections:20 OpenConnections:15 InUse:0 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:101 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:33.541734 +0800 CST m=+25.006214501  worke:9 Id:9565 {MaxOpenConnections:20 OpenConnections:20 InUse:18 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:101 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:28:33.541731 +0800 CST m=+25.006211209  worke:10        Id:9557 {MaxOpenConnections:20 OpenConnections:20 InUse:19 Idle:1 WaitCount:0 WaitDuration:0s MaxIdleClosed:101 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}

case3: MaxOpenConn=30&MaxIdleConn=15&job=50

Mysql结果： 正常连接的数量+15， 缓存线程不变 总创建的连接数 持续新增

  • Threads_cached = 5
  • Threads_connected = 16
  • Threads_created = 1708
  • Threads_running = 2

程序结果：15个连接Id一直在用，剩下连接ID一直在变，一直在和mysql 服务端做新的连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
t:2022-04-24 22:41:27.51513 +0800 CST m=+16.007155043   worke:38        Id:10531        {MaxOpenConnections:30 OpenConnections:30 InUse:15 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:219 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520503 +0800 CST m=+16.012528043  worke:7 Id:10768        {MaxOpenConnections:30 OpenConnections:28 InUse:13 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:221 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520502 +0800 CST m=+16.012527626  worke:27        Id:10760        {MaxOpenConnections:30 OpenConnections:29 InUse:14 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:221 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520575 +0800 CST m=+16.012600459  worke:49        Id:10759        {MaxOpenConnections:30 OpenConnections:27 InUse:12 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:222 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520615 +0800 CST m=+16.012640751  worke:46        Id:10763        {MaxOpenConnections:30 OpenConnections:26 InUse:11 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:223 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520643 +0800 CST m=+16.012668251  worke:45        Id:10761        {MaxOpenConnections:30 OpenConnections:25 InUse:10 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:224 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520692 +0800 CST m=+16.012716834  worke:29        Id:10766        {MaxOpenConnections:30 OpenConnections:24 InUse:9 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:225 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.52073 +0800 CST m=+16.012755376   worke:43        Id:10765        {MaxOpenConnections:30 OpenConnections:23 InUse:8 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:227 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520746 +0800 CST m=+16.012771751  worke:9 Id:10764        {MaxOpenConnections:30 OpenConnections:22 InUse:7 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:227 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520801 +0800 CST m=+16.012826251  worke:41        Id:10758        {MaxOpenConnections:30 OpenConnections:21 InUse:6 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:229 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.52082 +0800 CST m=+16.012845043   worke:18        Id:10770        {MaxOpenConnections:30 OpenConnections:20 InUse:5 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:229 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520872 +0800 CST m=+16.012897626  worke:25        Id:10771        {MaxOpenConnections:30 OpenConnections:19 InUse:4 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:231 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520881 +0800 CST m=+16.012906334  worke:26        Id:10769        {MaxOpenConnections:30 OpenConnections:18 InUse:3 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:231 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.520908 +0800 CST m=+16.012932918  worke:17        Id:10767        {MaxOpenConnections:30 OpenConnections:17 InUse:2 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:233 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.52092 +0800 CST m=+16.012945084   worke:8 Id:10772        {MaxOpenConnections:30 OpenConnections:16 InUse:1 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:233 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:41:27.52094 +0800 CST m=+16.012965709   worke:13        Id:10762        {MaxOpenConnections:30 OpenConnections:15 InUse:0 Idle:15 WaitCount:256 WaitDuration:265.780031ms MaxIdleClosed:234 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}

case4: MaxOpenConn=50&MaxIdleConn=15&job=15

Mysql结果： 正常连接的数量+15， 缓存线程-9 总创建的连接数 新增6个 后不再增加

  • Threads_cached = 0
  • Threads_connected = 16
  • Threads_created = 1821
  • Threads_running = 2

程序结果：15个连接Id一直在用，剩下连接ID一直在变，一直在和mysql 服务端做新的连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
t:2022-04-24 22:47:27.535263 +0800 CST m=+15.006615793  worke:1 Id:10993        {MaxOpenConnections:50 OpenConnections:15 InUse:5 Idle:10 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535219 +0800 CST m=+15.006572460  worke:2 Id:10983        {MaxOpenConnections:50 OpenConnections:15 InUse:8 Idle:7 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535235 +0800 CST m=+15.006588335  worke:0 Id:10985        {MaxOpenConnections:50 OpenConnections:15 InUse:6 Idle:9 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535235 +0800 CST m=+15.006587751  worke:9 Id:10989        {MaxOpenConnections:50 OpenConnections:15 InUse:7 Idle:8 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535198 +0800 CST m=+15.006550668  worke:8 Id:10991        {MaxOpenConnections:50 OpenConnections:15 InUse:9 Idle:6 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535359 +0800 CST m=+15.006711668  worke:11        Id:10988        {MaxOpenConnections:50 OpenConnections:15 InUse:4 Idle:11 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535372 +0800 CST m=+15.006725210  worke:13        Id:10986        {MaxOpenConnections:50 OpenConnections:15 InUse:3 Idle:12 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535382 +0800 CST m=+15.006735335  worke:3 Id:10981        {MaxOpenConnections:50 OpenConnections:15 InUse:2 Idle:13 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.535394 +0800 CST m=+15.006746960  worke:10        Id:10979        {MaxOpenConnections:50 OpenConnections:15 InUse:1 Idle:14 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:27.53546 +0800 CST m=+15.006812751   worke:12        Id:10990        {MaxOpenConnections:50 OpenConnections:15 InUse:0 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.531809 +0800 CST m=+16.003178710  worke:4 Id:10992        {MaxOpenConnections:50 OpenConnections:15 InUse:12 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.531864 +0800 CST m=+16.003233835  worke:14        Id:10987        {MaxOpenConnections:50 OpenConnections:15 InUse:13 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.531875 +0800 CST m=+16.003244876  worke:5 Id:10982        {MaxOpenConnections:50 OpenConnections:15 InUse:12 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532138 +0800 CST m=+16.003507835  worke:9 Id:10983        {MaxOpenConnections:50 OpenConnections:15 InUse:11 Idle:4 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532168 +0800 CST m=+16.003537793  worke:8 Id:10991        {MaxOpenConnections:50 OpenConnections:15 InUse:10 Idle:5 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532175 +0800 CST m=+16.003544626  worke:10        Id:10989        {MaxOpenConnections:50 OpenConnections:15 InUse:9 Idle:6 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532189 +0800 CST m=+16.003558960  worke:7 Id:10980        {MaxOpenConnections:50 OpenConnections:15 InUse:8 Idle:7 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532207 +0800 CST m=+16.003576626  worke:6 Id:10984        {MaxOpenConnections:50 OpenConnections:15 InUse:7 Idle:8 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532312 +0800 CST m=+16.003681751  worke:11        Id:10985        {MaxOpenConnections:50 OpenConnections:15 InUse:6 Idle:9 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532433 +0800 CST m=+16.003802460  worke:13        Id:10988        {MaxOpenConnections:50 OpenConnections:15 InUse:5 Idle:10 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532471 +0800 CST m=+16.003840626  worke:12        Id:10993        {MaxOpenConnections:50 OpenConnections:15 InUse:4 Idle:11 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532492 +0800 CST m=+16.003861710  worke:2 Id:10986        {MaxOpenConnections:50 OpenConnections:15 InUse:3 Idle:12 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532704 +0800 CST m=+16.004073335  worke:3 Id:10990        {MaxOpenConnections:50 OpenConnections:15 InUse:2 Idle:13 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532736 +0800 CST m=+16.004105876  worke:1 Id:10979        {MaxOpenConnections:50 OpenConnections:15 InUse:1 Idle:14 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:28.532746 +0800 CST m=+16.004115960  worke:0 Id:10981        {MaxOpenConnections:50 OpenConnections:15 InUse:0 Idle:15 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533542 +0800 CST m=+17.004928043  worke:8 Id:10991        {MaxOpenConnections:50 OpenConnections:15 InUse:12 Idle:3 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533563 +0800 CST m=+17.004948710  worke:11        Id:10983        {MaxOpenConnections:50 OpenConnections:15 InUse:11 Idle:4 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533541 +0800 CST m=+17.004927460  worke:5 Id:10992        {MaxOpenConnections:50 OpenConnections:15 InUse:13 Idle:2 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533572 +0800 CST m=+17.004958251  worke:14        Id:10982        {MaxOpenConnections:50 OpenConnections:15 InUse:10 Idle:5 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533575 +0800 CST m=+17.004960710  worke:9 Id:10980        {MaxOpenConnections:50 OpenConnections:15 InUse:9 Idle:6 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533538 +0800 CST m=+17.004924210  worke:4 Id:10987        {MaxOpenConnections:50 OpenConnections:15 InUse:14 Idle:1 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533587 +0800 CST m=+17.004972626  worke:6 Id:10989        {MaxOpenConnections:50 OpenConnections:15 InUse:8 Idle:7 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.533615 +0800 CST m=+17.005001335  worke:12        Id:10984        {MaxOpenConnections:50 OpenConnections:15 InUse:7 Idle:8 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}
t:2022-04-24 22:47:29.53363 +0800 CST m=+17.005015626   worke:10        Id:10985        {MaxOpenConnections:50 OpenConnections:15 InUse:6 Idle:9 WaitCount:0 WaitDuration:0s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}

最终结论：

  1. 当 任务数比 空闲连数 大的多的时候， 数据库会持续新建新连接，负责处理 客户端的请求， 超过最大连接数的连接 连接只用一次
  2. 当任务数比最大连接数大的时候， 会一直创建连接，直到达到设置的最大连接数限制
  3. 当任务数 比空闲连接数大， 但是小于数据库 thread_cache 数据库也不会新建连接， 而是复用缓存的连接

优化点：

  1. 分析系统QPS得到需要数据库连接数
  2. 增大 Golang数据库连接池的最大空闲连接
  3. 增大数据库 Thread_cache 参数， 使得数据库不用新建连接，复用资源