PostgreSQLで高信頼性ＤＢを構築しよう
OSC.Enterprise Osaka 2014SRA OSS, Inc. 日本支社
取締役支社長石井 達夫

自己紹介
SRA OSS, Inc.で日本支社運営を担当●
PostgreSQLの開発者(コミッタ)●
PostgreSQL用のクラスタソフト “pgpool-II” のコミュニティリーダ●

2Copyright(c) 2014 SRA OSS, Inc. Japan

SRA OSS, Inc.のご紹介●●

1999年よりPostgreSQLサポートを中心にOSSビジネスを開始、2005年に現在の形に至る
主なビジネス●

PostgreSQLなどのOSSサポート●

PowerGresファミリーの開発、販売●

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

3

Copyright(c) 2014 SRA OSS, Inc. Japanミッションクリティカル領域で求められること可用性(High Availability:HA)●
性能(スケールアップ、スケールアウト)●
これらの要件を実現するために開発されたのがクラスタシステム●4Copyright(c) 2014 SRA OSS, Inc. Japan可用性(High Availability:HA)とは●システムが停止しないで使い続けられる能力のこと●●

年間の稼働時間を目安にする(稼働率)

可用性を高めるには、以下のどちらかまたは両方を使う。今日は冗長化の方向でお話します(信頼性がさほど高くないコンポーネントを複数使って冗長化するのが最近のトレンド)
構成要素の信頼性を上げる
●
冗長化する
●

5Copyright(c) 2014 SRA OSS, Inc. Japan稼働率目標と実現手法
稼働率

年間動作不能時
間実現手法

99%

3日15時間36分

バックアップとリストア

99.9%

8時間46分

バックアップとリストアに加え、夜間待機が必要

99.99%

52分34秒クラスタ+手動切り替え+夜間待機

99.999%5分15秒

クラスタ+自動切り替え99.9999%

32秒

特殊なハードウェアが必要
Wikipediaより

6

Copyright(c) 2014 SRA OSS, Inc. Japan

PostgreSQLにおけるクラスタ技術のトレンド
ストリーミングレプリケーション

アクティブ
スタンバイ型のＨＡクラスタ

ストリーミングレプリけション+
検索負荷分散、自動フェイルオーバ(pgpool-II)

PostgreSQL

更新負荷分散パラレルクエリ(Postgres-XC)

カラム指向
パラレルクエリ(Redshift, GreenPlum)
7Copyright(c) 2014 SRA OSS, Inc. Japan主なPostgreSQL用のクラスタ・レプリ
ケーションツール●

ストリーミングレプリケーション
PostgreSQLネィティブの非同期レプリケーションシステム。master+複数のread only replica。簡単、確実、速い●でも自動フェイルオーバや負荷分散はしてくれない●●

pgpool-II●●

クライアントとPostgreSQLの間に入るproxyとして動作。pgpool-IIネィ
ティブの同期レプリケーションと、ストリーミングレプリケーションの両方をサポート。コネクションプーリング、負荷分散、自動フェイルオーバ機能あり。

Postgres-XC●

PostgreSQLを改造した比較的新しいクラスタシステム。パラレルクエリと、書き込み負荷分散をサポート。まだ実績は少ない。

8Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIとは●

●

●

●

●

●

PostgeSQLで使えるクラスタリングミ
ドルウェア
OSSとして公開 (BSDライセンス)
PostgreSQLから独立したproxyとして動作、アプリケーションの改修は最低限
幅広いPostgreSQLのバージョンに対応
性能や可用性を高める多くの機能
メジャーバージョンアップは年に1回、マイナーバージョンアップは2-3ヶ月に1回、最新バージョンは3.3系

9

Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIによるクラスタの概念(1)
アプリケーションからは普通のPostgreSQLに見える検索/更新
問い合わせを負荷分散できるかどうかを判断
検索
更新

PostgreSQL
primary

更新の伝搬はPostgreSQLの機能を利用
10

検索処理を複数のPostgreSQLで分担することにより、性能向上(負荷分散)

pgpool-II

検索

検索

PostgreSQL
standbystreaming replication
Copyright(c) 2014 SRA OSS, Inc. Japan

PostgreSQL
standbypgpool-IIによるクラスタの概念(2)

検索/更新
pgpool-II

検索
更新

PostgreSQL
primary

検索

PostgreSQL
standbystreaming replication
11Copyright(c) 2014 SRA OSS, Inc. Japan

ダウンしたPostgreSQLを自動切り離して運用を継続(自動フェイルオーバ)

Ｘ

PostgreSQL
standbypgpool-IIによるクラスタの概念(3)

検索/更新
pgpool-II

primaryのPostgreSQL
がダウンした場合にはそのままでは更新処理ができなくなってしまう

Ｘ

PostgreSQL
primary

検索
更新

検索

検索

PostgreSQL
standbystreaming replication
12

Copyright(c) 2014 SRA OSS, Inc. Japan

PostgreSQL
standbypgpool-IIによるクラスタの概念(4)検索/更新
pgpool-II

primaryサーバがダウンした時には切り離すとともにstandbyサーバをprimaryに自動昇格させる

Ｘ

PostgreSQL
primary

検索
更新

検索

PostgreSQL
primary

PostgreSQL
standbystreaming replication
13

Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIによるクラスタの概念(5)

検索/更新

待機用のpgpool-IIを用意し、稼動系のpgpool-IIがダウンした時にフェイルオーバさせることも可能(watchdog)

pgpool-II

検索
更新

PostgreSQL
primary

検索pgpool-II

検索

PostgreSQL
standbystreaming replication
14

Copyright(c) 2014 SRA OSS, Inc. Japan

PostgreSQL
standbypgpool-IIの構造
pgpool-IIメインプロセス

watchdog
起動

設定ファイル
pgpool.conf

pgpool-IIメインプロセス
監視起動

pgpool-II子プロセス
SQLパーサプロトコルエンジン

実際には多数のプロセスが起動される。

コネクションプール

PostgreSQL

PostgreSQL

クエリキャッシュ
PostgreSQL

15

Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIの必要とするリソース
ディスクIOはほとんど発生しない●
ネットワーク性能が重要●
ユーザのセッションに対応して多数のプロセスが並行して動くので、CPU数は多い方が良いが、CPU負荷は軽いので、特別高速なCPUである必要はない
●
必要メモリ
●

●

子プロセスあたり50MB位が目安

16

Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIのDB異常検技術●

以下のどちらかまたは両方を使える●

ヘルスチェック
定期的にPostgreSQLにパケット(接続開始パケット)を投げて、応答が返るかどうかをチェック– 指定回数リトライしてそれでも駄目ならそのＤＢを切り離す(フェイルオーバ)–●

PostgreSQLとの通信の最中に通信エラーが起きたら、即座にＤＢを切り離すことも可能–

数秒から数十秒でフェイルオーバできるが、敏感すぎるかも

17

Copyright(c) 2014 SRA OSS, Inc. Japanフェイルオーバ設定
フェイルオーバしたときに呼び出すスクリプトを設定
可能●
典型的な処理●primaryサーバがダウンした場合には、ユーザ好みのロジックで昇格候補のstandbyサーバを選び、昇格させる●
フェイルオーバした時に管理者にメールを送信することも可能●

18

Copyright(c) 2014 SRA OSS, Inc. JapanオンラインリカバリとはダウンしたスタンバイＤＢあるいは新規に作成したスタンバイＤＢを、プライマリサーバに同期させる●
同期中も更新可能●
同期が完了したら自動的に管理対象に追加して負荷分散の対象となる●

pgpool-II

pgpool_recovery
ベースバックアップを実行

pcp_recovery_node(管理コマンド)

primary
PostgreSQL

standbyPostgreSQL

更新差分はWALをロールフォワード
19

Copyright(c) 2014 SRA OSS, Inc. Japanチューニング●

relcache_expire
pgpool-IIが持つテーブルのメタ情報のキャッシュ期限●
頻繁にテーブルを作成したりする場合は無期限ではなく短くする●●

relcache_size●●

check_temp_table●●

テーブルが一時テーブルかどうかのチェックを行うかどうか。一時テーブルを使っていない場合はoffにすることにより、内部クエリの数を減らせる

memory_cache_enabled●●

上記キャッシュが保持できるテーブル数。テーブルが多い場合には増やす

検索中心のシステムでは有効にすることにより性能向上

開発中の3.4では更にチューニング項目が増えます！

20

Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIの運用管理(1)●pgpool-IIの起動●●

pgpool-IIの停止●●

pgpool start
pgpool stop

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

pgpool reload

21

Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIの運用管理(2)●DBノードの状態を表示# show pool_nodes;
node_id | hostname | port | status | lb_weight | role­­­­­­­­­+­­­­­­­­­­+­­­­­­­+­­­­­­­­+­­­­­­­­­­­+­­­­­­­­­
0| /tmp| 11000 | 2| 0.333333 | primary
1| /tmp| 11001 | 2| 0.333333 | standby
2| /tmp| 11002 | 2| 0.333333 | standby●

pgpool-IIの管理下から外す●●

pgpool-IIの管理下に組み入れる●●

pcp_detach_node
pcp_attach_node

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

pcp_recovery_node22

Copyright(c) 2014 SRA OSS, Inc. Japanpgpool-IIの運用管理(3)●新しいstandbyノードを追加する
PostgreSQLのインストールと、initdbだけやっておく(起動はしない)●
pgpool.confに新しいDBの情報を記述●“pgpool reload”でpgpool.confの再読み込み●
DBノードがダウン状態でpgpool-IIに認識される●
pcp_recovery_nodeを実行、ＤＢの同期を取り、オンライン状態にする●●

新しくpgpool-IIに接続したセッションから新しいＤＢノードを利用できるようになる
23

Copyright(c) 2014 SRA OSS, Inc. Japan

開発中のpgpool-II 3.4.0の新機能！(1)●

検索負荷分散のきめ細かな指定が可能になりました●

アプリケーション名による指定、ＤＢ名による指定が可能
Webアプリ

今まで
更新

primary

分析アプリ

pgpool-II 3.4

pgpool-II

更新

負荷分散
stanby1

分析standby

負荷分散用のstandbyはどのアプリでも同じ。分析アプリの重いクエリがstandby1も行ってしまう。
24

Webアプリprimary

分析アプリ

pgpool-II
負荷分散
standby1分析standby

分析アプリの重いクエリを分析standbyにのみ流してWebアプリに影響を与えない

Copyright(c) 2014 SRA OSS, Inc. Japan

開発中のpgpool-II 3.4.0の新機能！(2)●

具体的な指定の仕方●●●

app_name_redirect_preference_list ='psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
database_redirect_preference_list ='postgres:primary,mydb[0-4]:1,mydb[5-9]:2'

アプリケーション名とデータベース名に正規表現が利用可能

25

Copyright(c) 2014 SRA OSS, Inc. Japan

開発中のpgpool-II 3.4.0の新機能！(3)●

性能・信頼性に関する新しい設定項目●

check_unlogged_table–●

listen_backlog_multiplier–●

同時接続数が非常に多い場合に接続待ち行列を増やして性能と安定性を向上可能

connect_timeout–●

unloggedテーブルを使用していない場合にチェックをスキップしてマスタサーバの負荷を軽減できる

コネクション時のタイムアウトを調整して不安定、高負荷環境でネットワー
ク接続エラーによるフェイルオーバを低減することが可能

allow_sql_comments–

負荷分散に影響を与えずにSQLコメント(/* comment */)が使用可能

26

Copyright(c) 2014 SRA OSS, Inc. Japan

開発中のpgpool-II 3.4.0の新機能！(4)●ログに関する機能向上●

print_user–●

ログレベルの導入–●

ログに接続ユーザ名を印字
PostgreSQL同様debug[1-5],log,notice, errorなどのログレベルが設定できる

その他●

PostgreSQL 9.4対応–●

IPv6対応–●信頼性、性能の向上

ソースツリーの改良–●

PostgreSQLサーバのアドレス、pgpool-IIのバインドアドレスにIPv6を使用可能

PostgreSQLのメモリーマネージャ、例外処理マネージャを移植–●

たとえば “REFRESH MATERIALIZED VIEW CONCURRENTLY” を正しく認識できる

可読性のアップ

PostgreSQLの状態を記録するステータスファイルがテキストファイルになり、可読性が向上

27

Copyright(c) 2014 SRA OSS, Inc. Japan

まとめ
可用性とは
●
PostgreSQLで使えるクラスタ技術●
pgpool-IIの紹介●

28

Copyright(c) 2014 SRA OSS, Inc. Japan

参考URL●SRA OSSのサイト(多数のスライドや技術情報あり)●●

http://www.sraoss.co.jp

pgpool-IIオフィシャルサイト
http://www.pgpool.net●
http://www.pgpool.net/pgpoolweb/contrib_docs/simple_sr_setting2_3.3/index-ja.html●●

PostgreSQLに関する各種技術情報●

http://lets.postgresql.jp
29

Copyright(c) 2014 SRA OSS, Inc. Japan

オープンソースとともにCopyright(c) 2014 SRA OSS, Inc. Japan

30