在MariaDB中使用mysqltuner進行輕度調優

5 min

gnu-linuxmariadb
language: ja bn en es hi pt ru zh-cn zh-tw

大家好，我是無能。
在閒暇之餘，我一直在尋找MariaDB中是否有什麼可以調整的東西，結果發現了mysqltuner這個MySQL的調優軟體，於是就安裝了它。

安裝

在Devuan中，安裝方式如下：

sudo apt install mysqltuner

執行

它會像這樣以互動模式啟動。

$ mysqltuner
 >>  MySQLTuner 1.9.9
         * Jean-Marie Renouard <jmrenouard@gmail.com>
         * Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

最後出現了這樣的結果。

General recommendations:
   You are using n unsupported version for production environments
   Upgrade as soon as possible to a supported version !
   Reduce or eliminate unclosed connections and network issues
   Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
   We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
            See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
            (specially the conclusions at the bottom of the page).
   Performance schema should be activated for better diagnostics
   Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
   skip-name-resolve=1
   join_buffer_size (> 256.0K, or always use indexes with JOINs)
   table_definition_cache(400) > 585 or -1 (autosizing if supported)
   performance_schema=ON
   innodb_buffer_pool_size (>= 1.3G) if possible.
   innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals 25% of buffer pool size.

看來最好跳過DNS名稱解析。
※這似乎也是一個陷阱，如果MariaDB中設定為user@localhost，最好不要這樣做。: 當寫入skip-name-resolve時無法連接到DB的情況 – netcreates. blog
因此，我執行了grep -r mysqld來尋找目標區段的conf文件，發現路徑是/etc/mysql/mariadb.conf.d/50-server.cnf ，於是就添加了。
然後，看來最好調整join_buffer_size的大小，所以我就修改了。
表格定義的快取也最好設定一下，所以我也修改了。
InnoDB的日誌大小也最好指定一下，以避免過度膨脹。
我也指定了InnoDB的緩衝池大小，但設定得相當小。

[mysqld]
skip-name-resolve=1
join_buffer_size = 512K
table_definition_cache = 600
innodb_log_file_size = 32M
innodb_buffer_pool_size = 2G

如果語法中已經有註釋，則將其取消註釋，然後應用上述設定並重新啟動MariaDB。

sudo service mariadb restart

然後，再次執行mysqltuner的結果。

General recommendations:
    You are using n unsupported version for production environments
    Upgrade as soon as possible to a supported version !
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Performance schema should be activated for better diagnostics
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with JOINs)
    performance_schema=ON
    innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals 25% of buffer pool size.

我不想啟用performance_schema，而且其他也沒有什麼特別嚴格的問題了，所以暫時就到這裡吧。

那麼就到這裡。期待下次再會。

Related Posts