MySQL on WindowsのInnodb書き込み性能について
WindowsのInnodbでINSERTがめちゃ遅い。
ベンチマークしてたら異常に遅いことに気づいた。 どれくらい遅いかというと、
Linux: 1613 transaction / sec Windows: 30 transaction / sec
という具合。 ちなみにハードはHP Proliant DL360 Xeon 3.4GHz 2Gbyte RAM Ultra320 SCSI 12000rpm 80Gbyte。 スキーマは(c1 INT PRIMARY KEY, c2 TEXT)という単純なもの。クエリは’INSERT INTO tbl (c2) VALUES (’I drink coffee every morning’)をひたすら。
同じWindowsでもMyISAMと比較するとこれまた以下のように桁違い。
MyISAM: 4676 transaciton/ sec Innodb: 30 transaction / sec
なにやらWindowsでは異常にdisk I/O同期処理が重たい模様。
上記の結果は以下のパラメータで実施していました。
sync_binlog = 0 innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit が「１」だと、トランザクションコミット毎に物理ディクスに対するログのフラッシュが行われます。 原因はこれの模様。
innodb_flush_log_at_trx_commit = 0
としてやると、たちまち改善。
Innodb: 4065 transaction / sec
となりました。
ちなみにinnodb_flush_log_at_trx_commit = 0 としていても sysc_binlog = 1としてしまうとこれまたトランザクションコミット毎にバイナリログのdiskへのフラッシュが起きてしまうので 30 transaction /sec くらいになってしまいます。
MySQLのマニュアル通りですが、innodb_flush_log_at_trx_commitの各値での動作は以下の通り。
0 : 1秒毎にlog bufferからログファイルへの書き込み処理を行い、さらにOSのファイルシステムキャッシュから実際の物理diskへフラッシュが行われます。
1 : 毎トランザクションコミット毎にlog bufferからログファイルへの書き込み処理を行い、さらにOSのファイルシステムキャッシュから実際の物理diskへフラッシュが行われます。
2 : 1秒毎にlog bufferからログファイルへの書き込み処理を行い、さらにOSのファイルシステムキャッシュから実際の物理diskへフラッシュが行われます。 かつ、毎トランザクションコミット毎にlog bufferからログファイルへの書き込み処理を行います。 OSのファイルシステムキャッシュから実際の物理diskへフラッシュは行われません（OSに任されます）。
＊なお、イイdisk装置を搭載している場合は物理diskへフラッシュしたと思った後でも実際にはdisk cacheに乗っかって最終的なdiskへのフラッシュは行われないことがあります。