[root@xx scripts]# ls
table.txt testslownum.py testslowtable.py
[root@xx scripts]# cat testslowtable.py
#!/usr/bin/python
# coding:utf-8
import subprocess
import json
file='/usr/local/zabbix/scripts/table.txt'
tables=[]
with open(file,'r') as f:
for i in f.readlines():
table = i.strip('\n')
tables.append({'{#TABLE_NAME}': table})
print json.dumps({'data': tables}, indent=4, separators=(',', ':'))
结合lepus库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
[root@ct1 scripts]
# cat testslownum.py
#!/usr/bin/python
# coding:utf-8
import
datetime,time
import
sys
import
MySQLdb
import
sys
from
ConfigParser
import
ConfigParser
conf
=
ConfigParser()
timenow
=
time.localtime()
datenow
=
time.strftime(
'%Y-%m-%d 00:00:00'
, timenow)
logstr
=
"%s"
%
datenow
#datefmt='%Y-%m-%d %H:%M:%S'
con
=
MySQLdb.connect(
'ip'
,
'root'
,
'xx'
,
'lepus'
)
def
select(table):
cursor
=
con.cursor()
sql
=
"SELECT sum(t2.ts_cnt) as ts_cnt_sum FROM ( SELECT a.CHECKSUM,a.fingerprint,a.sample,a.first_seen,a.last_seen,b.serverid_max,b.db_max,b.user_max,b.ts_min,b.ts_max,sum(b.ts_cnt) AS ts_cnt,sum(b.Query_time_sum) / sum(b.ts_cnt) AS Query_time_avg,max(b.Query_time_max) AS Query_time_max,min(b.Query_time_min) AS Query_time_min,b.Query_time_sum AS Query_time_sum,max(b.Lock_time_max) AS Lock_time_max,min(b.Lock_time_min) AS Lock_time_min,sum(b.Lock_time_sum) AS Lock_time_sum FROM mysql_slow_query_review a JOIN mysql_slow_query_review_history b WHERE a. CHECKSUM = b. CHECKSUM AND b.serverid_max = '276' GROUP BY a. CHECKSUM) t2 WHERE t2.last_seen >= '%s' and t2.db_max='%s' ORDER BY t2.ts_cnt DESC"
%
(datenow,table)
##print sql
cursor.execute(sql)
template_list
=
cursor.fetchall()
res
=
template_list
for
m
in
res:
print
int
(m[
0
])
cursor.close()
if
__name__
=
=
'__main__'
:
table
=
sys.argv[
1
]
result
=
select(table)
|
[root@xx zabbix_agentd.conf.d]# cat slow_num.conf
UserParameter=slowtable.discovery,/usr/bin/python /usr/local/zabbix/scripts/testslowtable.py
UserParameter=slowtable.nums[*],sudo /usr/bin/python /usr/local/zabbix/scripts/testslownum.py $1
#chmod g-wx,o-wx ~/.cache/Python-Eggs
zabbix添加
主要是每天的慢查询各个库的查询数量，做这个，主要看下每个库的查询数量，目前发现读比写多。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
SELECT
sum
(t2.ts_cnt) as ts_cnt_sum
FROM
(
SELECT
a. CHECKSUM,
a.fingerprint,
a.sample,
a.first_seen,
a.last_seen,
b.serverid_max,
b.db_max,
b.user_max,
b.ts_min,
b.ts_max,
sum
(b.ts_cnt) AS ts_cnt,
sum
(b.Query_time_sum)
/
sum
(b.ts_cnt) AS Query_time_avg,
max
(b.Query_time_max) AS Query_time_max,
min
(b.Query_time_min) AS Query_time_min,
b.Query_time_sum AS Query_time_sum,
max
(b.Lock_time_max) AS Lock_time_max,
min
(b.Lock_time_min) AS Lock_time_min,
sum
(b.Lock_time_sum) AS Lock_time_sum
FROM
mysql_slow_query_review a
JOIN mysql_slow_query_review_history b
WHERE
a. CHECKSUM
=
b. CHECKSUM
AND b.serverid_max
=
'276'
AND b.ts_min >
=
'2017-11-21 00:00:00'
GROUP BY
a. CHECKSUM
) t2
WHERE
t2.last_seen >
=
'2017-11-21 00:00:00'
AND t2.db_max
=
'o2oservice'
AND t2.Query_time_min >
1
-
-
AND t2.ts_cnt >
50
ORDER BY
t2.ts_cnt DESC
|
本文转自 liqius 51CTO博客，原文链接：http://blog.51cto.com/szgb17/1980016，如需转载请自行联系原作者
