Ich habe die langsamen Protokolle geprüft und in 2 Stunden nur 4 Abfragen erhalten und alle waren ähnlich wie diese:
"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'"
Ich habe top und htop geprüft und erhalte häufig eine CPU-Auslastung von 100 auf allen 6 CPU-Kernen.
Der Großteil der CPU-Auslastung geht auf mysqld zurück, daher habe ich die Datenbank protokolliert:
iostat -xm 5 3 gab mir:
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
Ich habe das allgemeine Abfrageprotokoll geprüft, nachdem ich das langsame Abfrageprotokoll geprüft hatte, und war überrascht, dass ich so viele Abfragen erhielt. Bei normalem Datenverkehr erhielt ich: 136235 Abfragen, von denen die meisten SELECT-Abfragen nach 10 Minuten waren. Und bei hohem Datenverkehr erhielt ich: 195650 Abfragen in 10 Minuten. Ich bezweifle, dass es 195650 Besucher sind, aber aus irgendeinem Grund befinden sich die Aufrufe im allgemeinen Protokoll. Das langsame Abfrageprotokoll enthielt nur 4 Abfragen und sie sahen nicht wie nicht optimierte Abfragen aus. Gibt es noch etwas, das ich mir ansehen sollte, oder reicht dies aus, um zu vermuten, dass es vom Datenverkehr herrührt und wir den Server aktualisieren sollten?
oben sah es ungefähr so aus, ich konnte es nicht rechtzeitig erfassen, aber als es 95 %+ CPU-Auslastung erreichte, sah der Bildschirm so aus:
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
Da es bei so vielen SQL-Abfragen zu einer starken Verlangsamung kommt, gehe ich davon aus, dass dies an hohem Datenverkehr liegt. Ich habe die Cronjobs (WordPress-Cronjobs und PHP-Cronjobs) überprüft und es scheint, als würde nichts ausgeführt, wenn es langsamer wird. Möglicherweise läuft gleichzeitig ein Rsync-Prozess, aber der Rsync-Prozess läuft ständig, daher bezweifle ich, dass es daran liegt. Gibt es etwas, das ich überprüfen kann?
Antwort1
Analyse des GLOBALEN STATUS und der VARIABLEN:
Beobachtungen:
- Version: 10.4.12-MariaDB
- 32 GB RAM
- Betriebszeit = 19d 23:11:43
- Es scheint, dass Sie sowohl MyISAM als auch InnoDB ausführen.
- 240 Fragen pro Sekunde
Die wichtigeren Themen:
Ändern Sie long_query_timezu 1, damit Sie mehr Abfragen im Slowlog erfassen können. (Sie haben jetzt 10 Sekunden Zeit; das erklärt wahrscheinlich, warum Sie nur 4 Abfragen gefunden haben.) Es gibt mehrere Hinweise darauf, dass einige der Abfragen ineffizient ausgeführt werden. So finden Sie solche Abfragen: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Warum verwenden Sie MyISAM? Die Werte sind verwirrend – es ist, als ob Sie einen Index für eine große MyISAM-Tabelle [neu] erstellt hätten, aber sonst nicht viel getan hätten. In den meisten Fällen ist es besser, InnoDB zu verwenden.
innodb_buffer_pool_sizekann wahrscheinlich erhöht werden, um die InnoDB-Abfragegeschwindigkeit zu verbessern.
Seien Sie vorsichtig general_log– die Festplatte wird dadurch ziemlich schnell voll.
Der „Query Cache“ läuft ineffizient. Ich empfehle, ihn komplett auszuschalten: query_cache_type=offund query_cache_size=0.
Max_used_connectionsTreffer 152, was darauf hinweist, dass viele Benutzer gleichzeitig verbunden sind. (Dies bedeutet nicht, dass 152 Abfragen gleichzeitig ausgeführt wurden.)
Details und weitere Beobachtungen:
Konvertierung von MyISAM nach InnoDB
( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%-- Prozent des verwendeten Key_Buffer. Höchststand. -- Reduzieren Sie die Key_Buffer_Size (jetzt 134217728), um unnötigen Speicherverbrauch zu vermeiden.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%-- Der größte Teil des verfügbaren RAM sollte für die Zwischenspeicherung zur Verfügung gestellt werden. --http://mysql.rjweb.org/doc.php/memory
( general_log ) = general_log = ON-- Protokoll (DATEI oder TABELLE) aller ausgeführten Abfragen. -- Schalten Sie das allgemeine Protokoll (jetzt EIN) aus, wenn es nicht verwendet wird. Dieses Protokoll kann die Festplatte sehr schnell füllen.
( innodb_buffer_pool_size ) = 8,192 / 32768M = 25.0%-- % des für den InnoDB-Pufferpool verwendeten RAM -- Auf etwa 70 % des verfügbaren RAM eingestellt. (Zu niedrig bedeutet weniger effizient; zu hoch birgt das Risiko von Swapping.)
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%- (Metrik zur Beurteilung der RAM-Nutzung)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Arbeitsaufwand für Seitenreiniger pro Sekunde. -- „InnoDB: page_cleaner: 1000 ms beabsichtigte Schleife dauerte …“ kann möglicherweise durch Verringerung von lru_scan_depth behoben werden: Erwägen Sie 1000 / innodb_page_cleaners (jetzt 4). Überprüfen Sie auch das Swapping.
( innodb_lru_scan_depth ) = 1,024
- „InnoDB: page_cleaner: Die beabsichtigte Schleife dauerte 1000 ms …“ kann durch Verringern von lru_scan_depth behoben werden.
( innodb_io_capacity ) = 200-- Verwenden Sie beim Leeren diese Anzahl an IOPs. -- Das Lesen kann träge oder unregelmäßig sein.
( 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-- Verhältnis -- (siehe Protokoll)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,725,103 / 60 * 48M / 137804939264 = 10.5-- Minuten zwischen InnoDB-Logrotationen. Ab 5.6.8 kann dies dynamisch geändert werden; denken Sie daran, auch my.cnf zu ändern. -- (Die Empfehlung von 60 Minuten zwischen den Rotationen ist etwas willkürlich.) Passen Sie innodb_log_file_size an (jetzt 50331648). (Kann in AWS nicht geändert werden.)
( innodb_flush_method ) = innodb_flush_method = fsync-- Wie InnoDB das Betriebssystem auffordern soll, Blöcke zu schreiben. Schlagen Sie O_DIRECT oder O_ALL_DIRECT (Percona) vor, um doppeltes Puffern zu vermeiden. (Zumindest für Unix.) Siehe chrischandler für Vorbehalte zu O_ALL_DIRECT
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1– Eine kleine Optimierung beim Schreiben von Blöcken auf die Festplatte. – Verwenden Sie 0 für SSD-Laufwerke; 1 für HDD.
( innodb_io_capacity ) = 200- Mögliche E/A-Operationen pro Sekunde auf der Festplatte: 100 für langsame Laufwerke, 200 für rotierende Laufwerke, 1000–2000 für SSDs; mit dem RAID-Faktor multiplizieren.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON-- Sollte normalerweise eingeschaltet sein. -- Es gibt Fälle, in denen ausgeschaltet besser ist. Siehe auch innodb_adaptive_hash_index_parts (jetzt 8) (nach 5.7.9) und innodb_adaptive_hash_index_partitions (MariaDB und Percona). EIN war an seltenen Abstürzen beteiligt (Fehler 73890). 10.5.0 hat sich entschieden, standardmäßig ausgeschaltet zu sein.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Ob alle Deadlocks protokolliert werden sollen. -- Wenn Sie häufig von Deadlocks geplagt werden, schalten Sie diese Option ein. Achtung: Wenn Sie viele Deadlocks haben, kann dies zu einer großen Datenmenge auf der Festplatte führen.
( character_set_server ) = character_set_server = latin1
-- Zeichensatzprobleme können dadurch behoben werden, dass Sie character_set_server (jetzt latin1) auf utf8mb4 setzen. Das ist die zukünftige Standardeinstellung.
( local_infile ) = local_infile = ON
-- local_infile (jetzt EIN) = EIN ist ein potenzielles Sicherheitsproblem
( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%- Prozent des verwendeten Schlüsselpuffers. Hochwassermarke. - Reduzieren Sie die Schlüsselpuffergröße (jetzt 134217728), um unnötigen Speicherverbrauch zu vermeiden.
( Key_writes / Key_write_requests ) = 19,978,377 / 40284646 = 49.6%-- Effektivität des Key_Buffer beim Schreiben -- Wenn Sie über genügend RAM verfügen, lohnt es sich, die Key_Buffer_Size (derzeit 134217728) zu erhöhen.
( query_cache_size ) = 524,288 = 0.5MB-- Größe des QC -- Zu klein = nicht sehr nützlich. Zu groß = zu viel Aufwand. Wir empfehlen entweder 0 oder nicht mehr als 50 M.
( Qcache_lowmem_prunes ) = 125,234,412 / 1725103 = 73 /sec-- In QC wird der Platz knapp -- query_cache_size erhöhen (jetzt 524288)
( Qcache_lowmem_prunes/Qcache_inserts ) = 125,234,412/146211296 = 85.7%- Entfernungsverhältnis (Häufigkeit, mit der aufgrund von Speichermangel eine Bereinigung erforderlich ist)
( Qcache_not_cached ) = 78,413,835 / 1725103 = 45 /sec-- SQL_CACHE versucht, aber ignoriert -- Caching überdenken; qcache optimieren
( Qcache_hits / Qcache_inserts ) = 37,201,050 / 146211296 = 0.254-- Treffer-Einfüge-Verhältnis – hoch ist gut – Erwägen Sie das Deaktivieren des Abfrage-Cache.
( Qcache_hits / (Qcache_hits + Com_select) ) = 37,201,050 / (37201050 + 282029692) = 11.7%-- Trefferquote -- SELECTs, die QC verwendet haben -- Erwägen Sie das Deaktivieren des Abfrage-Cache.
( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 37,201,050 / (37201050 + 146211296 + 78413835) = 14.2%- Trefferquote des Abfrage-Cache – Am besten schalten Sie die Qualitätskontrolle wahrscheinlich aus.
( (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. Formel -- query_alloc_block_size anpassen (jetzt 16384)
( Created_tmp_tables ) = 96,501,765 / 1725103 = 56 /sec- Häufigkeit der Erstellung „temperierter“ Tabellen als Teil komplexer SELECT-Vorgänge.
( Created_tmp_disk_tables ) = 23,539,653 / 1725103 = 14 /sec-- Häufigkeit der ErstellungScheibe„Temporäre“ Tabellen als Teil komplexer SELECTs – erhöhen Sie tmp_table_size (jetzt 16777216) und max_heap_table_size (jetzt 16777216). Überprüfen Sie die Regeln für temporäre Tabellen, wenn MEMORY statt MyISAM verwendet wird. Vielleicht können kleinere Schema- oder Abfrageänderungen MyISAM vermeiden. Bessere Indizes und eine Neuformulierung von Abfragen werden wahrscheinlich eher helfen.
( Created_tmp_disk_tables / Questions ) = 23,539,653 / 414140316 = 5.7%-- Prozentsatz der Abfragen, die eine temporäre Tabelle auf der Festplatte benötigten. -- Bessere Indizes / Keine Blobs / usw.
( Select_full_join / Com_select ) = 30,333,225 / 282029692 = 10.8%-- % der Auswahlen, die indexlose Verknüpfungen sind -- Fügen Sie den in JOINs verwendeten Tabellen geeignete Indizes hinzu.
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (87669877 + 27242 + 0 + 0 + 1452911 + 0) / 1725103 = 52 /sec-- Schreibvorgänge/Sek. -- 50 Schreibvorgänge/Sek. + Log-Flushings werden die I/O-Schreibkapazität von Festplattenlaufwerken wahrscheinlich auf das Maximum bringen. Wenn Sie eine SSD haben, ist dieser Wert wahrscheinlich in Ordnung.
( binlog_format ) = binlog_format = MIXED-- STATEMENT/ROW/MIXED. -- ROW wird von 5.7 (10.3) bevorzugt
( long_query_time ) = 10-- Grenzwert (Sekunden) zum Definieren einer „langsamen“ Abfrage. -- Vorschlag 2
( Max_used_connections / max_connections ) = 152 / 151 = 100.7%-- Spitzenprozentsatz an Verbindungen -- Erhöhen Sie max_connections (jetzt 151) und/oder verringern Sie wait_timeout (jetzt 28800). Oder beschleunigen Sie Abfragen.
( Connections ) = 11,987,448 / 1725103 = 6.9 /sec-- Verbindungen -- Wait_timeout erhöhen (jetzt 28800); Pooling verwenden?
( Connection_errors_accept + Connection_errors_internal + Connection_errors_peer_address + Connection_errors_select + Connection_errors_tcpwrap ) = 0 + 26 + 0 + 0 + 0 = 26-- Andere Verbindungsfehler als max_connections. -- Weitere Informationen finden Sie unter SHOW GLOBAL STATUS LIKE 'Connection_errors%'
Ungewöhnlich klein:
Created_tmp_files = 0.094 /HR
innodb_spin_wait_delay = 4
Ungewöhnlich groß:
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
Abnormale Zeichenfolgen:
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