Go Mysql包 - database/sql(2) - 性能优化和观察

2022/08/23 in GOLANG • PROGRAMMING
library mysql
6 min read

Table of Content

1. 通过 Docker 配置 Mysql 服务

参考docker-compose.yaml配置，相关 path 路径可以单独存放在宿主机上
注意volumes可以根据自己环境调配
command可以按自己的情况配置
/conf.d会被

1.1. Docker-Compose 环境

1
2
3
4
5
6
# tree .
.
├── conf.d
│   ├── docker.cnf
│   └── mysql.cnf
└── docker-compose.yml

1.2. docker-compose.yml 配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
version: "3.1"
services:
  db:
    image: mysql
    command: --defaults-file=/etc/mysql/conf.d/mysql.cnf --default-authentication-plugin=mysql_native_password
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: Secret123.
    volumes:
      - /data/docker_volumes/mysql-data:/var/lib/mysql
      - /data/docker_volumes/mysql-data/run:/var/run/mysqld
      - ./conf.d:/etc/mysql/conf.d

1.3. conf.d/my.cnf - 开启慢查询、最大连接线程数、数据存储等配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// mysql - 慢查询可以看官方文档
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow-log.log
long_query_time=2

// 连接配置
max_connections=2048

// 数据存储
secure-file-priv= NULL
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql

1.4. 快速重新运行镜像加载相关配置

注意: 数据卷相关不会有变化，所以如果密码被重置过后，可能和docker-compose.yaml不一致

`1`	`$ docker-compose up -d --force-recreate`

2. Mysql服务监控

2.1. 已发送给数据库的在执行的慢查询

可以手动执行 sleep()函数，或者是执行全表扫描的慢SQL, 观察慢查询日志

1
2
3
4
5
6
    # tail -f slow-log.log           slow-log.log
    # Time: 2022-08-23T15:58:53.068335Z
    # User@Host: utuser[utuser] @  [10.99.17.139]  Id: 239048
    # Query_time: 940.381579  Lock_time: 0.000004 Rows_sent: 0  Rows_examined: 2438509
    SET timestamp=1661269392;
    select * from user where name like '%3043e4bc-a673-496b-8fa6-d582d1671d65';

2.2. 通过 show processlist 也可以看到慢查询情况

3. mytop 数据库连接监控利器

mytop 用于观察 mysqld 服务情况，比如当前 qps、线程数、增删改查的比例，方便进一步调优；

安装: 参考 https://www.digitalocean.com/community/tutorials/how-to-use-mytop-to-monitor-mysql-performance
配置: 编辑 ~/.mytop 输入相关连接参数，支持TCP连接和Socket两种方式配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
// 安装
sudo yum install mytop -y

// 通过TCP配置: vim ~/.mytop                                                                                                                                           7,1           All
host=127.0.0.1
user=root
pass=Secret123
db=conn_pool
delay=3
port=3306
#socket=/data/docker_volumes/mysql-data/run/mysqld.sock
batchmode=0
color=1
idle=1

图示:

4. 磁盘 IOStat 监控

最开始的时候，

1
2
3
4
安装: apt-get install sysstat

// 监控，需要在docker机器内运行
iostat -x -d 3

5. database/sql 包连接配置

6. 数据库表 - user

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2438975 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.09 sec)

7. 调试代码 tmysql.go

  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
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
package tmysql

import (
	"database/sql"
	_ "database/sql"
	"math/rand"
	"runtime"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"
	"github.com/google/uuid"
	"github.com/pkg/errors"
	log "github.com/sirupsen/logrus"
)

func init() {
	rand.Seed(time.Now().UnixNano())
}

type DBSetting struct {
	ConnMaxLifeTime time.Duration
	MaxOpenConns    int
	MaxIdleConns    int
	ConnMaxIdleTime time.Duration
}

type BchSetting struct {
	WriteClientNum int
	WriteInterval  time.Duration
	ReadClientNum  int
	ReadInternal   time.Duration
}

func StartServer(dns string, setting *DBSetting, bchSetting *BchSetting) error {
	// 1. 服务启动
	dbCfg, err := mysql.ParseDSN(dns)
	if err != nil {
		return errors.Wrap(err, "parse dns got err")
	}
	log.Infof("db cfg: %+v", dbCfg)

	// 2. DB连接实例
	db, err := sql.Open("mysql", dns)
	if err != nil {
		return errors.Wrapf(err, "sql open got err,")
	}

	// 3. Mysql实例+配置
	// See "Important settings" section.
	db.SetConnMaxLifetime(setting.ConnMaxLifeTime)
	db.SetMaxOpenConns(setting.MaxOpenConns)
	db.SetMaxIdleConns(setting.MaxIdleConns)
	db.SetConnMaxIdleTime(setting.ConnMaxIdleTime)

	// 4. 模拟多协程读、写用户(10w写入,50w读取)
	go srvStart(db, bchSetting)

	// 5. 启动协程，定期输出监控数据 db.Stat
	go srvMonitor(db)

	select {}
}

// 服务监控
func srvMonitor(db *sql.DB) {
	for {
		select {
		case <-time.Tick(1 * time.Second):
			log.Infof("db status: %+v", db.Stats())
			log.Infof("%s", strings.Repeat("-", 50))
			log.Infof("go routine num: %d", runtime.NumGoroutine())
		}
	}
}

// 模拟启动并发读、写协程
func srvStart(db *sql.DB, bchCfg *BchSetting) {
	// 读
	for i := 0; i < bchCfg.ReadClientNum; i++ {
		go func() {
			GetUser(bchCfg, db, uuid.New().String())
		}()
	}

	// 写
	for i := 0; i < bchCfg.WriteClientNum; i++ {
		go func() {
			NewUser(bchCfg, db, randUser())
		}()
	}

}

func NewUser(bcfg *BchSetting, db *sql.DB, u *User) {
	for {
		select {
		case <-time.Tick(bcfg.WriteInterval):
			// log.Infof("insert name[%s]", u.Name)
			_, err := db.Exec("insert into user (name) values(?)", u.Name)
			if err != nil {
				log.Errorf("insert db exec got err: %s", err)
			}
		}

	}
}

func GetUser(bcfg *BchSetting, db *sql.DB, s string) {
	for {
		select {
		case <-time.Tick(bcfg.ReadInternal):
			// rows, err := db.Query("select * from user where name like ?", "%"+s) : 可以人为改成为慢查询，这样连接池就会被打爆
			rows, err := db.Query("select * from user where name like ?", s)
			if err != nil {
				log.Errorf("read db query got err: %s", err)
				continue
			}
			rows.Close() // 不关闭的话，会有资源死锁问题
		}
	}
}

type User struct {
	ID   int
	Name string
}

func randUser() *User {
	return &User{
		Name: uuid.New().String(),
	}
}

8. tmysql_test.go

可以通过用例尝试不同的连接池，配合闲置连接数，以及模拟慢查询、多并发情况实验观察效果

 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
package tmysql

import (
	"testing"
	"time"
)

func TestStartServer(t *testing.T) {
	dns := `utuser:Secret@123@(127.0.0.1:3306)/conn_pool?timeout=3s&readTimeout=3s&writeTimeout=3s`
	setting := &DBSetting{
		ConnMaxLifeTime: 1 * time.Hour,
		ConnMaxIdleTime: 30 * time.Second,
		MaxOpenConns:    50, // 连接池大小，如果慢查询请求太多，一般会打爆连接池
		MaxIdleConns:    30, // 最大闲置连接，即控制复用率，越大表示闲置连接越多，在高并发场景下连接复用率也会增加
	}
	bchSetting := &BchSetting{
		WriteClientNum: 100,
		WriteInterval:  100 * time.Millisecond,
		ReadClientNum:  20,
		ReadInternal:   100 * time.Millisecond,
	}
	if err := StartServer(dns, setting, bchSetting); err != nil {
		t.Errorf("StartServer() error = %v", err)
	}
}

9. 关键配置

9.1. SetMaxOpenConns

设置连接池最大可以打开的连接数，这个参数需要结合数据库的 max_connections(limit)、以及连接数据库的节点数(N)、并发量综合考虑；

比如预估SQL的QPS 1w+左右，每个SQL执行100ms左右，则满打算1k连接；我们可以将Mysql服务器配置的 max_connection为2048（2倍），假定按10个节点分派，即平均每个计算节点可以有200个长连接；但考虑到后续计算节点可能会伸缩(比如扩展到20个)，我们可以为我们的Go服务实例配置 SetMaxOpenConns(100)；不过实际情况，我们可能会引入一主多从的架构，进一步分摊每个数据库的连接数，避免数据库单节点负载过高。

另外，我们有很多慢查询请求时候，我们的连接可能都会被这些慢查询给占用，导致后续SQL请求无连接可用，从而导致连接超时错误，这部分应该配合慢查询日志监控+告警及时解决 (通常是加分析加索引、加缓存、架构调整等进行优化)

1
2
3
4
5
6
7
8
// SetMaxOpenConns sets the maximum number of open connections to the database.
//
// If MaxIdleConns is greater than 0 and the new MaxOpenConns is less than
// MaxIdleConns, then MaxIdleConns will be reduced to match the new
// MaxOpenConns limit.
//
// If n <= 0, then there is no limit on the number of open connections.
// The default is 0 (unlimited).

9.2. SetMaxIdleConns

在连接池中可闲置的连接数，这个SetMaxIdleConns数据可以控制保活的空闲连接数，即使用完后不立马释放，做到连接资源复用；

如果连接资源平摊到各个计算节点不是很紧张，一般会将 SetMaxIdleConns 值直接配置成和 SetMaxOpenConns 一样大 (这部分可以直接在test用例中验证，即处于WaitCount的数量最少)，最大化连接利用率；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
// SetMaxIdleConns sets the maximum number of connections in the idle
// connection pool.
//
// If MaxOpenConns is greater than 0 but less than the new MaxIdleConns,
// then the new MaxIdleConns will be reduced to match the MaxOpenConns limit.
//
// If n <= 0, no idle connections are retained.
//
// The default max idle connections is currently 2. This may change in
// a future release.

9.3. db 连接状态 - `db.Stats()`

1
2
// 最大连接数, 当前打开的连接数, 当前闲置的连接数，等待连接累计，累计等待时间，最大闲置关闭，最大闲置超时关闭，最大生存期关闭
db status: {MaxOpenConnections:100 OpenConnections:100 InUse:100 Idle:0 WaitCount:2285 WaitDuration:31m54.706463293s MaxIdleClosed:0 MaxIdleTimeClosed:0 MaxLifetimeClosed:0}

9.4. 连接资源释放问题 - `db.Close()`

注意，在Query操作完后，应该及时释放资源，避免资源无法回收导致后续连接超时！

1
2
3
4
5
6
7
// rows, err := db.Query("select * from user where name like ?", "%"+s) : 可以人为改成为慢查询，这样连接池就会被打爆
rows, err := db.Query("select * from user where name like ?", s)
if err != nil {
    log.Errorf("read db query got err: %s", err)
    continue
}
defer rows.Close() // 不关闭的话，会有资源死锁问题

10. 小结

通过docker快速搭建了一个mysql测试环境，可以方便复用
可以通过mytop 工具观察连接情况
在go中使用database-sql连接池配置需要结合数据库的 max_connections(limit)、以及连接数据库的节点数(N)、并发量综合考虑，通常为了提升SQL连接利用率，一般会将 SetMaxIdleConns 值直接配置成和 SetMaxOpenConns 一样大
慢查询监控和告警应该关注和解决，可以配备相应Kill机制(需要确认对业务无影响)，避免连接池中的连接资源耗尽