MySQL performance monitoring

1. Obtain the total number of processes under the MySQL user

PS-Ef | awk '{print $1}' | grep "MySQL" | grep-V "grep" | wc-1

Ii. Host performance status

# Uptime

[Root @ ~] # Uptime
13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00

Iii. CPU usage

# Top
Or
# Vmstat

Iv. Disk I/O volume

# Vmstat or # iostat
5. Swap inbound/outbound [Memory]

# Free
Vi. database performance status
(1) QPS (queries per second)
QPS = questions (or queries)/Seconds
Mysql> show/* 50000 global */status like 'Question ';

(2) TPS (transaction volume per second)
TPS = (com_commit + com_rollback)/Seconds
Mysql> show status like 'com _ commit ';
Mysql> show status like 'com _ rollback ';

(3) Key Buffer hit rate
Key_buffer_read_hits = (1-key_reads/key_read_requests) * 100%
Key_buffer_write_hits = (1-key_writes/key_write_requests) * 100%

Mysql> show status like 'key % ';

(4) InnoDB buffer hit rate
Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%

Mysql> show status like 'innodb _ buffer_pool_read % ';

(5) query cache hit rate
Query_cache_hits = (qcahce_hits/(qcache_hits + qcache_inserts) * 100%;

Mysql> show status like 'qcache % ';
(6) Number of table cache statuses
Mysql> show status like 'open % ';

(7) thread cache hit rate
Thread_cache_hits = (1-threads_created/connections) * 100%

Mysql> show status like 'thread % ';

Mysql> show status like 'connections ';

(8) Lock status
Mysql> show status like '% lock % ';

(9) replication latency
Mysql> show slave status

(10) TMP Table Status (temporary table status)
Mysql> show status like 'create _ TMP % ';
(11) BINLOG Cache Usage
Mysql> show status like 'binlog _ cache % ';

(12) innodb_log_waits volume
Mysql> show status like 'innodb _ log_waits ';

Open-source monitoring software
1. rrdtool
2. Nagios
Iii. MRTG
Iv. cacti