How to monitor MySQL performance?

1. Obtain the total number of processes under the mysql user ps-ef | awk {print $1} | grepmysql | grep-vgrep | wc-1 2, host performance status # uptime [root @ ~] # Uptime13: 05: 52up53days, 52 min, 1 user, loadaverage: 0.00, 0.00, 0.00 III. CPU usage # top or # vmstat 4,

1. Obtain the total number of processes under the mysql user ps-ef | awk '{print $1}' | grep mysql | grep-v grep | wc-1 2, host performance status # uptime [root @ ~] # Uptime 13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00 3, CPU usage # top or # vmstat 4,

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