Scott's world.

MySQL-“饮鸩止渴”提高性能的方法笔记

Word count: 2.1kReading time: 7 min
2020/02/18 Share

MySQL-“饮鸩止渴”提高性能的方法笔记

通常我们有时候会遇到在业务高峰期,生产环境的MySQL压力太大,没法正常响应,需要短期内、临时性地提升一些性能。

所以今天的内容就是来聊聊一些可以快速解决性能问题但又有损的临时方案。

短连接风暴

正常的短连接模式:连接到数据库后,执行很少的SQL语句就断开,下次需要的时候再重连。

如果使用的是短连接,在业务高峰期的时候,就可能出现连接数突然暴涨的情况。这就会出现短连接风暴的问题。

首先我们知道MySQL建立连接的过程,除了正常的网络连接三次握手外,还需要做登录权限判断和获得这个连接的数据读写权限。所以成本还是很高的。

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

max_connections参数

max_connections参数:用来控制一个MySQL实例同时存在的连接数的上限,超过这个值,系统就会拒绝接下来的连接请求。

当我们遇到机器负载较高的情况,如果连接超过max_connections的限制,从而导致连接被拒绝,而被拒绝连接的请求来说,从业务角度来说就是数据库不可用。

碰到这种情况我们可能会想到调高max_connections的值。但这样做是有风险的。

设计max_connections这个参数的目的是想保护MySQL,让系统的负载得到合理控制,而如果把改得太多把大量的资源耗费在权限验证等逻辑上,可能会出现已经连接的线程拿不到CPU资源去执行业务的SQL请求。

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

首先先考虑其优先级,也就是将事务外空闲的连接先断开,再然后考虑断开事务内空闲太久的连接。

通过show processlist查看事务情况,而要看事务具体状态的话查看infromation_schema库的innodb_trx表来判断哪些事务是空闲的

从服务端断开连接使用的是`kill connection + id的命令, 一个客户端处于sleep状态时,它的连接被服务端主动断开后,这个客户端并不会马上知道。直到客户端在发起下一个请求的时候,才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

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

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

我们前面知道短连接风暴出现有部分原因是因为与MySQL建立连接的成本提高,特别是在权限验证阶段。

所以如果确认数据库是被连接行为打挂时有一种可能的做法就是让数据库跳过权限验证阶段。

跳过权限验证的方法是:重启数据库,并使用–skip-grant-tables参数启动

但这种方法风险极高,是特别不建议使用的方案。尤其你的库外网可访问的话,就更不能这么做了。

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

慢查询性能问题

索引没有设计好

这种场景一般就是通过紧急创建索引来解决。

MySQL 5.6版本以后,创建索引都支持Online DDL了,对于那种高峰期数据库已经被这个语句打挂了的情况,最高效的做法就是直接执行alter table 语句。

比较理想的是能够在备库先执行。假设你现在的服务是一主一备,主库A、备库B,这个方案的大致流程是这样的:

  1. 在备库B上执行set sql_log_bin=off,也就是不写binlog,然后执行alter table语句加上索引;
  2. 执行主备切换;
  3. 这时候主库是B,备库是A。在A上执行 set sql_log_bin=off,然后执行alter table 语句加上索引。

这是一个“古老”的DDL方案。平时在做变更的时候,你应该考虑类似gh-ost这样的方案,更加稳妥。但是在需要紧急处理时,上面这个方案的效率是最高的。

语句没写好

这时,我们可以通过改写SQL语句来处理。

MySQL 5.7提供了query_rewrite功能,可以把输入的一种语句改写成另外一种模式。即查询重写

如下这一句sql语句

1
2
3
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。

预先发现问题

上面所说的三种情况,实际上出现最多的是前两种。而这两种情况,恰恰是可以完全避免的。

比如,通过下面这个过程就可以预先发现问题。

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

QPS突增问题

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

所以最理想的情况是让业务把这个出现问题的部分下掉,服务自然就会恢复。

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

  1. 一种是由全新业务的bug导致的。假设你的DB运维是比较规范的,也就是说白名单是一个个加的。这种情况下,如果你能够确定业务方会下掉这个功能,只是时间上没那么快,那么就可以从数据库端直接把白名单去掉。
  2. 如果这个新功能使用的是单独的数据库用户,可以用管理员账号把这个用户删掉,然后断开现有连接。这样,这个新功能的连接不成功,由它引发的QPS就会变成0。
  3. 如果这个新增的功能跟主体功能是部署在一起的,那么我们只能通过处理语句来限制。这时,我们可以使用上面提到的查询重写功能,把压力最大的SQL语句直接重写成”select 1”返回。

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

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

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

同时你会发现,其实方案1和2都要依赖于规范的运维体系:虚拟化、白名单机制、业务账号分离。由此可见,更多的准备,往往意味着更稳定的系统。

CATALOG
  1. 1. MySQL-“饮鸩止渴”提高性能的方法笔记
    1. 1.1. 短连接风暴
      1. 1.1.1. max_connections参数
      2. 1.1.2. 第一种方法:处理掉那些占着连接但是不工作的线程
      3. 1.1.3. 第二种方法:减少连接过程的消耗
    2. 1.2. 慢查询性能问题
      1. 1.2.1. 索引没有设计好
      2. 1.2.2. 语句没写好
      3. 1.2.3. MySQL选错了索引
      4. 1.2.4. 预先发现问题
    3. 1.3. QPS突增问题