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 設定論壇。