mysql 连接存活时间_MySQL连接池与超时设定

Introduction

// $GOROOT/src/database/sql/sql.go

func SetMaxIdleConns(n int){}

func SetMaxOpenConns(n int){}‌复制代码

写一个网络后端, 启动数据库, 连接数据库, 开始之前配置你的数据库. 流量一大, 各种问题都出来了, 你开始认识到Mysql是一整套系统, 一套需要配置的系统.

以上两个函数是什么? 什么是MySQL连接,什么是连接池. 我应该怎么配置? 等你知道这些东西是什么, 怎么工作的. 你立刻就能理解应该怎么去配置他们.

背景知识

什么是连接? 什么是连接池?‌

现在你是一个go程序, 你需要用数据库, 你叮一下数据库, 告诉数据库我想查数据库. 然后你开始查. 你叮的哪一下就产生了一个连接. 你使用哪个连接来查询. 但是, 每查一次就叮一次, 这样是不是不合理, 很浪费时间. 事实上(默认参数下)MySQL会把你那个连接保存8小时, 也就是说8小时内, 你拿着这个连接去查数都是可以的. 如果超过8小时没人用这个链接, MySQL就会关掉这个链接.

‌你可以把那个连接存起来, 或者存5个连接, 想用的时候, 就从里面拿一个出来用一下, 这就构成了连接池.现在我们可以开始解释上面两个函数是干什么的了:

SetMaxOpenConns: 我允许你最多开这么多个连接

SetMaxIdleConns: 连接池里最多有这么多连接

假设这个时刻下现在有4个连接查询完毕, 那这些连接去哪儿呢? 一部分去了连接池, 连接池放不下的, 多余的部分就会直接关闭. 那么MaxOpenConns呢? 如果你的MaxOpen设置成3, 那根本就不会有4个连接同时返回. 因为你最多只能开3个连接, 第四个起就需要排队等.‌

多说一句, 所谓的排队等, 本质上就是先(在一个map里)登记一下, 然后守着一个chanel等. 等正在运行的请求结束了, 开始收尾了, 再去map里查看这个请求, 并往chanel塞上一个连接.

MySQL的超时参数

wait_timeout: 一个连接会有idle以及open两种状态, 那么一个长期处于idle的链接, MySQL服务器就会想要关掉它. 这个"长期"指的是多久? 就是这个参数指定的时长.官方文档‌

我多说一句, 网上有人喜欢提interactive_timeout的概念, 这个参数对于你后端链接毫无作用, 什么是interactive, 是你拿着键盘在mysql命令行下敲select, 这个叫interactive.引用

服务端的超时参数

MaxLifetime: 除了这些, 你的服务端自己也会关掉一些时间比较长/或者说比较"老"/比较"年龄大"的链接. 这个参数通过SetConnMaxLifetime设置, 假设你设置这个时间为1小时, 那么理论上一小时后, 这个连接就会被关闭.

请注意这个时间跟上面的时间并不是一个概念, MySQL关注的是这个链接idle多久了, idle够久了再关. 但是ConnLifeTime关注的是从创建开始, 到现在, 时间到了就关.

ReadTimeout: 在我们启动MySQL命令行的时候会有一个参数叫做readTimeout, 这个参数的意思是 : 从我发送请求开始, 如果到了时间我还没拿到我要的数据, 那么就算超时.‌

同样是服务端控制的, 跟上面不同的是Lifetime指的是一个连接的生命周期, 而这个则针对一次请求, 强调请求响应的快与慢. i/o timeout的问题常见于网络拥堵的环境下.

实验

调整wait_timeout带来的表现

wait_timeout:使用一个被mysql断开的链接: 如下所示, 首先我们把wait_timeout设置成1, 也就是说任何连接只要idle时间超过1立刻被断开. 随后, 我们修改源码, 在获得数据库链接对象以后, 休眠10秒, 这个时候你拿到的就是一个已经 被mysql断开的链接. 这种情况下会发生什么呢?

//调整mysql数据库设置

mysql > set global wait_timeout = 1

mysql > select @@global.wait_timeout

//服务器: $GOROOT/src/database/sql/sql.go

func(db* DB)query() {

dc,err := db.conn()

time.Sleep(10 * time.Second)

...

}复制代码

packets.go:122: closing bad idle connection: EOF

packets.go:36: unexpected EOF

panic: invalid connection

而上面的panic是我代码中的:rows,err := db.Query(), 也就是说在使用一个被服务器断开的链接的情况下, 会报错invalid connection , 至此, 我们已经知道了, 我们遇到的invalid connection到底是因为什么而出现的, 也知到"无效链接",到底是什么东西无效了

调整SetConnMaxLifetime的表现‌

SetConnMaxLifetime:如果是自己断开的链接 SetConnMaxLifetime是指你的go程序自己断开链接, 需要多久. 我们尝试一下这种情况下会发生什么. 首先我们知道,在默认表现下, 你只要db := sql.Open()就能获得一个连接, 缓存起来,然后下次查询的时候用起来. 就像下面一样:

numFree = 0       # 这是你Open的时候, 刚开数据库, 还没有可用连接

Retry = 0             # 你准备开始查询了, 三次重试, 这是第一次

numFree = 1       # 诶? 你发现你有一个缓存的连接, 直接拿来用

现在我们改一改, 把SetConnMaxLifetime设置成1, 然后再睡10秒, 这样你通过Open缓存的连接就无效了, 这样会发生什么?

db.err := sql.Open()

db.SetConnMaxLifetime(1)

time.Sleep(10 * time.Second)

results,err := db.Query()复制代码

numFree = 0            # Open缓存一个

Retry = 0                   # 开始查询

numFree = 0             # 超时, 无可用连接对象

here id = 239611      # 正确的输出

虽然已经超时了, 但是我们的服务端正确识别了已经被关闭的链接(毕竟也是你自己关的嘛), 在没有缓存连接的情况下, 服务端创建了一个新链接, 并使用起来, 没有报错

调整MaxOpen的表现

如果调整的是MaxOpen, 假设我们令它为1, 我们这个程序一次就只能发起一个数据库请求. 那也就是说如果我们并发的两个数据库请求, 第二个必然会等待. 会发生什么呢?

// $GOROOT/database/sql/sql.go

dc, err := db.conn(ctx, strategy)

time.Sleep(10 * time.Second)

// main.go 我的程序

db.SetMaxOpenConns(1)

go db.Query()

db.Query()

select{}复制代码

这种情况下, 两个查询里必然有一个会先拿到链接对象, 并进入sleep, 那么另一个就会一直等到第一个结束再拿到链接对象. 结果一切正常, 除了等待的时间长了一点以外, 没有任何超时或者报错的迹象

调整readTimeout的表现‌

虽然制造网络拥堵的条件不太容易(有位老哥在防火墙上做文章复现了这个场景/链接 ), 但是我们可以把这个超时限制设置的非常苛刻, 1毫秒, 你不返回我就算你超时.

// 超时时间非常苛刻, 设置成1ms

read := "&readTimeout=1ms"

source := "user:password@tcp/localhost?root" + read

db,err = gorm.Open("mysql", source)复制代码

packets.go:36: read tcp 127.0.0.1:64897->127.0.0.1:3306: i/o timeout

packets.go:36: read tcp 127.0.0.1:64897->127.0.0.1:3306: i/o timeout

packets.go:36: read tcp 127.0.0.1:64897->127.0.0.1:3306: i/o timeout

出现了因为没有及时读取到想要的数据, 而产生的i/o timeout报错‌

结论: 我到底应该怎么办

让你的Go程序自己结束链接, 不要让服务器终端链接, 因为你自己中断链接至少是安全的. 令: SetConnMaxLifetime < wait_timeout‌

假设你的wait_timeout是8小时, 那么你的链接起步就能存活8小时(从头到尾idle), ,那么你就设置SetConnMaxLifetime为7小时, 那你就一定能在MySQL中断之前, 自己先中断了, 安全的不行

Reference