专门做护肤品网站,网站空间怎么收费,成都十大设计工作室,网站建设后需要维护吗MySQL 有哪些“饮鸩止渴”提高性能的方法#xff1f;
需求#xff1a;业务高峰期#xff0c;生产环境的 MySQL 压力太大#xff0c;没法正常响应#xff0c;需要短期内、临时性地提升一些性能
短连接风暴
短连接模式#xff1a;执行很少的 SQL 语句就断开#xff0c;…MySQL 有哪些“饮鸩止渴”提高性能的方法
需求业务高峰期生产环境的 MySQL 压力太大没法正常响应需要短期内、临时性地提升一些性能
短连接风暴
短连接模式执行很少的 SQL 语句就断开下次需要的时候再重连。如果使用的是短连接在业务高峰期的时候就可能出现连接数突然暴涨的情况max_connections 参数用来控制一个 MySQL 实例同时存在的连接数的上限被拒绝的连接的请求从业务角度就是数据库不可用机器负载比较高时处理现有请求的时间变长每个连接保持的时间也更长就有可能超过 max_connections 参数一个比较自然的想法就是调高 max_connections 的值但是系统的负载可能进一步加大并且量的资源耗费在权限验证等逻辑上
第一种方法先处理掉那些占着连接但是不工作的线程
对于不需要保持的连接可以通过 kill connection 主动踢掉相当于设置 wait_timeout 参数效果一样
wait_timeout 参数一个线程空闲 wait_timeout 这么多秒之后就会被 MySQL 直接断开连接
例如下面例子
可以用过 SELECT CONNECTION_ID(); 查询当前会话 id通过 show processlist; 查看进程结果其中 109 为 A110 为 B112 为 C从而可以看到哪些会话是 Sleep 状态 通过 select * from information_schema.innodb_trx\G 可以看到事务具体状态 其中 trx_mysql_thread_id109 表示 id 109 A 线程还在事务中从服务端断开连接使用的是 kill connection id 它的连接被服务端主动断开后这个客户端并不会马上知道。直到客户端在发起下一个请求的时候才会收到这样的报错 注意从数据库端主动断开连接可能是有损的尤其是有的应用端收到这个错误后不重新连接而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去“MySQL 一直没恢复”。
第二种方法减少连接过程的消耗
有的业务代码会在短时间内先大量申请数据库连接做备用从而导致服务打挂那么一种可能的做法是让数据库跳过权限验证阶段跳过权限验证的方法
重启数据库并使用 –skip-grant-tables 参数启动注意风险极高特别是外网访问若确定开启该参数MYSQL8 会默认把 --skip-networking 参数打开表示这时候数据库只能被本地的客户端连接
慢查询性能问题
大体有以下三种可能
索引没有设计好SQL 语句没写好MySQL 选错了索引
导致慢查询的第一种可能是索引没有设计好
这种场景一般就是通过紧急创建索引来解决
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 这样的方案更加稳妥。但是在需要紧急处理时上面这个方案的效率是最高的
导致慢查询的第二种可能是语句没写好
我们可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能可以把输入的一种语句改写成另外一种模式MYSQL 8 安装 install_rewriter 插件需要从 share 目录找到 install_rewriter.sql 脚本
# 登录
mysql -uroot -p
# 执行脚本
source install_rewriter.sql
# 查看是否有 Rewriter 插件
show plugins
# 查看是否改写开启
show variables like %rewrite%例如语句被错误地写出了 select * from t where id 1 10000可以通过下面方式改写规则
# 其中 testdb 是你的库
mysql insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values (select * from t where id 1 ?, select * from t where id ? - 1, testdb);
# 这个存储过程是让插入的新规则生效也就是我们说的“查询重写
call query_rewrite.flush_rewrite_rules();通过 show warnings 可以看到是否生效 MySQL 选错了索引
应急方案是在语句加上 force index
总结
由慢查询导致性能问题的三种可能情况实际上出现最多的是前两种可以通过下面过程预先发现问题
上线前把慢查询日志slow log打开并且把 long_query_time 设置成 0确保每个语句都会被记录入慢查询日志在测试表里插入模拟线上的数据做一遍回归测试观察慢查询日志里每类语句的输出特别留意 Rows_examined 字段是否与预期一致
新增的 SQL 语句不多手动跑一下就可以。而如果是新项目的话或者是修改了原有项目的表结构设计全量回归测试都是必要的。这时候你需要工具帮你检查所有的 SQL 语句的返回结果。比如你可以使用开源工具 pt-query-digest
QPS 突增问题
有时候由于业务突然出现高峰或者应用程序 bug导致某个语句的 QPS 突然暴涨也可能导致 MySQL 压力过大影响服务
理想的情况让业务把这个功能下掉
如果从数据库端处理的话对应于不同的背景有不同的方法可用
一种是由全新业务的 bug 导致的如果 DB 运维是比较规范的说白名单是一个个加的。这种情况下如果你能够确定业务方会下掉这个功能只是时间上没那么快那么就可以从数据库端直接把白名单去掉这个新功能使用的是单独的数据库用户可以用管理员账号把这个用户删掉然后断开现有连接这是一个止血方案最低优先级这个新增的功能跟主体功能是部署在一起的那么我们只能通过处理语句来限制。这时我们可以使用上面提到的查询重写功能把压力最大的 SQL 语句直接重写成select 1返回 注意这个操作风险会很高如果别的功能里面也用到了这个 SQL 语句模板会有误伤很多业务并不是靠这一个语句就能完成逻辑的所以如果单独把这一个语句以 select 1 的结果返回的话可能会导致后面的业务逻辑一起失败
前 2 个方案都要依赖于规范的运维体系虚拟化、白名单机制、业务账号分离
