pgpool-II最新情報
pgpool-II 3.1の最新機能とは?
SRA OSS, Inc. 日本支社
石井 達夫

2

2011/4/26

Copyright(c)2011 Tatsuo Ishii

3

2011/4/26

Copyright(c)2011 Tatsuo Ishii

PostgreSQLはどんどん高性能化する●

レプリケーション／クラスタリング対応●●●

ストリーミングレプリケーション<--pgpool-IIの取り組み

単体のDBエンジンとしてもより高性能に●CPUスケーラビリティ●

更新性能向上(HOT)●

クエリオプティマイザの改良

pgpool-IIについてお話しする前に、まずPostgreSQLの性能向上の例を見てみましょう

2011/4/26

Copyright(c)2011 Tatsuo Ishii

4

実は地道に改良されている
クエリオプティマイザ●

実用的には、これが嬉しい
●

●

●

●

バージョンアップしただけで、アプリケーションが速くなる
バージョン毎に改良が加えられるので、新しいバージョンを使うのがおすすめ
本日は、大きなテーブルの結合(1000万件、300万件、100万件)の性能向上をバージョンを追って紹介します
おまけで、MySQLもテストしてみました

2011/4/26

Copyright(c)2011 Tatsuo Ishii

5

6

テスト環境
●

●

●

●

●

Vine Linux 5.2 (kernel 2.6.27)●

mem: 4GB, CPU: Core2Duo 1.6GHz(dual core)●

disk: 外付けUSB 2.0 2.5インチディスク

すべてノーチューニング(MySQLはmy_large.cnf, InnoDB使用)
PostgreSQL 8.1.23, 8.2.21, 8.3.15, 8.4.8, 9.0.4, MySQL
5.1.41
テーブルは、1,095,594件、3,605,456件、10,543,404件、インデックスあり。3つのテーブルをJOINし、400件抽出する
DB容量は2.1GB(PostgreSQL, MySQL)

2011/4/26

Copyright(c)2011 Tatsuo Ishii

7SELECT #1(副問い合わせ使用)
SELECT
C.package_name,
C.name AS class_name,
C.id AS class_id,
M.name AS method_name,
M.id AS method_id
FROM (SELECT id, name FROM java_method WHERE name LIKE 'copy%') M
JOIN class_method_map CM on CM.method_id = M.id
JOIN java_class C on C.id = CM.class_id
WHERE
C.name LIKE 'FileUtil%';
2011/4/26

Copyright(c)2011 Tatsuo Ishii

8

SELECT #2(単純JOIN)
SELECT
C.package_name,
C.name AS class_name,
C.id AS class_id,
M.name AS method_name,
M.id AS method_id
FROM java_method M,
class_method_map CM,
java_class C
WHERE
C.name LIKE 'FileUtil%' AND
M.name LIKE 'copy%' AND
CM.method_id = M.id AND
C.id = CM.class_id;
2011/4/26

Copyright(c)2011 Tatsuo Ishii

PostgreSQL 8.1->9.0で140倍の高速化！
600

500

実行時間 ( 秒 )

400

300

SELECT( 副問い合わせ )SELECT( 単純 JOIN)

200

1000
PostgreSQL 8.1

2011/4/26

PostgreSQL 8.2

PostgreSQL 8.3

PostgreSQL 8.4

PostgreSQL 9.0

Copyright(c)2011 Tatsuo Ishii

MySQL 5.1.41

910

pgpool-II 3.1の新機能●SQLパーサの更新●●

実はpgpool-IIは、SQLパーサを持っている–

複雑なSQLを確実に解析するため–クエリの書き換えを行うため

SQLパーサのコードはPostgreSQLから借用–●

PostgreSQL/pgpool-IIはどちらもBSDライセンスなので問題なし

PostgreSQL 9.0のパーサを導入–

2011/4/26

CREATE INDEX文でインデックス名の省略な
ど、PostgreSQL 9.0構文をサポート
Copyright(c)2011 Tatsuo Ishii

11Black/white関数リストの改良●

Black/white関数リストとは？
●●

PostgreSQLでは、SELECT文でありながら、実はデータベースを変更する関数を実行できるなので、「SELECTだから負荷分散してもいいよね」とやってしまうと、痛い目に...
–●例: currval(), nextval(), setval(), set_config()...

対策は...
–

レプリケーションモードでは、そういう関数を使っているSELECTはすべてのノードにSELECTを投げる–

マスタ/スレーブモードでは、必ずマスタ(プライマリ)に投げる

2011/4/26

Copyright(c)2011 Tatsuo Ishii

12Black/white関数リストの改良(2)●

問題は、どうやって書き込みを行う関数を見つけるか？
●

関数の属性がVOLATILEなら、DBを変更する、という議論はPostgreSQL 8.0より前では成り立たない●

結局人間が指定するしかない。それが Black/white関数リスト●

Black関数リスト–●

DBを変更する関数をコンマ区切りで指定する。このリストに現れない関数
はDBに書き込みを行わないと見なすWhite関数リスト–

DBを変更しない関数をコンマ区切りで指定する。このリストに現れない関数はDBに書き込みを行うと見なす●

Black、Whiteのどちらかを指定する●

通常はBlack関数リストを使う

2011/4/26

Copyright(c)2011 Tatsuo Ishii

13Black/white関数リストの改良(3)●●しかし、ユーザが大量に書き込みを行う関数を自作している環境では、Black関数リストの記述が長くなる
そこで、正規表現で関数を指定できるようにした●

my_profject_function1,my_profject_function2,my_profject_function3.
.. --> my_profject_function.*

2011/4/26

Copyright(c)2011 Tatsuo Ishii

14syslogへのログ出力が可能に●以前のおすすめ●●pgpool -n |/usr/sbin/rotatelogs/var/log/pgpool/pgpool.log.%A 86400

3.1では簡単にログをsyslogに出せる●

log_destination = 'syslog'●

syslog_facility = 'LOCAL0'●

syslog_ident = 'pgpool'

2011/4/26

Copyright(c)2011 Tatsuo Ishii

15

フォローマスターコマンド●●

pgpool-II+ストリーミングレプリケーション
で、3台以上のPostgreSQLを使う構成のときに有効フォローマスターコマンドがないと、なぜまずいか？を最初に説明します

2011/4/26

Copyright(c)2011 Tatsuo Ishii

16

PostgreSQL 3台構成で運用中
プライマリ
サーバ

Pgpool-IIでクエリ切り分け、死活監視、負荷分散を実施
更新/検索
問合わせ
問合わせ

DBクライアント2011/4/26

pgpool-II

検索問合わせCopyright(c)2011 Tatsuo Ishii

スタンバイ
サーバ #1スタンバイ
サーバ #2

17

プライマリサーバが故障！
Pgpool-IIでクエリ切り分け、死活監視、負荷分散を実施
更新/検索
問合わせ

X

問合わせ

DBクライアント2011/4/26

pgpool-II

検索問合わせCopyright(c)2011 Tatsuo Ishii

X
X

プライマリ
サーバ

X
スタンバイ
サーバ #1スタンバイ
サーバ #2

18

スタンバイサーバ#1が「昇格」(promote)旧プライマリサーバを切り離す

X

Pgpool-IIでクエリ切り分け、死活監視、負荷分散を実施

更新/検索
問合わせ

問合わせ

DBクライアント2011/4/26

pgpool-II

検索問合わせCopyright(c)2011 Tatsuo Ishii

X

プライマリ
サーバ

X
スタンバイ
サーバ #1スタンバイ
サーバ #2

19

しかし、スタンバイサーバ#2は浮いたままX

Pgpool-IIでクエリ切り分け、死活監視、負荷分散を実施

更新/検索
問合わせ

問合わせ

DBクライアント

pgpool-II

検索問合わせスタンバイサーバ#2にはレプリケーションされないので、どんどんデータが古くなっていく
delay_thresholdで対応するのが3.0までの考え方
2011/4/26

Copyright(c)2011 Tatsuo Ishii

X

プライマリ
サーバ

X
スタンバイ
サーバ #1スタンバイ
サーバ #2

20

フォローマスターコマンドとは●●

スタンバイが昇格した後に実行するスクリプトを定義できる機能
スクリプトには以下のものを書くのがおすすめ●●

スタンバイサーバ#2を停止(pg_ctlをssh経由で実行)
スタンバイサーバ#1をプライマリとして、スタンバイサーバ#2を「オンラインリカバリ」する(pcp_recovery_nodeの実行)

2011/4/26

Copyright(c)2011 Tatsuo Ishii

21

フォローマスターコマンドを使って、スタンバイサーバ#2を新プライマリサーバに同期させることができる

X
更新/検索
問合わせ

問合わせ

DBクライアント

プライマリサーバスタンバイ
サーバ #1

pgpool-II
検索問合わせ2011/4/26

Copyright(c)2011 Tatsuo Ishii

スタンバイ
サーバ #2

22

管理機能の改良●

SELECT文を使ってpgpool-IIの状態を取り出せる機能が追加●●●

CREATE FUNCTIONまたはCREATE EXTENTION用のモジュールの提供
この機能を使って、pgAdmin, phpPgAdminからpgpool-IIの状態をチェック可能になる！？
show pool_statusと同等の機能を持つpcpコマン
ド、およびユーザ定義関数も追加。SELECT文+WHERE句で、欲しい情報を検索できる

2011/4/26

Copyright(c)2011 Tatsuo Ishii

C関数として呼び出せる
pgpool-II管理コマンド
psqlなどのDBアプリ
pgpool-II
管理サーバ(pcpプロセス)

pgpool-II
親プロセス

pgpool-II
子プロセス

pgpool-IIの内部状態

PostgreSQLのC関数
PCP
ライブラリ
2011/4/26

Copyright(c)2011 Tatsuo Ishii

23

24

実行例
CREATE SCHEMA pgpool;
CREATE EXTENSION pgpool_adm WITH SCHEMA pgpool ;SELECT * FROM pgpool.pcp_node_info(0, 'localhost', 10, 9898,'pgpool_adm', 'password');host| port |status|weight----------+------+---------------------------------+------------localhost | 5491 | Waiting for connection to start |1.46602e+13

2011/4/26

Copyright(c)2011 Tatsuo Ishii

25

実行例
test=# SELECT * FROM pcp_pool_status('localhost', 10, 9898, 'postgres', 'pgpoolAdmin')test-# where item like '%time%';item| value |description------------------------+-------+-----------------------------------------------------------child_life_time| 0| if idle for this seconds, child exits
connection_life_time| 0| if idle for this seconds, connection closes
authentication_timeout | 60
| maximum time in seconds to complete client authentication
print_timestamp| 1| if true print time stamp to each log line
health_check_timeout| 10| health check timeout
pcp_timeout| 10| PCP timeout for an idle client
recovery_timeout| 60| max time in seconds to wait for the recovering node's
postmaster(7 rows)

2011/4/26

Copyright(c)2011 Tatsuo Ishii

26

今後の予定＆将来やりたいこと
●

今後の予定
●

●

pgpool-II 3.1の5月中の正式リリースを目指す

将来やりたいこと●

メモリーベースのクエリキャッシュ–

今もクエリキャッシュはあるが、今ひとつ使いにくく、性能が出ない●

大量INSERTの自動バッチ化●

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

2011/4/26

簡単に単一障害点を避ける
Copyright(c)2011 Tatsuo Ishii

27

参考URL
●

●

pgpool-IIのダウンロードはこちら●

http://pgfoundry.org/projects/pgpool/●開発ML(英語)もこちらから登録できます

メインWebサイト●●●

http://pgpool.projects.postgresql.org/SRA OSS's website●

http://pgpool.srasoss.jp●

日本語ML(pgpool-general-jp)登録もこちらからTwitter●@pgpool2

2011/4/26

Copyright(c)2011 Tatsuo Ishii