オンラインリカバリを実装した
pgpool-II 新バージョンのご紹介
SRA OSS, Inc. 日本支社
技術部
浅羽 義之
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

1

pgpool-II 2.0概要
y 11/16 リリース http://pgfoundry.org/frs/download.php/1521/pgpool-II2.0.1.tar.gz

y 様々な改良を追加
 レプリケーション
y 信頼性向上、高速化、オンラインリカバリ

 パラレルクエリ
y 高速化

 全般
y 他のシステムとの連携
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

2

レプリケーション

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

3

レプリケーション高信頼化(1)y pgpool-II 1.xでの問題
 autocommit の場合、セカンダリに送信する前にテーブルロックや行ロックが解放されてしまう
y 非常に短い時間であるが、セカンダリのロック獲得を別のユーザが割り込める可能性がある
DELETE FROM tbl …;

1. DELETE FROM tbl…
結果が返ってきた時点でトランザクションがコミット済

pgpool2: DELETE FROM tbl…

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

PostgreSQL

PostgreSQL

4

レプリケーション高信頼化(2)y 解決策 内部的にトランザクションを開始させ、マスタを最後にコミットさせることで、セカンダリに先にロックを解放させる
y セカンダリのロック獲得の順番がずれないことを保証
DELETE FROM tbl …;

pgpool
1: BEGIN
3: DELETE FROM tbl…
6: COMMIT

2: BEGIN
4: DELETE FROM tbl…
5: COMMIT

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

PostgreSQL

PostgreSQL

5レプリケーション高信頼化(2)y pgpool-II 1.x までは、更新の成功・失敗のみでデータの整合性を判断 更新数が異なっているとデータの不整合に気付かない

y 更新数の確認(pgpool-II 2.0)
 INSERT,UPDATE,DELETEの件数が一致していない場合
はトランザクションをアボート 不一致が発生した場合にはオンラインリカバリで復旧可能
x=# update t set a = a + 1;
ERROR: pgpool detected difference of the number of update tuples
HINT: check data consistency between master and other db node
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

6

レプリケーション高速化(1)y pgpool-II 1.xのレプリケーション
 順番にクエリを実行(replicate_strict = true)y 応答が返ってきたら次のノードへクエリを送信する仕組み
y 応答が返ってくる前にクエリを送信するとデッドロックする可能性がある ノード数が増えるだけ時間がかかる問題がある
INSERTpgpool
1. INSERT

3. INSERT
2. INSERT

PostgreSQL

PostgreSQL

PostgreSQL

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

7

レプリケーション高速化(2)y pgpool-II 2.0のレプリケーション
 マスタとそれ以外で分類
 マスタのクエリ完了後にその他のノードに並列にクエリを送信
INSERT
pgpool
1. INSERT

2. INSERT
2. INSERT

PostgreSQL

PostgreSQL

PostgreSQL

ノード数が増えてもPostgreSQL単体のCopyright © 2007 SRA OSS, Inc. Japan All rights reserved.
約2倍でレプリケーションが完了

8

レプリケーションのベンチマーク
y pgpool-II,PostgreSQL1台,Slony-Iで比較
y データ件数: 10万件，fill factor 50(pgbench -i-s 1 -F 50 で初期化)
y ノード数は4，レプリケーションモード，負荷分散あり
y pgbenchパラメータ
 pgbench -s 1 -c 80 -t 1000 -f ファイル スクリプトファイルは，s00w10-s10w00を順に実行¥set naccounts 100000 * :scale¥setrandom aid 1 :naccounts
SELECT abalance FROM accounts WHERE aid = :aid;¥setrandom delta -5000 5000
¥setrandom aid 1Copyright:naccounts© 2007 SRA OSS, Inc. Japan All rights reserved.
9UPDATE accounts SET abalance = abalance + :delta WHERE aid = :aid;

pgbench によるベンチマーク結果

検索比率が60%越えるとpgpool-II が速い。

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

10

pgbench(-C オプション付き)のベンチマーク結果

コネクションプーリングの効果もあり、
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.
常にpgpool-IIが速い。

11

オンラインリカバリとは？
y pgpoolを停止させずにデータを同期させてノードを復帰させる機能
pgpool-II 1.xはすべてを停止させてデータを同期×

pgpool-II 2.0は停止させずにデータを同期

pgpoolpgpool×

PostgreSQL

×PostgreSQL

PostgreSQL

PostgreSQL

データ同期 Copyright © 2007 SRA OSS, Inc. Japan All rights reserved. データ同期

12オンラインリカバリの仕組み
y 2段階に分けてデータを同期
 1段階をファーストステージと呼ぶ
y 並行して DB の参照・更新が可能

 2段階をセカンドステージと呼ぶ
y すべての接続が終了するまで待機
y 接続リクエストをすべてブロック

 最後にPostgreSQLを起動
y PostgreSQLが接続を受け付ける状態になった時点でブロックを解除

y リカバリ方法
 ユーザ自身でどのようにリカバリするかを決めることができる
 pgpool-IIのソースコードにサンプルスクリプトを同梱
 リカバリスクリプトはマスタのデータベースクラスタに配置し、C言語関数内からpostgresプロセスがコマンドを実行
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

13

オンラインリカバリの準備
y リカバリのためのC言語関数をインストール
 pgpool-IIから指定したコマンドをSQL経由で実行するためのC言語関数
y template1データベースにインストール

 権限の設定に注意！
% cd pgpool-II-2.0/sql/pgpool-recovery/% make install% psql –h master-node –f pgpool-recovery.sql template1
SELECT pgpool_recovery()system(3)pgpoolpostgres
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

リカバリコマンド14

オンラインリカバリ設定
y pcp.conf
 pcp_recovery_node コマンドを使うので設定は必須

y pgpool.conf
 recovery_usery リカバリ中にPostgreSQL(template1データベース)へ接続するためのユーザ名

 recovery_password
y recovery_userのパスワード

 recovery_1st_stage_commandy ファーストステージで実行するコマンド
y コマンドは$PGDATA以下におく必要がある(セキュリティ上の問題)

 recovery_2nd_stage_commandy セカンドステージで実行するコマンド
y コマンドは$PGDATA以下におく必要がある(セキュリティ上の問題)

 backend_data_directoryN(Nは数字)
y データベースクラスタディレクトリ名
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

15

オンラインリカバリ実行方法
y pcp_recovery_node コマンドy 管理ツール上からも実施可能

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

16

オンラインリカバリ内部処理
1. バッファ上のデータをディスクへ同期
2. recovery_1st_stage_commandの実行
3. バッファ上のデータをディスクへ同期yy

同期する前に接続中のクライアントがいなくなるまで待機接続はブロックされる(クライアント側からは接続に時間がかかるように見える)

4. recovery_2nd_stage_commandの実行
5. postmasterの起動(pgpool_remote_startの実行)
y

起動スクリプトは$PGDATA/pgpool_remote_startというファイル名

6. リカバリしたPostgreSQLへ接続できるか確認
7. 接続ブロックの解除
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

17

オンラインリカバリの実行例
y PITRを使ったオンラインリカバリ
 差分バックアップにより、接続をブロックする時間を短時間にすることが可能 ブロックする時間はpostmasterがアーカイブログからリカバリする
時間

y ファーストステージ
 ベースバックアップを取得し、ダウンしたホストへコピー

y セカンドステージ
 SELECT pg_switch_xlog() を実行し、最新のWALログをアーカイブさせる
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

18

オンラインリカバリの応用
y ノードの動的追加
 2.0 から pgpool.conf のリロードが可能 backend_* を追加し、オンラインカバリすることで負荷状況に応じてノードを追加ができる(スケールアップ)
y リロードするとノードは「ダウン」状態で追加される

pgpool
稼動中にノードを追加

PostgreSQLPostgreSQL

PostgreSQL

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

19オンラインリカバリ注意事項
y 一時的にpgpoolを1台での運用
 リカバリ中に一時的に更新系クエリを止めるが、別ホストで動作しているpgpoolを止めることはできない
y 例えば、負荷分散装置を一時的に1つのwebサーバへ送るなどの変更が必要
停止
リカバリ

webサーバ
pgpoolwebサーバ
pgpoolPostgreSQL

PostgreSQL

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

セカンドステージ

更新クエリ

20

デモ

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

21

オンラインリカバリデモ(準備)y 環境 ローカルにpgpoolとPostgreSQLを配置
pgpool-II(9999 ポート)ダウンさせ、その後リカバリ

PostgreSQL 8.2.5(5432 ポート)/pgdata/5432/data(PGDATA)/pgdata/5432/archive_log

PostgreSQL 8.2.5(5433 ポート)/pgdata/5433/data/pgdata/5433/archive_log

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

22

オンラインリカバリ手順
1. pgpool の設定・起動
2. ファーストステージスクリプト作成
3. セカンドステージスクリプト作成
4. postmaster起動スクリプト作成
5. スクリプトの配置
6. リカバリの実行
7. データの確認
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

23

オンラインリカバリデモ(pgpool設定)
y pgpool.conf
replication_mode = truerecovery_user = ‘y-asaba’recovery_1st_stage_command = 'base-backup.sh‘
recovery_2nd_stage_command = 'pgpool_recovery‘
backend_hostname0 = ''backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/pgdata/5432/data'
backend_hostname1 = ''backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/pgdata/5433/data'
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

24オンラインリカバリ(スクリプト)yファーストステージ用のスクリプト
スクリプト内で3つの引数を受けることが可能
1. マスタのデータベースクラスタディレクトリ名
2. リカバリターゲットのホスト名
3. リカバリターゲットのデータベースクラスタディレクトリ名

ベースバックアップを取得
1. pg_start_backup()の実行
2. rsync によるマスタのベースバックアップのコピー
3. pg_stop_backup()の実行Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

25

オンラインリカバリ(スクリプト)#! /bin/sh
DATA=/pgdata
MASTER_DATA=$1# マスタの PGDATARECOVERY_HOST=$2 # リカバリ先のホスト名
RECOVERY_DATA=$3 # リカバリ先の PGDATA
psql -c "select pg_start_backup('pgpool-recovery')" postgres# recovery.conf の生成
echo "restore_command = 'cp $DATA/5432/archive_log/%f %p'" >$MASTER_DATA/recovery.conf# データの退避とベースバックアップのコピー
rm -rf $RECOVERY_DATA.bk
mv -f $RECOVERY_DATA{,.bk}
rsync –az $DATA/5432/data/ $RECOVERY_DATA/
cp -f $RECOVERY_DATA.bk/postgresql.conf $RECOVERY_DATA
rm -f $RECOVERY_DATA/postmaster.pid
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

psql -c 'select pg_stop_backup()' postgres

26

オンラインリカバリ(スクリプト)y セカンドステージ用のスクリプト xlogの切り替え#! /bin/sh
psql -c 'select pg_switch_xlog()' postgres

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

27

オンラインリカバリ(スクリプト)
y postmaster 起動スクリプト
 1 引数目：リカバリノードのホスト名
 2 引数目：リカバリノードのデータベースクラスタパス#! /bin/sh
DEST=$1
DESTDIR=$2
pg_ctl -w -D $DESTDIR start

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

28

ノードの動的追加のデモ
y 新しく5434ポートで動くPostgreSQLを追加
 設定ファイルの編集、リロード
pgpool-II(9999 ポート)

PostgreSQL 8.2.5(5432 ポート)

PostgreSQL 8.2.5(5433 ポート)

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

PostgreSQL 8.2.5(5434 ポート)29

パラレルクエリ

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

30

パラレルクエリとは？
y 複数のノードでリクエスト(SELECT)を処理
 1つのテーブルを複数サーバに分割(テーブルパーティショニング)しておき、SELECTの結果をpgpool-IIでまとめることで、仮想的に1つのテーブルとして扱う
クライアント

pgpool-II
並列にSELECTが実行される

PostgreSQL

PostgreSQLPostgreSQL

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

PostgreSQL
31

パラレルクエリの変更点
y 1.xの挙動
 すべてのテーブルを分割する必要がある

y 2.0
 レプリケーションテーブルとパーティショニングテーブルを混在させることが可能
y クエリをより並列に動かすことが可能

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

32

パラレルクエリの弱点(1)y パラレルクエリの弱点
 ノードをまたぐテーブル結合
1357

13b2
468

2
baa

ノード1

ノード2a,bそれぞれを偶数、奇数で振り分け

SELECT * FROM a,b WHERE a.i = b.i
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

33

パラレルクエリの弱点(2)yノードをまたぐテーブル結合の流れ
I. テーブルaのデータをノード1、2から取得
II. テーブルbのデータをノード1、2から取得
III. SystemDB上でI, IIで取得したデータを結合123取得したデータを結合

1357
2468

1357
ノード1のa13
2

2468

13

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

ノード2のa全体のデータを取得

2

ノード1のb ノード2のb34

パラレルクエリの改良(1)y 部分レプリケーションテーブルのサポート
1
3
5
7

1
2
3

ノード1

2
4
6
8

1
2
3

ノード2

SELECT * FROM a,b WHERE a.i = b.i

部分レプリケーションによって、ノード内でJOINが完結することが可能
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

35

パラレルクエリの改良(2)y部分レプリケーションテーブルの流れ
I. ノード1でaとbをテーブル結合
II. ノード2でaとbをテーブル結合
III. I,IIの結果をまとめてクライアントに返却
123

結果をまとめるのみ

2 ノード内で並列に結合131357

123

2468

123

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

ノード1のa ノード1のbノード2のaノード2のb36

部分レプリケーションのルール登録
y replicate_def テーブルの追加
 分散ルールとほぼ同じスキーマ
 分散キーとルール定義関数名の登録は必要なし
INSERT INTO pgpool_catalog.replicate_def VALUES ('pgpool','public','branches',ARRAY['bid','bbalance','filler'],ARRAY['integer','integer','character(84)'] );pgbenchのbranchesテーブル定義例
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

37

DBT-3によるベンチマーク
PostgreSQLと３台並列処理の実行時間の比較
7

y line_itemとordersをテーブル分割
 PostgreSQL 3 ノード(3分割) 青線：パーティションテーブルとレプリ
ケーションテーブルの結合 赤線：レプリケーションテーブル同士の結合
 パラレルクエリの弱いクエリは未測定(13,15,17,20,22)

単体実行時間/3台分散実行時間

6

5

4

3

y scale factor=102

 インデックス含めてトータルで30GB

1

最大で6倍のパフォーマンス改善
01 2 3 4 5 6 7 8 9 10111213 141516 17181920 2122 (クエリ9番)
Query番号Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

38

全般

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

39

他のシステムとの連携
y failover_command ノードを切り離した時に指定したコマンドを実行
 Slony-I の連携やアラートメールを送ることが可能y failback_command ノードを戻した時に指定したコマンドを実行

y pgpool-IIから必要な情報を渡すことも可能(特殊文字を置換)
 %d： 対象ノード番号
 %h： 対象ノードのホスト名
 %p： 対象ノードのポート番号
 %D： 対象ノードのデータベースクラスタパス
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

40

Slony-Iとの連携例
pgpool-II(master_slave)

PostgreSQL(マスタ)

PostgreSQL(スレーブ)

pgpool-II(master_slave)×

PostgreSQL(マスタ)

PostgreSQL(マスタ)

Slony-I

Slony-I

通常時

failover_commandでSlony-Iに対してマスタを切り替えるように指示
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

41クライアントの強制切断
y client_idle_limit クライアントからクエリが届くまでの最大待ち時間を指定可能
 秒単位で指定
 TCPのタイムアウト待ち防止などに有効

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

42

参考URLy pgpool-II 開発サイト

 http://pgfoundry.org/projects/pgpool/y pgpool Wiki

 http://pgpool.sraoss.jp/

y pgpoolメーリングリスト

 http://www.sraoss.jp/mailman/listinfo/pgpool-general-jp

y マイコミジャーナルでのpgpool-IIの解説記事

 http://journal.mycom.co.jp/articles/2007/11/08/pgpool/in
dex.html
Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

43

ご清聴ありがとうございました。

Copyright © 2007 SRA OSS, Inc. Japan All rights reserved.

44