zabbix3.0.2使用percona mysql插件来监控mysql5.7

falldog

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