Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to check mysql status". In daily operation, I believe many people have doubts about how to view mysql status. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "how to check mysql status". Next, please follow the editor to study!

View the status of mysql in operation

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.

At this point, the study on "how to check the status of mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.