zabbix3.0.2使用percona mysql插件来监控mysql5.7
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 commitInnoDB Transaction Committing
handler rollbackInnoDB 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",0)*last("mysql.status",0)
last("Qcache_used_blocks",0)/last("mysql.status",0)
last("Qcache_used_memory",0)/last("Qcache_used_blocks",0)
last("mysql.status",0)-last("mysql.status",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 pages0
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 pages0
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 pages0
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 pages0
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 pages0
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 pages0
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 pages0
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 pages0
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 pages0
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
页:
[1]