MySQLの更新系処理のパフォーマンスを、innodb_flush_log_at_trx_commitのチューニングで上げる

経緯

Master（更新系のMySQL Database）が非常に高負荷となっている。 slowquerylogsに、下記のような「commitがボトルネックになっている」旨の出力が大量にでる

# User@Host: apps[apps] @ ip-10-163-30-24.ap-northeast-1.compute.internal [10.163.30.24]
# Query_time: 9.523171  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1349786490;
COMMIT;
# User@Host: apps[apps] @ ip-10-132-8-20.ap-northeast-1.compute.internal [10.132.8.20]
# Query_time: 4.698476  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1349786490;
COMMIT;
# User@Host: apps[apps] @ ip-10-152-185-146.ap-northeast-1.compute.internal [10.152.185.146]
# Query_time: 5.295836  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1349786490;
COMMIT;
# User@Host: apps[apps] @ ip-10-152-187-7.ap-northeast-1.compute.internal [10.152.187.7]
# Query_time: 6.456277  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1349786490;
COMMIT;
# User@Host: apps[apps] @ ip-10-132-8-20.ap-northeast-1.compute.internal [10.132.8.20]
# Query_time: 4.313587  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1349786490;
COMMIT;

ちなみに、MySQLのバージョンは5.5

解決策

リスクを認識した上で、innodb_flush_log_at_trx_commitの値を0に設定する

innodb_flush_log_at_trx_commitは、ログ バッファのDiskへの書き込みタイミングを制御するパラメータ

方法

オンラインで動的に変更する場合は、

mysql> set global innodb_flush_log_at_trx_commit = 0

my.cnf等で、恒久的に変更する場合

vim /etc/my.cnf

# 下記一行を追記
innodb_flush_log_at_trx_commit = 0

なお、設定のパラメータマトリックスは下記

0に設定すると、最大で1秒間分のデータを紛失する可能性がある。