对比zabbix全方位监控mysql，尤其注意以下监控项
MySQL InnoDB Buffer Pool
pool size
database pages InnoDB Buffer Pool Pages Data
free pages InnoDB Buffer Pool Pages Free
modified pages
MySQLTransactions Handler
handler commit InnoDB Transaction Committing
handler rollback InnoDB Transaction Rolling Back
handler savepoint
handler savepoint rollback
MySQLProcesslist
last("mysql.innodb[Innodb_node_heap_buffers]",0)*last("mysql.status[innodb_page_size]",0)
last("Qcache_used_blocks",0)/last("mysql.status[Qcache_queries_in_cache]",0)
last("Qcache_used_memory",0)/last("Qcache_used_blocks",0)
|
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
show processlist;
show full processlist;
show
open
tables;
show status like ‘
%
lock
%
’
show engine innodb status\G;
show variables like ‘
%
timeout
%
’;
1.
线程池
thread_cache_size
=
32
thread_stack
=
512K
#innodb_file_io_threads = 8
innodb_thread_concurrency
=
16
#thread_stack = 192K
#thread_concurrency = 128
2.zabbix
对比zabbix全方位监控mysql和Percona MySQL Server Template
尤其注意以下监控项
MySQL InnoDB
Buffer
Pool
pool size
database pages InnoDB
Buffer
Pool Pages Data
free pages InnoDB
Buffer
Pool Pages Free
modified pages
UserParameter
=
MySQL.pool
-
size,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh gq
UserParameter
=
MySQL.modified
-
pages,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh gt
UserParameter
=
MySQL.free
-
pages,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh gr
UserParameter
=
MySQL.database
-
pages,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh gs
MySQLTransactions Handler
handler commit InnoDB Transaction Committing
handler rollback InnoDB Transaction Rolling Back
handler savepoint
handler savepoint rollback
UserParameter
=
MySQL.Handler
-
rollback,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mw
UserParameter
=
MySQL.modified
-
pages,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh gt
UserParameter
=
MySQL.Handler
-
savepoint
-
rollback,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh my
UserParameter
=
MySQL.Handler
-
savepoint,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mx
UserParameter
=
MySQL.Handler
-
commit,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mm
MySQLProcesslist
UserParameter
=
MySQL.State
-
updating,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mi
UserParameter
=
MySQL.State
-
freeing
-
items,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lt
UserParameter
=
MySQL.State
-
other,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh ml
UserParameter
=
MySQL.State
-
none,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mk
UserParameter
=
MySQL.State
-
init,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lu
UserParameter
=
MySQL.State
-
sorting
-
result,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mg
UserParameter
=
MySQL.State
-
statistics,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mh
UserParameter
=
MySQL.State
-
copying
-
to
-
tmp
-
table,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lr
UserParameter
=
MySQL.State
-
end,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh ls
UserParameter
=
MySQL.State
-
login,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lw
UserParameter
=
MySQL.State
-
reading
-
from
-
net,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh ly
UserParameter
=
MySQL.State
-
locked,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lv
UserParameter
=
MySQL.State
-
sending
-
data,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lz
UserParameter
=
MySQL.State
-
preparing,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lx
UserParameter
=
MySQL.State
-
writing
-
to
-
net,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh mj
UserParameter
=
MySQL.State
-
closing
-
tables,
/
var
/
lib
/
zabbix
/
percona
/
scripts
/
get_mysql_stats_wrapper.sh lq
5.6
last(
"mysql.innodb[Innodb_node_heap_buffers]"
,
0
)
*
last(
"mysql.status[innodb_page_size]"
,
0
)
last(
"Qcache_used_blocks"
,
0
)
/
last(
"mysql.status[Qcache_queries_in_cache]"
,
0
)
last(
"Qcache_used_memory"
,
0
)
/
last(
"Qcache_used_blocks"
,
0
)
last(
"mysql.status[Qcache_total_blocks]"
,
0
)
-
last(
"mysql.status[Qcache_used_blocks]"
,
0
)
5.7
show engine innodb status
..............................................
0
lock struct(s), heap size
1136
,
0
row lock(s)
Hash
table size
1593643
, node heap has
273
buffer
(s)
Hash
table size
1593643
, node heap has
3
buffer
(s)
Hash
table size
1593643
, node heap has
21
buffer
(s)
Hash
table size
1593643
, node heap has
54
buffer
(s)
Hash
table size
1593643
, node heap has
71
buffer
(s)
Hash
table size
1593643
, node heap has
119
buffer
(s)
Hash
table size
1593643
, node heap has
203
buffer
(s)
Hash
table size
1593643
, node heap has
82
buffer
(s)
page
Pages flushed up to
55540154363
Database pages
314640
Old database pages
116191
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
22
,
not
young
0
Pages read
314037
, created
603
, written
33877
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
39644
Old database pages
14633
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
2
,
not
young
0
Pages read
39525
, created
119
, written
8064
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
38586
Old database pages
14240
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
19
,
not
young
0
Pages read
38534
, created
52
, written
1809
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
39304
Old database pages
14526
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
1
,
not
young
0
Pages read
39287
, created
17
, written
1287
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
39264
Old database pages
14497
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
0
,
not
young
0
Pages read
39197
, created
67
, written
8073
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
39169
Old database pages
14461
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
0
,
not
young
0
Pages read
39058
, created
111
, written
5199
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
39502
Old database pages
14601
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
0
,
not
young
0
Pages read
39426
, created
76
, written
3084
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
39574
Old database pages
14623
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
0
,
not
young
0
Pages read
39496
, created
78
, written
3300
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Database pages
39597
Old database pages
14610
Modified db pages
0
Pending writes: LRU
0
, flush
list
0
, single page
0
Pages made young
0
,
not
young
0
Pages read
39514
, created
83
, written
3061
Pages read ahead
0.00
/
s, evicted without access
0.00
/
s, Random read ahead
0.00
/
s
Variable_name: Qcache_free_blocks
Variable_name: Qcache_free_memory
Variable_name: Qcache_hits
Variable_name: Qcache_inserts
Variable_name: Qcache_lowmem_prunes
Variable_name: Qcache_not_cached
Variable_name: Qcache_queries_in_cache
Variable_name: Qcache_total_blocks
|
3.触发器
MySQL active threads more than 40 on {HOST.NAME}
依赖于:
Percona MySQL Server Template: MySQL active threads more than 100 on {HOST.NAME}
MySQL active threads more than 100 on {HOST.NAME}
依赖于:
Percona MySQL Server Template: MySQL is down on {HOST.NAME}
MySQL connections utilization more than 80% on {HOST.NAME}
依赖于:
Percona MySQL Server Template: MySQL connections utilization more than 95% on {HOST.NAME}
| MySQL connections utilization more than 95% on {HOST.NAME} 依赖于: |
MySQL slave lag more than 300 on {HOST.NAME}
依赖于:
Percona MySQL Server Template: MySQL slave lag more than 600 on {HOST.NAME}
MySQL slave lag more than 600 on {HOST.NAME}
依赖于:
Percona MySQL Server Template: Slave is stopped on {HOST.NAME}
Slave is stopped on {HOST.NAME}
依赖于:
Percona MySQL Server Template: MySQL is down on {HOST.NAME}
4.lepus
已用连接，可用表缓存，打开文件句柄数
threads,QPS-TPS,DML sec
transaction sec commit,rollback
innodb io read and pages flush
innodb rows read
innodb rows dml
key buffer
network
aborted_clients,aborted_connects
具体案例
cat /usr/local/zabbix/etc/.my.cnf
[mysql]
user=a
password=b
[mysqladmin]
user=a
password=b
yum -y install php php-mysql
zabbix ALL=(root) NOPASSWD:/bin/netstat, /usr/bin/omreport,/usr/bin/top,/bin/chown,/usr/bin/php
cd /usr/local/zabbix/scripts/percona/scripts
cat get_mysql_stats_wrapper.sh
#!/bin/sh
# The wrapper for Cacti PHP script.
# It runs the script every 5 min. and parses the cache file on each following run.
# Version: 1.1.6
#
# This program is part of Percona Monitoring Plugins
# License: GPL License (see COPYING)
# Copyright: 2016 Percona
# Authors: Roman Vynar
ITEM=$1
HOST=localhost
DIR=`dirname $0`
CMD="sudo /usr/bin/php -q $DIR/ss_get_mysql_stats.php --host $HOST --items gg"
CACHEFILE="/tmp/$HOST-mysql_cacti_stats.txt"
if [ "$ITEM" = "running-slave" ]; then
# Check for running slave
#RES=`HOME=~zabbix mysql -e 'SHOW SLAVE STATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ','`
RES=`/opt/mysql/bin/mysql -e 'SHOW SLAVE STATUS\G' | egrep '(Slave_IO_Running|Slave_SQL_Running):' | awk -F: '{print $2}' | tr '\n' ','`
if [ "$RES" = " Yes, Yes," ]; then
echo 1
else
echo 0
fi
exit
elif [ -e $CACHEFILE ]; then
# Check and run the script
TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`
TIMENOW=`date +%s`
if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; then
sudo chown -R zabbix.zabbix /tmp/$HOST-mysql_cacti_stats.txt
rm -f $CACHEFILE
$CMD 2>&1 > /dev/null
fi
else
$CMD 2>&1 > /dev/null
fi
# Parse cache file
if [ -e $CACHEFILE ]; then
cat $CACHEFILE | sed 's/ /\n/g; s/-1/0/g'| grep $ITEM | awk -F: '{print $2}'
else
echo "ERROR: run the command manually to investigate the problem: $CMD"
fi
监控锁表的数据
本文转自 liqius 51CTO博客，原文链接：http://blog.51cto.com/szgb17/1896244，如需转载请自行联系原作者
