Mariadb MySQL Tuner 報告令人困惑

我想請您澄清一下 mysqltuner 關於 MariaDB 數據庫的報告。mysqltuner 是用 –nogood 標誌呼叫的！

>>  MySQLTuner 1.7.1 - 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
[!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: (0B)
[!!] Log file  doesn't exist
[!!] Log file  isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 380M (Tables: 417)

-------- Security Recommendations ------------------------------------------------------------------
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15s (812 q [54.133 qps], 275 conn, TX: 258K, RX: 108K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 10.0G
[--] Other process memory: 1.2G
[--] Total buffers: 8.4G global + 10.7M per thread (150 max threads)
[--] P_S Max memory usage: 34M
[--] Galera GCache Max memory usage: 0B
[!!] Slow queries: 27% (221/812)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 521 selects)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 35.0M
[--] 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.0.29-MariaDB-0ubuntu0.16.04.1)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Read Key buffer hit rate: 80.0% (10 cached / 2 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (12.5 %): 512.0M * 2/8.0G should be equal 25%
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.

-------- 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.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
   MySQL started within last 24 hours - recommendations may be inaccurate
   Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
   query_cache_type (=0)
   query_cache_limit (> 256K, or use smaller result sets)
   innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=4G) if possible.

讓我感到困惑的是“日誌文件推薦”部分。我真的不知道該怎麼辦。然後也是這一行：

[!!] Query cache may be disabled by default due to mutex contention.

我也很好奇為什麼會推薦我將query_cache_type改為0並增加query_cache_limit？

我知道它至少有 24 小時沒有執行，那是因為我已經調整了配置並重新啟動了我的數據庫。我根據我對 MariaDB 的了解做了一些調整，但是對於這幾件事我感到很困惑。

我可以解釋這條線

[!!] Query cache may be disabled by default due to mutex contention.

InnoDB 儲存引擎和查詢記憶體一直處於戰爭狀態（請參閱我 1.5 年前的文章為什麼從 MySQL 5.6 開始預設禁用 query_cache_type？）

mysqltuner 建議將 query_cache_type 設置為0，以便顯式禁用查詢記憶體。請不要忘記也將query_cache_size設置為 0。否則，互斥行為無論如何都會被動發生。

正如我在舊文章中所說，如果您合理地知道最常見結果集的大小，則不必禁用查詢記憶體。如果你能算出這個大小，那麼你可以用 query_cache_limit和query_cache_min_res_unit作為結果集大小的上限和下限。只有這樣才能將query_cache_type設置為 1。

至於您的日誌文件建議

[--] Log file: (0B)
[!!] Log file  doesn't exist
[!!] Log file  isn't readable.

也許這可以解釋它

[!!] Currently running unsupported MySQL version 10.0.29-MariaDB-0ubuntu0.16.04.1

mysqltuner 可能無法像支持的版本那樣與此版本的 MariaDB 中的日誌文件相關聯。

不信任（只靠它）——對於mysqltunner，所有的設置都要根據真實的監控和載入進行調整。

一側的日誌文件大小 - 建議在此期間所有事務的大小為 0.5-1Hr

但從另一面來看 - 如果它在崩潰後重新啟動超過 1-2Gb 可能需要很長時間。作為更大的日誌 - 作為更長的開始。

所以它總是在兩者之間保持平衡。

從每個文件 512M（總 1G）開始，然後如果載入高 - 增加到 1024Gb

最好檢查一下里面的內容：

Slow queries: 27% (221/812)

• 哪些查詢？
• 為什麼慢？
• 是按數據大小嗎？還是通過錯誤的索引？

這可以為性能提供更多

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