Summary and induction of common performance indicators of MYSQL

(1) QPS (number of queries per second)

QPS = Questions (or Queries) / uptime

Mysql > show global status like 'Question%'

Mysql > show global status like 'uptime'

(2) TPS (transactions per second)

TPS = (Com_commit + Com_rollback) / uptime

Mysql > show global status like 'Com_commit'

Mysql > show global status like 'Com_rollback'

Mysql > show global status like 'uptime'

(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

Check the value of Seconds_Behind_Master. If it is 0, there is no delay.

(10) Tmp Table status (temporary table status)

Mysql > show global status like 'Created_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 global 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 innodblog buffer is waiting because of insufficient space.

(13) open file and table

Mysql > show global status like 'Open_files'

Mysql > show global status like 'Open_tables'

(14) slow inquiry

Open slow query:

1)。 Manually execute the command to turn on:

Mysql > set global slow_query_log=on

Mysql > set global long_query_time=1

2)。 Edit / etc/my.cnf, and add: in the [mysqld] field:

Slow_query_log= 1 # enables slow query

Slow_query_log_file=/data/data/localhost-slow.log # slow query log path

Duration of long_query_time= 1 # slow query

(15) full log

View the full log:

Show global variables like 'general_log'

Enable full log:

Set global general_log=on

Note that enabling full logging consumes server performance and is usually opened only briefly when troubleshooting problems.