2010-07-04レプリケーション比較 PostgreSQL vs. MySQL 
PostgreSQL は 9.0 でやっとレプリケーションが本体に取り込まれましたが、長年レプリケーションをサポートしてきた MySQL と比べてどうなのかなと興味が沸き、調べてみました。どちらもマニュアルを調べただけなので、実際の使用感には差があるかもしれません。間違いがあればツッコミしてください。
表にしてみた印象としては、PostgreSQL のレプリケーションは若いわりには意外に健闘しているかなと感じました。また、両者を比べて興味深いのは、同期ズレする可能性のある操作への両者の意識の違いでしょうか。PostgreSQL は基本的に全面禁止にしているのに対し、MySQL は自己責任の範囲で高度な使い方をしても良い、というスタンスのようです。
| 機能 | PostgreSQL 9.0 | MySQL 5.1 |
|---|---|---|
| 参照分散 | ○ ロードバランスは手動/pgpool | ○ ロードバランスは手動 |
| 更新分散 | × 一時テーブルもダメ。外部ソートは可。 | △ *1 |
| スレーブ更新 | × 常に禁止 | △ *2 |
| カスケード構成 | × WALレシーバの自作は一応可能 | ○ log_slave_updates |
| ノード識別 | 自動 (制御ファイル) | server-id (設定パラメータ) |
| 複製許可 | pg_hba.conf | SQL: GRANT REPLICATION SLAVE |
| 複製方式 | WAL (物理ログ) | バイナリログ (論理ログ) |
| 複製範囲 | DBすべてのみ | DBすべて/選択 (--binlog/replicate-ignore-xxx) |
| 初回コピー | オンライン・バックアップ | mysqldump, コールド・バックアップ |
| 開始位置特定 | 自動 (制御ファイル) | 手動 (CHANGE MASTER TO MASTER_LOG_POS) |
| 複製開始 | recovery.conf + pg_ctl start | SQL: START SLAVE |
| → バックアップの利用 | ○ アーカイブWALの限り | ○ バイナリログの限り |
| 複製一時停止 | △ サーバを止めて設定変更 | ○ START/STOP SLAVE @スレーブ |
| フェイルオーバー | 手動 (シェル: trigger_file) | 手動 (SQL: STOP SLAVE) |
| 状態取得 @スレーブ | SQL: pg_last_xlog_xxx_location() WAL位置なので遅れ時間は計算が必要。 | SQL: SHOW SLAVE STATUS 遅れ時間の情報を含む。 |
| 状態取得 @マスター | シェル: ps | SQL: SHOW PROCESSLIST |
| リプレイ競合 | クエリ側を中止 (max_standby_xxx_delay) | リプレイ側を中止 (slave_transaction_retries) |
| マスタのクラッシュ | ○ 耐性あり *3 | × 耐性なし (データがずれる) |
| 異なるバージョン | × 同一メジャーバージョンのみ | ○ 例: 5.0→5.1 |
- 初回コピー時の制約の少なさは、PostgreSQL が勝る。オンライン・バックアップのため参照/更新は止まらない。一方 MySQL では FLUSH TABLES WITH READ LOCK により更新を停止する必要があるもよう。ただし、両者共に、アーカイブWALやバイナリログが残っている限りは、昔のバックアップから始めて追いつけるので、日々のバックアップをきちんと設計できていれば問題にはならないかも。
- 構成の柔軟さは、MySQL が勝る。バイナリログやレプリケーション時に複製するDBやテーブルを選択できる上、カスケード構成も可能。カスケードせず、システム全体の複製ならば差はない。
- スレーブでの状態取得は、MySQL のほうが親切。というか、PostgreSQL がサボり過ぎ。一方、マスターでの状態取得は、どちらもプロセス一覧レベルであり、意外と弱い。
- データの整合性は、PostgreSQL が勝る。マスタのクラッシュにも耐性があり、物理複製なのでデータずれはまず生じない。MySQL は Row-based Replication (RBR) なら SBR よりは問題が少なそうだが、それでも pgpool でのレプリケーションと似たような問題に悩まされそう。
- スレーブにて、リプレイ処理と参照処理が競合するのは避けられないのだが、PostgreSQL ではクエリ側を中止するのに対し、MySQL では (指定回数のリトライ後) リプレイ側を中止するのは大きな思想の違いか。基本的にリプレイは最終的には必ず成功すべきであることを考えると、PostgreSQL のほうが便利と思われる (MySQL ではリトライ回数を∞に設定する一択にしかならないのでは?)。
- と、いつの間にか max_standby_delay が max_standby_archive_delay と max_standby_streaming_delay に分割されていた。両者ともにWALレコードを受け取った時刻から許される経過時間を指定するようだが、そのWALの入手ソースがアーカイブWAL or ストリーミングかで異なる? イマイチ使い分け方が分からないのだが……。
- 全般的に、PostgreSQL のほうが手順ミスは少なそう。必要な設定が少なく、不具合を起こす可能性のある操作は最初から禁止されるため。MySQL は柔軟で高度な応用は利くが、正しく動作させられるかは自己責任の扱い。
*1：マルチマスタ構成も取れるらしいが自己責任。使うにしても「たすきがけ型」になりそう。
*2：read_only しなければ更新できるが競合の解消は自己責任。スレーブだけが持つテーブルや一時テーブルを使うぶんには問題ない。
*3：まぁ、PostgreSQLもつい最近バグが修正されたんですが。
初回コピー時ですが、これは特に更新を停止せずに行うことが可能です。（ただしInnoDB利用時に限りますが、InnoDBがほぼ標準なので問題ないと思います。）まず第一の方法は、ZFSやLVMなどのスナップショットを利用する方法です。スナップショットを用いて起動するとクラッシュリカバリが走りますが、起動後のデータはある時点のマスターと同じになりますので、レプリケーションを開始することが可能です。開始位置はエラーログに表示されます。FLUSH TABLES WITH READ LOCKについては、このコマンドを実行してGlobal Read Lock取得後、トランザクションを開始すればすぐに解放することが可能ですので、FLUSH TABLES WITH READ LOCKコマンドの実行を更新停止と呼ぶかどうかは微妙だと思います。トランザクションが開始されると、（デフォルトの分離レベルであるREPEATABLE READから変更していなければ）トランザクション終了まで一貫性のある参照が可能です。ちなみに、mysqldumpコマンドに--master-data=2 --single-transactionというオプションを指定すると、FLUSH TABLES WITH READ LOCK実行時にレプリケーション開始位置を記録し、すぐにトランザクション開始＆ロックの解放を行うという挙動になります。あと、商用のバックアップツール（MySQL Enterprise Backup）を利用すると、MySQLサーバーへログインすることなくオンラインバックアップが取得可能です。（サードパーティ製ののXtraBackupというのもあります。）
データの整合性については、--sync-bnlog=1を指定すれば（バイナリログがトランザクションと同期するため）問題ありません。マスターがクラッシュしても更新が失われることはありません。もちろんディスクへの書き込みを行うことになるので、更新性能とトレードオフになりますので、このモードを使わずスレーブを昇格させるという運用の方がポピュラーです。これに加えて最新開発版のMySQL 5.5では、準同期レプリケーションという機能が追加されており、マスターがクラッシュしてもスレーブには更新が全て残った状態になるため、即座にフェイルオーバーさせることが可能です。PostgreSQLでデータずれが生じないかどうかは、これから実績を重ねた上で判断するべきでしょう。
スレーブにて、リプレイ処理と参照処理が競合するのは避けられないとありますが、MySQL（InnoDB）では参照と更新は競合しません。（MVCCのため）従って、スレーブで更新処理を行ったり、不要なロックをかけたりしない限り、リプレイがブロックされることはあり得ないのです。（スレーブ上で参照処理が走っている間もガンガン更新しますが、MVCCなので双方ブロックしないんです。）
参照処理のロードバランスについては、JDBCドライバを利用すれば自動でロードバランスさせることも可能です。（MySQL Proxyというソフトウェアを使ってもロードバランス可能です。）逆に更新の分散はできません。マルチマスターにしても、最終的にマスターは全ての更新を（他のマスターで行われた更新を含めて）行わなければならないからです。（SPIDERストレージエンジンを利用すれば更新の分散は可能です。）
なお、手前味噌ですみませんが、MySQLのレプリケーションについて紹介したスライドがあるのでご興味があればご覧ください。
http://www.slideshare.net/nippondanji/art-of-mysql-replication-4824469
※ダウンロード可能です。
そのほか個別に：
- FLUSH TABLES WITH READ LOCK は一瞬では終わらないでしょうから、問題になるケースもあるのではないでしょうか? PGのオンライン・バックアップは、一瞬たりともロックを必要としません。
- データの整合性については、--sync-bnlog=1 すればPostgreSQLと同様ですね。準同期レプリケーションは、PGでも次期9.1の開発課題になっています。
- リプレイ処理との競合の論点は MVCC ではなく、物理削除との競合です。ただ、MySQLは論理複製なので、PGで問題になっている物理削除との競合は生じにくいかもしれません。PGでもMVCCなので更新処理は問題なく、VACUUMやHOT相当の物理削除の影響のみがあります。
ちなみに、MySQLに近いのは、9.0のレプリケーションよりも、むしろ Slony-I だったりします。
> FLUSH TABLES WITH READ LOCK は一瞬では終わらないでしょうから、問題になるケースもあるのではないでしょうか? PGのオンライン・バックアップは、一瞬たりともロックを必要としません。
そういうニーズがある場合、MySQLではファイルシステムやストレージ側のスナップショットを使うことが多いですね。MySQL Enterprise Backup（旧名InnoDB Hot Backup）などを利用してもロック不要のオンラインバックアップが可能です。
結構、MySQLではオンラインバックアップ出来ないということをおっしゃる方が多いのですが、実際出来ますから、あんまり「出来ない出来ない」とは言わないで欲しいです。
> データの整合性については、--sync-bnlog=1 すればPostgreSQLと同様ですね。準同期レプリケーションは、PGでも次期9.1の開発課題になっています。
準同期レプリケーションには嬉しい副作用があって、スレーブ側へ更新が転送されていることが保証されるという前提で、マスター側でログの同期書き込みをOFFにするという運用が可能になるんです。その場合、更新性能が格段に向上します。（詳細は上に挙げたスライドにて。）
> ただ、MySQLは論理複製なので、PGで問題になっている物理削除との競合は生じにくいかもしれません。
構造上、そういう問題には遭遇しないです。MySQLのリプレイ処理は通常のトランザクションと同じように実行されますから、更新時におけるロックの扱いも通常のトランザクションと同じです。でもって、VACUUMやHOTというものがありませんので、更新系のトランザクションがバックグラウンド処理と競合するということは起きないんです。
> ちなみに、MySQLに近いのは、9.0のレプリケーションよりも、むしろ Slony-I だったりします。
Slony-Iとはちょっと違うような気がします。トリガを使ったりしませんし、ラージオブジェクトやDDLもそのまま伝搬出来ますから。MySQLは元々「SQL文をスレーブへ転送する」という方式ですから。ログをストリーミングし続けるという構造からすると、MySQLのRBRが9.0のレプリケーションに近い印象を持っています。最終的には別物なので、比べるのはナンセンスなんですけどね ;)
- statement-based replication ⇔ pgpool
- row-based replication ⇔ Slony-I
- RDBD 構成 ⇔ PG 9.0 Replication
> - statement-based replication ⇔ pgpool
この対応は適切だと思えません。pgpoolはSQLを中継するミドルウェアであってサーバーの機能ではないからです。MySQLのSBRはサーバー側のレプリケーション機能ですから、カスケードや1:N構成によるスケールアウトなど、サーバー自身の設定でトポロジを様々に構成することができます。スレーブを増やしても更新するべきサーバーはマスターだけですので、更新性能が劣化することはありません。
おそらくPostgreSQL側から見るとMySQLのSBRに相当するのはpgpoolだということになるのでしょうが、この対応には無理がありますので、該当無しとしたほうが良いように思えます。逆に、MySQL側から見てpgpoolに該当する機能を挙げるならば、MySQL ProxyというSQLを中継するミドルウェアになるのではないかと思います。
http://forge.mysql.com/wiki/MySQL_Proxy
ただ、MySQL ProxyはまだGA版ではないので機能は限定的であり、pgpoolから見るとずいぶんと役不足に映るかも知れません。なので、MySQL Proxyも現時点ではpgpoolの該当品だと考えるべきではないと思っています。
> - row-based replication ⇔ Slony-I
前回のコメントにも書かせて頂いたように、対応として挙げるのは違和感があります。確かに行データが非同期で連続的に転送されるというのは同じですが、ラージオブジェクトやDDLを転送出来ないというのは、運用が決定的に異なる要因になるからです。また、MySQLのレプリケーションは通信経路をSSLで保護したり、圧縮して帯域を節約することが可能である点も見逃せません。
以下のページの説明では、「スレーブ側のPostgreSQLの更新が終了するまで、 数100ミリ秒かそれ以上の時間的遅れが生じます。」とありますが、MySQLのレプリケーションはそんなにかかりません。（準同期が出来るぐらいですから。）
http://www.interdb.jp/techinfo/web+db48/slony.html
> - RDBD 構成 ⇔ PG 9.0 Replication
DRBDですよね？ ;)
DRBDはディスクそのものを同期するソリューションなので、スタンバイ側ではファイルシステムのマウントすら出来ません。フェイルオーバーが発生すると、ボリュームをPRIMARYにして、ファイルシステムをマウントし、サーバープロセスを起動し、クラッシュリカバリを行ってはじめて利用可能になります。DRBDを使ったときのフェイルオーバーの挙動は、共有ディスク型のHAクラスタと同じなんです。ちょっとレプリケーションとは違いますよね。また、DRBDはLinuxの機能なので、PostgreSQLでも利用可能です。
MySQLにはPostgreSQL 9.0のレプリケーションに対する該当品はありませんが、サードパーティ製のPBXTというストレージエンジンが、「エンジンレベルレプリケーション」という機能を実装しようとしているらしく、それが該当品になるのではないかと思います。PBXTは追記型アーキテクチャですし、PostgreSQLに通じるものがあるかも知れませんね。（注意：ELRが使えるのはバージョン2.0からのようですが、まだアルファ版です。期待して見守っています。）
http://pbxt.blogspot.com/2010/03/pbxt-engine-level-replication-works.html
とはいえ、運用面や性能面で比較する場合には、RBR ⇔ 9.0Rep の比較が妥当でしょうね。このトピックも最初はそのつもりでしたが、いつの間にか深い方向へ向かってしまいました ^^;
落ち着いたところで質問。
> MySQL は Row-based Replication (RBR) なら SBR よりは問題が少なそうだが、
> それでもpgpoolでのレプリケーションと似たような問題に悩まされそう。
pgpoolの問題って具体的には何を指しているでしょう？
あと、オンラインバックアップとSlonyについてコメントしてよいですか。
#他人へのコメントなので、一応お断りを。
コメントは歓迎です :-) 長くなるようならトラックバックしてもらっても。
"http://nippondanji.blogspot.com/2009/03/mysql10.html"の3.にMyISAMの大量データ更新問題がありますが、マスタ-スレーブ間の永続的なデータ不一致ではないですね。
デッドロック云々は(私の乏しい経験では)見聞したことがないです。現状、MySQLは大多数のユーザのニーズによく応えていると思います。SRでやっと同じ土俵にのった感じです。
>>オンラインバックアップ
マルチストレージエンジンが特徴の一つなのでMyISAMスルーはおかしい。MyISAMはロック必須でバックアップ中更新不可となるので、"オンライン"に違和感。また、本題は"RDBMSの機能比較"なのに"スナップショット云々"は議論のすり替えのようで強い違和感。全般に"できる/できない"の2元論は非生産的。長短両面正しく伝えるのがエンジニアの務めかと。
>>Slony-Iの遅延時間
Slony-Iは、slonyサーバが一定周期でマスタをポーリングし、変更があったらその変更分をスレーブに送信します。ここでの遅延は"ポーリング間隔＋変更データの転送時間＋スレーブでの更新時間"を指します。ポーリング間隔は任意に設定可能なので"数100ミリ秒以上"は参考値です。"準同期云々"はスレーブ側の変更時間が含まれないので比較対象になりません。
はコミットした順にREDOすれば大丈夫なのかな? 私の勘違いだったかもしれません……が、pgpoolのデータ不整合エラーには何度か泣かされたからなぁ……。
>スレーブは結果を1(SQL)スレッドで反映
PGでもREDOプロセスが1つなので似た問題はありますね。マスタでは複数プロセス/スレッドで更新できるのに、スレーブではシングルなので追いつけない場合も? PGのWAL方式はSQLの流しなおしよりは低コストなのが救いですが。
実は一年弱前にpgpoolのモデル検査をやってました：
http://d.hatena.ne.jp/interdb/20100815/1281809853
SR やXCなど立て続けに出てきたので、pgpoolはいつの間にか忘れていたんですが、さきほど机の下から出してきました。
簡単なモデル検証で strictモードでのデッドロック検出やv2.2のSERIALIZABLEでの不一致を検出しました。
このモデルではもう*バグはないことになってます*が、モデルの精密化に役立てたいので、どんな状況で不整合エラーが起きたのか、わかれば教えてください。
>>スレーブではシングルなので追いつけない場合も?
スレーブ側を詳しくみると、ログはI/Oスレッドで受信されて一旦、リレーログとして保存され、そのリレーログをSQLスレッドが読んで実際に更新を行います。つまりリレーログがバッファの役割を果たしています。なので、追いつけない＝ログを取りこぼす可能性は皆無といっていいと思います。v4.0以降の由緒ある機構です。
また、RBR/RBLはストレージエンジンにつっこむ直前のデータ (行データそのもの)で、パースやプランニングなどが不要なので、データの反映コストはかなり低いです。
# SQLスレッドという名称はSBLしかなかったv4.0からの名残です。
マルチストレージエンジンのためのプラグイン化はすごく進化していて、RBL/RBRとほんとにうまくかみ合ったように見えますし、Myエンジンをつくりたくなるような面白さです。