我有一台具有 120GB RAM 和 mariadb 10.6.16 的 Plesk Ubuntu 22.04 服务器，我用它来运行单个网站的数据库。仅数据库就有大约 8GB，但尽管我进行了所有优化，mariadb 仍然以超过 140% 的 CPU 运行。这会导致网站响应时间变慢。
我已经进行了相当多的 Google 搜索并优化了 PHP FPM 和 MySQL，但尽管如此，网站的速度仍然很慢，我将矛头指向 mariadb，它的进程占用了处理器的 140% 以上。我将在下面留下屏幕截图。
PHP FPM 配置
我设置：
pm.max_children = 90
pm.max_requests = 200
pm = static
memory_limit = 10240M
max_execution_time = 300
max_input_time = 600
post_max_size = 1024M
upload_max_filesize = 512M
MariaDB配置
在/etc/mysql/my.cnf文件中，我设置了这些值
[mysqld] sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
local-infile=0
loose-local-infile=1
innodb_buffer_pool_size = 96G
innodb_log_file_size = 12G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 512M
query_cache_min_res_unit = 4k
innodb_lock_wait_timeout = 6000
这也很奇怪，但在下面的内存截图中，缓存的部分（米色）太小了。 内存截图
服务器本身在 64Gb Ubuntu 20.04 服务器上运行良好，没有出现问题。但自从这次迁移以来，速度很慢。即使我们在 Ubuntu 22.04 服务器中将 RAM 从 64Gb 更改为 120Go。
有人已经遇到过这种问题吗？
在此先感谢您的帮助。
问候
以下是基于评论的附加信息： 服务器 CPU 是 24 个 AMD EPYC 7282 16 核处理器 硬盘驱动器是 NVMe
MariaDB [(none)]> SHOW ENGINE INNODB STATUS;
| InnoDB | |
=====================================
2024-02-06 09:18:33 0x7fd0721fe640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 386065 srv_idle
srv_master_thread log flush and writes: 386059
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 5295351
Purge done for trx's n:o < 5295349 undo n:o < 0 state: running
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7fe8c01d0880), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cdc80), not started
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cb080), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c8480), not started
mysql tables in use 6, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c4d80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cbb80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c4280), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01d1380), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cd180), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01ce780), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cc680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c3780), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c0b80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01d1e80), not started
mysql tables in use 2, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c9a80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cfd80), not started
mysql tables in use 1, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01cf280), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01ca580), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c7980), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c2c80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c6e80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c2180), not started
mysql tables in use 5, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c8f80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c6380), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c5880), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fe8c01c1680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
406980 OS file reads, 1500489 OS file writes, 309011 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.07 writes/s, 0.56 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 10381178784
Log flushed up to 10381178282
Pages flushed up to 9758411800
Last checkpoint at 9758411788
0 pending log flushes, 0 pending chkp writes
1500490 log i/o's done, 1.07 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 103213432832
Dictionary memory allocated 613451784
Buffer pool size 6230016
Free buffers 5820886
Database pages 409130
Old database pages 151006
Modified db pages 21938
Percent of dirty pages(LRU & free pages): 0.352
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 1300160, not young 5227092
0.04 youngs/s, 0.00 non-youngs/s
Pages read 406341, created 12733, written 0
0.00 reads/s, 0.02 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 409130, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 774791, updated 948664, deleted 367, read 777328684815
0.85 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1854176.96 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
根据 Stackexchange 固定的限制，其他命令的结果太长，无法插入到描述中。
其他信息：
显示全球状态； https://justpaste.it/4iip0
显示全局变量； https://justpaste.it/d9k81
地位; https://justpaste.it/fk95g
ulimit -a https://justpaste.it/bc50r
Thanks very much for your response.
I solve my problem by taking a Dedicated server instead of VPS.
The old VPS was : 120Go RAM 24 Core AMD CPU NVMe as storage Ubuntu 22.04
新的是：64Go RAM 18 核 Intel CPU NVMe 作为存储 Ubuntu 22.04
关于Mariadb优化，我使用了下面的配置，网站加载得更好
我认为这是 VPS 的问题。
每秒速率 = RPS
操作系统命令提示符要考虑的建议，
这是一个动态变量，经过测试后需要在操作系统停止/启动周期中保持不变。我们可以提供帮助。
10.6.16 MariaDB my.cnf [mysqld] 部分需要考虑的建议
还有许多其他机会可以改进您的配置并降低 CPU 繁忙百分比。
观察结果：
更重要的问题：
请在几个小时后重新运行“显示全球状态”。由于只运行了 15 分钟，以下分析不会很有用。
每秒 6,190 个查询，占用大量 CPU 时间。但qps通常有那么高吗？或者这只是在启动过程中？
建议的设置更改：
详细信息和其他观察结果：
( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 0 + 0 + 400094 + 0 + 0 + 0 ) / 200 / 912 = 219.3%-- 这可能是一个指标，表明 innodb_io_capacity 设置合理。-- 如果硬件可以处理，则增加 innodb_io_capacity （现在为 200）。( table_open_cache ) = 8,000-- 要缓存的表描述符的数量 -- 通常几百个就足够了。( innodb_lru_scan_depth ) = 1,536-- innodb_lru_scan_depth 是一个命名非常糟糕的变量。更好的名称是 innodb_free_page_target_per_buffer_pool。它是 InnoDB 试图在每个缓冲池实例中保持空闲的页面数量，以加速读取和页面创建操作。--“InnoDB：page_cleaner：1000ms预期循环花费了...”可以通过降低lru_scan_深度来修复( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 4,791,284 * 16384 / 81920M = 91.4%-- 缓冲池空闲 -- buffer_pool_size 大于工作集；可以减少它( innodb_io_capacity ) = 200-- 刷新时，使用这么多 IOP。-- 读取可能缓慢或尖刻。如果使用 SSD 驱动器，请使用 2000。( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 4,791,284 / 5191680 = 92.3%-- 当前未使用的 buffer_pool 的百分比 -- innodb_buffer_pool_size（现在为 85899345920）是否大于必要的？( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10-- 容量：max/plain -- 建议 2。最大值应大约等于 I/O 子系统可以处理的 IOP。（如果驱动器类型未知，2000/200 可能是合理的一对。）( innodb_change_buffering ) = innodb_change_buffering = none-- 在 5.6.11 / 5.5.31 之前，有一个错误使“更改”成为更安全的选项。MariaDB 10.5.15 正在走向“无”并在 10.9 中弃用( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 6,560,088,064 / 81920M = 7.6%-- 数据占用缓冲池的百分比 -- 小百分比可能表明 buffer_pool 不必要地大。( innodb_doublewrite ) = innodb_doublewrite = OFF-- ON 导致额外的 I/O，但在崩溃时具有额外的安全性。-- 对于 FusionIO、Galera、Replicas、ZFS、EXT4，关闭即可。( innodb_log_buffer_size ) = 1024M-- 建议 2MB-64MB，并且至少与事务中设置的最大 blob 一样大。-- 调整 innodb_log_buffer_size （现在为 1073741824）。( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 912 / 60 * 16384M / 4567040 = 57,177-- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 17179869184). (Cannot change in AWS.)( default_tmp_storage_engine ) = default_tmp_storage_engine =( innodb_flush_neighbors ) = innodb_flush_neighbors = 1-- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 400094 + 0 ) / 912 / 200 = 219.3%-- If > 100%, need more io_capacity. -- Increase innodb_io_capacity (now 200) if the drives can handle it.( innodb_io_capacity ) = 200-- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor. Limits write IO requests per second (IOPS). -- For starters: HDD: 200; SSD: 2000.( innodb_flush_log_at_trx_commit ) = 2-- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 0)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.( sync_binlog ) = 0-- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.( innodb_purge_threads ) = 8-- Number of threads to clean up history list. -- If you have a lot of writes, recommend 4 in versions 5.6 and 10.0 or later.( local_infile ) = local_infile = ON-- local_infile (now ON) = ON is a potential security issue( bulk_insert_buffer_size ) = 8 / 122880M = 0.01%-- Buffer for multi-row INSERTs and LOAD DATA -- Too big could threaten RAM size. Too small could hinder such operations.( tmp_table_size ) = 512M-- Limit on size of MEMORY temp tables used to support a SELECT -- Decrease tmp_table_size (now 536870912) to avoid running out of RAM. Perhaps no more than 64M.( binlog_format ) = binlog_format = MIXED-- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)( slow_query_log ) = slow_query_log = OFF-- Whether to log slow queries. (5.1.12)( long_query_time ) = 10-- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2( Uptime_since_flush_status ) = 912 = 15m 12s-- How long (in seconds) since FLUSH STATUS (or server startup). -- GLOBAL STATUS has not been gathered long enough to get reliable suggestions for many of the issues. Fix what you can, then come back in a several hours.( Uptime ) = 912 = 15m 12s-- How long (in seconds) the server has been running. -- The system has not been up long enough to get reliable suggestions for many of the issues. Fix what you can, then come back with fresh values after the system has been running several hours.( Max_used_connections / max_connections ) = 37 / 500 = 7.4%-- Peak % of connections -- Since several memory factors can expand based on max_connections (now 500), it is good not to have that setting too high.( thread_cache_size / Max_used_connections ) = 100 / 37 = 270.3%-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.( thread_pool_max_threads ) = 65,536-- One of many settings for MariaDB's thread pooling -- Lower the value.Abnormally small:
Abnormally large:
Abnormal strings: