mysql数据库监控哪些指标_MYSQL性能监控观测重点指标

很多人都会问MySQL监控是怎么一回事，该如何进行mysql的性能监控呢？应该从哪些点关注mysql的性能呢？

一，获取mysql用户下的进程总数

PS-ef|awk'{print$1}'|grep"mysql"|grep-v"grep"|wc-1

二，主机性能状态

#uptime

[root@~]#uptime

13:05:52up53days,52min,1user,loadaverage:0.00,0.00,0.00

三，CPU使用率

#top

或

#vmstat

四，磁盘IO量

#vmstat或#iOStat

五，swap进出量[内存]

#free

六，数据库性能状态

(1)QPS(每秒Query量)

QPS=Questions(orQueries)/seconds

mysql>show/*50000global*/statuslike'Question';

(2)TPS(每秒事务量)

TPS=(Com_commit+Com_rollback)/seconds

mysql>showstatuslike'Com_commit';

mysql>showstatuslike'Com_rollback';

(3)keyBuffer命中率

key_buffer_read_hits=(1-key_reads/key_read_requests)*100%

key_buffer_write_hits=(1-key_writes/key_write_requests)*100%

mysql>showstatuslike'Key%';

(4)InnoDBBuffer命中率

innodb_buffer_read_hits=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)*100%

mysql>showstatuslike'innodb_buffer_pool_read%';

(5)QueryCache命中率

Query_cache_hits=(Qcahce_hits/(Qcache_hits+Qcache_inserts))*100%;

mysql>showstatuslike'Qcache%';

(6)TableCache状态量

mysql>showstatuslike'open%';

(7)ThreadCache命中率

Thread_cache_hits=(1-Threads_created/connections)*100%

mysql>showstatuslike'Thread%';

mysql>showstatuslike'Connections';

(8)锁定状态

mysql>showstatuslike'%lock%';

(9)复制延时量

mysql>showslavestatus

(10)TmpTable状况(临时表状况)

mysql>showstatuslike'Create_tmp%';

(11)BinlogCache使用状况

mysql>showstatuslike'Binlog_cache%';

(12)Innodb_log_waits量

mysql>showstatuslike'innodb_log_waits';

开源监控软件

一，RRDTool

二，Nagios

三，MRTG

四，Cacti