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 を利用してインストールします。

1# yum --enablerepo=epel install mysqltuner

MySQLTuner の実行

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

1# mysqltuner -buffers
2Please enter your MySQL administrative login: <ユーザー名>
3Please 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 を実行し、最後に表示される推奨事項を確認します。

1# mysqltuner -buffers
2
3.....
4
5-------- Recommendations -----------------------------------------------------
6General recommendations:
7    Run OPTIMIZE TABLE to defragment tables for better performance
8    Enable the slow query log to troubleshoot bad queries
9    When making adjustments, make tmp_table_size/max_heap_table_size equal
10    Reduce your SELECT DISTINCT queries which have no LIMIT clause
11    Set thread_cache_size to 4 as a starting value
12Variables to adjust:
13    query_cache_size (>= 8M)
14    tmp_table_size (> 16M)
15    max_heap_table_size (> 16M)
16    thread_cache_size (start at 4)

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

MariaDB の設定を変更する

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

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

server.cnf
1[mysql]
2slow_query_log = 1
3long_query_time = 1
4slow_query_log_file = /var/log/mariadb/slow_query.log
5
6query_cache_size = 16M
7tmp_table_size = 32M
8max_heap_table_size = 32M
9thread_cache_size = 4

MariaDB を再起動します。