느린 로그를 확인했는데 2시간 동안 4개의 쿼리만 받았는데 모두 다음과 유사했습니다.
"SELECT HEX(uhash) AS uhash, vehid, IF(deleted = 0 AND follow_price_drop = 1, 1, 0) AS follow_price_drop, email, deleted
FROM wp_ product_favorite_count AS cfc
INNER JOIN wp_ product_favorite_user AS cfu ON cfc. product_favorite_user_uhash = cfu.uhash
WHERE cfc.updated > '2021-09-23 12:49:02' OR cfu.updated > '2021-09-23 12:49:02'"
나는 top과 htop을 확인했고 종종 6개의 CPU 코어 모두에서 100 CPU 사용량을 얻었습니다.
대부분의 CPU 사용량은 mysqld에서 발생하므로 db를 기록했습니다.
iostat -xm 5 3은 나에게 다음을 제공했습니다:
avg-cpu: %user %nice %system %iowait %steal %idle
11.34 0.01 1.80 1.13 0.08 85.65
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 39.75 720.61 79.81 192.29 0.99 3.57 34.30 0.02 0.09 0.19 0.04 0.09 2.53
^[[A^[[A^[[Aavg-cpu: %user %nice %system %iowait %steal %idle
84.15 0.00 6.16 0.05 0.03 9.61
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 0.80 31.00 14.40 19.80 0.65 0.20 50.95 0.02 0.73 0.93 0.58 0.43 1.48
^[[A^[[Bavg-cpu: %user %nice %system %iowait %steal %idle
84.54 0.00 4.95 0.10 0.05 10.36
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 0.00 2.40 22.60 1.60 1.77 0.02 151.40 0.02 1.02 1.04 0.75 0.64 1.56
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 128341
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 128341
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
느린 쿼리 로그를 확인한 후 일반 쿼리 로그를 확인해 보니 쿼리가 너무 많아서 놀랐습니다. 트래픽이 정상일 때 136235개의 쿼리를 얻었으며 대부분은 10분 후 SELECT 쿼리입니다. 그리고 트래픽이 높을 때 10분 안에 195650개의 쿼리를 받았습니다. 방문자가 195650명인지는 의심스럽지만 어떤 이유에서인지 호출은 General_log 내부에 있습니다. Slow_query_log에는 쿼리가 4개만 있었고 최적화되지 않은 쿼리처럼 보이지 않았습니다. 내가 살펴봐야 할 다른 것이 있습니까? 아니면 트래픽 때문이라고 추측하기에 충분합니까? 서버를 업그레이드해야합니까?
top은 대략 이렇습니다. 제 시간에 캡처할 수는 없지만 CPU가 95% 이상에 도달했을 때 화면은 다음과 같았습니다.
top - 13:04:51 up 1140 days, 19:59, 2 users, load average: 26.57, 16.21, 8.92
Tasks: 429 total, 12 running, 421 sleeping, 0 stopped, 0 zombie
Cpu(s): 91.3%us, 1.6%sy, 0.0%ni, 65.7%id, 3.1%wa, 0.0%hi, 0.2%si, 0.1%st
Mem: 32877280k total, 31367584k used, 1509696k free, 3960824k buffers
Swap: 0k total, 0k used, 0k free, 3980580k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14576 mysql 20 0 12.9g 8.5g 8424 S 951.6 27.2 18841:47 mysqld
6032 martind 20 0 510m 65m 9160 S 61.4 0.2 2:49.40 php-fpm
7329 martind 20 0 498m 63m 5556 R 57.6 0.2 0:47.15 php-fpm
7321 martind 20 0 487m 52m 5532 R 46.1 0.2 0:45.18 php-fpm
7160 martind 20 0 488m 52m 5540 R 44.1 0.2 1:02.67 php-fpm
6031 martind 20 0 511m 67m 8076 S 42.2 0.2 2:50.87 php-fpm
6696 martind 20 0 498m 63m 5700 S 38.4 0.2 1:36.38 php-fpm
7283 martind 20 0 494m 59m 5268 S 34.5 0.2 0:46.19 php-fpm
7314 martind 20 0 490m 55m 5536 R 33.0 0.2 0:44.22 php-fpm
7330 martind 20 0 496m 60m 5436 R 26.4 0.2 0:46.82 php-fpm
7305 martind 20 0 494m 58m 5572 R 25.4 0.2 0:48.85 php-fpm
6706 martind 20 0 507m 62m 8060 S 13.7 0.2 1:40.55 php-fpm
7276 martind 20 0 498m 63m 5264 S 7.7 0.2 0:49.89 php-fpm
17464 redis 20 0 4328m 2.3g 888 R 7.7 7.3 7827:30 redis-server
6402 martind 20 0 511m 67m 8056 S 5.8 0.2 2:15.21 php-fpm
6405 martind 20 0 512m 69m 9204 S 5.8 0.2 2:14.32 php-fpm
6703 martind 20 0 513m 67m 8056 S 5.8 0.2 1:39.40 php-fpm
6705 martind 20 0 513m 68m 9040 S 5.8 0.2 1:36.18 php-fpm
7303 martind 20 0 493m 57m 6556 S 5.8 0.2 0:47.04 php-fpm
7304 martind 20 0 494m 59m 5264 S 5.8 0.2 0:48.70 php-fpm
7323 martind 20 0 511m 67m 7772 S 5.8 0.2 0:45.53 php-fpm
24515 nginx 20 0 123m 66m 2452 S 5.8 0.2 7231:17 nginx
6039 martind 20 0 507m 63m 8200 S 3.8 0.2 2:48.39 php-fpm
6400 martind 20 0 511m 68m 8204 S 3.8 0.2 2:13.54 php-fpm
6401 martind 20 0 510m 66m 9052 S 3.8 0.2 2:13.36 php-fpm
6404 martind 20 0 512m 68m 9048 S 3.8 0.2 2:12.75 php-fpm
그래서 SQL 쿼리가 너무 많아 속도가 느려지는 경향이 있기 때문에 트래픽이 많아서 발생하는 것이라고 생각합니다. cronjobs(wordpress cronjobs 및 php cronjobs)를 확인했는데 속도가 느려지면 아무것도 실행되지 않는 것 같습니다. 동시에 실행 중인 rsync 프로세스가 있을 수 있지만 rsync 프로세스는 항상 실행되므로 이것이 원인인지 의심됩니다. 제가 확인할 수 있는 것이 있나요?
답변1
글로벌 상태 및 변수 분석:
관찰:
- 버전: 10.4.12-MariaDB
- 32GB RAM
- 가동 시간 = 19일 23:11:43
- MyISAM과 InnoDB를 모두 실행 중인 것 같습니다.
- 240QPS
더 중요한 문제:
느린 로그에서 더 많은 쿼리를 포착할 수 있도록 으로 long_query_time변경 하세요 . 1(이제 10초 남았습니다. 이는 쿼리를 4개만 찾은 이유일 것입니다.) 일부 쿼리가 비효율적으로 실행되고 있다는 몇 가지 단서가 있습니다. 이러한 쿼리를 찾는 방법은 다음과 같습니다. http://mysql.rjweb.org/doc.php/mysql_analytic#slow_queries_and_slowlog
MyISAM을 사용하는 이유는 무엇입니까? 값이 혼란스럽습니다. 마치 대규모 MyISAM 테이블에 대한 인덱스를 [재]구축했지만 다른 작업을 수행하지 않은 것과 같습니다. 대부분의 경우 InnoDB를 사용하는 것이 더 좋습니다.
innodb_buffer_pool_sizeInnoDB 쿼리 속도를 향상시키기 위해 아마도 증가될 수 있습니다.
주의하세요 general_log. 디스크가 오히려 빠르게 채워집니다.
"쿼리 캐시"가 비효율적으로 실행되고 있습니다. 완전히 끄는 것이 query_cache_type=off좋습니다 query_cache_size=0.
Max_used_connections152번이 떴는데, 이는 동시에 많은 사용자가 접속했음을 의미합니다. (152개의 쿼리가 동시에 실행되었다는 의미는 아닙니다.)
세부 사항 및 기타 관찰 사항:
MyISAM에서 InnoDB로 변환
( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%-- key_buffer가 사용된 비율입니다. 하이 워터마크. -- 불필요한 메모리 사용을 방지하려면 key_buffer_size(현재 134217728)를 낮추세요.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%-- 사용 가능한 RAM의 대부분을 캐싱에 사용할 수 있어야 합니다. --http://mysql.rjweb.org/doc.php/memory
( general_log ) = general_log = ON-- 모든 쿼리 실행의 로그(FILE 또는 TABLE)입니다. -- 사용하지 않을 때는 General_log를 끕니다(현재 ON). 해당 로그는 디스크를 매우 빠르게 채울 수 있습니다.
( innodb_buffer_pool_size ) = 8,192 / 32768M = 25.0%-- InnoDB buffer_pool에 사용된 RAM의 비율 -- 사용 가능한 RAM의 약 70%로 설정합니다. (낮을수록 효율성이 떨어지고, 너무 높을수록 교체 위험이 있습니다.)
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%-- (RAM 사용량을 판단하기 위한 측정항목)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- 매초 페이지 클리너 작업량. -- "InnoDB: page_cleaner: 1000ms 의도된 루프 소요 ..."는 lru_scan_깊이를 낮추면 해결될 수 있습니다. 1000 / innodb_page_cleaners(현재 4)를 고려하십시오. 교환 여부도 확인해보세요.
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms 의도한 루프가 걸렸습니다..."는 lru_scan_length를 낮추면 수정될 수 있습니다.
( innodb_io_capacity ) = 200-- 플러시할 때 이만큼의 IOP를 사용합니다. -- 읽기가 느리거나 급격하게 진행될 수 있습니다.
( Innodb_log_writes ) = 43,856,157 / 1725103 = 25 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 137,804,939,264 / (1725103 / 3600) / 2 / 48M = 2.86-- 비율 -- (분 참조)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,725,103 / 60 * 48M / 137804939264 = 10.5-- InnoDB 로그 회전 간격(분) 5.6.8부터 동적으로 변경될 수 있습니다. my.cnf도 변경하십시오. -- (회전 간격 60분 권장은 다소 임의적입니다.) innodb_log_file_size(현재 50331648)를 조정합니다. (AWS에서는 변경할 수 없습니다.)
( innodb_flush_method ) = innodb_flush_method = fsync-- InnoDB가 OS에 블록 쓰기를 요청하는 방법. 이중 버퍼링을 방지하려면 O_DIRECT 또는 O_ALL_DIRECT(Percona)를 제안하세요. (적어도 Unix의 경우) O_ALL_DIRECT에 대한 주의 사항은 chrischandler를 참조하세요.
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1-- 디스크에 블록을 쓸 때 약간의 최적화가 이루어졌습니다. -- SSD 드라이브에는 0을 사용합니다. 1은 HDD용입니다.
( innodb_io_capacity ) = 200- 디스크에서 사용할 수 있는 초당 I/O 작업 수입니다. 느린 드라이브의 경우 100; 회전 드라이브의 경우 200; SSD의 경우 1000-2000; RAID 요소를 곱합니다.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON-- 일반적으로 ON 상태여야 합니다. -- OFF가 더 좋은 경우도 있습니다. innodb_adaptive_hash_index_parts(현재 8)(5.7.9 이후) 및 innodb_adaptive_hash_index_partitions(MariaDB 및 Percona)도 참조하세요. ON은 드문 충돌(버그 73890)과 관련이 있습니다. 10.5.0은 기본값을 OFF로 결정했습니다.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 모든 교착 상태를 기록할지 여부입니다. -- Deadlocks로 인해 어려움을 겪고 있다면 이 기능을 켜십시오. 주의: 교착 상태가 많으면 디스크에 많은 양이 기록될 수 있습니다.
( character_set_server ) = character_set_server = latin1
-- charset 문제는 Character_set_server(현재 latin1)를 utf8mb4로 설정하면 도움이 될 수 있습니다. 이것이 미래의 기본값입니다.
( local_infile ) = local_infile = ON
-- local_infile (현재 ON) = ON은 잠재적인 보안 문제입니다.
( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%-- key_buffer 사용 비율 . 하이 워터마크. -- 불필요한 메모리 사용을 방지하려면 key_buffer_size(현재 134217728)를 낮추세요.
( Key_writes / Key_write_requests ) = 19,978,377 / 40284646 = 49.6%-- 쓰기에 대한 key_buffer 효율성 -- RAM이 충분하다면 key_buffer_size(현재 134217728)를 늘리는 것이 좋습니다.
( query_cache_size ) = 524,288 = 0.5MB-- QC 크기 -- 너무 작음 = 많이 사용되지 않음. 너무 크다 = 오버헤드가 너무 많다. 0 또는 50M 이하를 권장합니다.
( Qcache_lowmem_prunes ) = 125,234,412 / 1725103 = 73 /sec-- QC 공간 부족 -- query_cache_size 증가(현재 524288)
( Qcache_lowmem_prunes/Qcache_inserts ) = 125,234,412/146211296 = 85.7%-- 제거 비율(메모리 부족으로 인해 정리해야 하는 빈도)
( Qcache_not_cached ) = 78,413,835 / 1725103 = 45 /sec-- SQL_CACHE가 시도되었지만 무시되었습니다. -- 캐싱을 다시 생각해 보세요. qcache 조정
( Qcache_hits / Qcache_inserts ) = 37,201,050 / 146211296 = 0.254-- 삽입 적중률 -- 높을수록 좋음 -- 쿼리 캐시를 끄는 것이 좋습니다.
( Qcache_hits / (Qcache_hits + Com_select) ) = 37,201,050 / (37201050 + 282029692) = 11.7%-- 적중률 -- QC를 사용한 SELECT -- 쿼리 캐시를 끄는 것을 고려하세요.
( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 37,201,050 / (37201050 + 146211296 + 78413835) = 14.2%-- 쿼리 캐시 적중률 -- QC를 끄는 것이 가장 좋습니다.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (524288 - 78344) / 82 / 16384 = 0.332-- query_alloc_block_size 대 공식 -- query_alloc_block_size 조정(현재 16384)
( Created_tmp_tables ) = 96,501,765 / 1725103 = 56 /sec-- 복잡한 SELECT의 일부로 "임시" 테이블을 생성하는 빈도입니다.
( Created_tmp_disk_tables ) = 23,539,653 / 1725103 = 14 /sec-- 생성 빈도디스크복잡한 SELECT의 일부인 "임시" 테이블 - tmp_table_size(현재 16777216) 및 max_heap_table_size(현재 16777216)를 늘립니다. MyISAM 대신 MEMORY를 사용하는 경우 임시 테이블에 대한 규칙을 확인하세요. 아마도 사소한 스키마나 쿼리 변경으로 인해 MyISAM이 방지될 수 있습니다. 더 나은 색인과 쿼리 재구성이 도움이 될 가능성이 더 높습니다.
( Created_tmp_disk_tables / Questions ) = 23,539,653 / 414140316 = 5.7%-- 온디스크 임시 테이블이 필요한 쿼리 비율입니다. -- 더 나은 인덱스 / 블롭 없음 / 등.
( Select_full_join / Com_select ) = 30,333,225 / 282029692 = 10.8%-- 인덱스 없는 조인인 선택 비율 -- JOIN에 사용되는 테이블에 적합한 인덱스를 추가합니다.
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (87669877 + 27242 + 0 + 0 + 1452911 + 0) / 1725103 = 52 /sec-- 쓰기/초 -- 50개 쓰기/초 + 로그 플러시는 아마도 HDD 드라이브의 I/O 쓰기 용량을 최대화할 것입니다. SSD가 있는 경우 이 측정항목이 적합할 것입니다.
( binlog_format ) = binlog_format = MIXED-- 명령문/행/혼합. -- ROW는 5.7(10.3)에서 선호됩니다.
( long_query_time ) = 10-- "느린" 쿼리를 정의하기 위한 컷오프(초)입니다. -- 제안 2
( Max_used_connections / max_connections ) = 152 / 151 = 100.7%-- 최대 연결 비율 -- max_connections(현재 151)를 늘리거나 wait_timeout(현재 28800)을 줄입니다. 또는 쿼리 속도를 높이세요.
( Connections ) = 11,987,448 / 1725103 = 6.9 /sec-- 연결 -- wait_timeout을 늘립니다(현재 28800). 풀링을 사용 하시겠습니까?
( Connection_errors_accept + Connection_errors_internal + Connection_errors_peer_address + Connection_errors_select + Connection_errors_tcpwrap ) = 0 + 26 + 0 + 0 + 0 = 26-- max_connections 이외의 연결 오류입니다. -- 자세한 내용은 'Connection_errors%'와 같은 전역 상태 표시를 참조하세요.
비정상적으로 작음:
Created_tmp_files = 0.094 /HR
innodb_spin_wait_delay = 4
비정상적으로 크다:
Aria_pagecache_writes = 34 /sec
Aria_transaction_log_syncs = 25,641
Com_show_warnings = 40 /HR
Connection_errors_internal = 0.054 /HR
Handler_read_key = 85109 /sec
Handler_tmp_update = 839 /sec
Innodb_buffer_pool_read_requests = 675158 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100.0%
Innodb_rows_updated = 356 /sec
performance_schema_max_cond_classes = 90
비정상적인 문자열:
Innodb_have_punch_hole = OFF
aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
ft_boolean_syntax = + -><()~*:
innodb_fast_shutdown = 1
log_output = TABLE
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off