몇 가지 중요한 MySQL 성능 지수 계산 및 최적화 방법에 대한 코드 요약

아래 편집기는 MySQL에 대한 몇 가지 중요한 성능 지수 계산 및 최적화 방법을 요약한 것입니다. 에디터가 꽤 좋다고 생각해서 지금 공유해서 참고용으로 올려보겠습니다. 에디터를 따라가 보겠습니다

1 QPS 계산(초당 쿼리 수)

MyISAM 엔진 기반 DB의 경우

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, mysql이 시작된 이후의 평균 QPS입니다. 특정 기간 내의 QPS를 계산하려면 피크 기간 동안의 간격 t2-t1을 구한 다음 계산하면 됩니다. t2 및 t1 시간의 q 값은 각각 QPS=(q2-q1)/(t2-t1)

InnnoDB 엔진 기반 DB의 경우

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, 특정 기간 내 QPS 쿼리 방법은 위와 동일합니다.

2 TPS 계산(초당 트랜잭션 수)

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 스레드 연결 수 및 적중률

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)

Threads_connected에 따르면 thread_cache_size 값이 얼마나 커야 하는지 추정할 수 있습니다. 일반적으로 메모리가 충분히 큰 경우 250이 좋은 상한입니다. threadads_connected 값과 같습니다.

또는 thread_created 값을 관찰하여 값이 크거나 계속 커지면 sleep 상태에서 thread_cache_size 값을 적절하게 늘릴 수 있으며 각 스레드는 약 256KB의 메모리를 차지합니다. 이므로 메모리가 충분할 경우 너무 작게 설정하면 값이 이미 수천을 넘지 않는 한 메모리가 많이 절약되지 않습니다.

4 테이블 캐시

mysql> show global status like 'open_tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables  | 2228 |
+---------------+-------+
1 row in set (0.00 sec)

오픈 테이블의 캐시와 우리가 설정한 테이블 정의 캐시

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)

MyISAM의 경우:

mysql이 테이블을 열 때마다 table_open_cache 캐시로 일부 데이터를 읽습니다. mysql이 이 캐시에서 해당 정보를 찾을 수 없으면 읽기 상태가 됩니다. 디스크에서 직접 가져오므로 테이블 정의를 다시 열고 다시 구문 분석할 필요가 없도록 값을 충분히 크게 설정해야 합니다. 일반적으로 max_connections의 10배로 설정되지만 10,000 이내로 유지하는 것이 가장 좋습니다.

또 다른 기준은 status open_tables의 값에 따라 설정하는 것입니다. open_tables의 값이 매초 크게 변하는 것을 발견하면 table_open_cache의 값을 늘려야 할 수도 있습니다.

table_definition_cache는 테이블이 수만 개가 아닌 이상 일반적으로 단순히 서버에 존재하는 테이블 수로 설정됩니다.

InnoDB의 경우:

MyISAM과 달리 InnoDB의 오픈 테이블과 오픈 파일은 직접적인 관련이 없습니다. 즉, frm 테이블이 열릴 때입니다. ibd 파일이 닫힐 수 있습니다.

따라서 InnoDB는 table_definiton_cache만 사용하고 table_open_cache는 사용하지 않습니다.

frm 파일은 table_definition_cache에 저장되며 idb는 innodb_open_files에 의해 결정됩니다. (innodb_file_per_table이 활성화된 경우) 서버가 모든 .ibd 파일을 동시에 열어 둘 수 있도록 innodb_open_files를 충분히 크게 설정하는 것이 가장 좋습니다.

5 최대 연결 수

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

우리가 설정하는 max_connections 크기

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

일반적으로 max_connections의 크기를 설정해야 합니다. Max_used_connections보다 큰 경우 상태 값이 큽니다. Max_used_connections 상태 값은 특정 기간 동안 서버 연결이 급증하는지 여부를 반영합니다. 값이 max_connections 값보다 크면 클라이언트가 최소한 거부되었음을 의미합니다. 간단히 다음 조건을 충족하도록 설정할 수 있습니다: Max_used_connections/max_connections=0.8

6 Innodb 캐시 적중률

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)

버퍼 풀 적중률 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_read s)=99.994%

값이 99.9% 미만인 경우 innodb_buffer_pool_size 값을 늘리는 것이 좋습니다. 이 값은 일반적으로 7로 설정됩니다. 5 전체 메모리 크기의 %-85% 또는 운영 체제에 필요한 캐시 + 각 mysql 연결 크기를 계산합니다. 필요한 메모리(예: 정렬 버퍼 및 임시 테이블) + MyISAM 키 캐시, 나머지 메모리는 에 제공됩니다. innodb_buffer_pool_size를 사용하지만 너무 크게 설정하면 안 됩니다. 이렇게 하면 빈번한 메모리 교환, 긴 준비 및 종료 시간 및 기타 문제가 발생할 수 있습니다.

7 MyISAM 키 버퍼 적중률 및 버퍼 사용률

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)

버퍼 사용률=1-(Key_blocks_unused*key_cache_block_size/ key_buffer_size )=18.6 %

읽기 적중률=1-Key_reads /Key_read_requests=99.98%

쓰기 적중률=1-Key_writes / Key_write_requests =99.05%

버퍼 사용량을 볼 수 있습니다. 키 버퍼의 비율이 높지 않습니다. 오랜 시간이 지나도 키 버퍼가 모두 사용되지 않으면 버퍼 영역을 줄이는 것을 고려할 수 있습니다.

키 캐시 적중률은 애플리케이션과 관련되어 있기 때문에 큰 의미가 없을 수도 있습니다. 일부 애플리케이션은 95%의 적중률로 잘 작동하고 일부는 99.99%를 요구하므로 경험상 초당 캐시율은 수치입니다. 독립 디스크가 초당 100번의 임의 읽기를 수행할 수 있다고 가정하면 초당 5번의 버퍼 미스가 I/O 사용을 유발하지는 않지만 초당 80번의 경우 문제가 발생할 수 있습니다.

초당 캐시 누락=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，如果值比较大则表示系统的锁阻塞情况比较严重

위 내용은 몇 가지 중요한 MySQL 성능 지수 계산 및 최적화 방법에 대한 코드 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!