mysql性能指标_mysql 主要性能指标

1.mysql connections->threads cached->threads cached

mysql connections->threads cached->threads connected(已用连接数,连接池)

mysql connections->threads cached->connections per second

2.表缓存，已经打开的表数

3.已经打开的文件数

4.QPS/TPS

show status like ‘queries‘

QPS：Query per second，每秒查询量

show status like ‘com_commit‘

TPS：Transaction per second，每秒事物量

TPS = (Com_commit + Com_rollback) / Seconds

5.DML persecond图标

对应mysql innodb rows

6.Transaction Persecond(mysql innodb transactions)

commit/rollback

7.innodb (mysql innodb buffer pool)

innodb buffer pool reads

innodb buffer pool pages flushed

8.key buffer

key buffer read rate

key buffer write rate

key blocks used rate

9.network

bytes_received,bytes_sent

10.aborted

aborted_clients,aborted_connects

11.MySQL InnoDB, MySQL InnoDB Buffer Pool

database pages    InnoDB Buffer Pool Pages Data

free pages    InnoDB Buffer Pool Pages Free

modified pages   InnoDB Rows Modified per second

12.MySQL Performance

MySQLTransactions Handler

handler commit  InnoDB Transaction Committing

handler rollback  InnoDB Transaction Rolling Back

handler savepoint

handler savepoint rollback

InnoDB Last Checkpoint at per second (MySQL InnoDB, MySQL InnoDB Log File)

13.MySQLProcesslist

具体如下

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

尤其注意以下监控项

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)  (MySQL InnoDB)

last("Qcache_used_blocks",0)/last("mysql.status[Qcache_queries_in_cache]",0)  (MySQL Query Cache)

last("Qcache_used_memory",0)/last("Qcache_used_blocks",0)

(MySQL Query Cache)

last("mysql.status[Qcache_total_blocks]",0)-last("mysql.status[Qcache_used_blocks]",0) (MySQL Query Cache)

本文出自 “python 运维” 博客，谢绝转载！