22 mysql有那些”饮鸩止渴”提高性能的方法?

时间:2022-09-22 12:25:48

22 mysql有那些”饮鸩止渴”提高性能的方法?

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

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

在数据库压力比较小的时候,这些额外的成本并不明显。

但是,短连接模型存在一个风险,就是一旦数据处理得慢一些,连接数就会暴涨。max_connections参数,用来控制一个mysql实例同时存在的连接数的上限,

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

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

碰到这种情况,一个比较自然的想法,就是调高max_connections的值,这样做是有风险的,因为设计max_connections的这个参数的目的是想保护MySQL

如果改得太大,让更多的连接都可以进来,那么系统的负载可能会进一步加大,大量的资源耗费在权限验证等逻辑上,结果可能适得其反,已经连接的线程拿不到cpu资源去执行业务的SQL请求。

这种情况下,建议两种方法:

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

max_connections的计算,不是看谁在running,是只要连着就占用一个计数位置。对于那些不需要保持的连接,可以通过kill connection主动踢掉。

这个行为跟事先设置wait_timeout的效果是一样的。设置wait_timeout参数表示的是,一个线程空闲wait_timeout这么多秒之后,就会被MySQL直接断开连接。

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

 

SESSION A

SESSION B

SESSION C

T

begin;

insert into t values(1,1);

select *from t where id =1;

 

T+30S

 

 

show processlist;

在上面的例子中,如果断开session A的连接,因为这时候还没有提交,所以mysql只能按照回滚事务来处理,而断开session B的连接,

就没什么大的影响。所以,如果按照优先级来说,应该处理像session B这样的事务外空闲的session

怎么判断事务外空闲的呢,执行show processlist

22 mysql有那些”饮鸩止渴”提高性能的方法?

其中,处于sleep状态的会话,要看具体的事务状态的话,可以查看

(system@127.0.0.1:3306) [(none)]> select * from information_schema.innodb_trx\G;

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

从服务器端断开连接使用的是kill connection +id的命令,一个客户端处于sleep状态时,它的连接被server主动断开后,

这个客户端并不会马上知道,直到客户端发起下一个请求的时候,才会收到这样的错误ERROR 2013 (HY000): Lost connection to MySQL server during query”。

22 mysql有那些”饮鸩止渴”提高性能的方法?

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

所以,如果是一个支持业务的DBA,不要假设所有的应用代码都会被正确处理,即使只是一个断开连接的操作,也要确保通知到业务开发团队。

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

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

方法,重启数据库,并使用参数-skip-grant-tables参数启动,这样,这个mysql会跳过所有的权限认证阶段,包括连接过程和语句执行过程在内。

但是,这种方法是符合”饮鸩止渴”,风险极高,是特别不建议的方案,尤其是库外网可以访问的话,就更不能这么做。

mysql 8.0的版本里,打开参数-skip-grant-tables参数,mysql会默认把-skip-networking参数打开,表示这个时候数据库只能被本地的客户端连接,可见,对安全的问题的重视。

慢查询性能问题

mysql中,会引发性能问题的慢查询,大体有一下三种可能:

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

分析这三种可能,以及应对的方案

导致慢查询的第一种可能,索引没有设计好

这种场景一般就是通过紧急创建索引来解决。Mysql5.6版本以后,创建索引支持online ddl,对于那种高峰期数据库已经被这个语句打挂了情况,最高效的做法就是直接执行alter table语句。

比较理想的是能够在备库先执行,假设现在一主一备,主库A、备库B

1 在备库B上执行set sql_log_bin=off,不写binlog,然后执行alter table 加上索引

2 执行主备切换

3 在新备库A上执行set sql_log_bin=off,不写binlog,然后alter 加上所以

这是一个古老的ddl方案,平时在做变更的时候,可以考虑类似的go-ost的方案,比如ptoak等工具

导致慢查询的第二种可能,语句没写好

比如select * from t where id +1=9999;

这种,我们可以通过改写sql来处理,mysql5.7.6之后提供了query_rewrite功能,可以把输入的一种语句改写成为另外一种模式。

这里可以找到query_rewrite的用法

(system@127.0.0.1:3306) [(none)]> show variables like '%plug%';

+---------------+--------------------------+

| Variable_name | Value                    |

+---------------+--------------------------+

| plugin_dir    | /usr/lib64/mysql/plugin/ |

+---------------+--------------------------+

--create table

--create procedure

--mysql -u root -p < install_rewriter.sql

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

 

call query_rewrite.flush_rewrite_rules();

这里调用过程,是让插入的规则生效,也就是查询重新

导致慢查询的第三种可能,就是mysql使用错误的索引

这时候的应急方案就是给这个语句加上force index

同样的,使用查询重写功能,给原来的语句加上for index,也可以解决这个问题

 

预发现问题
1上线前,在测试环境,把慢查询(slow log)打开,设置long_query_time=0,确保每个语句都会记录慢查询日志

2 在测试表里插入模拟线上的数据,做一遍回归测试

3 观察慢查询日志里每类语句的输出,特别留意Rows_examined字段是否与预期的一致

如果新增的语句sql不多,手动跑一下,而如果是新项目的话,或者修改原有项目表结构设计,全量回归测试都是必要的,这时候,可以用工具来检查所有的sql的返回结果--pt-query-digest(https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html)

QPS突增问题

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

之前碰到过一类情况,是由一个新功能的bug导致的,当然,最理想的情况让业务把这个功能下掉,服务就会自然恢复。

而下掉一个功能,如果从数据库端处理的话,对应于不同的背景,有不同的方法可用。

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

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

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

当然,这个操作的风险很高,需要特别的细致,它可能存在两个副作用

1 如果别的功能里面也有用到这个sql语句模板,会有误伤

2 很多业务并不是靠这一个语句就完成逻辑,所以如果单独把这个语句以select 1 返回结果的话,可能会导致后面的业务逻辑的一起失败

所以,方案3是用于止血的,跟前面提到的去掉用户权限验证一样,应该是方案里优先级最低的一个。

小结

以业务高峰期的性能问题为背景,介绍了一些紧急的处理手段。

这些手段中,既包括了粗暴的拒绝连接和断开连接,也有通过查询重写来绕过一些坑,既有临时的高危方案,又有相对安全的预案。

在实际开发中,我们要尽量避免一些低效的做法,比如大量的使用短连接,同时,做业务开发,要知道,连接异常断开是常有的事,代码里要有正确的重连并重试的机制。

DBA虽然可以通过语句重写来暂时处理问题,但这本身是一个风险极高的操作,做好SQL审计以减少需要这样操作的机会。

 

上起问题解答

SESSION A

SESSION B

SESSION C

begin;

select * from t20 where c>=15 and c<=20 order by c desc for update;

 

 

 

insert into t20

values(11,11,11);(blocked)

 

 

 

insert into t20 values(6,6,6);(blocked)

SESSION B阻塞已经不用分析,来分析SESSION C为什么会阻塞

1 session A加锁分析,由于是order by c desc,第一个要定位的是索引c上的最右边”c=20的行,所以会加上间隙锁(20,25)next-key lock(15,20]

2 索引c向左遍历,要扫描到c=10才停下来,所以next-key lock会加到(5,10],这个正是阻塞session c的原因

3 在扫描过程中,c+20,c=15,c=10这三个值都存在,由于是select * ,所以会在主键id上加3个行锁

因此SESSION A的加锁返回

1 索引c(5,25)

2 主键索引上c=10,c=15,c=20 三个行锁。