perl mysqltuner.pl --host 127.0.0.1
[--] Skipped version check for MySQLTuner script
[--] Performing tests on 127.0.0.1:3306
[!!] Successfully authenticated with no password - SECURITY RISK!
[!!] failed to execute: SHOW SLAVE STATUS\G
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW SLAVE HOSTS\G
[!!] FAIL Execute SQL / return code: 256
[OK] Currently running supported MySQL version 10.4.17-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[!!] InnoDB is enabled but isn't being used
[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 none of known auth columns exists
-------- CVE Security Recommendations --------------------------------------------------------------
[!!] CVE-2021-27928(<= 10.4.17) : "A remote code execution issue was discovered in MariaDB 10.2 before 10.2.37
[!!] 1 CVE(s) found for your MySQL release.
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 20h 44m 2s (48K q [0.196 qps], 384 conn, TX: 885M, RX: 18M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 1.8G
[--] Max MySQL memory : 46.5G
[--] Other process memory: 0B
[--] Total buffers: 8.3G global + 258.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 11.3G (617.92% of installed RAM)
[!!] Maximum possible memory usage: 46.5G (2537.09% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/48K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Aborted connections: 1.30% (5/384)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 19.8% (11K cached / 56K selects)
[!!] Query cache prunes per day: 7584
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 9K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 14% (1K on disk / 10K total)
[OK] Thread cache hit rate: 80% (73 created / 384 connections)
[OK] Table cache hit rate: 99% (74K hits / 74K requests)
[OK] table_definition_cache(400) is upper than number of tables(77)
[OK] Open file limit used: 0% (48/32K)
[OK] Table locks acquired immediately: 99% (5K immediate / 5K 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: 1 thread(s).
[--] Using default value is good enough for your version (10.4.17-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/0B
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] No tables are Innodb
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/0B
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.171875 %): 48.0M * 2/8.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 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% (609465732 hits/ 609485088 total)
[!!] InnoDB Write Log efficiency: 88.45% (3109 hits/ 3515 total)
[OK] InnoDB log waits: 0.00% (0 waits / 406 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 94.9% (28K cached / 1K 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
我们的一台生产服务器存在一些性能问题，我发现这主要与 MySQL 有关，运行 MySQLTuner 之后，我发现了一些有趣的事情。令人惊讶的是，没有索引就没有连接，所以我认为我无能为力，这似乎是由于缓存造成的，我知道我们正在使用 W3T Total Cache，但是除了 WP 管理面板中的常规配置外，我没有看到任何配置，所以我想知道是否有办法缓存更多的查询，因为出于某种原因，似乎只有 20％ 的查询被缓存了。而且查询缓存修剪得太高了，所以我想知道我是否正确理解了这些指标，并且我每天可以修剪更少的查询以便能够缓存至少 40％。另外，我认为增加 RAM 可以帮助我们的服务器，但这几乎是另一个问题。
这些似乎是最相关的：
[!!] Maximum reached memory usage: 11.3G (617.92% of installed RAM)
[!!] Maximum possible memory usage: 46.5G (2537.09% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 19.8% (11K cached / 56K selects)
[!!] Query cache prunes per day: 7584
答案1
您的所有表似乎都是 MyISAM 表。建议现在将所有表设置为 InnoDB 表。
由于各种原因，MySQL 查询缓存现已被弃用问题，因此最好禁用它。
将所有表更改为 InnoDB 后，再次运行 MySQLTuner 并根据其建议调整 InnoDB 缓冲池。
您还必须升级您的 MySQL 服务器，因为您的版本存在一个开放的漏洞。
答案2
我怀疑交换才是真正的问题。您已过度使用 RAM。以下是我发现的问题：
Physical Memory : 1.8G
InnoDB buffer pool / data size: 8.0G/0B
Highest usage of available connections: 7% (12/151)
切换到 InnoDB 并关闭 QC（按照 Tero 的建议）后，请注意您的 RAM 对于给定而言太小innodb_buffer_pool_size。
更具体地说，进行以下配置更改：
innodb_buffer_pool_size = 500M
max_connections = 30
query_cache_type = 0
query_cache_size = 0
key_buffer_size = 24M
如果在进行这些更改后仍然遇到问题，请在 dba.stackexchange.com 上提出新问题；它是有关 MySQL/MariaDB 配置的更好的论坛。