使用 Mysql Tuner 優化 MariaDB

我已經閱讀了很多關於低記憶體伺服器的 MariaDB 優化的文章。有很多關於這方面的指南，但人們總是在分享他們自己的配置。這讓我或其他初學者很難理解。

根據以下指南，MariaDB 基金會建議我禁用性能模式。它只是節省了100mb的記憶體。 https://mariadb.com/resources/blog/starting-mysql-on-low-memory-virtual-machines/

問題是我的伺服器有 1GB 記憶體，當我通過 Mysql Tuner 檢查它時一切都很好。物理記憶體為1GB，最大 MySQL 記憶體為800MB

然後我昨天將我的伺服器從 1GB 記憶體升級到了 2GB 記憶體。現在，正如您在下面看到的，Mysqltuner 建議我在升級後專用我的伺服器。為什麼會出現這些警告？

root@WordPress:~# perl mysqltuner.pl --checkversion --updateversion
>>  MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] You have the latest version of MySQLTuner(1.7.19)
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 10.4.12-MariaDB-1:10.4.12+maria~bionic
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log(408B)
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[!!] /var/log/mysql/error.log contains 4 error(s).
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 17.4M (Tables: 89)
[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 are 620 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 16h 4m 42s (166K q [2.870 qps], 8K conn, TX: 170M, RX: 14M)
[--] Reads / Writes: 81% / 19%
[--] Binary logging is disabled
[--] Physical Memory     : 1.9G
[--] Max MySQL memory    : 3.3G
[--] Other process memory: 0B
[--] Total buffers: 432.0M global + 18.8M per thread (151 max threads)
[--] P_S Max memory usage: 104M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 649.3M (32.58% of installed RAM)
[!!] Maximum possible memory usage: 3.3G (169.44% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/166K)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Aborted connections: 0.00%  (0/8261)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (838 on disk / 93K total)
[OK] Thread cache hit rate: 99% (6 created / 8K connections)
[OK] Table cache hit rate: 95% (139 open / 145 opened)
[OK] table_definition_cache(400) is upper than number of tables(258)
[OK] Open file limit used: 1% (52/4K)
[OK] Table locks acquired immediately: 100% (231 immediate / 231 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 104.4M
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 1 thread(s).
[--] Using default value is good enough for your version (10.4.12-MariaDB-1:10.4.12+maria~bionic)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 256.0M/17.4M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 32.0M * 2/256.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 2 for 1 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: 99.92% (2744805 hits/ 2747120 total)
[!!] InnoDB Write Log efficiency: 74.36% (25782 hits/ 34670 total)
[OK] InnoDB log waits: 0.00% (0 waits / 8888 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/320.0K
[!!] Aria pagecache hit rate: 93.4% (12K cached / 850 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 error line(s) into /var/log/mysql/error.log file
   MySQL was started within the last 24 hours - recommendations may be inaccurate
   Reduce your overall MySQL memory footprint for system stability
   Dedicate this server to your database for highest performance.

MariaDB 版本： 10.4 和伺服器配置： LEMP 堆棧

這是我的 MariaDB 設置：

query_cache_type = 0
query_cache_size = 0
performance_schema = ON
innodb_buffer_pool_size = 256M
innodb_log_file_size = 32M
skip-name-resolve=1
join_buffer_size=256K
innodb_stats_on_metadata = 0

我需要你的絕妙建議。問候。

• 我曾經在一台 256MB 的非專用機器上使用 MySQL。但是，由於作業系統膨脹、更多 MySQL/MariaDB 功能等，如今 512MB 可能是最低要求。
• 如果您將 設置得太高my.cnf，則會出現交換，這對性能來說很糟糕。
• 使用小型 VM 時，減少而不是增加 my.cnf 中的內容。
• 要減少的主要是innodb_buffer_pool_size. 128M處於“太小”（可能崩潰）和“太大”（可能交換）之間的臨界點附近。
• 其他事情： max_connections=10, table_open_cache=50, query_cache_type=0, query_cache_size=0, performance_schema=OFF。
• 特定的工作負載可能需要增加一些東西的大小，但要小心。
• 沒有有效的“最大記憶體”公式；不要把 mysqltuner 的價值看得太重。它既低於可能達到的最大值*，*也低於你可能達到的最大值。
• 如果您可以提供一些交換空間，請這樣做。交換比崩潰（OOM）更好。
• MysqltunerInnodb data size可能很有用：buffer_pool 不需要超過大約兩倍。然而，一個小的 buffer_pool 可以處理多少數據是沒有限制的——權衡的是更多的 I/O。

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