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