Detailed explanation of MySQL performance indicators and calculation methods

Most MySQL performance indicators can be obtained in the following two ways:

mysqladmin

MySQL performance indicators obtained using the mysqladmin extended-status command default to cumulative values. If you want to understand the current status, you need to calculate the difference; add the parameter --relative(-r), you can see the difference of each indicator, and use the parameter --sleep(-i) Specify how often to refresh.

Show global status

Can list various status values ​​of MySQL server running, cumulative value.

Mysqladmin extended-status command and show global status get a lot of indicator items. In practical applications, focus on the following performance indicators:

1. tps/qps

tps: Transactions Per Second, number of transactions per second;

qps: Queries Per Second Number of queries per second;

There are usually two ways to calculate tps/qps:

Method 1: Calculate tps based on com_commit and com_rollback, and calculate qps based on questions.

TPS = Com_commit/s Com_rollback/s

in,

Com_commit /s= mysqladmin extended-status --relative --sleep=1|grep -w Com_commit

Com_rollback/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_rollback

QPS refers to the total amount of Query executed by MySQL Server per second, which is approximately represented by the change in the Questions (number of customer queries) status value per second, so there are:

QPS = mysqladmin extended-status --relative --sleep=1|grep -w Questions

You can also get the number of mysql select, insert, update, delete, etc. per second by following the above method, such as:

Com_select/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_select

Com_select/s: Average number of select statement executions per second

Com_insert/s: average number of insert statement executions per second

Com_update/s: Average number of update statement executions per second

Com_delete/s: average number of delete statement executions per second

Method 2: Calculate tps based on com_%, qps

tps= Com_insert/s Com_update/s Com_delete/s

qps=Com_select/s Com_insert/s Com_update/s Com_delete/s

2. Thread status

threads_running: The number of threads currently active

threads_connected: The number of currently connected threads

3. Traffic status

Bytes_received/s: Average number of bytes received from all clients per second, unit KB

Bytes_sent/s: The average number of bytes sent to all clients per second, unit KB

4. Innodb file read and write times

innodb_data_reads: The average number of times innodb reads from the file per second

innodb_data_writes: The average number of times innodb writes from the file per second

innodb_data_fsyncs: The average number of fsync() operations performed by innodb per second

5. innodb read and write volume

innodb_data_read: The average amount of data read by innodb per second, in KB

innodb_data_written: The average amount of data written by innodb per second, in KB

6. innodb buffer pool status

innodb_buffer_pool_reads: Average number of pages read from the physical disk per second

innodb_buffer_pool_read_requests: Average number of reads from the innodb buffer pool per second (number of logical read requests)

innodb_buffer_pool_write_requests: Average number of writes to the innodb buffer pool per second

innodb_buffer_pool_pages_dirty: The average number of dirty pages in the innodb cache pool per second

innodb_buffer_pool_pages_flushed: The average number of page refresh requests in the innodb cache pool per second

Innodb buffer pool read hit rate
innodb_buffer_read_hit_ratio = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

Innodb buffer pool utilization

Innodb_buffer_usage = ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100

7. innodb log

innodb_os_log_fsyncs: Average number of fsync() writes to the log file per second

innodb_os_log_written: Average number of bytes written to the log file per second

innodb_log_writes: Average number of physical writes to the log file per second

innodb_log_write_requests: Average number of log write requests per second

8. innodb line

innodb_rows_deleted: The average number of rows deleted from the innodb table per second

innodb_rows_inserted: Average number of rows inserted from the innodb table per second

innodb_rows_read: The average number of rows read from the innodb table per second

innodb_rows_updated: The average number of rows updated from the innodb table per second

innodb_row_lock_waits: The number of times a row must wait for a lock

innodb_row_lock_time: The total time spent on row locking, in milliseconds

innodb_row_lock_time_avg: average row lock time, in milliseconds

9. MyISAM read and write times

key_read_requests: The average number of MyISAM reads from the buffer pool per second

Key_write_requests: The average number of MyISAM writes from the buffer pool per second

key_reads: The average number of times MyISAM reads from the hard disk per second

key_writes: The average number of times MyISAM writes from the hard disk per second

10. MyISAM Buffer Pool

MyISAM average key buffer utilization per second

Key_usage_ratio =Key_blocks_used/(Key_blocks_used Key_blocks_unused)*100

MyISAM average key buffer read hit rate per second

Key_read_hit_ratio=(1-Key_reads/Key_read_requests)*100

MyISAM average key buffer write hit rate per second

Key_write_hit_ratio =(1-Key_writes/Key_write_requests)*100

11. Temporary table

Created_tmp_disk_tables: The number of temporary tables automatically created on the hard disk when the server executes a statement

Created_tmp_tables: The number of temporary tables in memory that are automatically created when the server executes a statement

The ratio of Created_tmp_disk_tables/Created_tmp_tables should not exceed 10%. If the value of Created_tmp_tables is relatively large, there may be too many sorting sentences or the connection sentences are not optimized enough

12. Others

slow_queries: Number of queries whose execution time exceeds long_query_time seconds (important)

sort_rows: Number of sorted rows

open_files: Number of open files

open_tables: Number of currently open tables

select_scan: The number of joins for a full scan of the first table

In addition, there are some performance indicators that cannot be obtained directly through mysqladmin extended-status or show global status, but they are very important.

13. response time: response time

Percona provides the tcprstat tool to count response times. This function is turned off by default. This function can be turned on by setting the parameter query_response_time_stats=1.

There are two ways to check the response time:

(1) View response time statistics through the command SHOW QUERY_RESPONSE_TIME;

(2) View through the table QUERY_RESPONSE_TIME in INFORMATION_SCHEMA.

http://www.orczhou.com/index.php/2011/09/thanks-percona-response-time-distribution/comment-page-1/(reference article)

14. Slave delay: Standby database delay

You can execute the show slave status\G command on the slave node. The value of the Seconds_Behind_Master item is the current delay of the slave, in seconds.

The above is the detailed content of Detailed explanation of MySQL performance indicators and calculation methods. For more information, please follow other related articles on the PHP Chinese website!