スロー ログを確認したところ、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 コアすべてで CPU 使用率が 100 になることがよくあります。
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
スロークエリログを確認した後、一般クエリログを確認したところ、クエリが非常に多くて驚きました。トラフィックが通常のときは、10 分後に 136,235 件のクエリがあり、そのほとんどは SELECT クエリです。トラフィックが多いときは、10 分間で 195,650 件のクエリがありました。195,650 人の訪問者がいるとは思えませんが、何らかの理由で呼び出しが general_log 内にあります。slow_query_log には 4 件のクエリしかなく、最適化されていないクエリのようには見えませんでした。他に確認すべき点はありますか、それともトラフィックによるものであると推測するにはこれで十分で、サーバーをアップグレードする必要がありますか?
上部は大体このように見えますが、時間内にキャプチャできませんでしたが、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
- 32 GBのRAM
- 稼働時間 = 19日 23:11:43
- MyISAM と InnoDB の両方を実行しているようです。
- 240 QPS
より重要な問題:
long_query_timeに変更する1と、スローログでより多くのクエリをキャッチできるようになります。(現在 10 秒あります。これが、クエリが 4 つしか見つからない理由です。) 一部のクエリが非効率的に実行されていることを示す手がかりがいくつかあります。このようなクエリを見つける方法は次のとおりです。 http://mysql.rjweb.org/doc.php/mysql_analysis#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/メモリ
( 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-- ページ クリーナーの 1 秒あたりの作業量。 -- 「InnoDB: page_cleaner: 意図したループに 1000 ミリ秒かかりました...」は、lru_scan_depth を下げることで修正できる可能性があります。1000 / innodb_page_cleaners (現在は 4) を検討してください。また、スワッピングも確認してください。
( innodb_lru_scan_depth ) = 1,024
-- 「InnoDB: page_cleaner: 1000ms の意図したループに要した時間...」は、lru_scan_depth を下げることで修正できる可能性があります。
( 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、HDD の場合は 1 を使用します。
( innodb_io_capacity ) = 200-- ディスク上で実行可能な 1 秒あたりの 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-- すべてのデッドロックをログに記録するかどうか。 -- デッドロックに悩まされている場合は、これをオンにします。注意: デッドロックが多数ある場合、ディスクに大量のデータが書き込まれる可能性があります。
( 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%-- 削除率（メモリ不足によりプルーニングが必要となる頻度）
( 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 と formula -- 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%-- ディスク上の tmp テーブルを必要とするクエリの割合。 -- インデックスの改善 / BLOB なし / その他
( 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-- STATEMENT/ROW/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 以外の接続エラー。 -- 詳細については、SHOW GLOBAL STATUS LIKE '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