Este încetinirea din cauza traficului în fiecare zi în jurul orei 12:00

Am verificat jurnalele lente și am primit doar 4 interogări în 2 ore și toate au fost similare cu aceasta:

„SELECT HEX(uhash) AS uhash, vehid, IF(deleted = 0 AND follow_price_drop = 1, 1, 0) AS follow_price_drop, e-mail, șters 
       DE LA 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' SAU cfu.updated > '2021-09-23 12:49:02'"

Am verificat top și htop și am adesea o utilizare de 100 de procesoare pe toate cele 6 nuclee de procesor.

Cea mai mare parte a utilizării procesorului provine de la mysqld, așa că am logat db:

https://pastebin.com/BBv7ngW5

iostat -xm 5 3 mi-a dat:

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

Dispozitiv: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_wait w_wait 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

Dispozitiv: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_wait w_wait 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

Dispozitiv: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_wait w_wait 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

dimensiunea fișierului de bază (blocuri, -c) 0
dimensiunea segmentului de date (kbytes, -d) nelimitat
prioritate de programare (-e) 0
dimensiunea fișierului (blocuri, -f) nelimitată
semnale în așteptare (-i) 128341
memorie maximă blocată (kbytes, -l) 64
dimensiunea maximă a memoriei (kbytes, -m) nelimitată
deschide fișiere (-n) 1024
dimensiunea conductei (512 octeți, -p) 8
Cozi de mesaje POSIX (octeți, -q) 819200
prioritate în timp real (-r) 0
dimensiunea stivei (kbytes, -s) 10240
timp CPU (secunde, -t) nelimitat
max. procese utilizator (-u) 128341
memorie virtuală (kbytes, -v) nelimitată
blocări de fișiere (-x) nelimitate

Am verificat jurnalul general de interogări după ce am verificat jurnalul de interogări lente și am fost surprins că am primit atât de multe interogări. Când traficul este obișnuit, am primit: 136235 interogări, dintre care majoritatea sunt interogări SELECT după 10 minute. Și când traficul este mare, am primit: 195650 de interogări în 10 minute. Mă îndoiesc că sunt 195650 de vizitatori, dar din anumite motive apelurile sunt în general_log. Slow_query_log a avut doar 4 interogări și nu arăta ca interogări neoptimizate. Ar trebui să mă uit la altceva sau este suficient pentru a presupune că este din trafic și ar trebui să facem upgrade la server?

sus arată cam așa, nu l-am putut captura la timp, dar când a ajuns la 95%+ CPU, ecranul arăta astfel:

sus - 13:04:51 până 1140 de zile, 19:59, 2 utilizatori, medie de încărcare: 26.57, 16.21, 8.92
Sarcini: 429 total, 12 alergare, 421 dormit, 0 oprit, 0 zombi
CPU(e): 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 folosite, 1509696k gratuit, 3960824k buffere
Schimbare: 0k total, 0k folosite, 0k gratuit, 3980580k stocate în cache

  PID UTILIZATOR PR NI VIRT RES SHR S %CPU %MEM TIME+ COMANDA                                                 
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 

Deci, deoarece există atât de multe interogări SQL când tinde să încetinească foarte mult, cred că este cauzat de un trafic ridicat. Am verificat cronjob-urile (wordpress cronjobs și php cronjobs) și nimic nu pare să ruleze când încetinește, ar putea exista un proces rsync care rulează în același timp, dar procesul rsync rulează tot timpul, așa că mă îndoiesc că este cauzat de acest lucru. Pot verifica ceva?

Analiza STATULUI GLOBAL și a VARIABILLOR:

Observatii:

• Versiune: 10.4.12-MariaDB
• 32 GB de RAM
• Timp de funcționare = 19d 23:11:43
• Se pare că rulați atât MyISAM, cât și InnoDB.
• 240 QPS

Cele mai importante probleme:

Schimbare timp_de_interogare_lung la 1 astfel încât să puteți prinde mai multe interogări în slowlog. (Aveți 10 secunde acum; acest lucru explică probabil de ce ați găsit doar 4 interogări.) Există mai multe indicii că unele dintre interogări rulează ineficient. Iată o modalitate de a găsi astfel de interogări: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

De ce folosești MyISAM? Valorile sunt confuze -- este ca și cum ați [re]construit un index pentru un tabel MyISAM mare, dar nu ați făcut altceva. În cele mai multe cazuri, este mai bine să utilizați InnoDB.

innodb_buffer_pool_size poate fi crescut probabil pentru a îmbunătăți viteza de interogare InnoDB.

Fii precaut cu privire la general_log -- umple discul destul de repede.

„Query Cache” rulează ineficient. Recomand să îl dezactivați complet: query_cache_type=off și query_cache_size=0.

Max_conexiuni_utilizate a lovit 152, indicând faptul că o mulțime de utilizatori sunt conectați în același timp. (Acest lucru nu înseamnă că 152 de interogări rulau simultan.)

Detalii și alte observații:

Conversie de la MyISAM la InnoDB ( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0,35% -- Procentul de key_buffer utilizat. Marca de apă mare. -- Scădeți key_buffer_size (acum 134217728) pentru a evita utilizarea inutilă a memoriei.

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7% -- Majoritatea memoriei RAM disponibile ar trebui să fie disponibile pentru stocare în cache. -- http://mysql.rjweb.org/doc.php/memory

( general_log ) = general_log = ON -- Jurnalul (FIȘIER sau TABEL) al tuturor interogărilor rulate. -- Dezactivați general_log (acum ON) când nu este utilizat. Jurnalul respectiv poate umple discul foarte rapid.

( innodb_buffer_pool_size ) = 8.192 / 32768M = 25,0% -- % din RAM utilizat pentru InnoDB buffer_pool -- Setat la aproximativ 70% din RAM disponibilă. (Prea scăzut este mai puțin eficient; prea mare riscă schimbarea.)

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7% -- (metrică pentru evaluarea utilizării RAM)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1.024 * 4 = 4.096 -- Cantitatea de muncă pentru curățarea paginilor în fiecare secundă. -- „InnoDB: page_cleaner: 1000ms intentionat bucla a durat...” poate fi reparat prin scăderea lru_scan_depth: Luați în considerare 1000 / innodb_page_cleaners (acum 4). De asemenea, verificați pentru schimbare.

( innodb_lru_scan_depth ) = 1.024 -- „InnoDB: page_cleaner: bucla intenționată a durat 1000 ms...” poate fi remediată prin scăderea lru_scan_depth

( innodb_io_capacity ) = 200 -- Când spălați, utilizați atât de multe IOP-uri. -- Citirile pot fi lente sau țepoase.

( 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 = 286. -- Raport -- (vezi minutele)

( Timp de funcționare / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1.725.103 / 60 * 48M / 137804939264 = 10,5 -- Minute între rotațiile jurnalului InnoDB Începând cu 5.6.8, aceasta poate fi schimbată dinamic; asigurați-vă că schimbați și my.cnf. -- (Recomandarea de 60 de minute între rotații este oarecum arbitrară.) Ajustați innodb_log_file_size (acum 50331648). (Nu se poate modifica în AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- Cum ar trebui InnoDB să ceară sistemului de operare să scrie blocuri. Sugerați O_DIRECT sau O_ALL_DIRECT (Percona) pentru a evita tamponarea dublă. (Cel puțin pentru Unix.) Consultați chrischandler pentru avertismente despre O_ALL_DIRECT

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = 1 -- O optimizare minoră la scrierea blocurilor pe disc. -- Utilizați 0 pentru unitățile SSD; 1 pentru HDD.

( innodb_io_capacity ) = 200 - Capacitate operațiuni I/O pe secundă pe disc. 100 pentru drive-uri lente; 200 pentru unități de filare; 1000-2000 pentru SSD-uri; înmulțiți cu factorul RAID.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- De obicei, ar trebui să fie ON. -- Există cazuri în care OFF este mai bine. Vezi și innodb_adaptive_hash_index_parts (acum 8) (după 5.7.9) și innodb_adaptive_hash_index_partitions (MariaDB și Percona). ON a fost implicat în accidente rare (bug 73890). 10.5.0 a decis să dezactiveze implicit.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Dacă să înregistrezi toate blocajele. -- Dacă sunteți afectat de Deadlocks, activați acest lucru.Atenție: dacă aveți o mulțime de blocaje, acest lucru poate scrie mult pe disc.

( caractere_set_server ) = character_set_server = latin1 -- Problemele setului de caractere pot fi rezolvate prin setarea character_set_server (acum latin1) la utf8mb4. Acesta este viitorul implicit.

( local_infile ) = local_infile = ON -- local_infile (acum ON) = ON este o problemă potențială de securitate

( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0,35% -- Procentul key_buffer utilizat . Marca de apă mare. -- Scădeți key_buffer_size (acum 134217728) pentru a evita utilizarea inutilă a memoriei.

( Key_writes / Key_write_requests ) = 19.978.377 / 40284646 = 49,6% -- eficacitatea key_buffer pentru scrieri -- Dacă aveți suficientă memorie RAM, ar fi util să creșteți key_buffer_size (acum 134217728).

( query_cache_size ) = 524.288 = 0,5 MB -- Dimensiunea QC -- Prea mic = nu prea folosește. Prea mare = prea mult deasupra capului. Recomandați fie 0, fie nu mai mult de 50M.

( Qcache_lowmem_prunes ) = 125.234.412 / 1725103 = 73 /sec -- A rămas fără cameră în QC -- mărește query_cache_size (acum 524288)

( Qcache_lowmem_prunes/Qcache_inserts ) = 125.234.412/146211296 = 85,7% -- Raportul de eliminare (frecvența necesității de a tăia din cauza memoriei insuficiente)

( Qcache_not_cached ) = 78.413.835 / 1725103 = 45 /sec -- SQL_CACHE a încercat, dar a fost ignorat -- Regândiți stocarea în cache; reglați qcache

( Qcache_hits / Qcache_inserts ) = 37.201.050 / 146211296 = 0,254 -- Raport de lovire la inserare -- mare este bun -- Luați în considerare dezactivarea memoriei cache a interogărilor.

( Qcache_hits / (Qcache_hits + Com_select) ) = 37.201.050 / (37201050 + 282029692) = 11,7% -- Rata de lovituri -- SELECTS care au folosit QC -- Luați în considerare dezactivarea memoriei cache a interogărilor.

( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 37.201.050 / (37201050 + 146211296 + 78413835) = 14,2% -- Rata de accesare a interogărilor cache -- Probabil cel mai bine să dezactivați QC-ul.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (524288 - 78344) / 82 / 16384 = 0,332 -- query_alloc_block_size vs formula -- Ajustați query_alloc_block_size (acum 16384)

( Created_tmp_tables ) = 96.501.765 / 1725103 = 56 /sec -- Frecvența creării tabelelor „temp” ca parte a SELECT-urilor complexe.

( Created_tmp_disk_tables ) = 23.539.653 / 1725103 = 14 /sec -- Frecvența creării disc tabele „temp” ca parte a SELECT-urilor complexe -- creșteți tmp_table_size (acum 16777216) și max_heap_table_size (acum 16777216). Verificați regulile pentru tabelele temporare când se folosește MEMORY în loc de MyISAM. Poate că modificările minore ale schemei sau ale interogării pot evita MyISAM. Indexurile mai bune și reformularea interogărilor sunt mai susceptibile de a ajuta.

( Created_tmp_disk_tables / Întrebări ) = 23.539.653 / 414140316 = 5,7% -- Pct de interogări care au avut nevoie de tabelul tmp pe disc. -- Indici mai buni / Fără blobs / etc.

( Select_full_join / Com_select ) = 30.333.225 / 282029692 = 10,8% -- % dintre selecții care sunt îmbinări fără index -- Adăugați indexuri adecvate la tabelele utilizate în JOIN-uri.

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (87669877 + 27242 + 0 + 0 + 1452911 + 0) / 1725103 = 52 /sec -- scrie/sec -- 50 de scrieri/sec + ștergerea jurnalelor va maximiza probabil capacitatea de scriere I/O a unităților HDD. Dacă aveți SSD, atunci această măsurătoare este probabil bună.

( binlog_format ) = binlog_format = MIXED -- DECLARAȚIE/RÂND/MIX. -- RÂNDUL este preferat de 5,7 (10,3)

( long_query_time ) = 10 -- Cutoff (secunde) pentru definirea unei interogări „lente”. -- Sugerează 2

( Max_used_connections / max_connections ) = 152 / 151 = 100,7% -- Vârf % din conexiuni -- crește max_connections (acum 151) și/sau reduce wait_timeout (acum 28800). Sau accelerați interogările.

( Conexiuni ) = 11.987.448 / 1725103 = 6,9 /sec -- Conexiuni -- Creșteți wait_timeout (acum 28800); folosiți pooling?

( Connection_errors_accept + Connection_errors_internal + Connection_errors_peer_address + Connection_errors_select + Connection_errors_tcpwrap ) = 0 + 26 + 0 + 0 + 0 = 26 -- Erori de conexiune, altele decât max_connections. -- Pentru mai multe informații, consultați AFIȘAȚI STAREA GLOBALĂ CA „Connection_errors%”

Anormal de mic:

Created_tmp_files = 0,094 /HR
innodb_spin_wait_delay = 4

Anormal de mare:

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

Corzi anormale:

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 = activat = dezactivat