高并发慢查询提前告警

作者：小旋风
    监控系统是确保系统服务长时间稳定运行，就好像一个国家的边防力量，边防力量薄弱，内部必然  烽烟四起，民不聊生。因此对我们的系统服务，应该要有男儿血性之精神：强敌来犯，必截杀之。

事件驱动
   1：上个月的某个星期天，某个平台的一个高并发慢查询，竟然肆无忌惮的执行时间持续十几个小时，最后开发人员反应问题，才得以解决
   2：上个月oracle 组的一个兄弟，处理一个 突然高并发慢查询，引发的雪崩效应，经过各种统计分析，最终定位问题。

事后思考：
 为什么在我们dba 自己的地盘发生这种事情，我们恰恰是最后知道的，我们的边防大将”监控“何在？我们现在已经有慢查询平台【收集所有平台的慢查询的统计数据】，问题也来了。那么就应该通过数据统计分析解决问题。如果可以提前对高并发的慢查询提前告警，那么我们就可以从原来的 被动救火， 转变成 主动协调 开发人员 等一系列 人配合我们。果断地位就高达上了。

监控的好处：
              1 ：争取更多时间解决问题， ：把问题扼杀在摇篮中，持续而稳定的对外提供服务。
              2： 变被动为被动，处于支配地位。
              3： 我的地盘我做主，一动一静尽在掌握中。

废话不说了，直接上干货

对高并发的慢查询提前邮件告警
      1、 定义：  高并发的慢查询统计维度：统计当前时间点、连续10分钟内、执行次数超过5次每分钟 的慢查询。

      2、统计基础数据： ”慢查询平台数据库“ 收集说明
         慢查询数据收集是利用pt-query-digest分析mysql slow log 文件，每5分钟统计一次： 主要思路是把同一类型的sql 格式化成一个sql 字符串,再根据其生成唯一的数值checksum。每个类型sql 生成一行记录包括：checksum,数据实例名，数据库名，sql案例(统计时此类型sql最后一个)，sql 第一次出现时间， sql 最后一次出现时间，这一类型sql 出现次数，总计执行时间，最小执行时间，最大执行时间，发送行数，扫描行数.............。 插入数据库表时判断该行记录的 （数据库实例名、checksum、sql 第一次出现时间、sql 最后一次出现时间）在数据表中有没有记录，有则不插入，无则插入。即重复执行 统计slow log 文件 ，如果 同一类型的慢sql,在上次统计后没有新添加 就不向数据表中添加记录，否则添加一条记录。
   
    3、统计算法：每15分钟统计1次

        根据checksum 分组 ，再根据”sql 最后一次出现时间“ 降序排序 获取最新的3条记录，记录 a ,b c 
        统计当前时间点：确保每组的最大”sql 最后一次出现时间“ 小于等于300秒+50秒
        连续10分钟内：3条记录相邻记录时间间隔小于等于300秒+50秒
        执行次数超过5次每分钟 的慢查询：( a. sql 出现次数-c.   sql 出现次数)/(a. sql 最后一次出现时间-a. sql 最后一次出现时间)>=5次每分钟

   4、算法详解：
        假设某一类型的 慢查询，执行的频率很高，pt-query-digest 每5分钟统计一次慢日志，那么每间隔5分钟必有一条对应的行记录插入数据表中，我在任何一个时间点统计高并发的慢查询 那么当前的时间点必定小于等于300S  又因为 统计慢日志的那个时间区间会有日志插入 根据历史数据统计 当前统计时间点 某一类型的sql 最后一次出现的时间 比 上次统计的时间点 多 300S + 10 S 左右。索性就 高并发的慢查询的 满足：  统计当前时间点：确保每组的最大”sql 最后一次出现时间“ 小于等于300秒+50秒； 如何满足 统计时的当前时间点的连续10分钟内？又因为每5分钟插入一条记录 那么 肯定有 3条记录 而且 3条记录相邻记录时间间隔小于等于300秒+50秒；执行频率就不说咯。

5、 高并发的慢查询统计sql      
      
    
 6、   高并发的慢查询统计邮件发送 
        
#!/usr/bin/python
# -*- coding: utf-8 -*-
# author  liudongfa
# date    20160325

import os
import time
import shutil
import MySQLdb
import smtplib
import requests
import datetime


from email.MIMEText import MIMEText
from email.MIMEImage import MIMEImage
from email.MIMEMultipart import MIMEMultipart


import sys
default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)

# db infor
ZABBIX_DB_HOST = 'localhost'
ZABBIX_DB_USER = 'anemometer'
ZABBIX_DB_PWD  = 'anemometer'
ZABBIX_DB_NAME = 'slow_query_log'
zabbiX_DB_SOCKET='/tmp/mysql.sock'


# email infor
mailserver = " "XXXXXX@XXXX.com.cn"" 
EMAIL_USERNAME = 'XXXxx'
EMAIL_PASSWORD = 'XXXxx'


def slow_query_statistical():
    conn = MySQLdb.connect(host=ZABBIX_DB_HOST, user=ZABBIX_DB_USER, passwd=ZABBIX_DB_PWD,
                           db=ZABBIX_DB_NAME,unix_socket=zabbiX_DB_SOCKET, charset='utf8', connect_timeout=20)
    cur = conn.cursor()
    count = cur.execute("""
        select   checksum ,hostname_max ,db_max,max(ts_cnt)-min(ts_cnt) numbers ,round((unix_timestamp(max(ts_max))-unix_timestamp(min(ts_max)))/ (max(ts_cnt)-min(ts_cnt)),1 ) frequencyi
                 ,min(ts_max) ts_min,max(ts_max) ts_max
from (
select *,if( @_check=checksum,@row_num:=@row_num+1,@row_num:=1)  rownum,
if(if( @_check=checksum,round(unix_timestamp(ts_max)-unix_timestamp(@befor_end)),0)<=350 ,1,0) time_cha,  
@_check:=checksum  checksum2,@befor_end:=ts_max  befor_end
from global_query_review_history  a,(select @_check:=0,@row_num:=1,@befor_end:='') b
order by checksum ,ts_max desc
) a
where rownum<=3
group by checksum 
having    unix_timestamp(now())- unix_timestamp(max(ts_max))<=350 
  and  count(*)=3 and  min(time_cha) =1   
  and (max(ts_cnt)-min(ts_cnt))/((unix_timestamp(max(ts_max))-unix_timestamp(min(ts_max)))/60 )>=5;                      
                       """ )
    if count == 0:
        result = 0
    else:
        result = cur.fetchall()
    cur.close()
    conn.close()
    return result

def send_mail(title, stat, to_list):
    me = "XXXXXX@XXXX.com.cn"


    def _create_msg():
        msg = MIMEMultipart('related')
        msg['Subject'] = '高并发慢查询提前告警'
        msg['From'] = me
        msg['To'] = ';'.join(to_list)
        msg.preamble = 'This is a multi-part message in MIME format.'


        contents = "高并发慢查询：当前时间之前、连续10分钟内、执行频率超过5次/min 的慢sql
"
        contents+= "以下是统计时间点最近10分钟内的统计，详情请登录慢查询平台
"
        contents +='     .border-table {    border-collapse: collapse;   border: none;   }   .border-table td {     border: solid #000 1px;    }    .border-table th {     border: solid #000 1px;    } '
        contents += ''
        contents +="   "
        
        for j,(checksum ,hostname_max,db_max,numbers,frequency,ts_min,ts_max) in enumerate(stat):
            #print j
                contents +="  " %  checksum 
                contents +="        " %  hostname_max
                contents +="        " %  db_max
                contents +="        " %  numbers
                contents +="        " %  frequency
                contents +="        " %  ts_min
                contents +="       " %  ts_max


        contents += "

checksum	实例	数据库	执行次数	平均执行时间（秒/1次）	首次时间	最后时间
%s	%s	%s	%s	%s	%s	%s

"        
        msg_text = MIMEText(contents, 'html','utf-8')
        msg_alternative = MIMEMultipart('alternative')
        msg_alternative.attach(msg_text)
        msg.attach(msg_alternative)
        #print msg
        return msg


    try:
        server = smtplib.SMTP()
        server.connect(mailserver)
        server.login(EMAIL_USERNAME, EMAIL_PASSWORD)
        server.sendmail(me, to_list, _create_msg().as_string())
        server.close()
        print 'send mail Ok!'
    except Exception, e:
        print e

if __name__ == '__main__':
    # get statistical data
    stat = slow_query_statistical()
    if stat != 0:
       send_mail('High concurrent slow query early warning', stat, ['XXXXXX@XXXX.com.cn','XXXXXX@XXXX.com.cn']

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-2082085/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-2082085/