View mysql status QPS/TPS/cache hit rate _ MySQL

Reprinted: mysql status view QPSTPS cache hit rate view Reprinted: http://blog.163.com/ji_1006/blog/static/106123412013101421530142/

View the running mysql status

Monitor running mysql. one of the following methods is to view the running status of mysql.


(1) QPS (queries 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 rate
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) number of Table Cache statuses
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 large, it indicates that the table lock causes serious blocking.
Innodb_row_lock_waits innodb row lock, which is too large and may be caused by a gap Lock

(9) replication latency
Mysql> show slave status
View latency

(10) Tmp Table status (temporary Table status)
Mysql> show status like 'create _ tmp % ';
The Created_tmp_disk_tables/Created_tmp_tables ratio should not exceed 10%. if the value of Created_tmp_tables is large,
It may be that too many sentences are sorted or sentences are not optimized enough.

(11) Binlog Cache usage
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.

(12) Innodb_log_waits volume
Mysql> show status like 'innodb _ log_waits ';
If the Innodb_log_waits value is not equal to 0, it indicates that innodb log buffer is waiting because of insufficient space.

For example, the command:
> # Show global status;
Although you can use:
> # Show global status like %... %;
But it is necessary to find out what each item means for a long list.