我檢查了慢速日誌，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，因此我記錄了資料庫：
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 大概是這樣的，我沒能及時捕捉到，但是當它達到 95%+ cpu 時，螢幕看起來像這樣：
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
- 32 GB 內存
- 正常運轉時間 = 19 天 23:11:43
- 看來您正在運行 MyISAM 和 InnoDB。
- 240 每秒吞吐量
更重要的問題：
變更long_query_time為1以便您可以在慢日誌中捕獲更多查詢。 （現在您有 10 秒的時間；這可能解釋了為什麼您只找到 4 個查詢。）有一些線索表明某些查詢運行效率低下。以下是尋找此類查詢的方法： http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
為什麼使用MyISAM？這些值令人困惑——就好像您為一個大型 MyISAM 表重新建立了一個索引，但沒有做太多其他事情。大多數情況下，最好使用InnoDB。
innodb_buffer_pool_size可能可以增加以提高InnoDB查詢速度。
請小心general_log——它會很快填滿磁碟。
「查詢快取」運作效率低。我建議完全關閉它：query_cache_type=off和query_cache_size=0。
Max_used_connections打到152，說明有很多用戶同時連線。 （這並不是說同時執行 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%-- 大多數可用記憶體應可用於快取。 --http://mysql.rjweb.org/doc.php/memory
( general_log ) = general_log = ON-- 所有執行的查詢的日誌（檔案或表格）。 -- 不使用式關閉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_深度來修復
( 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 應該如何要求作業系統寫入區塊。建議使用 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 個用於硬碟。
( 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決定預設為關閉。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 是否記錄所有死鎖。 -- 如果您受到死鎖的困擾，請開啟此功能。注意：如果存在大量死鎖，這可能會向磁碟寫入大量資料。
( character_set_server ) = character_set_server = latin1
-- 透過將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%-- Removal Ratio（由於記憶體不足而需要剪枝的頻率）
( 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 的 SELECTs -- 考慮關閉查詢快取。
( 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）。檢查臨時表的規則，以了解何時使用 MEMORY 而不是 MyISAM。也許微小的架構或查詢更改可以避免 MyISAM。更好的索引和重新制定查詢更有可能有所幫助。
( Created_tmp_disk_tables / Questions ) = 23,539,653 / 414140316 = 5.7%-- 需要磁碟臨時表的查詢百分比。 -- 更好的索引/沒有斑點/等等。
( Select_full_join / Com_select ) = 30,333,225 / 282029692 = 10.8%-- 無索引連線的選擇百分比 -- 將適當的索引新增至連線中使用的資料表。
( 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