Code summary of several important MySQL performance index calculation and optimization methods

The following editor will bring you a MySQL summary of several important performance index calculation and optimization methods. The editor thinks it is quite good, so I will share it with you now and give it as a reference for everyone. Let’s follow the editor to take a look

1 QPS calculation (queries per second)

For DBs 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 it started. If you want to calculate the QPS within a certain period of time, you can get the interval t2-t1 during the peak period, and then calculate t2 and t1 respectively. The q value of time, 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, the QPS query method within a certain time period 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 Number of thread connections and hit rate

mysql> show global status like 'threads_%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Threads_cached  | 480  |   //代表当前此时此刻线程缓存中有多少空闲线程
| Threads_connected | 153  |  //代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数
| Threads_created  | 20344 |  //代表从最近一次服务启动，已创建线程的数量
| Threads_running  | 2   |   //代表当前激活的（非睡眠状态）线程数
+-------------------+-------+
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)

线程缓存命中率=1-Threads_created/Connections  = 99.994%

我们设置的线程缓存个数

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, you can estimate how big the thread_cache_size value should be set. Generally speaking, 250 is a good upper limit. If the memory is large enough, you can also set the thread_cache_size value to be the same as the threadads_connected value;

Or by observing the threads_created value, if the value is large or keeps growing, you can increase the value of thread_cache_size appropriately; in the sleep state, each thread occupies about 256KB of memory, so when the memory is sufficient, set too Being small won't save much memory unless the value is already 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 cache of open tables and the cache of table definitions

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 some data into the table_open_cache cache. When mysql cannot find the corresponding information in this cache, it will go Read directly from the disk, so the value should be set large enough to avoid the need to reopen and re-parse the table definition. It is generally set to 10 times max_connections, but it is best to keep it within 10,000.

Another basis is to set according to the value of status open_tables. If it is found that the value of open_tables changes greatly every second, then the value of table_open_cache may need to be increased.

table_definition_cache is usually simply set to the number of tables that exist in the server, unless there are tens of thousands of tables.

For InnoDB:

Unlike MyISAM, InnoDB’s open table and open file are not directly related, that is, when the frm table is opened, it corresponds The ibd file may be closed;

Therefore, InnoDB will only use table_definiton_cache and will not use table_open_cache;

The frm file is saved in table_definition_cache, and the idb is determined by innodb_open_files (provided that With innodb_file_per_table enabled), it is best to set innodb_open_files large enough so 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)

Usually the size of max_connections should be set to be larger than Max_used_connections The status value is large. The Max_used_connections status value reflects whether there are spikes in server connections during a certain period of time. If the value is greater than the max_connections value, it means that the client has been rejected at least once. It 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    |   //预读的页数
| Innodb_buffer_pool_read_ahead_evicted | 0      |   
| Innodb_buffer_pool_read_requests   | 480291074970 | //从缓冲池中读取的次数
| Innodb_buffer_pool_reads       | 29912739   |     //表示从物理磁盘读取的页数
+---------------------------------------+--------------+
5 rows in set (0.00 sec)

Buffer pool hit rate = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)=99.994%

If the value is less than 99.9%, it is recommended to increase the value of innodb_buffer_pool_size. This value is generally set to 75%-85% of the total memory size, or calculate the cache required by the operating system + the size of each mysql connection. The required memory (such as sort buffer and temporary table) + MyISAM key cache, the remaining memory is given to innodb_buffer_pool_size, but it should not be set too large, which will cause frequent memory exchange, long warm-up and shutdown time and other problems.

7 MyISAM Key Buffer hit rate and buffer usage rate

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 usage rate=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 the buffer The usage rate of the key buffer is not high. If all the key buffers are not used up after a long period of time, you can consider reducing the buffer area.

The key cache hit rate may not mean much, because it is application-related. Some applications work well with a 95% hit rate, and some require 99.99%, so from experience, the cache per second The number of misses is more important. Assuming that an independent disk can do 100 random reads per second, then 5 buffer misses per second may not cause I/O busy, but 80 per second may cause problems.

Cache misses per second=Key_reads/uptime=0.33

8 临时表使用情况

mysql> show global status like 'Created_tmp%';
+-------------------------+----------+
| Variable_name      | Value  |
+-------------------------+----------+
| Created_tmp_disk_tables | 19226325 |
| Created_tmp_files    | 117   |
| Created_tmp_tables   | 56265812 |
+-------------------------+----------+
3 rows in set (0.00 sec)

mysql> show variables like '%tmp_table_size%';
+----------------+----------+
| Variable_name | Value  |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+
1 row in set (0.00 sec)

可看到总共创建了56265812 张临时表，其中有19226325 张涉及到了磁盘IO，大概比例占到了0.34，证明数据库应用中排序，join语句涉及的数据量太大，需要优化SQL或者增大tmp_table_size的值，我设的是64M。该比值应该控制在0.2以内。

9 binlog cache使用情况

mysql> show status like 'Binlog_cache%'; 
+-----------------------+----------+
| Variable_name     | Value  |
+-----------------------+----------+
| Binlog_cache_disk_use | 15    |
| Binlog_cache_use   | 95978256 |
+-----------------------+----------+
2 rows in set (0.00 sec)

mysql> show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name   | Value  |
+-------------------+---------+
| binlog_cache_size | 1048576 |
+-------------------+---------+
1 row in set (0.00 sec)

Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数

Binlog_cache_use 表示 用binlog_cache_size缓存的次数

当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

10 Innodb log buffer size的大小设置

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+---------+
| Variable_name     | Value  |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
1 row in set (0.00 sec)
mysql> show status like 'innodb_log_waits';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| Innodb_log_waits | 0   |
+------------------+-------+
1 row in set (0.00 sec)

innodb_log_buffer_size我设置了8M，应该足够大了；Innodb_log_waits表示因log buffer不足导致等待的次数，如果该值不为0，可以适当增大innodb_log_buffer_size的值。

11 表扫描情况判断

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：使用索引扫描的次数，该值大小说不清系统性能是好是坏

Handler_read_key：通过key进行查询的次数，该值越大证明系统性能越好

Handler_read_next：使用索引进行排序的次数
Handler_read_prev：此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY ... DESC

Handler_read_rnd：该值越大证明系统中有大量的没有使用索引进行排序的操作，或者join时没有使用到index

Handler_read_rnd_next：使用数据文件进行扫描的次数，该值越大证明有大量的全表扫描，或者合理地创建索引，没有很好地利用已经建立好的索引

12 Innodb_buffer_pool_wait_free

mysql> show global status like 'Innodb_buffer_pool_wait_free';
+------------------------------+-------+
| Variable_name        | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0   |
+------------------------------+-------+
1 row in set (0.00 sec)

该值不为0表示buffer pool没有空闲的空间了，可能原因是innodb_buffer_pool_size设置太大，可以适当减少该值。

13 join操作信息

mysql> show global status like 'select_full_join';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| Select_full_join | 10403 |
+------------------+-------+
1 row in set (0.00 sec)

该值表示在join操作中没有使用到索引的次数，值很大说明join语句写得很有问题

mysql> show global status like 'select_range';
+---------------+----------+
| Variable_name | Value  |
+---------------+----------+
| Select_range | 22450380 |
+---------------+----------+
1 row in set (0.00 sec)

该值表示第一个表使用ranges的join数量，该值很大说明join写得没有问题，通常可查看select_full_join和select_range的比值来判断系统中join语句的性能情况

mysql> show global status like 'Select_range_check';
+--------------------+-------+
| Variable_name   | Value |
+--------------------+-------+
| Select_range_check | 0   |
+--------------------+-------+
1 row in set (0.00 sec)

如果该值不为0需要检查表的索引是否合理，表示在表n+1中重新评估表n中的每一行的索引是否开销最小所做的联接数，意味着表n+1对该联接而言并没有有用的索引。

mysql> show GLOBAL status like 'select_scan';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| Select_scan  | 116037811 |
+---------------+-----------+
1 row in set (0.00 sec)

select_scan表示扫描第一张表的连接数目，如果第一张表中每行都参与联接，这样的结果并没有问题；如果你并不想要返回所有行但又没有使用到索引来查找到所需要的行，那么计数很大就很糟糕了。

14 慢查询

mysql> show global status like 'Slow_queries';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Slow_queries | 114111 |
+---------------+--------+
1 row in set (0.00 sec)

该值表示mysql启动以来的慢查询个数，即执行时间超过long_query_time的次数，可根据Slow_queries/uptime的比值判断单位时间内的慢查询个数，进而判断系统的性能。

15表锁信息

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)

这两个值的比值：Table_locks_waited /Table_locks_immediate 趋向于0，如果值比较大则表示系统的锁阻塞情况比较严重

The above is the detailed content of Code summary of several important MySQL performance index calculation and optimization methods. For more information, please follow other related articles on the PHP Chinese website!