开发笔记:MySQL45讲提升性能的临时方案

业务高峰期&＃xff0c;生产环境的mysql压力太大&＃xff0c;没法正常响应&＃xff0c;需要短期内临时性地提升一些性能

接下来看看这些临时方案&＃xff0c;并着重说说可能存在的风险

一、短连接风暴

正常的短连接模式就是连接到数据库后&＃xff0c;执行很少的SQL语句就断开&＃xff0c;下次需要的时候再重连
如果使用的是短连接&＃xff0c;在业务高峰期的时候&＃xff0c;就可能出现连接数突然暴涨的情况

1.1 短连接成本

MySQL建立连接的过程&＃xff0c;成本是很高的&＃xff0c;除了正常的网络连接三次握手外&＃xff0c;还需要做登录权限判断和获得这个连接的数据读写权限

在数据库压力比较小的时候&＃xff0c;这些额外的成本并不明显
但是&＃xff0c;短连接模型存在一个风险&＃xff0c;就是一旦数据库处理得慢一些&＃xff0c;连接数就会暴涨

1.2 max_connections

max_connections参数&＃xff0c;用来控制一个MySQL实例同时存在的连接数的上限

超过这个值&＃xff0c;系统就会拒绝接下来的连接请求&＃xff0c;并报错提示Too many connections
对于被拒绝连接的请求来说&＃xff0c;从业务角度看就是数据库不可用

在机器负载比较高的时候&＃xff0c;处理现有请求的时间变长&＃xff0c;每个连接保持的时间也更长&＃xff0c;这时&＃xff0c;再有新建连接的话&＃xff0c;就可能会超过max_connections的限制

碰到这种情况时&＃xff0c;一个比较自然的想法&＃xff0c;就是调高max_connections的值

但这样做是有风险的&＃xff0c;因为设计max_connections这个参数的目的是想保护MySQL&＃xff0c;如果把它改得太大&＃xff0c;让更多的连接都可以进来&＃xff0c;那么系统的负载可能会进一步加大&＃xff0c;大量的资源耗费在权限验证等逻辑上&＃xff0c;结果可能是适得其反&＃xff0c;已经连接的线程拿不到CPU资源去执行业务的SQL请 求

那么这种情况下&＃xff0c;还有没有别的建议呢&＃xff1f;接下来有两种有损的处理方法

1.3 处理连接但不工作的线程

第一种方法是先处理掉那些占着连接但是不工作的线程

max_connections的计算&＃xff0c;不是看谁在running&＃xff0c;是只要连着就占用一个计数位置
对于那些不需要保持的连接&＃xff0c;可以通过kill connection主动踢掉&＃xff0c;这个行为跟事先设置wait_timeout的效果是一样的

设置wait_timeout参数表示的是&＃xff0c;一个线程空闲wait_timeout这么多秒之后&＃xff0c;就会被MySQL直接断开连接

但是需要注意&＃xff0c;在show processlist的结果里&＃xff0c;踢掉显示为sleep的线程&＃xff0c;可能是有损的

看看下面这个例子

在上面这个例子里&＃xff1a;

• 如果断开session A的连接&＃xff0c;因为这时候session A还没有提交&＃xff0c;所以 MySQL只能按照回滚事务来处理
• 如果断开session B的连接&＃xff0c;就没什么大影响

所以&＃xff0c;如果按照优先级来说&＃xff0c;你应该优先断开像session B这样的事务外空闲的连接

但是&＃xff0c;怎么判断哪些是事务外空闲的呢&＃xff1f;session C在T时刻之后的30秒执行show processlist的结果如下&＃xff1a;

上图中id&＃61;4和id&＃61;5的两个会话都是Sleep 状态

要看事务具体状态的话&＃xff0c;可以查information_schema库的innodb_trx表

select * from information_schema.innodb_trx;


执行结果如下&＃xff1a;

这个结果里&＃xff0c;trx_mysql_thread_id&＃61;4&＃xff0c;表示id&＃61;4的线程还处在事务中

因此&＃xff0c;如果是连接数过多&＃xff0c;可以优先断开事务外空闲太久的连接&＃xff0c;如果这样还不够&＃xff0c;再考虑断开事务内空闲太久的连接

从服务端断开连接使用的是kill connection &＃43; id的命令&＃xff0c; 一个客户端处于sleep状态时&＃xff0c;它的连接被服务端主动断开后&＃xff0c;这个客户端并不会马上知道&＃xff0c;直到客户端在发起下一个请求的时候&＃xff0c;才会收到如下报错

ERROR 2013 (HY000): Lost connection to MySQL server during query


从数据库端主动断开连接可能是有损的&＃xff0c;尤其是有的应用端收到这个错误后&＃xff0c;不重新连接&＃xff0c; 而是直接用这个已经不能用的句柄重试查询&＃xff0c;这会导致从应用端看上去&＃xff0c;“MySQL一直没恢复”

1.4 减少连接过程的消耗

第二种方法是减少连接过程的消耗

有的业务代码会在短时间内先大量申请数据库连接做备用&＃xff0c;如果现在数据库确认是被连接行为打挂了&＃xff0c;那么一种可能的做法&＃xff0c;是让数据库跳过权限验证阶段

跳过权限验证的方法是&＃xff1a;重启数据库&＃xff0c;并使用–skip-grant-tables参数启动
这样&＃xff0c;整个MySQL会跳过所有的权限验证阶段&＃xff0c;包括连接过程和语句执行过程在内

但是&＃xff0c;这种方法风险极高&＃xff0c;特别不建议使用的方案&＃xff0c;尤其数据库外网可访问的话&＃xff0c;就更不能这么做了

在MySQL 8.0版本里&＃xff0c;如果启用–skip-grant-tables参数&＃xff0c;MySQL会默认把 --skip-networking参数打开&＃xff0c;表示这时候数据库只能被本地的客户端连接 可见&＃xff0c;MySQL官方对skip-grant-tables这个参数的安全问题很重视

除了短连接数暴增可能会带来性能问题外&＃xff0c;实际上&＃xff0c;在线上碰到更多的是查询或者更新语句导致的性能问题

其中&＃xff0c;查询问题比较典型的有两类&＃xff1a;

1. 由新出现的慢查询导致
2. 由QPS(每秒查询数)突增导致

接下来看看慢查询的这两种典型的问题

二、慢查询性能问题

在MySQL中&＃xff0c;会引发性能问题的慢查询&＃xff0c;大体有以下三种可能&＃xff1a;

1. 索引没有设计好
2. SQL语句没写好
3. MySQL选错了索引

接下来具体分析这三种可能&＃xff0c;以及对应的解决方案

2.1 索引没有设计好

导致慢查询的第一种可能是&＃xff0c;索引没有设计好&＃xff0c;这种场景一般就是通过紧急创建索引来解决

2.2 语句没写好

导致慢查询的第二种可能是&＃xff0c;语句没写好&＃xff0c;这时可以通过改写SQL语句来处理

2.3 MySQL选错索引

导致慢查询的第三种可能是&＃xff0c;MySQL选错索引&＃xff0c;这时的应急方案就是给这个语句加上force index

2.4 预先发现问题

上面由慢查询导致性能问题的三种可能情况&＃xff0c;实际上出现最多的是前两种&＃xff0c; 即&＃xff1a;索引没设计好和语句没写好&＃xff0c;而这两种情况&＃xff0c;恰恰是完全可以避免的

通过下面这个过程&＃xff0c;可以预先发现问题

1. 上线前&＃xff0c;在测试环境&＃xff0c;把慢查询日志(slow log)打开&＃xff0c;并且把long_query_time设置成 0&＃xff0c;确保每个语句都会被记录入慢查询日志
2. 在测试表里插入模拟线上的数据&＃xff0c;做一遍回归测试
3. 观察慢查询日志里每类语句的输出&＃xff0c;特别留意Rows_examined字段是否与预期一致

三、QPS突增问题

有时候由于业务突然出现高峰&＃xff0c;或者应用程序bug&＃xff0c;导致某个语句的QPS突然暴涨&＃xff0c;也可能导 致MySQL压力过大&＃xff0c;影响服务

由新功能的bug导致这样的情况时&＃xff0c;最理想的情况是让业务把这个功能下掉&＃xff0c;服务自然就会恢复

3.1 QPS突增处理方式

而下掉一个功能&＃xff0c;如果从数据库端处理的话&＃xff0c;对应于不同的背景&＃xff0c;有不同的方法可用&＃xff0c;接下来看看如何处理

1. 全新业务的bug导致
   假设DB运维是比较规范的&＃xff0c;也就是说白名单是一个个加的&＃xff0c;这种情况下&＃xff0c;如果能够确定业务方会下掉这个功能&＃xff0c;只是时间上没那么快&＃xff0c;那么就可以从数据库端直接把白名单去掉

2. 新功能使用单独数据库用户
   如果这个新功能使用的是单独的数据库用户&＃xff0c;可以用管理员账号把这个用户删掉&＃xff0c;然后断开现有连接
   这样&＃xff0c;这个新功能的连接不成功&＃xff0c;由它引发的QPS就会变成0

3. 新功能和主体功能一起
   如果这个新增的功能跟主体功能是部署在一起&＃xff0c;那么只能通过处理语句来限制
   这时&＃xff0c;可以使用上面提到的查询重写功能&＃xff0c;把压力最大的SQL语句直接重写成select 1返回

3.2 风险分析

针对方案三的操作风险极高&＃xff0c;而且需要特别细致&＃xff0c;还可能存在两个副作用&＃xff1a;

1. 如果别的功能里面也用到了这个SQL语句模板&＃xff0c;会有误伤
2. 很多业务并不是靠这一个语句就能完成逻辑的&＃xff0c;所以如果单独把这一个语句以select 1的结 果返回的话&＃xff0c;可能会导致后面的业务逻辑一起失败

所以&＃xff0c;方案3和前面提到的去掉权限验证一样&＃xff0c;应该是所有选项里优先级最低的一个方案

其实方案1和2都要依赖于规范的运维体系&＃xff1a;

• 虚拟化
• 白名单机制
• 业务账号分离

由此可见&＃xff0c;更多的准备&＃xff0c;往往意味着更稳定的系统