我在使用 WordPress、Apache 和 MySQL 的網站上遇到 CPU 使用問題。白天，MySQL 和 Apache 的 CPU 使用率有時會高達 2400%（我總共有 24 個核心），伺服器凍結，平均負載高達 24。
最近流量比平常多了一點，不過這東西應該不會是永久性的吧？我已經更新了核心、資料庫、函式庫，重新啟動了很多次。儘管如此，它還是凍結了。我查看了資料庫的進程列表，但沒有什麼特別的。在資料庫中，有相當大量的數據。就在幾週前，它還運作得很好，但現在卻不行了。因此，它不應該是未最佳化的查詢。
造成這種行為的原因是什麼？
更新：
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) 顯示全域狀態，如「正常運作時間%」；
+---------------------------+--------+
| 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)
ps 我的伺服器仍然有問題。我需要更改其中一個資料庫的字元集，花了一天多的時間才完成，只有 400 000 行。以前，這需要一些時間，但沒有那麼多。我想知道，會不會在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。我用於備份的某些程序（例如 htop 或 gzip）有時也會出現較高的 CPU 使用率。那會是什麼？這是否是 DDOS 攻擊的結果？
答案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 個執行緒。
因此，某些聽起來非常糟糕的查詢效能並且不能直接進入您的 Apache Web 伺服器，您是否嘗試過「WP Redis 快取」外掛程式將您的查詢快取到 Redis 中以保存查詢查找？
您可以嘗試安裝的下一個外掛程式是“Query Monitor”，它會顯示您正在動態呼叫的 SQL 查詢，它是 WordPress 的一個非常好的偵錯工具。
請記住，如果您在 WordPress 中開發自己的插件，您應該透過使用內建函數來快取查詢結果來自行管理 Redis。
在這種偵錯方式結束時，我建議您對超過 1 秒鐘的所有內容啟用 MySQL 慢日誌查詢，您可能會發現查詢缺少列索引。