优化及排查

MySQL-15.短连接风暴、查询语句导致的性能问题及临时性解决

2019-08-15  本文已影响20人  王侦

1.短连接风暴

现象描述：

1.1 先处理掉那些占着连接但是不工作的线程。

max_connections 的计算，不是看谁在 running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。这个行为跟事先设置wait_timeout 的效果是一样的。设置 wait_timeout 参数表示的是，一个线程空闲wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。


如果断开 session A 的连接，因为这时候 session A 还没有提交，所以MySQL 只能按照回滚事务来处理；而断开 session B 的连接，就没什么大影响。所以，如果按照优先级来说，你应该优先断开像 session B 这样的事务外空闲的连接。

mysql> show processlist;
+---------+-------------+-----------------------+----------+---------+------+----------+------------------+
| Id      | User        | Host                  | db       | Command | Time | State    | Info             |
+---------+-------------+-----------------------+----------+---------+------+----------+------------------+
| 2421400 | wz          | 47.99.62.246:58886    | sqllearn | Sleep   |   28 |          | NULL             |
| 2421401 | wz          | 47.99.62.246:58888    | sqllearn | Sleep   |   13 |          | NULL             |
| 2421402 | wz          | 47.99.62.246:58890    | sqllearn | Query   |    0 | starting | show processlist |
+---------+-------------+-----------------------+----------+---------+------+----------+------------------+

查看事务具体的状态：

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 2743315
                 trx_state: RUNNING
               trx_started: 2019-08-15 08:38:28
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 2421400
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

可以看出 trx_mysql_thread_id: 2421400线程还在事务中。

断开连接：

mysql> kill connection 2421400;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_20 where id=22;
Empty set (0.00 sec)

mysql> select * from test_20 where id=22;
ERROR 2013 (HY000): Lost connection to MySQL server during query

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

1.2 减少连接过程的消耗——风险极高

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

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

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

2.慢查询性能问题

在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

2.1 索引没有设计好

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

操作流程：

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

2.2 语句没写好

通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。

语句被错误地写成了 select * from t where id + 1 = 10000，可以进行如下处理：

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

call query_rewrite.flush_rewrite_rules();

该特性需要新装一个数据库：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| query_rewrite      |
| sys                |
+--------------------+

2.3 MySQL选错了索引

应急方案就是给这个语句加上 force index。同样地，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题。

预先发现问题的方法：

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

3.QPS突增问题

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

最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

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

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

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

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

4.其他问题

上一篇下一篇

猜你喜欢

热点阅读