Có phải chậm lại do giao thông mỗi ngày gần 12 giờ đêm

Tôi đã kiểm tra các bản ghi chậm và tôi chỉ nhận được 4 truy vấn trong 2 giờ và tất cả chúng đều tương tự như sau:

"CHỌN HEX(uhash) NHƯ uhash, vehid, IF(đã xóa = 0 VÀ follow_price_drop = 1, 1, 0) NHƯ follow_price_drop, email, đã xóa 
       TỪ wp_ product_favorite_count NHƯ cfc 
       INNER THAM GIA wp_ product_favorite_user NHƯ cfu TRÊN cfc. product_favorite_user_uhash = cfu.uhash
       WHERE cfc.updated > '2021-09-23 12:49:02' HOẶC cfu.updated > '2021-09-23 12:49:02'"

Tôi đã kiểm tra top và htop và tôi thường nhận được mức sử dụng 100 cpu trên cả 6 lõi cpu.

Hầu hết việc sử dụng CPU đến từ mysqld, vì vậy tôi đã đăng nhập db:

https://Pastebin.com/BBv7ngW5

iostat -xm 5 3 đã cho tôi:

avg-cpu: %user %nice %system %iowait %steal %idle
          11,34 0,01 1,80 1,13 0,08 85,65

Thiết bị: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz đang chờ 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

Thiết bị: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz đang chờ 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

Thiết bị: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz đang chờ 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

kích thước tệp lõi (khối, -c) 0
kích thước phân đoạn dữ liệu (kbyte, -d) không giới hạn
ưu tiên lập lịch trình (-e) 0
kích thước tệp (khối, -f) không giới hạn
tín hiệu chờ xử lý (-i) 128341
bộ nhớ bị khóa tối đa (kbyte, -l) 64
kích thước bộ nhớ tối đa (kbyte, -m) không giới hạn
mở tệp (-n) 1024
kích thước đường ống (512 byte, -p) 8
Hàng đợi tin nhắn POSIX (byte, -q) 819200
ưu tiên thời gian thực (-r) 0
kích thước ngăn xếp (kbyte, -s) 10240
thời gian cpu (giây, -t) không giới hạn
quy trình người dùng tối đa (-u) 128341
bộ nhớ ảo (kbyte, -v) không giới hạn
khóa tệp (-x) không giới hạn

Tôi đã kiểm tra nhật ký truy vấn chung sau khi kiểm tra nhật ký truy vấn chậm và ngạc nhiên rằng tôi nhận được rất nhiều truy vấn. Khi lưu lượng truy cập bình thường, tôi nhận được: 136235 truy vấn, hầu hết trong số đó là truy vấn CHỌN sau 10 phút. Và khi lưu lượng truy cập cao, tôi nhận được: 195650 truy vấn trong 10 phút. Tôi nghi ngờ đó là 195650 khách truy cập, nhưng vì lý do nào đó, các cuộc gọi nằm trong tệp general_log. slow_query_log chỉ có 4 truy vấn và chúng không giống như các truy vấn chưa được tối ưu hóa. Tôi nên xem xét điều gì khác hay điều này đủ để phỏng đoán rằng đó là do lưu lượng truy cập và chúng tôi nên nâng cấp máy chủ?

top đại khái như thế này, mình chụp không kịp, nhưng khi cpu đạt 95%+ thì màn hình như sau:

hàng đầu - 13:04:51 lên 1140 ngày, 19:59, 2 người dùng, tải trung bình: 26,57, 16,21, 8,92
Nhiệm vụ: tổng cộng 429, 12 đang chạy, 421 đang ngủ, 0 đã dừng, 0 zombie
(Các) CPU: 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 tổng, 31367584k used, 1509696k free, 3960824k đệm
Hoán đổi: tổng cộng 0k, 0k đã sử dụng, 0k miễn phí, 3980580k được lưu trong bộ nhớ cache

  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 máy chủ redis                                             
 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 

Vì vậy, vì có quá nhiều truy vấn SQL khi nó có xu hướng chậm lại rất nhiều, tôi nghĩ rằng đó là do lưu lượng truy cập cao. Tôi đã kiểm tra cronjobs (cronjobs wordpress và cronjobs php) và dường như không có gì chạy khi nó chạy chậm, có thể có một quy trình rsync đang chạy cùng lúc, nhưng quy trình rsync luôn chạy, vì vậy tôi nghi ngờ nguyên nhân là do điều này. Có bất cứ điều gì tôi có thể kiểm tra?

Phân tích TÌNH TRẠNG TOÀN CẦU và BIẾN:

Quan sát:

• Phiên bản: 10.4.12-MariaDB
• RAM 32 GB
• Thời gian hoạt động = 19ngày 23:11:43
• Có vẻ như bạn đang chạy cả MyISAM và InnoDB.
• 240 QPS

Các vấn đề quan trọng hơn:

Biến đổi long_query_time đến 1 để bạn có thể bắt được nhiều truy vấn hơn trong quá trình ghi chậm. (Bây giờ bạn có 10 giây; điều này có thể giải thích tại sao bạn chỉ tìm thấy 4 truy vấn.) Có một số dấu hiệu cho thấy một số truy vấn đang chạy không hiệu quả. Đây là một cách để tìm các truy vấn như vậy: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Tại sao bạn sử dụng MyISAM? Các giá trị khó hiểu -- như thể bạn [đã]xây dựng lại một chỉ mục cho một bảng MyISAM lớn, nhưng không làm gì khác. Trong hầu hết các trường hợp, tốt hơn là sử dụng InnoDB.

innodb_buffer_pool_size có thể được tăng lên để cải thiện tốc độ truy vấn InnoDB.

Hãy thận trọng về chung_log -- nó làm đầy đĩa khá nhanh.

"Query Cache" đang chạy không hiệu quả. Tôi khuyên bạn nên tắt hoàn toàn: query_cache_type=tắt và truy vấn_cache_size=0.

Max_used_connections đạt 152, cho thấy có rất nhiều người dùng được kết nối cùng một lúc. (Điều này không có nghĩa là 152 truy vấn đang chạy đồng thời.)

Chi tiết và các quan sát khác:

Chuyển đổi từ MyISAM sang InnoDB ( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0,35% -- Phần trăm key_buffer được sử dụng. Dấu nước cao. -- Giảm key_buffer_size (hiện tại là 134217728) để tránh sử dụng bộ nhớ không cần thiết.

( (key_buffer_size / 0,20 + innodb_buffer_pool_size / 0,70) ) = ((128M / 0,20 + 8192M / 0,70)) / 32768M = 37,7% - Hầu hết ram có sẵn nên được cung cấp cho bộ nhớ đệm. -- http://mysql.rjweb.org/doc.php/memory

( general_log ) = general_log = BẬT -- Nhật ký (FILE hoặc TABLE) của tất cả truy vấn chạy. -- Tắt general_log (bây giờ là BẬT) khi không sử dụng. Nhật ký đó có thể lấp đầy đĩa rất nhanh.

( innodb_buffer_pool_size ) = 8.192 / 32768M = 25,0% -- % RAM được sử dụng cho InnoDB buffer_pool -- Đặt thành khoảng 70% RAM khả dụng. (Thấp là kém hiệu quả hơn; rủi ro hoán đổi quá cao.)

( (key_buffer_size / 0,20 + innodb_buffer_pool_size / 0,70) ) = ((128M / 0,20 + 8192M / 0,70)) / 32768M = 37,7% -- (số liệu để đánh giá việc sử dụng RAM)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1.024 * 4 = 4.096 -- Lượng công việc cho trình dọn dẹp trang mỗi giây. -- "InnoDB: page_cleaner: mất 1000ms vòng lặp dự kiến..." có thể sửa được bằng cách hạ thấp lru_scan_depth: Xem xét 1000 / innodb_page_cleaners (hiện là 4). Cũng kiểm tra trao đổi.

( innodb_lru_scan_depth ) = 1.024 -- "InnoDB: page_cleaner: mất 1000ms vòng lặp dự định..." có thể được khắc phục bằng cách hạ thấp lru_scan_depth

( innodb_io_ capacity ) = 200 -- Khi tuôn ra, hãy sử dụng nhiều IOP này. -- Số lần đọc có thể chậm chạp hoặc đột ngột.

( Innodb_log_writes ) = 43,856,157/1725103 = 25 /giây

( Innodb_os_log_write / (Thời gian hoạt động / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 137.804.939.264 / (1725103 / 3600) / 2 / 48M = 2,86 -- Tỉ lệ -- (xem phút)

( Thời gian hoạt động / 60 * innodb_log_file_size / Innodb_os_log_write ) = 1.725.103/60 * 48M / 137804939264 = 10,5 -- Số phút giữa các lần quay nhật ký InnoDB Bắt đầu với 5.6.8, điều này có thể được thay đổi linh hoạt; hãy chắc chắn cũng thay đổi my.cnf. -- (Khuyến nghị 60 phút giữa các lần quay là tùy ý.) Điều chỉnh innodb_log_file_size (hiện tại là 50331648). (Không thể thay đổi trong AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- Cách InnoDB nên yêu cầu hệ điều hành viết các khối. Đề xuất O_DIRECT hoặc O_ALL_DIRECT (Percona) để tránh đệm đôi. (Ít nhất là đối với Unix.) Xem chrischandler để biết trước về O_ALL_DIRECT

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbor ) = 1 -- Một tối ưu hóa nhỏ khi ghi các khối vào đĩa. -- Sử dụng 0 cho ổ SSD; 1 cho ổ cứng.

( innodb_io_ capacity ) = 200 - I/O ops mỗi giây có khả năng trên đĩa. 100 cho ổ đĩa chậm; 200 đối với ổ quay; 1000-2000 cho SSD; nhân với hệ số RAID.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = BẬT -- Thông thường nên BẬT. -- Có những trường hợp TẮT tốt hơn. Xem thêm innodb_adaptive_hash_index_parts (hiện là 8) (sau 5.7.9) và innodb_adaptive_hash_index_partitions (MariaDB và Percona). BẬT có liên quan đến các sự cố hiếm gặp (lỗi 73890). 10.5.0 đã quyết định TẮT mặc định.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = TẮT -- Có đăng nhập tất cả các Bế tắc hay không. -- Nếu bạn đang mắc kẹt với Bế tắc, hãy bật tính năng này lên.Thận trọng: Nếu bạn có nhiều bế tắc, điều này có thể ghi rất nhiều vào đĩa.

( character_set_server ) = character_set_server = latin1 -- Các vấn đề về bộ ký tự có thể được giải quyết bằng cách đặt character_set_server (nay là latin1) thành utf8mb4. Đó là mặc định trong tương lai.

( local_infile ) = local_infile = BẬT -- local_infile (hiện đang BẬT) = BẬT là sự cố bảo mật tiềm ẩn

( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0,35% -- Phần trăm key_buffer được sử dụng . Dấu nước cao. -- Giảm key_buffer_size (hiện tại là 134217728) để tránh sử dụng bộ nhớ không cần thiết.

( Key_writes / Key_write_requests ) = 19.978.377 / 40284646 = 49,6% -- hiệu quả của key_buffer đối với việc ghi -- Nếu bạn có đủ RAM, bạn nên tăng key_buffer_size (hiện tại là 134217728).

( query_cache_size ) = 524,288 = 0,5 MB -- Kích thước QC -- Quá nhỏ = không được sử dụng nhiều. Quá lớn = quá nhiều chi phí. Đề xuất 0 hoặc không quá 50M.

( Qcache_lowmem_prunes ) = 125,234,412/1725103 = 73 /giây -- Hết phòng QC -- tăng query_cache_size (nay là 524288)

( Qcache_lowmem_prunes/Qcache_inserts ) = 125.234.412/146211296 = 85,7% -- Tỷ lệ loại bỏ (tần suất cần cắt tỉa do không đủ bộ nhớ)

( Qcache_not_cached ) = 78,413,835/1725103 = 45 /giây -- SQL_CACHE đã thử, nhưng bị bỏ qua -- Suy nghĩ lại về bộ nhớ đệm; điều chỉnh qcache

( Qcache_hits / Qcache_inserts ) = 37.201.050 / 146211296 = 0,254 -- Hit để chèn tỷ lệ -- cao là tốt -- Xem xét việc tắt bộ đệm truy vấn.

( Qcache_hits / (Qcache_hits + Com_select) ) = 37.201.050 / (37201050 + 282029692) = 11,7% -- Tỷ lệ trúng -- CHỌN đã sử dụng QC -- Xem xét việc tắt bộ đệm truy vấn.

( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 37.201.050 / (37201050 + 146211296 + 78413835) = 14,2% -- Tỷ lệ trúng bộ đệm truy vấn -- Có lẽ tốt nhất là tắt QC đi.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (524288 - 78344)/82/16384 = 0,332 -- query_alloc_block_size so với công thức -- Điều chỉnh query_alloc_block_size (hiện là 16384)

( Created_tmp_tables ) = 96,501,765/1725103 = 56 /giây -- Tần suất tạo các bảng "tạm thời" như một phần của CHỌN phức tạp.

( Created_tmp_disk_tables ) = 23.539.653/1725103 = 14 /giây -- Tần suất tạo đĩa các bảng "tạm thời" như một phần của CHỌN phức tạp -- tăng tmp_table_size (hiện là 16777216) và max_heap_table_size (hiện là 16777216). Kiểm tra các quy tắc cho các bảng tạm thời khi MEMORY được sử dụng thay vì MyISAM. Có lẽ những thay đổi nhỏ về lược đồ hoặc truy vấn có thể tránh được MyISAM. Các chỉ mục tốt hơn và định dạng lại các truy vấn có nhiều khả năng hữu ích hơn.

( Created_tmp_disk_tables / Câu hỏi ) = 23.539.653 / 414140316 = 5,7% -- Pct truy vấn cần thiết trên bảng tmp trên đĩa. -- Chỉ mục tốt hơn / Không có đốm màu / v.v.

( Select_full_join / Com_select ) = 30.333.225 / 282029692 = 10,8% -- % lựa chọn là liên kết không có chỉ mục -- Thêm (các) chỉ mục phù hợp vào các bảng được sử dụng trong THAM GIA.

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (87669877 + 27242 + 0 + 0 + 1452911 + 0) / 1725103 = 52 /giây -- ghi/giây -- 50 lần ghi/giây + xóa nhật ký có thể sẽ tối đa hóa khả năng ghi I/O của ổ đĩa cứng. Nếu bạn có SSD, thì số liệu này có thể ổn.

( binlog_format ) = binlog_format = HỖN HỢP -- TUYÊN BỐ/ HÀNG/ HỖN HỢP. -- ROW được ưu tiên bởi 5.7 (10.3)

( long_query_time ) = 10 -- Ngưỡng (Giây) để xác định truy vấn "chậm". -- Đề nghị 2

( Max_used_connections / max_connections ) = 152/151 = 100,7% -- Đỉnh % kết nối -- tăng max_connections (hiện là 151) và/hoặc giảm wait_timeout (hiện là 28800). Hoặc tăng tốc truy vấn.

( Kết nối ) = 11.987.448 / 1725103 = 6,9 /giây -- Kết nối -- Tăng wait_timeout (nay là 28800); sử dụng tổng hợp?

( Connection_errors_accept + Connection_errors_internal + Connection_errors_peer_address + Connection_errors_select + Connection_errors_tcpwrap ) = 0 + 26 + 0 + 0 + 0 = 26 -- Lỗi kết nối khác với max_connections. -- Để biết thêm thông tin, hãy xem HIỂN THỊ TÌNH TRẠNG TOÀN CẦU NHƯ 'Connection_errors%'

Nhỏ bất thường:

Created_tmp_files = 0,094 /HR
innodb_spin_wait_delay = 4

Lớn bất thường:

Aria_pagecache_writes = 34 /giây
Aria_transaction_log_syncs = 25.641
Com_show_warnings = 40 /HR
Connection_errors_internal = 0,054 /HR
Handler_read_key = 85109 /giây
Handler_tmp_update = 839 /giây
Innodb_buffer_pool_read_requests = 675158 /giây
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100,0%
Innodb_rows_updated = 356 /giây
performance_schema_max_cond_classes = 90

Chuỗi bất thường:

Innodb_have_punch_hole = TẮT
aria_recover_options = SAO LƯU,NHANH CHÓNG
ngắt kết nối_on_expired_password = TẮT
ft_boolean_syntax = + -><()~*:
innodb_fast_shutdown = 1
log_output = BẢNG
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = MẶC ĐỊNH
Optimizer_trace = đã bật = tắt