转 zabbix+percona监控MySQL之各个监控项 V2

https://blog.51cto.com/szgb17/1896244

http://tongcheng.blog.51cto.com/6214144/1620158

http://www.cnblogs.com/caoxiaojian/p/5706992.html

http://blog.csdn.net/mchdba/article/details/51447750

 

后面图形不清楚，可以看原版

https://blog.51cto.com/szgb17/1896244

 

 

对比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)

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}
依赖于:

Percona MySQL Server Template: MySQL is down 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

 

监控锁表的数据