mysql雪崩

故事

小王刚下班回家正准备露一手做2个菜。
手机就收到监控报警,网站打不开报警,mysql IO报警,xx报警
还没等开电脑,公司的人就来电话了
老板:小王啊,网站怎么又打不开了?快点解决啊!损失很大啊!
小王:好的马上处理。
凭借小王丰富的经验,mysql IO都那么高了,肯定是什么sql查询把IO都耗干净了,估计还卡了一堆查询
熟练的用show processlist看了下mysql正在处理的查询,果不其然,这查询队列都长到姥姥家了
苦逼的小心易易的结束了卡的时间很长的SELECT查询,这他妈结束的数据远远跟不上新增加的慢查询的速度呀
应用层程序也来不及改呀
最后小王还是恢复了数据库,可时间已经过去了整整1小时。

事后
老版:这次网站挂了1小时,以后别这样,的解决啊!
小王解释道:网站被恶意爬虫了,爬到了耗IO的sql查询,这个时候业务又是高峰期,导致数据库卡死了。
(对于业务来说,更多时候只关注结果,业务不能离线)

其他公司怎么做的?

  • 数据库中间件处理
  • 缓存机制保证不会让数据库太难受
  • 应用层熔断机制
    等等

纯PHP + MYSQL的项目如何应对数据库雪崩?

  • 数据库中间件处理
    我想绝大用PHP + MYSQL的中小公司是没有MYSQL中间件的,有中间件的可以在数据库中间件处理
    有用Swoole写MYSQL中间件的案例
  • 缓存要顶用
    设计好Redis机制,最小量的请求落在MYSQL上
  • 用MySQL Query Rewrite Plugin 进行SQL语句重写
    MySQL · 5.7新特性 · Query Rewrite Plugin
  • 用pt-kill自动杀掉过长的慢查询
    pt-kill是Percona-Toolkit系列之一,Percona公司出品的工具都没用过,绝对有相见恨晚的感觉。
    pt-kill杀掉慢查询,这算是一个简单粗暴的“熔断方案”,都已经到了让数据库雪崩的阶段,与其业务都不可用,还不如有损服务。
    需要注意的是只结束从库的SELECT慢查询

直接上手,pt-kill常驻进程自动结束SQL慢查询实例

以下实例作用是
* 127.0.0.1这台msyql实例上
* 字符集指定为 utf8
* 查询中有关键字 “SELECT|select” (支持正则)
* mysql帐号是 “业务mysql帐号”
* 查询时间大于60秒
* kill掉查询,不kill mysql连接
* 表示从匹配的结果中选择,类似SQL中的where部分,all是全部的查询
* 10秒检查一次
* 日志记录位置
* 打印日志
* 守护进程的方式运行

pt-kill \
--host 127.0.0.1 --port 3306 --user 'root' --password 'password' \
--charset utf8 \
--match-info "SELECT|select" \
--match-user 业务mysql帐号 \
--busy-time 60 \
--kill-query \
--victims all \
--interval 10 \
--log=/var/log/mysql-pt-kill.log \
--print \
--daemonize

常用参数说明

  • host 连接数据库的地址
  • port 连接数据库的端口
  • user 连接数据库的用户名
  • passowrd 连接数据库的密码
  • charset 指定字符集
  • match-command 指定杀死的查询类型
  • match-user 指定杀死的用户名,即杀死该用户的查询
  • match-info 匹配查询的信息
  • busy-time 指定杀死超过多少秒的查询
  • kill 执行kill命令
  • kill-query 与kill匹配查询的连接不同,此选项只会kill查询,而不会杀死连接
  • victims 表示从匹配的结果中选择,类似SQL中的where部分,all是全部的查询
  • interal 每隔多少秒检查一次
  • print 把kill的查询打印出来
  • log 输出日志到文件仅在 daemonize 守护进程的时候
  • daemonize 守护进程(常驻进程)
    更多参考官方pt-kill手册

监控多个mysql

如果多个mysql都需要用pt-kill监控慢查询并干掉,那么运行多个即可

/usr/bin/pt-kill \
--host 10.10.0.1 \
--port 3306 \
--user ptkill \ 
--password ptkill账号密码 \
--charset utf8 \
--match-info 'SELECT|select' \ 
--match-user 业务mysql帐号 \
--busy-time 20 \
--victims all \
--interval 10 \
--log=/var/log/pt-kill-mysql-slave1.log \
--pid=/var/run/pt-kill-mysql-slave1.pid \
--kill \
--print \ 
--daemonize

/usr/bin/pt-kill \
--host 10.10.0.2 \
--port 3306 \
--user ptkill \ 
--password ptkill账号密码 \
--charset utf8 \
--match-info 'SELECT|select' \ 
--match-user 业务mysql帐号 \
--busy-time 20 \
--victims all \
--interval 10 \
--log=/var/log/pt-kill-mysql-slave2.log \
--pid=/var/run/pt-kill-mysql-slave2.pid \
--kill \
--print \ 
--daemonize

pt-kill守护进程

虽然pt-kill自带参数daemonize可以后台守护运行,但是很多时候会莫名其妙的进程挂掉,
那么还需要一个外部的进程守护工具来守护他,在pt-kill自动退出后再次把进程拉起来
这里进程守护工具我们使用 Monit
配置文件参考为

#pt-kill-mysql 从库1守护
check process pt-kill-mysql-slave1 with pidfile /var/run/pt-kill-mysql-slave1.pid
   start program  "/usr/bin/pt-kill --host 10.10.0.1 --port 3306 --user ptkill --password ptkill账号密码 --charset utf8 --match-info 'SELECT|select' --match-user 业务mysql帐号 --busy-time 20 --victims all --interval 10 --log=/var/log/pt-kill-mysql-slave1.log --kill --print --pid=/var/run/pt-kill-mysql-slave1.pid --daemonize"
   stop program  "kill `cat /var/run/pt-kill-mysql-slave1.pid`"
   if changed pid then start

#pt-kill-mysql 从库2守护
check process pt-kill-mysql-slave2 with pidfile /var/run/pt-kill-mysql-slave2.pid
   start program "/usr/bin/pt-kill --host 10.10.0.2 --port 3306 --user ptkill --password ptkill账号密码 --charset utf8 --match-info 'SELECT|select' --match-user 业务mysql帐号 --busy-time 20 --victims all --interval 10 --log=/var/log/pt-kill-mysql-slave2.log --kill --print --pid=/var/run/pt-kill-mysql-slave2.pid --daemonize"
   stop program  "kill `cat /var/run/pt-kill-mysql-slave2.pid`"
   if changed pid then start

参考

官方pt-kill手册