How to check the status of mysql in operation

This article introduces the relevant knowledge of "how to check the status of mysql in operation". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

One way to monitor a running mysql is to check the running status of the mysql.

(1) QPS (number of queries per second)

QPS = Questions (or Queries) / seconds

Mysql > show global status like 'Question%'

(2) TPS (transactions 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) Table Cache state quantity

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) locked statu

Mysql > show global status like'% lock%'

Table_locks_waited/Table_locks_immediate=0.3% if this ratio is relatively large, it means that the blocking caused by the table lock is more serious.

Innodb_row_lock_waits innodb row lock, too large may be caused by gap lock

(9) replication delay

Mysql > show slave status

View delay time

(10) Tmp Table status (temporary table status)

Mysql > show status like 'Create_tmp%'

The Created_tmp_disk_tables/Created_tmp_tables ratio had better not exceed 10%, if the Created_tmp_ tables value is relatively large

It may be that there are too many sorted sentences or the connected 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 size

(12) Innodb_log_waits quantity

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.

Such as commands:

> # show global status

Although you can use:

> # show global status like%...%

To filter, but it is still necessary to figure out what each item means corresponding to the long list.

This is the end of the content of "how to view the status of running mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!