WordPress、Apache、MySQL を使用する Web サイトの CPU 使用率に問題があります。日中、時々、MySQL と Apache による CPU 使用率が 2400% まで上昇し (合計 24 個のコアがあります)、サーバーがフリーズし、平均負荷が 24 まで上昇します。
最近、いつもより少しトラフィックが多かったのですが、これは永続的なものではないはずですよね？カーネル、データベース、ライブラリを更新し、何度も再起動しました。それでも、フリーズします。DBのプロセスリストを見ましたが、異常はありません。データベースには、かなり大量のデータがあります。ほんの数週間前は問題なく動作していたのに、今は動作しません。したがって、最適化されていないクエリではないはずです。
このような行動の原因は何でしょうか?
アップデート：
A) SHOW GLOBAL STATUS LIKE 'com_%r%_table'; の結果
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Com_alter_table | 5 |
| Com_create_table | 34 |
| Com_drop_table | 0 |
| Com_rename_table | 0 |
| Com_show_create_table | 0 |
+-----------------------+-------+
5 rows in set (3.04 sec)
B) 「uptime%」のようなグローバルステータスを表示します。
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| Uptime | 455524 |
| Uptime_since_flush_status | 455524 |
+---------------------------+--------+
2 rows in set (0.01 sec)
C) '%dirty%' のようなグローバル ステータスを表示します。
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
+--------------------------------+-------+
2 rows in set (0.00 sec)
追伸: サーバーにまだ問題があります。データベースの 1 つで文字セットを変更する必要があり、400,000 行だけなのに完了するまでに 1 日以上かかりました。以前は、多少時間はかかっていましたが、それほどではありませんでした。DDOS 攻撃の後、データベースに何らかの変更が加えられ、パフォーマンスが悪化する可能性があるのではないかと思いました。
アップデート2:
mysqltuner の結果:
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 8.0.26-0ubuntu0.20.04.2
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log(0B)
[--] Log file /var/log/mysql/error.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 262.4G (Tables: 179)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5d 11h 4m 6s (15M q [31.945 qps], 191K conn, TX: 80G, RX: 2G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 31.4G
[--] Max MySQL memory : 9.8G
[--] Other process memory: 0B
[--] Total buffers: 176.0M global + 65.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.8G (31.14% of installed RAM)
[OK] Maximum possible memory usage: 9.8G (31.14% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/15M)
[!!] Highest connection usage: 100% (151/151)
[OK] Aborted connections: 0.09% (174/191712)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 2M total)
[OK] Thread cache hit rate: 92% (15K created / 191K connections)
[OK] Table cache hit rate: 99% (21M hits / 21M requests)
[OK] table_definition_cache(2000) is upper than number of tables(506)
[OK] Open file limit used: 0% (6/10K)
[OK] Table locks acquired immediately: 100% (9 immediate / 9 locks)
[OK] Binlog cache memory access: 99.57% (25538 Memory / 25647 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/262.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 98.29% (925392031 hits/ 941450541 total)
[!!] InnoDB Write Log efficiency: 309.39% (25100125 hits/ 8112662 total)
[!!] InnoDB log waits: 0.00% (65 waits / 33212787 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: *link*
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
innodb_buffer_pool_size (>= 262.4G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_log_buffer_size (>= 16M)
アップデート3:
今日、サーバーがまたフリーズしました。CPU をオーバーロードしていたプロセスは apache2 でした。サービスを停止できました。すると突然、すべてがスムーズに動き始めました。行数の多いデータベースのバックアップを試みましたが、問題なく動作しました。しかし、しばらくするとすべてが再びフリーズしました。一部のプロセスの CPU 使用率は 2400% で、負荷平均は 24 を超えました。したがって、CPU をロードしているのは apache ではなく、MySQL でもないというのが私の考えです。バックアップに使用している htop や gzip などのプロセスも、時々 CPU 使用率が高くなります。では、これは何なのでしょうか。DDOS 攻撃の結果でしょうか。もしそうなら、どのように修正すればよいでしょうか。
答え1
1秒あたりのレート = RPS
my.cnf [mysqld] セクションに考慮すべき提案
innodb_buffer_pool_size=22G # from 128M to better accomodate your 262G of data
max_connections=256 # from 151 since you have had all connections used
thread_cache_size=150 # from 9 to reduce threads_created RPhr of 111
innodb_log_file_size=4G # from 50M to support more than and hour before rotation
innodb_log_buffer_size=1G # from 16M to support ~ 30 min before write to media
パフォーマンスが大幅に向上するはずです。パフォーマンスを向上させる機会は他にもたくさんあります。連絡先情報と、パフォーマンスを向上させるための無料ダウンロード可能なユーティリティ スクリプトについては、プロフィールをご覧ください。
答え2
非常に難しいことですが、WordPress を実行していて、ピーク時には 24 個のコアが 100% になるということです。1 つのクエリで一度に 1 つのスレッドしか使用できないわけではないことに注意してください。
クエリのパフォーマンスが非常に悪く、Apache Web サーバーに直接接続されていない場合は、「WP Redis Cache」プラグインを使用してクエリを Redis にキャッシュし、クエリ検索を節約することを試しましたか?
次にインストールできるプラグインは「Query Monitor」です。これは、呼び出している SQL クエリをその場で表示します。これは、WordPress にとって非常に優れたデバッグ ツールです。
WordPress で独自のプラグインを開発している場合は、組み込み関数を使用してクエリ結果をキャッシュすることで、Redis を自分で管理する必要があることに注意してください。
そして、このデバッグ方法の最後に、1 秒を超えるすべてのものに対して MySQL のスロー ログ クエリを有効にすることをお勧めします。これにより、列のインデックスが欠落しているクエリが見つかる場合があります。