futuremix

昨日、MySQL のクエリキャッシュを有効にして WordPress を高速化するという記事を書きましたが、MySQL のチューニング策としてもうひとつメモリバッファのサイズを変更してみるのも有効です。この手法は MySQL で使用しているストレージエンジンが InnoDB の場合に有効です。また以下の方法は管理者権限が必要です。

MySQL では頻繁に使われるデータやインデックスをメモリにキャッシュして効率化・高速化を図りますが、CentOS 5.4 の MySQL ではデフォルトの設定は少なめです。メモリにある程度余裕があれば、この値を増やすことでパフォーマンスが改善される可能性があります。

MySQL のバッファのサイズは SHOW VARIABLES で表示されます。大量に表示されるので対象を絞るために LIKE を使用します。

# mysql (databasename) -u root
mysql > SHOW VARIABLES LIKE "innodb_%_size";
+---------------------------------+---------+
| Variable_name                   | Value   |
+---------------------------------+---------+
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size         | 8388608 |
| innodb_log_buffer_size          | 1048576 |
| innodb_log_file_size            | 5242880 |
+---------------------------------+---------+
4 rows in set (0.00 sec)

デフォルトでは innodb_buffer_pool_size は 8M しか割り当てられていません。WordPress においてMySQL がボトルネックになっていると感じる場合はこの値を増やして様子を見ます。変更は /etc/my.cnf ファイルで行ないます。

#/etc/my.cnf
#Change Buffer Pool Size //以下の2行を追加
innodb_buffer_pool_size = 16M
innodb_log_file_size = 2M

なお DSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！ によると innodb_buffer_pool_sizeを大きくしたら、innodb_log_file_sizeもあわせて調整しないとパフォーマンスが向上しません。 とあります。このため innodb_log_file_size も合わせて変更しています。

mysqld を再起動すると設定が反映されますが、ログファイル (ib_logfile0 と ib_logfile1)を消してから再起動しないと変更が反映されず、mysqld がエラーで起動できなくなることがあります。（コメントでご指摘頂きました。）CentOS で安全にログファイルを変更するための手順は以下のようになります。

# service mysqld restart
//再起動後、ログが適用されたら書き込みがないうちにすぐに停止
# service mysqld stop
//my.cnf でログファイルサイズの書き換え
# vi /etc/my.cnf
//ログファイルの移動（念のため削除ではなくバックアップ）
# mv /var/lib/mysql/ib_logfile* /(backupdir)/
// mysqld 起動
# service mysqld start
// ログファイルが指定したサイズで再作成されたことを確認
# ls -l /var/lib/mysql/ib_logfile* 

MySQL が起動したら接続してパラメータを確認します。

# mysql (databasename) -u root
mysql > SHOW VARIABLES LIKE "innodb_%_size";
+---------------------------------+----------+
| Variable_name                   | Value    |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 1048576  |
| innodb_buffer_pool_size         | 16777216 |
| innodb_log_buffer_size          | 1048576  |
| innodb_log_file_size            | 2097152  |
+---------------------------------+----------+
4 rows in set (0.00 sec)

データベース専用のサーバでしたら、この値をかなり大きく取るのが普通ですが、Apache などと共用で使っているサーバでは Apache のメモリを圧迫しないように配分する必要がありますね。スワップが発生するとかえって遅くなりますからほどほどに。