The vast majority of MySQL performance metrics can be obtained in the following two ways:
(1) mysqladmin
The MySQL performance metrics obtained with the mysqladmin extended-status command are cumulative values by default. If you want to know the current state, you need to compute the difference; adding the parameter —relative(-r) lets you see the delta of each metric, and with the parameter —sleep(-i) you can specify the refresh frequency.
(2) Show global status
It can list the various status values of the running MySQL server, as cumulative values.
The mysqladmin extended-status command and show global status return a great many metric items. In practice, focus on the following performance metrics:
tps/qps
tps: Transactions Per Second, the number of transactions per second;
qps: Queries Per Second, the number of queries per second;
There are usually two ways to calculate tps/qps:
Method 1:
Compute tps based on com_commit and com_rollback, and compute qps based on questions.
TPS = Com_commit/s + Com_rollback/s
where,
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 number of Queries executed by the MySQL Server per second, approximated by the per-second change in the Questions (number of client queries) status value, so we have:
QPS = mysqladmin extended-status --relative --sleep=1|grep -w Questions
Following the same method you can also get the number of selects, inserts, updates and deletes per second in MySQL, such as:
Com_select/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_select
Com_select/s: average number of select statements executed per second
Com_insert/s: average number of insert statements executed per second
Com_update/s: average number of update statements executed per second
Com_delete/s: average number of delete statements executed per second
Method 2:
Compute tps and qps based on com_%
tps= Com_insert/s + Com_update/s + Com_delete/s
qps=Com_select/s + Com_insert/s + Com_update/s + Com_delete/s
Thread status
* threads_running:
The number of threads currently in an active state
* threads_connected:
The number of currently connected threads
### Traffic status
* Bytes_received/s:
The average number of bytes received from all clients per second, in KB
* Bytes_sent/s:
The average number of bytes sent to all clients per second, in KB
innodb file read/write counts
* innodb_data_reads:
The average number of reads from files per second in innodb
* innodb_data_writes:
The average number of writes to files per second in innodb
* innodb_data_fsyncs:
The average number of fsync() operations per second in innodb
innodb read/write volume
* innodb_data_read:
The average amount of data read per second in innodb, in KB
* innodb_data_written:
The average amount of data written per second in innodb, in KB
innodb buffer pool status
* innodb_buffer_pool_reads:
The average number of pages read from the physical disk per second
* innodb_buffer_pool_read_requests:
The average number of reads from the innodb buffer pool per second (logical read requests)
* innodb_buffer_pool_write_requests:
The 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 buffer pool per second
* innodb_buffer_pool_pages_flushed:
* innodb_buffer_read_hit_ratio = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100
The read hit ratio of the innodb buffer pool
* Innodb_buffer_usage = ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100
The utilization of the innodb buffer pool
innodb logs
innodb_os_log_fsyncs:
The average number of fsync() writes completed to the log file per second
innodb_os_log_written:
The average number of bytes written to the log file per second
innodb_log_writes:
The average number of physical writes to the log file per second
innodb_log_write_requests:
The average number of log write requests per second
innodb rows
innodb_rows_deleted:
The average number of rows deleted from innodb tables per second
innodb_rows_inserted:
The average number of rows inserted into innodb tables per second
innodb_rows_read:
The average number of rows read from innodb tables per second
innodb_rows_updated:
The average number of rows updated in innodb tables per second
innodb_row_lock_waits:
The number of times a row lock had to wait
innodb_row_lock_time:
The total time spent on row locking, in milliseconds
innodb_row_lock_time_avg:
The average time of row locking, in milliseconds
MyISAM read/write counts
key_read_requests:
The average number of reads from the buffer pool per second in MyISAM
Key_write_requests:
The average number of writes to the buffer pool per second in MyISAM
key_reads :
The average number of reads from the hard disk per second in MyISAM
key_writes :
The average number of writes to the hard disk per second in MyISAM
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 ratio per second
Key_read_hit_ratio=(1-Key_reads/Key_read_requests)*100
MyISAM average key buffer write hit ratio per second
Key_write_hit_ratio =(1-Key_writes/Key_write_requests)*100
Temporary tables
Created_tmp_disk_tables:
The number of temporary tables automatically created on disk by the server when executing statements
Created_tmp_tables:
The number of temporary tables automatically created in memory by the server when executing statements
The Created_tmp_disk_tables/Created_tmp_tables ratio is best kept under 10%. If Created_tmp_tables is relatively large, it may be that there are too many sort statements or that the join statements are not optimized enough
Other
slow_queries:
The number of queries whose execution time exceeds long_query_time seconds (important)
sort_rows:
The number of rows already sorted
open_files:
The number of open files
open_tables:
The number of currently open tables
select_scan:
The number of joins that perform a full scan on the first table
In addition, there are some performance metrics that cannot be obtained directly via mysqladmin extended-status or show global status, but which are very important.
response time: Response Time
Percona provides the tcprstat tool to measure response time. This feature is disabled by default; you can enable it by setting the parameter query_response_time_stats=1.
There are two ways to view response time:
(1) View response time statistics via the command SHOW QUERY_RESPONSE_TIME;
(2) View them via the table QUERY_RESPONSE_TIME in INFORMATION_SCHEMA.
Slave delay: Replica Delay
You can run the show slave status\G command on the slave node; the value of the Seconds_Behind_Master item is the slave’s current delay, in seconds.
Original article address:
https://www.cnblogs.com/yuyue2014/p/3679628.html
