Archive for the ‘InnoDB’ tag
InnoDBのリカバリ機能を検証してみる
innodbはクラッシュ時のリカバリ機能を実装しており、mysqldがクラッシュした場合やOS自体がクラッシュした場合にもしコミットされたデータがテーブルから失われていても再起動時にib_logfileからコミットされたトランザクションを読み出し、それが実データ（デフォルトだとibdata）に反映されていなければ反映させるという処理が走ります。MySQLはinnodb_flush_log_at_trx_commitの値により、1秒毎、またはコミット毎にトランザクションをログファイルにフラッシュします。
今回は以下２点について検証を行いました。
１．クライアント側から発行したクエリがクラッシュ後にどこまで復旧されているか
２．リカバリにかかる時間の測定（ログのファイルサイズとの因果関係にも言及）
１の検証についてはinnodb_flush_log_at_trx_commitの設定を0と1に変えて結果をみてみました。
0の場合、コミットされたトランザクションは１秒毎にログファイルにフラッシュされ、コミット毎にはフラッシュされません。 つまり最大で１秒間の間にコミットされたトランザクションはクラッシュ時には失われる可能性があります。 検証はクライアントから 4000 transaction/sec程のクエリを発行しながら途中でブチっと電源を落とすという手順で行いました。 １秒間のトランザクションが失われる可能性があるということなので、クラッシュ後にデータベースに格納されているレコード数はクライアントがコミットしたトランザクション数と最大で4000レコード程差が出る可能性があると予想されます。 結果は以下の通りです。
クライアントがコミットしたトランザクション数：2405940
データベース上のレコード数： 2402151
おぉ。予想通り4000弱のデータが失われています。 いい感じです。
次にinnodb_flush_log_at_trx_commitを1にしたときの結果です。1の場合はコミットされたクエリは都度ログファイルにフラッシュされるので、失われるデータは理論上０となるはずです。ちなみに前回のポストでも言及しましたがinnodb_flush_log_at_trx_commitを1にするとWindowsでは極端にスループットが低下（30 transaction/sec）します。 今回はとりあえずこのWindowsでテストしてます。 結果は以下の通りです。
クライアントがコミットしたトランザクション数：8227
データベース上のレコード数： 8227
おぉぉ。 予想通りデータは完全に整合性がとれています。 やはりクリティカルなサービスを運用している場合はデータがなくなっちゃうかもしれないのでinnodb_flush_log_at_trx_commitを1にしておかないとダメ、ということがわかりました。 でもWIndowsだとイイDisk装置使わないとスループット全然でないからそこんとこは注意、ということですね。
次に２のリカバリ時間の検証ですが、１の検証はどちらも以下の設定で行いました。
innodb_log_file_size = 256M innodb_log_files_in_group = 5
当初リカバリ時間はログのファイルサイズ設定に比例するのではと予想していました。 innodbは設定したサイズのファイルを起動時に作成してしまうので、リカバリ時のログのスキャンに時間がかかるであろうというのが根拠です。 上記の例ではデフォルトよりかなり大きな値を設定しており、したがってリカバリにもある程度の時間を要するのではと思っていました。
しかし結果は同じログファイルサイズでもinnodb_flush_log_at_trx_commitの値を変えてテストしたところリカバリ時間にはある程度の差が出ており、さらにリカバリ時間が短い方（innodb_flush_log_at_trx_commit = 1 でのテスト時）ではわずか2秒で処理を終了しています。 一方リカバリ時間が長い方（innodb_flush_log_at_trx_commit = 0でのテスト時）では120秒を要しています。
ということは、、と思って次に以下の設定でテストしてみました。
要はログファイルはデフォルト（小さい）で、スループットは高くなる設定です。
innodb_log_file_size = 5M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 0
結果、まずリカバリの精度については以下のようになりました。
クライアントがコミットしたトランザクション数： 2185089
データベース上のレコード数： 2184239
およそ1000トランザクション弱が失われています。失われるトランザクションはクラッシュするタイミングによって0からそのときのtransaction/secの間でブレることが予想されますので妥当な結果です。(本テストでもスループットは4000transaction/sec程度） そしてこのときのリカバリ時間は50秒弱でした。 120秒かかっていたケースと比較して短縮されていますが、これはログファイルサイズ設定によるものではなく、クラッシュしたタイミングでたまたまリカバリ対象のデータが減ったためだと思います。
この結果から導き出せる結論としては、リカバリ時間は単にログファイルの容量設定に比例するのではなく、テーブルに反映されていないトランザクションがどれほどログに存在するかに影響されるということだと思われます。 恐らくリカバリ時のスキャンは、まずテーブルをスキャンして最後尾のレコードのシーケンスを取得し、ログファイルはそのシーケンスをもとにファイルの途中からスキャンしていくというようなイメージではないかと思います。
結局のところリカバリ時間は、
(ログに記録されたトランザクション数) ー (テーブルに反映されてないトランザクション数)
に比例するのではないかと思います。
＊上記の値を大きくするにはスループットをあげてクエリを投げつづけている状態でクラッシュさせることが必要になるので、その意味でinnodb_flush_log_at_trx_commit = 0としてテストしましたがLinux上等、innodb_flush_log_at_trx_commit = 1でも性能が出る環境であればこの設定でも問題なく、つまりリカバリ時間は最終的に失われてしまったトランザクション数に比例するのではなく、あくまで(ログに記録されたトランザクション数) ー (テーブルに反映されてないトランザクション数)に比例すると思われます。
今回は検証データやマニュアルを元にinndbの仕様を推察しましたが、今後ソースファイルを読み進めてそのあたりを明らかにしていこうと思います。
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へのフラッシュは行われないことがあります。