pgpool-II 入門！
～PostgreSQL 用クラスタツールの使い方を基礎から学ぼう～

OSC 2015 Tokyo/Fall
2015/10/25
SRA OSS, Inc. 日本支社
マーケティング部 OSS技術グループ

長田 悠吾

自己紹介
●長田悠吾（ナガタユウゴ）– SRA OSS, Inc. 日本支社
– マーケティング部 OSS技術グループ– pgpool-II 開発者– PostgreSQL 関連の技術調査– OSS の技術サポート– PostgreSQL の開発にも参加

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

2

SRA OSS, Inc. 日本支社●

1999年よりPostgreSQLサポートを中心にOSSビジネスを開始●

PostgreSQL、Hinemos、Zabbix などのOSSサポート●PowerGresファミリーの開発、販売●

トレーニング、導入、設計コンサルティングサービス

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

3本日のお話●

PostgreSQLのクラスタリング機能–●

ストリーミングレプリケーション

解決すべき課題
アプリケーションには手を入れたくない– 負荷分散で検索性能をスケールアウトさせたい– サーバが落ちた場合には自動フェイルオーバさせたい–●

pgpool-II による解決機能と使い方– 新バージョン 3.5 の新機能–

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

4

PostgreSQLでクラスタリング

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

5

PostgreSQLとは●

代表的なオープンソースのRDBMSの１つ–●●

カリフォルニア大学で開発された研究用RDBMSのIngres(1970)
を先祖に持つオーナー企業を持たず、コミュニティによる開発が続けられている–

年１回のメジャーバージョンアップ

–9.5beta1が最近リリース

PostgreSQLライセンスで配布–

BSDタイプの緩いライセンス

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

6

クラスタリング●

データベースクラスタリングの目的は？
– 高可用性の確保

サービスを停止させたくない●
１つのデータベースが故障しても、別のデータベースが肩代わりする●– 参照負荷分散

大量のアクセスをさばきたい●
負荷を分散して検索性能を向上●– 並列処理大量のデータを解析したい●
複数のサーバで並列的に処理●

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

7

PostgreSQLのクラスタ技術●

HAクラスタ– Pacemaker+DRBD、共有ストレージなどを利用– 待機側はサービス停止●

ストリーミングレプリケーション– PostgreSQL自体が持つ、非同期レプリケーション機能– プライマリ（更新可能） + 複数のスタンバイDB（検索のみ）– 簡単、確実、速い●

pgpool-II– クライアントとPostgreSQLの間に入ってレプリケーション機能を提供– コネクションプーリング、負荷分散、自動フェイルオーバなど他の機能もある●

Postgres-XC– PostgreSQLを改造したクラスタシステム– 書き込み性能の負荷分散
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

8

PostgreSQLのストリーミングレプリケーション●

ストリーミングレプリケーション（PostgreSQL 9.0 ～）–マスタからスレーブにトランザクションログ（WAL）を転送することによりデータの複製を実現
スタンバイは参照クエリを受け付けることができる （Hot Standby）

プライマリへは更新・参照の両方とも可能
更新クエリ
参照クエリ

クライアント

参照クエリ

WAL（トランザクション内容）を転送
プライマリ

WALを適用し続ける
スタンバイ

WAL 書き込み

WAL 書き込み

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

9

PostgreSQLのストリーミングレプリケーション●

複数のスタンバイにレプリケーション可能
クライアント

更新クエリ
参照クエリ

参照クエリ
参照クエリ

プライマリ
スタンバイ

スタンバイ

レプリケーション

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

10カスケードレプリケーション●

カスケードレプリケーション（PostgreSQL 9.2 ～）–スレーブからさらに別のスレーブへのレプリケーションが可能–

スタンバイ増加によるプライマリへの負荷の集中を回避
参照

クライアント

更新/参照

複製

複製

プライマリ
スタンバイ
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

11その他のレプリケーション関連機能（１）●

同期レプリケーション (PostgreSQL 9.1～）–

スタンバイ側のディスクに WAL が書き込まれることを保証するモード●●

注意：データの同期を保証するものではない

レプリケーションスロット （PostgreSQL 9.4～）–

レプリケーション状態や付帯情報を保持する枠組み●●

スタンバイに必要な WAL が削除され、レプリケーション不能になるのを防止
スタンバイが参照しているデータが物理的に削除されてコンフリクトが起きるのを防止

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

12その他のレプリケーション関連機能（２）●

論理デコーディング (PostgreSQL 9.4～)–

テーブルへの更新内容を「SQLレベル」の更新情報として出力するモード–

将来的な機能拡張を実現するための基盤となる●–部分レプリケーション、マルチマスタレプリケーション、異種DBへのレプリケーション・・・・など

BDR(Bi-Directional Replication)●●

双方向レプリケーションを実現する外部モジュール
論理デコーディング機能を使用

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

13残る課題は・・・・？
負荷分散はどうするの？
更新クエリ、参照クエリの振り分けは？
アプリケーションを書き換えなきゃだめ？

更新クエリ
参照クエリ

クライアント参照クエリ
参照
クエリ

プライマリ

レプリケーション

DBサーバに障害が発生したら？
手動で対応するの？

スタンバイ

スタンバイ

プライマリがダウンしたら更新ができなくなる？！
サービスが停止してしまう！？
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

14

クラスタツール「pgpool-II」で解決！

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

15

pgpool-II とは●

アプリケーションとPostgreSQLの間に入って、クラスタリング機能を提供するミドルウェア–●

アプリケーションからは普通のPostgreSQLに見える

オープンソースソフトウェア（BSDライセンス）

PostgreSQL– メジャーバージョンアップは年１回– 今年の冬に 3.5.0 がリリース予定

クエリ
●

多彩な機能
コネクションプーリング– 参照負荷分散– クエリキャッシュ– ヘルスチェック– 自動フェイルオーバ– オンラインリカバリ–

クエリ

pgpool-II

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

16

参照負荷分散

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

17

参照負荷分散●

クエリの自動振り分け
更新クエリはプライマリサーバへ– 参照クエリはサーバ間で振り分け
–

更新・参照
プライマリ

レプリケーション

参照

更新・参照

pgpool-II

参照

振り分けの重みを指定可能
レプリケーション遅延が大きいサーバには振り分けない3.4.0 からはよりきめ細やかな振り分けが可能に！

スタンバイ

スタンバイ

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

18

きめ細かな負荷分散（3.4～）●

アプリケーション名、DB名によって接続先が指定できる

重い検索

更新・参照

分析アプリケーション

プライマリ
参照

更新・参照

Webアプリケーション

レプリケーション

pgpool-II

重い検索

スタンバイ

スタンバイ
（分析用）
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

19負荷分散の設定例# PostgreSQL のホスト名、ポート番号、ロードバランスの重みを設定
backend_hostname0 = 'host1'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = 'host2'
backend_port1 = 5432
backend_weight1 = 1
backend_hostname2 = 'host3'
backend_port2= 5432
backend_weight2 = 0# 接続アプリケーションによって、接続先DBを変更
app_name_redirect_preference_list = 'analyze_app:2'
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

20データベースに障害が発生したら？

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

21

自動フェイルオーバ●

DBサーバの障害を自動検出（ヘルスチェック機能）–ダウンしたPostgreSQLを切り離す→ 負荷分散の対象から外れる
更新・参照
プライマリ

レプリケーション

参照

更新・参照

pgpool-II

スタンバイ
障害発生

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

22自動フェイルオーバ●

プライマリサーバに障害が発生した場合は？
–そのままでは更新ができなくなってしまう！

障害発生

更新・参照
プライマリ

レプリケーション

参照

更新・参照

pgpool-II

参照

スタンバイ

スタンバイ
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

23

自動フェイルオーバ●

プライマリサーバに障害が発生した場合そのままでは更新ができなくなる– 負荷分散からの切り離し– スタンバイをプライマリに自動昇格
–

障害発生

更新・参照

更新・参照

pgpool-II

フェイルオーバ時に実行される具体的な処理はユーザがスクリプトで定義可能（サンプルあり）

参照

プライマリ

レプリケーションスタンバイ

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

24

ダウンしたデータベースの復旧

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

25

オンラインリカバリ●ダウンしたスタンバイをプライマリに同期させる●

同期中も更新が可能

同期中
更新・参照

更新・参照

pgpool-II

参照プライマリレプリケーションスタンバイ
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

26

オンラインリカバリ●ダウンしたスタンバイをプライマリに同期させる●

同期中も更新可能●

同期完了後、自動的に負荷分散
参照

対象となる

スタンバイ
更新・参照

更新・参照

レプリケーション

pgpool-II

オンラインリカバリ時の処理もスクリプトで定義（サンプルあり）

参照

プライマリ

スタンバイ

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

27

オンラインリカバリ●

新しいスタンバイサーバの追加–

pgpool-II に新しいサーバの情報を読み込ませてから、オンラインリカバリ– 同期完了後、負荷分散対象に参照

同期中

スタンバイ更新・参照

更新・参照

レプリケーション

pgpool-II

参照

プライマリ

スタンバイ
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

28

オンラインリカバリ●

スタンバイの増設も容易–

参照

検索性能のスケールアウト！
参照
スタンバイ更新・参照

更新・参照

レプリケーション

pgpool-II

参照

プライマリ

スタンバイ
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

29

ここまでのまとめ●

参照性能の負荷分散–●

更新クエリと参照クエリの適切な振り分け

自動フェイルオーバ
データベース障害の自動検出＆切り離し– プライマリがダウンしたら、スタンバイが新プライマリに昇格–●

オンラインリカバリサービスを停めずにダウンしたサーバを復帰– 新しいスタンバイの追加も簡単–●

基本的にアプリケーションの書き換えは必要なし！

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

30

pgpool-II の単一障害点回避

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

31

pgpool-II の単一障害点回避●もし、pgpool-II に障害が発生したら？！
–

単一障害点 (Single Point of Failure) ？
参照
プライマリ

障害発生

レプリケーション更新・参照

更新・参照

pgpool-II

参照

スタンバイ

スタンバイ
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

32

watchdog●

pgpool-II 組み込みのHA機能–

pgpool-II を Active/Standby 構成にする–

仮想IPでpgpool-IIにアクセス
pgpool-II(Active)
更新・参照

更新・参照

プライマリ

レプリケーション

仮想IP
参照
相互監視
参照

pgpool-II(Standby)

スタンバイ

スタンバイ

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

33

watchdog●

Active pgpool-II に障害発生すると・・・–

Standby pgpool-II が Active に昇格–

仮想IPでの付け替えが行われる
pgpool-II(Down)

更新・参照

相互監視

仮想IP

pgpool-II(Active)

更新・参照

プライマリ

レプリケーション

参照
スタンバイ
参照

スタンバイCopyright © 2015 SRA OSS, Inc. Japan All rights reserved.

34

クエリキャッシュによる性能向上

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

35

インメモリクエリキャッシュ●SELECTクエリの結果をメモリ内にキャッシュする機能
同じクエリが来たときに再利用する– DBへのアクセスが減り、応答速度が向上–

クエリ

pgpool-II
クエリ

結果

結果

キャッシュの保存/検索
memcached

PostgreSQL
メモリ

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

36

pgpool-II 活用事例

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

37

第一法規株式会社様 事例
●

●

●

●

大量の判例などを検索するシステム
PostgreSQLのストリーミン
グレプリケーション +pgpool-II で、負荷分散による性能向上、可用性向上インメモリクエリキャッシュ機能を活用して検索性能を向上
一度発生したDB障害でもpgpool-II の自動フェイルオーバ機能により、サービスは停止することなく継続できた。

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

38

株式会社 Gengo様 事例●

翻訳サービスのクラウド
ソーシング企業●

AWS上でシステム構築●

３万トランザクション/日●●

同時にPostgreSQLのバージョンアップ。オンラインリカバリ機能を活用しダウンタイムを最小限に。
AWSによる強制インスタン
ス再起動メンテナンスも自動フェイルオーバ機能で乗り切った

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

39

pgpool-II の運用管理

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

40

pgpool-IIの運用管理コマンド●

pgpool コマンド– 起動●

pgpool start– 停止●

pgpool stop

– 設定ファイルの読み込み●

pgpool reload– 起動時オプション

デーモンモード
Initd 起動スクリプトのサンプルもあります●
デバッグモード●
設定ファイルの指定●
バックエンドステータスの破棄● etc●

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

41

pgpool-IIの運用管理コマンド●

pcp コマンド– DBノードをpgpool-IIの管理下から外す●

pcp_detach_node– DBノードをpgpool-IIの管理下に組み入れる●

pcp_attach_node– オンラインリカバリを実行する●

pcp_recovery_node– DBノードの状態を取得する●

pcp_node_info– watachdog ステータスを取得する●

pcp_watchdog_info
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

42

pgpool-IIの運用管理コマンド●SHOW コマンド–pgpool-II の内部情報を取得できるクエリ–

ノード情報、プロセス情報、コネクションプーリング、キャッシュヒット率、現在の設定、バージョン

postgres=# SHOW pool_nodes;
node_id | hostname | port | status | lb_weight | role­­­­­­­­­+­­­­­­­­­­+­­­­­­­+­­­­­­­­+­­­­­­­­­­­+­­­­­­­­­
0| /tmp| 11002 | 2| 0.333333 | primary
1| /tmp| 11003 | 2| 0.333333 | standby
2| /tmp| 11004 | 2| 0.333333 | standby(3 rows)
postgres=# SHOW pool_version;
pool_version­­­­­­­­­­­­­­­­­­­­­
3.4.3 (tataraboshi)(1 row)
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

43

GUI 管理ツール

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

44

pgpoolAdmin●

pgpool-II のGUI管理ツール–

PHPで書かれたWebアプリケーション–

pgpool-II と DBノードの操作および状態確認が可能

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

45

pgpool-II のインストール方法

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

46

pgpool-II のインストール●

RPM からのインストール–

オフシャルレポジトリから rpm ファイルをダウンロード

http://www.pgpool.net/yum/– yum からのインストールも可能●●

ソースからのインストール
オフィシャルページからソースコードをインストール– configure & make & make install–●

インストーラの利用
対話形式で質問に答えながらインストールが完了– ２台のサーバ、それぞれにpgpool-II、PostgreSQLをセットアップ––

Watchdog 設定、pgpoolAdmin もインストールされる
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

47

pgpool-II 3.5.0 の新機能

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

48

pgpool-II 3.5.0●

12月にリリース予定

●

主な変更点
– PostgreSQL 9.5 対応– 性能改善– watchdog 機能の改善– pcp コマンドの改善– パラレルクエリモードの廃止

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

49

PostgreSQL 9.5 対応●

pgpool-II 3.5 では PostgreSQL 9.5 の SQL パーサを移植–

SQL 文字列を解析してパーツツリーに変換するモジュール
pgpoo-II 3.5

PostgreSQL 9.5

移植
パーサ–パーサ

参照負荷分散、クエリキャッシュが、新しい SELECT 構文に対応
GROUPING SET, CUBE, ROLLUP● TABLESAMPLE●Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

50

拡張プロトコル使用時の性能改善●拡張プロトコル–

●

20000

Java アプリケーションのJDBC ドライバで使用される

性能改善

18000

約 47% の性能改善

16000
14000

–

ストリーミングレプケーション使用時 12000の処理を改善–

無駄なメッセージやりとりを削減

10000

–

pgbench で １ 秒あたりの SELECT
処理回数 (TPS) を比較

8000
6000
4000

Let's note CF-SX3
CORE i7 x 2@2.1GHz
Mem 16GB
SSD 512GB

2000
0pgbench -S -n -M extended -c 8 -j 4 -T 30 test

pgpool-II 3.4

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

pgpool- II3.5
51

Watchdog 機能の改善●

内部コードの改善

よりデバッグ、メンテナンス、機能拡張がしやすいコードへ●
スプリットブレイン対策––●

プロセス間通信方式の変更–●

Quorum のサポート
UNIXドメインソケット、JSON の採用

ノードの優先度– pgpool-II が「アクティブ」に選ばれる「優先度」を設定可

能Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

52

Watchdog 機能の改善（１）●

スプリットブレイン対策の改善–

ネットワークが分離された時に、どの pgpool-II がアクティブとなるか決められなくなる問題–

Quorum のサポート●

クラスタに参加している全ノードのうち半数以上が自分と同じネットワークに属しているかどうか、をチェックする

アクティブはこちらのグループから選出

pgpool-II

pgpool-II

pgpool-II

ネットワーク分断
pgpool-II

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

pgpool-II

53Watchdog 機能の改善（2）●watchdog 内部で行われる、プロセス間通信方式の変更–●UNIX ドメインソケット & JSON 形式データ

これにより、外部のサードパーティツールとの連携が可能に–

例）外部ツールを使った死活監視など
新バージョン

以前のバージョン

死活監視
プロセス

JSONpgpool-II

pgpool-II

クラスタ管理プロセス

他 pgpoo-II

JSON

共有メモリ

変更

死活監視
プロセス

クラスタ管理
プロセス
JSON

外部ツール
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

54

その他の変更●

PCP コマンドの改善– 引数の与え方の改善： オプションとして指定可能に旧） $ pcp_node_info 0 localhost 9898 admin_user admin_pass 0新） $ pcp_node_info ­h localhost ­U admin_user 0– パスワードをコマンドラインで渡さなくても良いようになった●~/.pcppass ファイルに書いておけばよい– 複数ｐｃｐコマンドの同時実行●●

たとえば時間のかかる ｐｃp_recovery_node （オンラインリカバリ）の実行中に他のpcpコマンドを実行できる

パラレルクエリモードの廃止– ユーザが少なく、その割にメンテナンスの手間が大きかった
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

55

まとめ
●

PostgreSQLのクラスタ技術–●

ストリーミングレプリケーション + pgpool-II の組み合わせ

pgpool-II の機能–

負荷分散 & 自動クエリ振り分け

自動フェイルオーバ– オンラインリカバリ– watchdog––●

インメモリクエリキャッシュ

pgpool-II 3.5 新機能の紹介–

PostgreSQL 9.5 対応、性能改善、watchdog改善、pcp コマンド改善・・・

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

56

参考URL●●●

pgpool-II オフィシャルサイト–

http://www.pgpool.net/–

http://www.pgpool.net/jp/

SRA OSS, Inc. 日本支社–

セミナー資料、事例情報、技術情報–

http://www.pgecons.org/

Let's Postgres–PostgreSQL 情報のポータルサイト–

http://lets.postgresql.jp/

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

57

オープンソースとともにURL： http://www.sraoss.co.jp/
E-mail： sales@sraoss.co.jp
Tel： 03-5979-2701
Copyright © 2015 SRA OSS, Inc. Japan All rights reserved.

58