网站建设推销,阎良网站建设公司,宝塔一键部署wordpress,南昌网站建设_南昌做网站公司MySQL的“饮鸩止渴”提高性能的方法
HI#xff0c;我是阿昌#xff0c;今天学习记录的是关于MySQL的“饮鸩止渴”提高性能的方法的内容。
不知道在实际运维过程中有没有碰到这样的情景#xff1a;
业务高峰期#xff0c;生产环境的 MySQL 压力太大#xff0c;没法正常响…MySQL的“饮鸩止渴”提高性能的方法
HI我是阿昌今天学习记录的是关于MySQL的“饮鸩止渴”提高性能的方法的内容。
不知道在实际运维过程中有没有碰到这样的情景
业务高峰期生产环境的 MySQL 压力太大没法正常响应需要短期内、临时性地提升一些性能。以前做业务护航的时候就偶尔会碰上这种场景。
用户的开发负责人说不管用什么方案让业务先跑起来再说。但如果是无损方案的话肯定不需要等到这个时候才上场。 一、短连接风暴
正常的短连接模式就是连接到数据库后执行很少的 SQL 语句就断开下次需要的时候再重连。
如果使用的是短连接在业务高峰期的时候就可能出现连接数突然暴涨的情况。 在基础架构中说过MySQL 建立连接的过程成本是很高的。
除了正常的网络连接三次握手外还需要做登录权限判断获得这个连接的数据读写权限。
在数据库压力比较小的时候这些额外的成本并不明显。
但是短连接模型存在一个风险就是一旦数据库处理得慢一些连接数就会暴涨。
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 A 还没有提交所以 MySQL 只能按照回滚事务来处理而断开 session B 的连接就没什么大影响。
所以如果按照优先级来说你应该优先断开像 session B 这样的事务外空闲的连接。但是怎么判断哪些是事务外空闲的呢
session C 在 T 时刻之后的 30 秒执行 show processlist看到的结果是这样的。 图中 id4 和 id5 的两个会话都是 Sleep 状态。
要看事务具体状态的话可以查 information_schema 库的 innodb_trx 表。 这个结果里trx_mysql_thread_id4表示 id4 的线程还处在事务中。
因此如果是连接数过多可以优先断开事务外空闲太久的连接如果这样还不够再考虑断开事务内空闲太久的连接。
从服务端断开连接使用的是 kill connection id 的命令 一个客户端处于 sleep 状态时它的连接被服务端主动断开后这个客户端并不会马上知道。
直到客户端在发起下一个请求的时候才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。
从数据库端 主动断开连接可能是有损的 尤其是有的应用端收到这个错误后不重新连接而是直接用这个已经不能用的句柄重试查询。
这会导致从应用端看上去“MySQL 一直没恢复”。
可能觉得这是一个冷笑话但实际上碰到过不下 10 次。
所以如果是一个支持业务的 DBA不要假设所有的应用代码都会被正确地处理。
即使只是一个断开连接的操作也要确保通知到业务开发团队。
第二种方法减少连接过程的消耗。
有的业务代码会在短时间内先大量申请数据库连接做备用如果现在数据库确认是被连接行为打挂了那么一种可能的做法是让数据库跳过权限验证阶段。
跳过权限验证的方法是重启数据库并使用–skip-grant-tables 参数启动。
这样整个 MySQL 会跳过所有的权限验证阶段包括连接过程和语句执行过程在内。但是这种方法特别符合标题里说的“饮鸩止渴”风险极高是特别不建议使用的方案。尤其库外网可访问的话就更不能这么做了。 在 MySQL 8.0 版本里如果启用–skip-grant-tables 参数MySQL 会默认把 --skip-networking 参数打开表示这时候数据库只能被本地的客户端连接。可见MySQL 官方对 skip-grant-tables 这个参数的安全问题也很重视。
除了短连接数暴增可能会带来性能问题外实际上在线上碰到更多的是查询或者更新语句导致的性能问题。其中查询问题比较典型的有两类
一类是由新出现的慢查询导致的一类是由 QPS每秒查询数突增导致的。 二、慢查询性能问题
在 MySQL 中会引发性能问题的慢查询大体有以下三种可能
索引没有设计好SQL 语句没写好MySQL 选错了索引。
1、索引没有设计好
导致慢查询的第一种可能是索引没有设计好。
这种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后创建索引都支持 Online DDL 了对于那种高峰期数据库已经被这个语句打挂了的情况最高效的做法就是直接执行 alter table 语句。比较理想的是能够在备库先执行。 假设现在的服务是一主一备主库 A、备库 B这个方案的大致流程是这样的
在备库 B 上执行 set sql_log_binoff也就是不写 binlog然后执行 alter table 语句加上索引执行主备切换这时候主库是 B备库是 A。在 A 上执行 set sql_log_binoff然后执行 alter table 语句加上索引。
这是一个“古老”的 DDL 方案。平时在做变更的时候应该考虑类似 gh-ost 这样的方案更加稳妥。但是在需要紧急处理时上面这个方案的效率是最高的。
2、语句没写好
导致慢查询的第二种可能是语句没写好。
比如犯了在只查一行的语句也执行这么慢中提到的那些错误导致语句没有使用上索引。这时可以通过改写 SQL 语句来处理。
MySQL 5.7 提供了 query_rewrite 功能可以把输入的一种语句改写成另外一种模式。
比如语句被错误地写成了 select * from t where id 1 10000可以通过下面的方式增加一个语句改写规则。
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();这里call query_rewrite.flush_rewrite_rules() 这个存储过程是让插入的新规则生效也就是说的“查询重写”。 可以用图 4 中的方法来确认改写规则是否生效。 3、选错了索引
在MySQL 为什么有时候会选错索引中提到的情况MySQL 选错了索引。
这时候应急方案就是给这个语句加上 force index。同样地使用查询重写功能给原来的语句加上 force index也可以解决这个问题。 由慢查询导致性能问题的三种可能情况实际上出现最多的是前两种即索引没设计好和语句没写好。而这两种情况恰恰是完全可以避免的。
比如通过下面这个过程可以预先发现问题。
上线前在测试环境把慢查询日志slow log打开并且把 long_query_time 设置成 0确保每个语句都会被记录入慢查询日志在测试表里插入模拟线上的数据做一遍回归测试观察慢查询日志里每类语句的输出特别留意 Rows_examined 字段是否与预期一致。
不要吝啬这段花在上线前的“额外”时间因为这会帮你省下很多故障复盘的时间。如果新增的 SQL 语句不多手动跑一下就可以。而如果是新项目的话或者是修改了原有项目的 表结构设计全量回归测试都是必要的。这时候需要工具帮你检查所有的 SQL 语句的返回结果。比如可以使用开源工具 pt-query-digest 三、QPS 突增问题
有时候由于业务突然出现高峰或者应用程序 bug导致某个语句的 QPS 突然暴涨也可能导致 MySQL 压力过大影响服务。一类情况是由一个新功能的 bug 导致的。
当然最理想的情况是让业务把这个功能下掉服务自然就会恢复。而下掉一个功能如果从数据库端处理的话对应于不同的背景有不同的方法可用。
一种是由全新业务的 bug 导致的。假设 DB 运维是比较规范的也就是说白名单是一个个加的。这种情况下如果能够确定业务方会下掉这个功能只是时间上没那么快那么就可以从数据库端直接把白名单去掉。如果这个新功能使用的是单独的数据库用户可以用管理员账号把这个用户删掉然后断开现有连接。这样这个新功能的连接不成功由它引发的 QPS 就会变成 0。如果这个新增的功能跟主体功能是部署在一起的那么只能通过处理语句来限制。这时我们可以使用上面提到的查询重写功能把压力最大的 SQL 语句直接重写成select 1返回。当然这个操作的风险很高需要特别细致。
它可能存在两个副作用
如果别的功能里面也用到了这个 SQL 语句模板会有误伤很多业务并不是靠这一个语句就能完成逻辑的所以如果单独把这一个语句以 select 1 的结果返回的话可能会导致后面的业务逻辑一起失败。
所以方案 3 是用于止血的跟前面提到的去掉权限验证一样应该是所有选项里优先级最低的一个方案。同时你会发现其实方案 1 和 2 都要依赖于规范的运维体系虚拟化、白名单机制、业务账号分离。由此可见更多的准备往往意味着更稳定的系统。 四、总结
以业务高峰期的性能问题为背景介绍了一些紧急处理的手段。
这些处理手段中既包括了粗暴地拒绝连接和断开连接也有通过重写语句来绕过一些坑的方法既有临时的高危方案也有未雨绸缪的、相对安全的预案。
在实际开发中也要尽量避免一些低效的方法比如避免大量地使用短连接。
同时如果做业务开发的话要知道连接异常断开是常有的事代码里要有正确地重连并重试的机制。
DBA 虽然可以通过语句重写来暂时处理问题但是这本身是一个风险高的操作做好 SQL 审计可以减少需要这类操作的机会。
其实可以看得出来在文章中提到的解决方法主要集中在 server 层。 是否碰到过在业务高峰期需要临时救火的场景又是怎么处理的呢 主从切换时遇到的问题主从切换前主库要改为只读设置只读后show master status发现binlog一直在变化当时应用没断开。 主库并不是其他库的从库怎么搞的呢 检查业务用户权限发现拥有super权限查看授权语句原来是grant all on . to user这里要说的是*.* 权限就太大了而且这个也很容易被误解需要特别注意。
对的readonly对super无效 一方面是尽量不要给业务super 一方面你做完readonly还会去确认binlog有没有变这个意识很好哦
