def get_query_by_params(self, request, sorts=None):
start = request.query_params.get('start')
end = request.query_params.get('end')
s = SlowQuery.search()
if start is None or not isinstance(start, str) or len(start.strip()) == 0:
start = None
if end is None or not isinstance(end, str) or len(end.strip()) == 0:
end = None
if start is not None and end is not None:
options = {
# greater or equal than -> gte 大于等于
# greater than -> gt 大于
# little or equal thant -> lte 小于或等于
'gte': start,
'lte': end
}
s = s.filter('range', **{'@timestamp': options})
sorts = request.query_params.get('sorts', sorts)
if isinstance(sorts, str) and len(sorts) > 0:
sorts = [item.strip() for item in sorts.split(",") if len(item.strip()) > 0]
s = s.sort(*sorts)
else:
s = s.sort('-@timestamp')
return s
def send_slow_alarm(record):
msg_template = "您的数据库【{}】存在慢SQL: {}，平均执行时间为{}，一分钟执行了{}次，请关注"
s = SlowQuery.search()
results = s.filter("term", hash__keyword=record['hash']).execute()
print(results)
sql_printer = ""
if len(results.hits.hits) > 0:
sql_printer = results.hits.hits[0]['_source']['finger']
msg = msg_template.format(record['schema'], sql_printer, record['avg_query_time'], record['hash_count'])
send_wechat_msg("ZhangWenBing", msg)
def send_with_matplotlib(request):
s = SlowQuery.search()
options = {
# greater or equal than -> gte 大于等于
# greater than -> gt 大于
# little or equal thant -> lte 小于或等于
'gte': '2020-12-22T00:00:00.000Z',
'lte': '2021-01-21T00:00:00.000Z'
}
s = s.filter('range', **{'@timestamp': options})
aggs = {
"aggs": {
"date": {
"date_histogram": {
"field": "@timestamp",
"calendar_interval": "day"
},
"aggs": {
"avg_query_time": {
"avg": {
"field": "query_time"
}
},
"avg_lock_time": {
"avg": {
"field": "lock_time"
}
}
}
}
}
}
get_aggs(s.aggs, aggs)
result = s.execute().aggregations
rs = get_results(aggs, result)
dates = [r['date'][:10] for r in rs]
counts = [r['date_count'] for r in rs]
print(dates)
print(counts)
return Response("success")
def list(self, request, *args, **kwargs):
# 入参： 开始时间、结束时间、库名、第几页、每页多少个
start = request.query_params.get('start')
end = request.query_params.get('end')
schema = request.query_params.get('schema', None)
is_aggr_by_hash = request.query_params.get('is_aggr_by_hash', False)
if isinstance(is_aggr_by_hash, str) and is_aggr_by_hash.lower() == 'true':
is_aggr_by_hash = True
else:
is_aggr_by_hash = False
interval = request.query_params.get('interval', '1d')
# 参数验证过程就省略
s = SlowQuery.search()
if schema is not None and len(schema) > 0:
s = s.filter('term', schema__keyword=schema)
if start is not None and end is not None:
options = {
# greater or equal than -> gte 大于等于
# greater than -> gt 大于
# little or equal thant -> lte 小于或等于
'gte': start,
'lte': end
}
s = s.filter('range', **{'@timestamp': options})
s = s.sort('-@timestamp')
paginator = CustomPagination()
if is_aggr_by_hash:
aggs = {
"aggs": {
"date": {
"date_histogram": {
"field": "@timestamp",
"calendar_interval": interval
},
"aggs": {
"schema": {
"terms": {
"field": "schema.keyword"
},
"aggs": {
"hash": {
"terms": {
"field": "hash.keyword"
},
"aggs": {
"rows_sent": {
"avg": {
"field": "rows_sent"
}
},
"rows_examined": {
"avg": {
"field": "rows_examined"
}
},
"query_time": {
"avg": {
"field": "query_time"
}
},
"sql": {
"top_hits": {
"_source": [
"finger",
"@timestamp"
],
"sort": [
{
"@timestamp": {
"order": "desc"
}
}
],
"size": 1
}
}
}
}
}
}
}
}
}
}
get_aggs(s.aggs, aggs)
result = s.execute().aggregations
rs = get_results(aggs, result)
if is_aggr_by_hash:
data = paginator.paginate_queryset(rs, request)
else:
data = paginator.paginate_queryset(s, request)
data = [q.to_dict() for q in data]
# 补充处理
return paginator.get_paginated_response(data)
def alarm(request):
end = int(time.time())
start = end - 60
global_query_time = 10
global_query_count = 10
global_cfg = AlarmSettingModel.objects.filter(schema=None).order_by("-id")
if global_cfg.exists():
global_query_time = global_cfg.first().query_time
global_query_count = global_cfg.first().query_count
s = SlowQuery.search()
# options = {
# # greater or equal than -> gte 大于等于
# # greater than -> gt 大于
# # little or equal thant -> lte 小于或等于
# 'gte': start,
# 'lte': end
# }
# s = s.filter('range', **{'@timestamp': options})
aggs = {
"aggs": {
"schema": {
"terms": {
"field": "schema.keyword"
},
"aggs": {
"hash": {
"terms": {
"field": "hash.keyword"
},
"aggs": {
"avg_query_time": {
"avg": {
"field": "query_time"
}
},
"avg_lock_time": {
"avg": {
"field": "lock_time"
}
}
}
}
}
}
}
}
get_aggs(s.aggs, aggs)
result = s.execute().aggregations
rs = get_results(aggs, result)
schema_cfg_dict = {}
schema_cfg_queryset = AlarmSettingModel.objects.exclude(schema__isnull=True).filter(stop_alarm=False, delete=False)
for schema_cfg in schema_cfg_queryset:
# schema_cfg_dict[schema_cfg.schema + "#" + schema_cfg.sql_print_hash] = schema_cfg
cfg_key = schema_cfg.schema.schema + "#" + schema_cfg.sql_print_hash
schema_cfg_dict[cfg_key] = schema_cfg
for r in rs:
threshold_query_time = global_query_time
threshold_query_count = global_query_count
cfg = schema_cfg_dict.get(r.get('schema') + "#" + r.get('hash'), None)
if cfg is not None:
threshold_query_count = cfg.query_count
threshold_query_time = cfg.query_time
if r['avg_query_time'] > threshold_query_time and r['hash_count'] > threshold_query_count:
send_slow_alarm(r)
break
return HttpResponse("success")
def send_with_matplotlib(request):
s = SlowQuery.search()
options = {
# greater or equal than -> gte 大于等于
# greater than -> gt 大于
# little or equal thant -> lte 小于或等于
'gte': '2019-12-01T00:00:00.000Z',
'lte': '2019-12-31T00:00:00.000Z'
}
s = s.filter('range', **{'@timestamp': options})
aggs = {
"aggs": {
"date": {
"date_histogram": {
"field": "@timestamp",
"calendar_interval": "day"
},
"aggs": {
"avg_query_time": {
"avg": {
"field": "query_time"
}
},
"avg_lock_time": {
"avg": {
"field": "lock_time"
}
}
}
}
}
}
get_aggs(s.aggs, aggs)
result = s.execute().aggregations
rs = get_results(aggs, result)
dates = [r['date'][:10] for r in rs]
counts = [r['date_count'] for r in rs]
rcParams['axes.unicode_minus'] = False # 用来正常显示负号
matplotlib.rcParams['font.family'] = 'SimHei'
mpl.rcParams['font.sans-serif'] = ['SimHei'] # 更新字体格式
mpl.rcParams['font.size'] = 14
plt.figure(figsize=(12, 4))
# 生成图形
plt.title('慢SQL数量趋势图')
plt.plot(dates, np.asarray(counts), label='慢SQL数量')
plt.legend()
plt.xticks(rotation=-30, ha='left')
plt.tick_params(axis='x', labelsize=8)
# 显示图形
now = float(time.time())
pic_name = str(now) + ".jpg"
plt.savefig(pic_name)
plt.close()
report = render_to_string("slowsql.html", context={
"send_time": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
})
my_sender = "*****@*****.**"
msg = MIMEMultipart('related')
msg['From'] = formataddr([" ", my_sender])
# [收件人邮箱昵称、收件人邮箱账号], 昵称随便
msg['To'] = formataddr([" ", "*****@*****.**"])
# 邮件的主题，也就是邮件的标题
msg['Subject'] = "慢SQL周报"
msg.attach(MIMEText(report, 'html', 'utf-8'))
with open(pic_name, "rb") as f:
pic = MIMEImage(f.read())
pic.add_header('Content-ID', '<pic>')
msg.attach(pic)
server = smtplib.SMTP_SSL('smtp.exmail.qq.com', 465)
# server.starttls()
# Next, log in to the server
server.login(my_sender, "ZSTmail2021")
server.sendmail("*****@*****.**", "*****@*****.**", msg.as_string())
server.quit()
os.remove(pic_name)
return HttpResponse("success")
def es_test(self, request):
# print(1)
# return HttpResponse(1)
s = SlowQuery.search()
# 查询所有的数据
# result = s.scan()
# 等值查询
# result = s.filter('term', schema__keyword='Ena').scan()
# 排序
# result = s.sort('-@timestamp')
# 指定范围
# from_date = '2020-12-20T00:00:00'
# to_date = '2020-12-21T00:00:00'
# options = {
# 'gte': from_date,
# 'lte': to_date
# }
# result = s.filter('range', **{'@timestamp': options}).execute()
#
# result = s.filter('wildcard', schema__keyword='*An*').scan()
# result = s.filter('prefix', schema__keyword='L').scan()
# result = s.filter('match', query_sql='abc cda').scan()
# result = s.filter('match', query_sql='abc urtaevhwcz').scan()
# result = s.filter('match', query_sql='T_computer').filter('term', schema__keyword='Cna').scan()
q = Q("match", query_sql='T_computer') | Q("term",
schema__keyword="Cna")
result = s.filter(q).scan()
# result = s.sort('-@timestamp', 'rows_examined')
# 一层聚合数据: 简单的一个根据日期聚合的
# 查询语句
timeAggs = A('date_histogram', field='@timestamp', fixed_interval="1d")
# 'date' 为分组的名称
s.aggs.bucket('date', timeAggs)
# 获取数据
aggs = s.execute().aggregations
# 这里要 debugger 一下，然后才知道怎么取数据
print(aggs)
for date_item in aggs['date'].buckets:
print(date_item)
# for i in result:
# # print(i)
# print(i.to_dict(include_meta=True))
return Response('success')
"""
def alarm_minute():
# 从settings中获取配置信息
r = redis.StrictRedis(host='localhost', port=6379, db=0)
key_name = "slowsql_alarm_start"
duration = 60
start = int(time.time()) - duration
if r.exists(key_name):
start = r.get(key_name)
else:
r.set(key_name, start + duration)
end = start + duration
print("start: {}, type: {}".format(start, type(start)))
global_query_time = 10
global_query_count = 10
global_cfg = AlarmSettingModel.objects.filter(schema=None).order_by("-id")
if global_cfg.exists():
global_query_time = global_cfg.first().query_time
global_query_count = global_cfg.first().query_count
s = SlowQuery.search()
options = {
# greater or equal than -> gte 大于等于
# greater than -> gt 大于
# little or equal thant -> lte 小于或等于
'gte': start,
'lte': end
}
s = s.filter('range', **{'@timestamp': options})
aggs = {
"aggs": {
"schema": {
"terms": {
"field": "schema.keyword"
},
"aggs": {
"hash": {
"terms": {
"field": "hash.keyword"
},
"aggs": {
"avg_query_time": {
"avg": {
"field": "query_time"
}
},
"avg_lock_time": {
"avg": {
"field": "lock_time"
}
}
}
}
}
}
}
}
get_aggs(s.aggs, aggs)
result = s.execute().aggregations
rs = get_results(aggs, result)
schema_cfg_dict = {}
schema_cfg_queryset = AlarmSettingModel.objects.exclude(
schema__isnull=True).filter(stop_alarm=False, delete=False)
for schema_cfg in schema_cfg_queryset:
# schema_cfg_dict[schema_cfg.schema + "#" + schema_cfg.sql_print_hash] = schema_cfg
cfg_key = schema_cfg.schema.schema + "#" + schema_cfg.sql_print_hash
schema_cfg_dict[cfg_key] = schema_cfg
for r in rs:
threshold_query_time = global_query_time
threshold_query_count = global_query_count
cfg = schema_cfg_dict.get(r.get('schema') + "#" + r.get('hash'), None)
if cfg is not None:
threshold_query_count = cfg.query_count
threshold_query_time = cfg.query_time
if r['avg_query_time'] > threshold_query_time and r[
'hash_count'] > threshold_query_count:
send_slow_alarm(r)
break