记一次高并发下Mysql调优过程

最近业务上遇到一个需要频繁更新数据库的业务,随着接口的QPS的暴增,数据库也出现了一些之前没有见过的棘手的问题。这里做一下总结,记录一下在高并发操作数据库情境下需要注意的一些点。

Part1: 理解数据库连接(TCP)

首先,我们先来确定一下数据库连接的本质,Mysql的数据连接本质上是一个TCP连接,当然也可以是一个TCP长连接,这里会涉及到一些MYSQL的参数,后续会讲到。而TCP连接,需要一个四元组(即源主机IP+源主机端口+MYSQL主机IP+MYSQL主机端口),才能确定的一个连接。当我们创建每当创建出一个新的连接后,都会占用一个新的主机端口,而MYSQL的端口一般都是默认的3306,也可以是自己设定的其他端口。 所以,当客户端不断创建新连接的时候,会不断耗费客户端主机的端口资源,当增速过快,则会出现一些问题。

这里可以做一个实验,我本机使用docker部署了一个mysql程序,端口为3306,内网MYSQL的地址为172.17.0.2

# MYSQL配置信息:

anderson@anderson:~/Anderson/deploy$ docker ps | grep mysql
dcec472f19a1  mysql "docker-entrypoint.s…"  13 months ago  Up 8 hours  0.0.0.0:3306->3306/tcp mysql

使用netstat查看MYSQL连接,这里使用MYSQL官方的workbench来连接,此时已创建的TCP连接如下:

anderson@anderson:~/Anderson/deploy$ netstat -n | grep 172.17.0.2:3306
tcp        0      0 172.17.0.1:44524        172.17.0.2:3306         ESTABLISHED
tcp        0      0 172.17.0.1:44528        172.17.0.2:3306         ESTABLISHED

我们可以看到,这里已经有两个已经创建的TCP连接建立了,分别占用了客户端的4452444528端口。那这里为什么会是两个连接呢?这里是mysql workbench自己额外建立了一个连接,此时,如果我们用mysql cli来建立一个连接:

anderson@anderson:~/Anderson/deploy$ mysql -h 172.17.0.2 -P 3306 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.21 MySQL Community Server (GPL)
Mysql > 

此时,我们再查看所有的TCP连接:

anderson@anderson:~/Anderson/deploy$ netstat -n | grep 172.17.0.2:3306
tcp        0      0 172.17.0.1:44540        172.17.0.2:3306         ESTABLISHED
tcp        0      0 172.17.0.1:44524        172.17.0.2:3306         ESTABLISHED
tcp        0      0 172.17.0.1:44528        172.17.0.2:3306         ESTABLISHED

发现多了一个44540端口,所以每次客户端创建一个新的数据连接,客户端主机都会创建一个全新的TCP连接

此时,我们关掉workbench创建的两个TCP连接,再查看:

anderson@anderson:~/Anderson/deploy$ netstat -n | grep 172.17.0.2:3306
tcp        0      0 172.17.0.1:44540        172.17.0.2:3306         ESTABLISHED
tcp        0      0 172.17.0.1:44528        172.17.0.2:3306         TIME_WAIT 

发现已经变成了两个,而且其中有个连接被标记为了TIME_WAIT状态。学过网络的都知道,TCP建立连接后,主动关闭连接的一方在主动发送最后一个ACK信号后进入TIME_WAIT状态,再最多停留2个MSL(Maximum Segment Lifetime最长报文段寿命)时间后会进入CLOSED状态,之后才由系统回收端口资源。所以主动关闭连接后,连接不是立马就会被释放的,而是有一定的延迟时间

Part2:理解数据库连接池

为了复用已经创建的TCP连接,一般通过创建一个数据库连接池来解决。因为MYSQL的TCP连接支持长连接,所以每次操作完数据库,可以不必直接关掉连接,而是等待下次使用的时候在复用这个连接。我们知道,所有的Socket长连接都是通过TCP自带的ping来维持心跳(TCP保活),从而保持连接状态,而我们熟悉的websocket,也正是通过TCP的心跳来维持连接不被中断。

数据库连接池即一系列连接的集合,当需要操作数据库时,从池中取出一个空闲的连接,当操作完成后,将连接重新归还到连接池中,等待下次取出使用,这样达到了连接复用的目的,因为数据库连接的创建是需要耗费资源和时间的

因为系统的资源是有限的,可用的端口数量也是有限的。在Linux中,所有外设包括连接可以被理解为文件描述的形式。

anderson@anderson:~/Anderson/deploy$ ulimit -n
1024

即默认情况下,系统限制了进程能够打开的文件描述符的最大数目,即连接池的最大的连接数也不能超过这个数值,当然这个数值可以修改,但是不应该全部使用

当数据库连接数超出了系统所限制的文件数时,就会出现这个错误:

Too many open files

除了数据库连接会遇到这个问题外,任何其他占用设备的都会引发这个错误,包括无限地使用httpClient来并发请求接口数据等等。

Part3:Mysql数据库相关配置

除了源主机对连接数有限制外,Mysql对连接也会有一定的限制,主要有下面这几个:

mysql> show status like 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 10    |
+---------------+-------+
1 row in set (0.00 sec)

Mysql的配置可以在全局变量中查询和设置,相关的配置主要可以查询下面这些:

配置 含义
Connections 尝试连接Mysql的连接数,不管连接成功与否,该值都会+1
Threads_connected 已经建立的连接数,单节点下一般小于最大连接池最大连接数
max_connections Mysql限制的最大的可连接的数量
wait_timeout 即MYSQL长连接(非交互式)的最大生命时长,默认是8小时
interactive_timeout 长连接(交互式)的最大生命时长,默认是8小时

首先,Connections限制了客户端可同时连接的最大的连接数max_connections,超出了这个连接数,会报以下错误:

query failed Error 1040: Too many connections

第二个Threads_connected可以查看当前数据库已连接的正常的数据库连接数目,查询这个值有助于排查问题。

另外,一个连接超过了wait_timeout后,MYSQL会主动断开连接,此时对于客户端来说是非感知的,当客户端用了一个MYSQL已经关闭的连接来操作数据库时,就会报以下错误:

packets.go:32: unexpected EOF
packets.go:118: write tcp 192.168.3.90:3306: broken pipe

在golang的mysql驱动中会主动使用该连接尝试两次,如果两次都失败,那么会连接池会主动放弃这个连接,并重新创建一个新的连接对象。

Part4: 高并发下连接池设置

高并发下,如果客户端的time_wait属性设置不当,会导致短时间内大量的TCP连接处于TIME_WAIT状态,这回造成系统大量的端口资源被占用,而导致以下异常:

Cannot assign requested address

一般数据库默认是8小时,客户端只需要将该值设置为略小于8小时即可。

Part5: 数据一致性问题

另外,高并发下,应当考虑数据安全问题,以及数据库锁问题。

Mysql支持类似于Mongo的upsert操作,即ON DUPLICATE KEY UPDATE,本质上Mysql会执行Insert语句,如果抛出以下异常:

Duplicate entry 'XXX' for key 'XXX'

那么,Mysql会继续执行Update操作。

在这个过程中,执行Insert语句时,会引发X锁(排他),在查询时,会引发S锁(共享),当有多个连接同时执行这个语句时,本质上还是会有机率出现死锁的情况,但是这个情况已经被Mysql自己处理回滚了,所以表面上看起来这个upset是一个原子操作。

另外,高并发下,数据库的设计也是一个值得思考的问题,良好的结构设计可以避免在并发下很多数据安全性问题。

Part6: 效率问题

  • 合并多次操作为一次批量操作,时间相当,但效率提升N倍
  • 加入缓存层,避免实时高并发读库
  • 加入消息队列层,避免实时高并发写库

Part7: Beego举例

//第一个整型参数为连接池的最大空闲连接数,即长时间没用的空闲连接数量
//第二个整型参数为连接池的最大可用连接数,即连接池的容量
orm.RegisterDataBase("default", "mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/use_duration?charset=utf8", user, password, host, port), 300, 300)

//这里设置客户端的长连接最大生存时长,即超过这个时间,客户端会主动断开这个连接
dbInfo, _ := orm.GetDB("default")
dbInfo.SetConnMaxLifetime(time.Hour * 7)
赞赏我吗