我在使用 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)
附言：我的服务器仍然有问题。我需要更改其中一个数据库的字符集，花了一天多一点的时间才完成，只有 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。所以，我的建议是，不是 apache 占用了 CPU，也不是 MySQL。我用于备份的某些进程（如 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 网络服务器的东西，您是否尝试过使用“WP Redis Cache”插件将您的查询缓存到 Redis 中以保存查询查找？
您可以尝试安装的下一个插件是“查询监视器”，它将显示您正在动态调用的 SQL 查询，它是 WordPress 的一个非常好的调试工具。
请记住，如果您正在 WordPress 中开发自己的插件，则应该使用内置函数来缓存查询结果，自行处理 Redis。
在这种调试方式的最后，我建议您为所有超过 1 秒的 MySQL 启用慢日志查询，您可能会发现缺少列索引的查询。