Running MySQL Status View monitor running MySQL, one way is to view MySQL running status. (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 S Tatus 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 ratio 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 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 this ratio is larger, the blocking caused by the table lock is more serious innodb_row_ Lock_waits InnoDB line Lock, too large may be caused by Gap lock (9) Replication delay amount mysql > show slave status View delay time (10) TMP table status (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 than the, may be the sort sentence too much or the connection sentence is not optimized (one) 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 size (innodb_log_waits) Volume mysql > Show status Like ' Innodb_log_waits '; iThe nnodb_log_waits value is not equal to 0, indicating that InnoDB log buffer waits for a lack of space (13) To view the connection thread show processlist; such as command: ># Show global status; can be filtered using the: > #show global status like%...%; , but it is necessary to figure out what the long list means for each item.
MySQL Status View qps/tps/cache Hit ratio view
