MySQLTuner を使用して MySQL(MariaDB) のチューニングを行なう

はじめに

MySQLTuner を使用して、MySQL(MariaDB) のチューニングを行います。

注意事項

コマンドはすべて root 権限での操作です。

環境

VPS : さくらVPS 1G SSD
OS : CentOS 7.3
その他 : MariaDB 5.5.52, MySQLTuner 1.6.0

MySQLTuner とは

MySQLTuner は、Perl で書かれたスクリプトで、パフォーマンスと安定性を高めるためのチューニングについてのポイントを表示してくれます。

インストール

yum を利用してインストールします。

# yum --enablerepo=epel install mysqltuner

MySQLTuner の実行

以下のコマンドを入力して実行します。ユーザー名とパスワードを入力するよう求められるので、管理者権限のあるユーザーを入力します。

# mysqltuner -buffers
Please enter your MySQL administrative login: <ユーザー名>
Please enter your MySQL administrative password: <パスワード>

MySQLのチューニングに関する内容が項目毎に表示されます。

Storage Engine Statistics : ストレージエンジンの統計についての項目
Security Recommendations : セキュリティの推奨事項についての項目
Performance Metrics : パフォーマンス指標についての項目
MyISAM Metrics : MyISAM指標についての項目
InnoDB Metrics : InnoDB指標についての項目
AriaDB Metrics : AriaDB指標についての項目
Replication Metrics : レプリケーション指標についての項目
Recommendations : 推奨事項についての項目

MySQL(MariaDB) のチューニング

ここでは、実際の MariaDB に対して、MySQLTuner を使用してチューニングを行ないます。まずは MySQLTuner を実行し、最後に表示される推奨事項を確認します。

# mysqltuner -buffers

.....

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)

一般的な推奨事項と、調整する変数についての情報が表示されるので、それぞれ確認していきます。

MariaDB の設定を変更する

上記の内容を元に、MariaDB の設定ファイルを変更します。ここではスロークエリをログに出力し、各種変数を調整してみます。

# vi /etc/my.cnf.d/server.cnf

server.cnf
[mysql]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mariadb/slow_query.log

query_cache_size = 16M
tmp_table_size = 32M
max_heap_table_size = 32M
thread_cache_size = 4

MariaDB を再起動します。