binlogとredo logの違いについて以下の記事をご参考ください。
redo logとbinlogの違い
| redo log | binlog | |
|---|---|---|
| 利用シーン | 1. crash-safe/crash recovery | 1. replication(master instanceからstand-by or read-only instanceへのデータ同期) 2. バックアップからデータ復元：point-in-time recovery |
| 物理/論理 | 物理ログ | 論理ログ |
| 記録内容 | データベースの変更の物理的な記録(どのデータページが変更されたか,ページ内の変更が行われた位置,更新前のレコードの値,更新後のレコードの値など) | 1. Statement-Based Logging(SBR）:更新系(insert,update,delete,createなど読み取り以外)クエリ文そのものが記録される 2. Row-Based Logging(RBR): 変更前の行の内容と変更後の行の内容そのものが記録される 3. Mixed-Based Logging(MBR）SBRとRBRを組み合わせた形式で、デフォルトではSBRを使用し、必要に応じてRBRに切り替える |
| 実現のlayer | 1. innodb storage engineのログ 2. InnoDB以外のストレージエンジンはそもそもredo logがありません |
1. SQL layer(サーバー層)のログ 2. どんなストレージエンジンを使ったとしてもかならずbinlogログを出力 |
| 記録方式 | 1. ファイルの個数とサイズが固定 2. ラウンドロビン(ループ)方式で最後のファイルが一杯になったら、最初のファイルに戻って再利用(上書き) |
1. ファイルのmax size制限がありますが、ファイルの個数に制限がありません。 2. binlogの合計のサイズはある程度超えると古いファイルが自動的に削除される。 3. 上書きはしません |
| diskに書き込むタイミング | transaction開始する時 | transaction commitする時 |
MySQLのbinlog(論理ログ)の弊害
そもそも初期のMySQLにはInnoDBストレージエンジンがなくて、デフォルトのストレージエンジンはMyISAMでした。
MyISAMにはクラッシュセーフ機能がなく、binlogのログはアーカイブにしか使えません。
InnoDBはInnobase社(2005年10月にInnobase社がオラクルに買収)の製品であり、プラグインとしてMySQLに導入したもので、binlogだけではクラッシュセーフにならないため、InnoDBで別のログシステムであるredo logを使ってクラッシュセーフの機能を無理矢理に実現しました。
トランザクションのACID特徴中のC(一貫性)を保つためにredo logとbinlogを２フェースコミットの形で実現しています。
上図の最後の三つのステップでredo logとbinlogを２フェースコミットの形で更新します。これは、redo logとbinlogの一致性を保つための仕組みです。
MySQLが再起動する時に、redo logファイルをスキャンして、コミット状態であれば、redo logに記述されたレコードの更新内容をコミットします。prepared状態であれば、まずbinlogの状態をチェックします。該当binlogが完了していない場合は（上図①のタイミングでクラッシュ）、redo logに記述されたレコードの更新内容をロールバックします。該当binlogが完了している場合は（上図②のタイミングでクラッシュ）、更新内容をコミットします。
２フェースコミットの詳細について以下の記事をご参考ください。
- 物理ログは論理ログに比べて、信頼性、安定性、性能の面で優れているため、そもそも、商用データベース(OracleやSQL Serverなど)では論理ログを使っていません。
- トランザクション処理する時に使われているredo logとbinlogの２フェースコミット仕組みは、安定性と性能面について大きな制約と課題があり、従来の商用データベースに比べて処理性能に大きなギャップがあります。
- MySQLの同一トランザクションの論理ログは連続的に書き出す必要があるため、より大きなトランザクションには対応できず、実行は失敗しがちです。
一方、物理ログはトランザクションのログをセグメント（トランザクション開始、操作1、操作2、トランザクションコミット）で書き込むことができるため、大規模なトランザクションも対応できる。 - MySQLの論理ログは、レコード全体の前後のイメージを保持するため、書き込みが大量に発生し、I/O負荷が増大し、パフォーマンスが低下しがちです。
一方物理ログは変更カラムのみをコンパクトに記録し、ログ総量を減らすことで、IO性能が向上し、性能低下が発生しにくく、高い性能と安定性が実現できる。 - stand-by instance,read-only instanceへのレプリケーションはbinlog(論理ログ)が使われては、binlogは再びSQL層を通過しクエリを実行しなおす必要があり、実行パスが長く、ネットワークの速度に左右されやすく、レイテンシーが発生しがちです。
一方物理ログは、完全なトランザクション情報を含むため、簡単に並列再生できて、もしレプリケーションに利用できれば、遅延はミリ秒レベルまで大幅に短縮できる。 - MySQLの論理ログは、トランザクション情報が含まれていなくて、任意の時点から復元できるため、認識不足や操作ミスによる想定外の問題は発生しがちです。
一方物理ログは、完全なトランザクション情報は含まれていて、連続性的に検出し自動的に最後の中断pointを識別します。人間の判断を削減し、ヒューマンエラーが防げます。
PolarDBでbinlogとredo logの利用状況
前提
- そもそもPolarDBではbinlogがデフォルトとして無効になっています。
- つまりredo logとbinlogの２フェースコミットという仕組みもそもそもありません。
- binlogがoffになっていることを確認する方法：
Parametersでloose_polar_log_binを検索
- RDSはデフォルトとしてbinlogが有効であり、トランザクション処理する時に、２フェースコミットがあるので、PolarDBのトランザクション処理はRDS MySQLよりは早いです。
master/slave間のデータレプリケーションはどうやって実現しているのか？
- polardbは物理ログ(redo log)を使ってレプリケーションを実現しています。
point-in-time recoveryはどう実現しているのか？
- ログのバックアップでもバックアップされているのはbinlogではなく、redo logです。
- そしてpoint-in-time recoveryを行う時にはredo logを利用しています。
binlogを手動で有効にする方法
デフォルトでbinlogが無効になっていますが、手動で有効にすることも可能です。
Binlogを有効にすると、ElasticSearch、AnalyticDBなどのデータ製品に接続でき、PolarDBからRDS、、PolarDB間のリアルタイムデータ同期ができるようになります。
Binlogをオンにすると、SELECTクエリーのパフォーマンスには影響しませんが、書き込み更新（INSERT、UPDATE、DELETE）のパフォーマンスに影響します。 一般に、読み取り/書き込みのバランスの取れたデータベースでは、Binlogをオンにした場合は性能の低下は10%以内です。