One way to monitor running MySQL is to check the status of MySQL running.
(1) QPS (query amount per second)
QPS = Questions (or Queries)/seconds
MySQL > Show global status Like "question%";
(2) TPS (transaction volume per second)
TPS = (com_commit + com_rollback)/seconds
MySQL > Show global status Like "Com_commit";
MySQL > Show global status Like "Com_rollback";
(3) key Buffer hit ratio
Mysql>show Global status like "key%";
Key_buffer_read_hits = (1-key_reads/key_read_requests) * 100%
Key_buffer_write_hits = (1-key_writes/key_write_requests) * 100%
(4) InnoDB Buffer Hit Rate
Mysql> Show status like "innodb_buffer_pool_read%";
Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests) * 100%
(5) Query Cache Hit Rate
Mysql> Show status like "qcache%";
Query_cache_hits = (Qcahce_hits/(qcache_hits + qcache_inserts)) * 100%;
(6) Table Cache State Amount
Mysql> Show global status Like "open%";
Compare open_tables and Opend_tables values
(7) Thread Cache Hit Rate
Mysql> Show global status Like "thread%";
Mysql> Show global status Like "Connections";
Thread_cache_hits = (1-threads_created/connections) * 100%
(8) Lock status
Mysql> Show global status Like "%lock%";
table_locks_waited/table_locks_immediate=0.3% if the ratio is larger, it indicates that the blocking caused by the table lock is more serious.
Innodb_row_lock_waits Innodb line Lock, too large may be caused by a gap lock
(9) Replication delay Amount
MySQL > Show slave status
View delay Time
() TMP Table condition (temporary table condition)
MySQL > Show status like "create_tmp%";
Created_tmp_disk_tables/created_tmp_tables ratio is best not more than 10%, if the Created_tmp_tables value is larger,
It may be that there are too many sentences or a connection sentence is not optimized
(one) Binlog Cache usage Status
MySQL > Show status like "binlog_cache%";
If the Binlog_cache_disk_use value is not 0, you may need to increase the binlog_cache_size size
(innodb_log_waits) Quantity
MySQL > Show status like "Innodb_log_waits";
Innodb_log_waits value is not equal to 0, indicating that Innodb log buffer is waiting due to lack of space
such as command:
> #show Global status;
Although you can use:
> #show Global status Like%...%;
To filter, but for a long list, each item represents what it means, or it is necessary to figure it out.
One, get the total number of processes under MySQL user
Ps-ef | awk ' {print '} ' | grep "MySQL" | Grep-v "grep" | Wc-1
Second, host performance status
# uptime
[Email protected] ~]# uptime
13:05:52 up, Min, 1 user, Load average:0.00, 0.00, 0.00
Third, CPU utilization
# Top
Or
# Vmstat
Four, disk IO volume
# Vmstat or # Iostat
Five, swap volume [memory]
# free
VI, Database performance status
(1) QPS (query amount 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 ratio
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) Table Cache State Amount
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 delay Amount
MySQL > Show slave status
() TMP Table condition (temporary table condition)
MySQL > Show status like ' create_tmp% ';
(one) Binlog Cache usage Status
MySQL > Show status like ' binlog_cache% ';
(innodb_log_waits) Quantity
MySQL > Show status like ' Innodb_log_waits ';
Open source monitoring software
One, RRDTool
Two, Nagios.
Three, MRTG
Four, Cacti
Monitor running MySQL