我需要另一双眼睛来检查我的 MariaDB 设置。在高峰负载时间，CPU 可能会达到 600% 甚至更多，从而减慢 Wordpress 应用程序的速度。我使用 MySQLTuner 调整了一些设置。
服务器是 Debian 10.7、32GB 内存、8 核 CPU、Linode 上的共享 VM。它为 Wordpress 应用程序运行 Apache 和 MariaDB。
我怀疑它可能是正在查询的大表，它有超过 6301558 行，大小为 1.1GB。
mariadb.cnf 文件：
[mysqld]
symbolic-links=0
skip-external-locking
key_buffer_size = 128K
max_allowed_packet = 4M
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 900
sort_buffer_size = 128K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
net_buffer_length = 4K
thread_stack = 480K
innodb_file_per_table = 1
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
max_connections=300
max_user_connections=50
wait_timeout=50
interactive_timeout=50
long_query_time=5
以下是 MySQLTuner 摘要：
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 1.3G (Tables: 50)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 16h 34m 53s (34M q [146.936 qps], 60K conn, TX: 162G, RX: 6G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 31.4G
[--] Max MySQL memory : 6.1G
[--] Other process memory: 0B
[--] Total buffers: 4.4G global + 5.8M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 4.6G (14.58% of installed RAM)
[OK] Maximum possible memory usage: 6.1G (19.43% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (4K/34M)
[OK] Highest usage of available connections: 11% (33/300)
[!!] Aborted connections: 8.12% (4923/60626)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (352 temp sorts / 808K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 46% (415K on disk / 892K total)
[OK] Thread cache hit rate: 99% (44 created / 60K connections)
[OK] Table cache hit rate: 94% (308 open / 325 opened)
[OK] table_definition_cache(400) is upper than number of tables(209)
[OK] Open file limit used: 0% (59/14K)
[OK] Table locks acquired immediately: 100% (52 immediate / 52 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.3.27-MariaDB-0+deb10u1)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 25.8% (33K used / 131K cache)
[OK] Key buffer size / total MyISAM indexes: 128.0K/124.0K
[!!] Read Key buffer hit rate: 90.0% (20 cached / 2 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 4.0G/1.3G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk : 32 for 4 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: 100.00% (61483993760 hits/ 61484070155 total)
[OK] InnoDB Write log efficiency: 92.60% (3810440 hits/ 4114724 total)
[OK] InnoDB log waits: 0.00% (0 waits / 304284 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 98.8% (16M cached / 189K reads)
-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysql/error.log file
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
以下是显示 InnoDB 状态摘要：
| InnoDB | |
=====================================
2021-02-22 04:27:30 0x7fab5b545700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 43199 srv_active, 0 srv_shutdown, 186293 srv_idle
srv_master_thread log flush and writes: 229472
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4983384
OS WAIT ARRAY INFO: signal count 19744358
RW-shared spins 51316513, rounds 100262737, OS waits 897220
RW-excl spins 455471, rounds 5720299, OS waits 147869
RW-sx spins 2141, rounds 25812, OS waits 246
Spin rounds per wait: 1.95 RW-shared, 12.56 RW-excl, 12.06 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 20743979
Purge done for trx's n:o < 20743956 undo n:o < 0 state: running
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421853553754616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421853553750400, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421853553746184, ACTIVE 4 sec fetching rows
mysql tables in use 6, locked 0
0 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 58994, OS thread handle 140373653628672, query id 33162998 localhost wordpress Sending data
SELECT SQL_CALC_FOUND_ROWS DISTINCT `t1`.`id` FROM `gf_entry` AS `t1` LEFT JOIN `gf_entry_meta` AS `m2` ON (`m2`.`entry_id` = `t1`.`id` AND `m2`.`meta_key` = 228) LEFT JOIN `gf_entry_meta` AS `m3` ON (`m3`.`entry_id` = `t1`.`id` AND `m3`.`meta_key` = 68) LEFT JOIN `gf_entry_meta` AS `m4` ON (`m4`.`entry_id` = `t1`.`id` AND `m4`.`meta_key` = 71) LEFT JOIN `gf_entry_meta` AS `m5` ON (`m5`.`entry_id` = `t1`.`id` AND `m5`.`meta_key` = 42) LEFT JOIN `gf_entry_meta` AS `c6` ON (`c6`.`entry_id` = `t1`.`id` AND `c6`.`meta_key` = 'sequence') WHERE (`t1`.`form_id` IN
Trx read view will not see trx with id >= 20743955, sees < 20743955
---TRANSACTION 421853553741968, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
78113 OS file reads, 2188616 OS file writes, 877265 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 18.25 writes/s, 8.69 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 17, seg size 19, 77 merges
merged operations:
insert 95, delete mark 23, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 1106407, node heap has 2 buffer(s)
Hash table size 1106407, node heap has 9533 buffer(s)
Hash table size 1106407, node heap has 1 buffer(s)
Hash table size 1106407, node heap has 2 buffer(s)
Hash table size 1106407, node heap has 33 buffer(s)
Hash table size 1106407, node heap has 3 buffer(s)
Hash table size 1106407, node heap has 116 buffer(s)
Hash table size 1106407, node heap has 59 buffer(s)
581124.87 hash searches/s, 6749.39 non-hash searches/s
---
LOG
---
Log sequence number 77876397893
Log flushed up to 77876397893
Pages flushed up to 77876397893
Last checkpoint at 77876397846
0 pending log flushes, 0 pending chkp writes
335013 log i/o's done, 3.19 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4418699264
Dictionary memory allocated 154224
Buffer pool size 262144
Free buffers 170996
Database pages 81399
Old database pages 29966
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 284, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 78047, created 3352, written 1647191
0.00 reads/s, 0.00 creates/s, 12.81 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: 81399, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 65536
Free buffers 42613
Database pages 20486
Old database pages 7542
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 56, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19547, created 939, written 502730
0.00 reads/s, 0.00 creates/s, 3.75 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: 20486, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 65536
Free buffers 42824
Database pages 20336
Old database pages 7486
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 78, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19366, created 970, written 435222
0.00 reads/s, 0.00 creates/s, 5.37 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: 20336, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 65536
Free buffers 42837
Database pages 20259
Old database pages 7458
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 101, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19576, created 683, written 288950
0.00 reads/s, 0.00 creates/s, 0.62 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: 20259, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 65536
Free buffers 42722
Database pages 20318
Old database pages 7480
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 49, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 19558, created 760, written 420289
0.00 reads/s, 0.00 creates/s, 3.06 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: 20318, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=913, Main thread ID=140373985957632, state: sleeping
Number of rows inserted 324605, updated 93101, deleted 46031, read 45083162989
1.00 inserts/s, 0.75 updates/s, 0.12 deletes/s, 607231.05 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
============================
答案1
我假设您使用 InnoDB 作为存储引擎。这里要调整的最重要的参数（用于读取（也用于写入））是 innodb_buffer_pool_size。在仅数据库的机器上，它应该占用大约 75% 的 RAM。但您在同一台机器上运行 Apache。所以我不会在第一次尝试中超过 50%。但您的症状 600% CPU 使用率听起来更像是查询调整问题。所以我会关注这一点。从慢速查询日志开始。它会告诉您哪些是存在问题的查询...