Common performance metrics for MySQL

(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 status Like ' Com_commit ';

MySQL > Show global status Like ' Com_rollback ';

(3) key Buffer hit ratio

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 Amount

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 larger, it indicates that the blocking caused by the table lock is more serious.

Innodb_row_lock_waits Innodb line Lock, too large may be caused by a gap lock

(9) Replication delay Amount

MySQL > Show slave status

View delay Time

() TMP Table condition (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,

It may be that there are too many sentences or a connection sentence is not optimized

(one) Binlog Cache usage Status

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) Quantity

MySQL > Show status like ' Innodb_log_waits ';

Innodb_log_waits value is not equal to 0, indicating that innodblog buffer is waiting due to lack of space

Open File and table

Mysql> mysql-e "show global statuslike ' open_files ';

Mysql> mysql-e "show global statuslike ' open_tables ';

(14) Slow query

To turn on slow queries:

Edit the/etc/my.cnf and add it in the [Mysqld] field:

Slow_query_log= 1 # Turn on slow query

Slow_query_log_file=/data/mysql/slow.log # Slow Query log path

Long_query_time= 1 # Length of slow query

To view the number of slow queries:

Executive Mysqladminstatus

(15) Full-time Records

View full-day log:

Show global variables like ' general_log ';

Open full-day log:

Set global general_log=on;

Note Opening a full-day log consumes server performance and is typically only briefly opened when troubleshooting problems.

########### Split Line ###########

We need to configure the optimization according to our own situation, it is good practice that the MySQL server has been running stably for a period of time, according to the server's "state" and then optimize.

Common performance metrics for MySQL