MySQL のキャッシュサイズ変更で高速化

2010-4-21 14:38
このエントリーをはてなブックマークに追加

昨日、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 のメモリを圧迫しないように配分する必要がありますね。スワップが発生するとかえって遅くなりますからほどほどに。

2件のコメント

  1. innodb_log_file_sizeを変更したら、ログファイルを再作成する必要があります。(そうしないとInnoDBが起動しません。)ただし、手順には注意が必要で、ログファイルの中にテーブルスペースに反映されていないデータが残っていると、ログファイルを削除することでデータロストしてしまいます。安全にログファイルを削除するには、次のように作業するといいでしょう。

    ・MySQLをシャットダウンする
    ・MySQLを再起動する <=== ここでログの適用が行われます。
    ・更新を一切しないうちにMySQLをシャットダウンする
    ・ログファイルを削除する&my.cnf内のinnodb_log_file_sizeを書き換える
    ・MySQLを再起動する

    Comment by nippondanji2010-04-22 09:55

  2. ご指摘ありがとうございます。本文を修正&追記しました。
    単にログファイルを削除しただけではデータロストの可能性があるのですね。勉強になりました。

    Comment by 津田 ふみか2010-04-22 11:40

現在、コメントフォームは閉鎖中です。

67 queries. HTML convert time: 1.157 sec. Powered by WordPress. Valid XHTML
Copyright © 2003-2017 @ futuremix.org ログイン