Summary of Several Important Performance Index Calculation and Optimization Methods of MySQL
- 2021-07-26 09:02:47
- OfStack
1 QPS calculation (queries per second)
For DB based on MyISAM engine
MySQL> show GLOBAL status like 'questions';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Questions | 2009191409 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388402 |
+---------------+--------+
1 row in set (0.00 sec)
QPS=questions/uptime=5172, the average QPS of mysql since startup. If you want to calculate QPS in a certain time interval, you can obtain the interval time t2-t1 during the peak period, and then calculate q values at t2 and t1 respectively, QPS= (q2-q1)/(t2-t1)
For DB based on InnnoDB engine
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
QPS= (com_update+com_insert+com_delete+com_select) /uptime=3076, and the query method of QPS in a certain time interval is the same as above.
2 TPS calculation (transactions per second)
mysql> show global status like 'com_commit';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_commit | 7424815 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> show global status like 'com_rollback';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Com_rollback | 1073179 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 389467 |
+---------------+--------+
1 row in set (0.00 sec)
TPS=(com_commit+com_rollback)/uptime=22
3 Thread Connections and Hit Ratio
mysql> show global status like 'threads_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 480 | // Represents how many free threads are currently in the thread cache at this time
| Threads_connected | 153 | // Represents the number of connections currently established because 1 A connection is required 1 Threads, so it can also be regarded as the number of threads currently in use
| Threads_created | 20344 | // Representative from the most recent 1 Secondary service startup, number of threads created
| Threads_running | 2 | // Represents the number of threads currently active (non-sleep)
+-------------------+-------+
4 rows in set (0.00 sec)
mysql> show global status like 'Connections';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Connections | 381487397 |
+---------------+-----------+
1 row in set (0.00 sec)
Thread cache hit rate =1-Threads_created/Connections = 99.994%
Number of thread caches we set up
mysql> show variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 500 |
+-------------------+-------+
1 row in set (0.00 sec)
According to Threads_connected, we can predict how much thread_cache_size value should be set. Generally speaking, 250 is a good upper limit value. If the memory is large enough, it can also be set to the same value as thread_cache_size and threaads_connected;
Or by observing the value of threads_created, if the value is very large or 1 is increasing, the value of thread_cache_size can be appropriately increased; In hibernation, each thread consumes about 256KB of memory, so when memory is sufficient, setting it too small will not save much memory unless the value is over a few thousand.
4 table cache
mysql> show global status like 'open_tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2228 |
+---------------+-------+
1 row in set (0.00 sec)
We set the open table cache and table definition cache
mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| table_open_cache | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'table_defi%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| table_definition_cache | 2000 |
+------------------------+-------+
1 row in set (0.00 sec)
For MyISAM:
Every time mysql opens a table, it will read in some data to table_open_cache cache. When mysql can't find the corresponding information in this cache, it will go to the disk to read directly. Therefore, the value should be set large enough to avoid the need to reopen and re-analyze the definition of the table. Generally, it is set to 10 times of max_connections, but it is best to keep it within 10000.
Another basis is to set according to the value of the state open_tables. If the value of open_tables changes greatly every second, it may be necessary to increase the value of table_open_cache.
table_definition_cache is typically simply set to the number of tables that exist on the server, unless there are tens of thousands of tables.
For InnoDB:
Unlike MyISAM, open table of InnoDB is not directly related to open E113EN, that is, its corresponding ibd file may be closed when the frm table is opened;
Therefore, InnoDB will only use table_definiton_cache, not table_open_cache;;
Its frm file is saved in table_definition_cache, and idb is determined by innodb_open_files (provided innodb_file_per_table is turned on), preferably setting innodb_open_files large enough that the server can keep all. ibd files open at the same time.
5 Maximum number of connections
mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1785 |
+----------------------+-------+
1 row in set (0.00 sec)
The max_connections size we set
mysql> show variables like 'max_connections%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 4000 |
+-----------------+-------+
1 row in set (0.00 sec)
Generally, the size of max_connections should be set to be larger than the Max_used_connections state value, which reflects whether the server connection has spiked during a certain period of time. If this value is greater than the max_connections value, it means that the client has been rejected at least once, and can be simply set to meet the following conditions: Max_used_connections/max_connections=0. 8
6 Innodb cache hit rate
mysql> show global status like 'innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 268720 | // Number of pages to read in advance
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 480291074970 | // Number of reads from buffer pool
| Innodb_buffer_pool_reads | 29912739 | // Represents the number of pages read from the physical disk
+---------------------------------------+--------------+
5 rows in set (0.00 sec)
Buffer pool hit ratio = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_read_ahead + Innodb_buffer_pool_reads) = 99.994%
If the value is less than 99.9%, It is suggested that the value of innodb_buffer_pool_size should be increased, which is generally set to 75%-85% of the total memory size, or calculate the cache required by the operating system + mysql the memory required by each connection (such as sorting buffer and temporary table) + MyISAM key cache, and the remaining memory is given to innodb_buffer_pool_size, but it should not be set too large, which will cause frequent memory exchange, preheating and long shutdown time.
7 MyISAM Key Buffer Hit Ratio and Buffer Utilization
mysql> show global status like 'key_%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 106662 |
| Key_blocks_used | 107171 |
| Key_read_requests | 883825678 |
| Key_reads | 133294 |
| Key_write_requests | 217310758 |
| Key_writes | 2061054 |
+------------------------+-----------+
7 rows in set (0.00 sec)
mysql> show variables like '%key_cache_block_size%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| key_cache_block_size | 1024 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%key_buffer_size%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
1 row in set (0.00 sec)
Buffer utilization = 1-( Key_blocks_unused*key_cache_block_size/key_buffer_size) = 18.6%
Read hit rate = 1-Key_reads/Key_read_requests=99. 98%
Write hit rate = 1-Key_writes/Key_write_requests = 99.05%
You can see that the utilization rate of the buffer is not high. If you haven't used up all the key buffers after a long period of time, you can consider reducing the buffer by 1 point.
The key cache hit rate may not make much sense, Because it is related to applications, some applications work well at 95% hit rate, while others need 99.99%, so empirically, the number of cache misses per second is more important. Assuming that a separate disk can do 100 random reads per second, five cache misses per second may not cause I/O to be busy, but 80 misses per second may cause problems.
Cache misses per second = Key_reads/uptime=0. 33
8 Temporary table usage
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
It can be seen that a total of 56,265,812 temporary tables have been created, of which 19,226,325 involve disk IO, accounting for 0.34, which proves that the amount of data involved in sorting and join statements in database application is too large, so it is necessary to optimize SQL or increase the value of tmp_table_size. I set 64M. The ratio should be controlled within 0.2.
9 binlog cache usage
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
Binlog_cache_disk_use represents the number of times temporary files were used to cache binary logs due to insufficient memory in our binlog_cache_size design
Binlog_cache_use represents the number of times cached with binlog_cache_size
When the corresponding value of Binlog_cache_disk_use is relatively large, we can consider appropriately increasing the corresponding value of binlog_cache_size
10 Size Settings for Innodb log buffer size
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
innodb_log_buffer_size I set 8M, which should be large enough; Innodb_log_waits indicates the number of waits caused by insufficient log buffer. If the value is not 0, the value of innodb_log_buffer_size can be appropriately increased.
11 Table Scanning Judgment
mysql> show global status like 'Handler_read%';
+-----------------------+--------------+
| Variable_name | Value |
+-----------------------+--------------+
| Handler_read_first | 19180695 |
| Handler_read_key | 30303690598 |
| Handler_read_last | 290721 |
| Handler_read_next | 51169834260 |
| Handler_read_prev | 1267528402 |
| Handler_read_rnd | 219230406 |
| Handler_read_rnd_next | 344713226172 |
+-----------------------+--------------+
7 rows in set (0.00 sec)
Handler_read_first: The number of times index scans are used, a value that does not tell whether system performance is good or bad
Handler_read_key: The number of queries made through key, the higher the value, the better the system performance
Handler_read_next: Number of times to sort using index
Handler_read_prev: This option indicates the number of times data is fetched from the data file in reverse index order during index scanning, which is generally ORDER BY... DESC
Handler_read_rnd: The larger the value, the greater the number of operations in the system that are not sorted using indexes, or index is not used in join
Handler_read_rnd_next: The number of scans made using data files, the greater the value, indicating that there were a lot of full table scans, or that indexes were created reasonably and did not make good use of existing indexes
12 Innodb_buffer_pool_wait_free
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
A value other than 0 indicates that there is no free space for buffer pool, possibly because the setting of innodb_buffer_pool_size is too large and the value can be reduced appropriately.
13 join Operational Information
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
This value represents the number of times the index was not used in the join operation, and a large value indicates that the join statement was written problematically
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
This value indicates the number of join using ranges in the first table. The large value indicates that join is written without problems. Generally, the performance of join statements in the system can be judged by looking at the ratio of select_full_join and select_range
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
If the value is not 0, it is necessary to check whether the index of the table is reasonable, which means that the index per 1 row in the table n is reevaluated in the table n+1. The number of joins made means that the table n+1 has no useful index for this join.
mysql> show GLOBAL status like 'select_scan';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| Select_scan | 116037811 |
+---------------+-----------+
1 row in set (0.00 sec)
select_scan indicates the number of joins scanned for the first table. If every row in the first table participates in the join, this result is no problem; If you don't want to return all the rows, but you don't use the index to find the rows you need, it's bad to have a large count.
14 slow query
mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)
mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime | 388816 |
+---------------+--------+
1 row in set (0.00 sec)
This value indicates the number of slow queries since mysql started, that is, the number of times when the execution time exceeds long_query_time. The number of slow queries per unit time can be judged according to the ratio of Slow_queries/uptime, and then the performance of the system can be judged.
15 table lock information
mysql> show global status like 'table_lock%';
+-----------------------+------------+
| Variable_name | Value |
+-----------------------+------------+
| Table_locks_immediate | 1644917567 |
| Table_locks_waited | 53 |
+-----------------------+------------+
2 rows in set (0.00 sec)
The ratio of these two values: Table_locks_waited/Table_locks_immediate tends to 0, and a larger value indicates that the lock blocking condition of the system is serious