極慢的網站，MySQLTuner 日誌，有什麼建議嗎？

我們正在執行一個 Wordpress 站點，並且在必須錯過 Nginx 記憶體的頁面的響應時間極慢方面遇到了重大問題。

我將我們的數據庫性能視為罪魁禍首，並且有一個 MySQLTuner 日誌。我應該在這裡改變/調整什麼？

>>  MySQLTuner 1.7.9 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.32-MariaDB-1~jessie
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/shoegaze.err(0B)
[!!] Log file /var/lib/mysql/shoegaze.err doesn't exist
[!!] Log file /var/lib/mysql/shoegaze.err isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 477M (Tables: 229)
[--] Data in MyISAM tables: 564M (Tables: 74)
[OK] Total fragmented tables: 0

-------- 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 17h 4m 19s (8M q [38.070 qps], 143K conn, TX: 170G, RX: 8G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 1.0G
[--] Max MySQL memory    : 1.3G
[--] Other process memory: 309.1M
[--] Total buffers: 624.0M global + 7.5M per thread (100 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 895.2M (87.42% of installed RAM)
[!!] Maximum possible memory usage: 1.3G (134.49% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/8M)
[OK] Highest usage of available connections: 36% (36/100)
[OK] Aborted connections: 0.01%  (15/143505)
[!!] 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.
[OK] Query cache efficiency: 37.9% (4M cached / 11M selects)
[!!] Query cache prunes per day: 20139
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 96K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 31% (131K on disk / 421K total)
[OK] Thread cache hit rate: 99% (36 created / 143K connections)
[OK] Table cache hit rate: 57% (400 open / 691 opened)
[OK] Open file limit used: 1% (219/16K)
[OK] Table locks acquired immediately: 97% (3M immediate / 3M locks)
[OK] Binlog cache memory access: 99.99% (15789 Memory / 15790 Total)

-------- 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: 4 thread(s).
[--] Using default value is good enough for your version (10.1.32-MariaDB-1~jessie)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (134M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/226.3M
[OK] Read Key buffer hit rate: 99.8% (53M cached / 134K reads)
[OK] Write Key buffer hit rate: 99.3% (1M cached / 1M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 256.0M/477.1M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M * 2/256.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.99% (29731944 hits/ 29735567 total)
[!!] InnoDB Write Log efficiency: 44.99% (12271 hits/ 27274 total)
[OK] InnoDB log waits: 0.00% (0 waits / 15003 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 84.8% (860K cached / 131K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] 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 your overall MySQL memory footprint for system stability
   Dedicate this server to your database for highest performance.
   Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
   When making adjustments, make tmp_table_size/max_heap_table_size equal
   Reduce your SELECT DISTINCT queries which have no LIMIT clause
   Performance should be activated for better diagnostics
   Consider installing Sys schema from https://github.com/mysql/mysql-sys

Variables to adjust:
 *** MySQL's maximum memory usage is dangerously high ***
 *** Add RAM before increasing MySQL buffer variables ***
   query_cache_size (=0)
   query_cache_type (=0)
   query_cache_size (> 64M)
   tmp_table_size (> 32M)
   max_heap_table_size (> 32M)
   performance_schema = ON enable PFS
   innodb_buffer_pool_size (>= 477M) if possible.
   innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
   innodb_buffer_pool_instances (=1)

1GB 的 RAM在今天看來是*微不足道的。*你已經過度分配了它。你看到有交換嗎？交換對於 MySQL 性能來說是很糟糕的。您似乎混合了 MyISAM 和 InnoDB 表；這是故意的嗎？

以下是一些應該有所幫助的更改：

key_buffer_size = 50M
innodb_buffer_pool_size = 300M
aria_pagecache_buffer_size = 32M
query_cache_size -- either 0 or no more than 32M

請注意，MyISAM數據使用作業系統中的可用空間進行記憶體。您的設置可能幾乎沒有留下任何空間，從而大大減慢了 MyISAM SELECTs。

如果您確實選擇了更多 RAM，我們應該在至少一天的正常執行時間後重新訪問設置 -SHOW GLOBAL STATUS;並且SHOW GLOBAL VARIABLES;，加上新的 RAM 大小。

wp_postmeta，如目前指定的那樣，效率低下。 這是改進它的建議。

如果 nginx 的記憶體在同一台機器上，那麼爭奪有限 RAM 是另一回事。“不要將記憶體放在另一個記憶體之前。”

1° 查詢記憶體禁用查詢記憶體似乎是一個不錯的選擇

啟動 query_cache 通常總是不好的。查詢記憶體是 MySQL 和 MariaDB 中眾所周知的競爭點

$$ !! $$由於互斥量爭用，查詢記憶體可能預設禁用。 1° RAM 太低

此外，嘗試調整 1GB RAM 不是建議事實上，Linux 核心還需要 RAM 以確保最低限度的重要可行作業系統功能。在您的報告中，其他程序正在使用您目前 RAM 空間的大約 40%

$$ – $$其他程序記憶體：309.1M 一些，如果想要更高的性能，需要增加RAM大小。 您的數據庫需要 477MB RAM 才能正常工作

3° 你的 MariaDB 版本

最後一個版本是：10.1.32

https://mariadb.com/kb/en/mariadb-10148-release-notes/

自 2020 年 11 月起不再支持此版本

升級你的數據庫版本

4° 使用最新版本的 MySQLtuner

https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

最後一個版本是 1.9.4

5° 大量臨時表您需要在數據庫上添加更多索引

引用自：https://dba.stackexchange.com/questions/208216