Memo/PostgreSQL/pgpool

pgpoolで可用性を高める
ベンチマーク
- JMeterでの負荷テスト
参考URL

Memo/PostgreSQL

pgpoolで可用性*1を高める †

↑

ダウンロード †

PgFoundry: pgpool: Project Filelist rpm, src他パッケージ

↑

紹介 †

pgpool開発元 http://www2b.biglobe.ne.jp/~caco/pgpool/
CVS http://pgfoundry.org/projects/pgpool/
コラム http://pcweb.mycom.co.jp/column/yetanother/021/

↑

特徴 †

開発元サイトより抜粋

メリット
- アプリケーションの変更の必要がありません
- どの言語でも使えます
- prefork型アーキテクチャ
- PostgreSQLへの接続数の制限が可能
- フェイルオーバー機能を装備
- レプリケーション機能を装備(2台まで)
- SELECT文の負荷分散機能を装備(PostgreSQL 7.4以上)

デメリット
- オーバヘッドがあります(2%～17%)
- すべてのlibpqプロトコルがサポートされていません(trust, passwordのみ)
- pgpool自体にpg_hba.confによるアクセス制限はかかりません
  (iptablesで制限をかける)
- template1, regressionという名前のデータベースはコネクションプールの対象になりません．
- 一時テーブルについて、フロントエンドがセッションを終了しても削除されません
- レプリケーションにおいて、同じ問い合わせを送っても異なる結果を返すようなデータはレプリケーションされません

↑

インストール †

ソースから

$ wget http://pgfoundry.org/frs/download.php/225/pgpool-2.5.2.tar.gz
$ tar xvfz pgpool-2.5.2.tar.gz 
$ cd pgpool-2.5.2
$ ./configure
$ make
$ su
# make install

以下にインストールされました。

/usr/local/bin/pgpool
/usr/local/etc/pgpool.conf.sample

インストール先を変更するには、以下のように指定する。

configure --prefix=path

rpm

$ wget http://pgfoundry.org/frs/download.php/500/pgpool-2.6-5.i686.rpm
# rpm -ivh pgpool-2.6-5.i686.rpm

↑

起動と停止 †

起動
```
# service pgpool start
```
停止
```
# service pgpool stop
```
再起動
```
# service pgpool restart
```

↑

起動スクリプト †

# vi /etc/rc.d/init.d/pgpool

pgpool

# chmod 755 /etc/rc.d/init.d/pgpool
# chkconfig --add pgpool
# chkconfig pgpool on

↑

基本テスト †

Playing with regression test

  $ cd /usr/local/src/postgresql-7.3.4/src/test/regress
  $ make all
  $ ./pg_regress --schedule=parallel_schedule --port=9999

Playing with benchmarking
Here is a brief explanation how to play with benchmarking using pgbench/PHP/ab.
Initialize the pgbench database.

  $ su - postgres
  $ createdb test
  $ pgbench -i test
  $ pgbench test

Prepare PHP script. Here is an example PHP script.

  <?php
   ini_set("track_errors", "1");
   define_syslog_variables();
   
   $con = pg_connect("dbname=test user=postgres port=9999");
   if ($con == FALSE) {
     syslog(LOG_ERR, "could not connect $php_errormsg");
     trigger_error("Could not connect to DB", E_USER_ERROR);
     exit(1);
   }
   $aid = rand(1,10000);
   pg_query($con, "SELECT * FROM accounts WHERE aid = $aid");
   pg_close($con);
 ?>

run ab.

 $ /usr/local/apache/bin/ab -c 100 -n 1000 "http://localhost/bench.php"

↑

設定 †

↑

セキュリティ †

pgpoolはpg_hba.conf設定ファイルには影響されない。TCP/IPによるネットワーク接続を受け付ける設定にすると、基本的に無条件でアクセスを許可してしまう。
よって、pgpoolで使用するポートをiptablesでローカルIPからのみアクセス許可しておく。

例：

# iptables -A INPUT -s 192.168.0.0/24 -p tcp --dport 5433 -j ACCEPT
# iptables -A INPUT -p tcp --dport 5433 -j DROP
# service iptables save
# service iptables restart

RedHat?系ならば

# vi /etc/sysconfig/iptables
-A Firewall-1-INPUT -s 192.168.0.0/24 -p tcp -m state --state NEW -m tcp --dport 5433 -j ACCEPT
-A Firewall-1-INPUT -p tcp --dport 5433 -j DROP
# service iptables restart

↑

現在の状態確認方法 †

pg_poolに接続後、「show pool_status;」コマンドで行う。

↑

具体的な構成例 †

以下のような構成の場合。

WEBサーバ(IP b.example.com)
- httpd(port 80)
- pgpool(port 5433)
- セカンダリ PostgreSQL(port 5432)
DBサーバ(IP example.com)
- マスター PostgreSQL(port 5432)
機能
- レプリケーション：ON
- SELECT文のロードバランシング：ON
- ヘルスチェックを行う間隔：30秒

「pgpool.conf.sample」を「pgpool.conf」にコピーして、設定を行う。

項目名	デフォルト	説明
num_init_children	32	preforkするpgpoolのサーバプロセスの数
max_pool	4	最大コネクション数。[ユーザ名:データベース名]のペアの種類の数だけをmax_poolに指定すると良い

設定ファイルの編集

# cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
# vi /usr/local/etc/pgpool.conf

/usr/local/etc/pgpool.conf(デフォルトから変更点のみ記述)

listen_addresses = '*'
port = 5433
backend_host_name = 'example.com'
backend_port = 5432
secondary_backend_host_name = 'b.example.com'
secondary_backend_port = 5432
replication_mode = true
health_check_period = 30
health_check_user = 'postgres'

pgpool、マスタ、セカンダリDB停止

b.example.com

# su -
# service pgpool stop
# service postgresql stop

example.com
```
# su -
# service postgresql stop
```

マスターDBからセカンダリDBへコピー
DBはOIDも合わせなければいけないので、pg_dumpallかファイルコピーを行う。
b.example.comで作業。
```
$ su - postgres
$ cd /var/lib/pgsql
$ rsync -avz -e ssh example.com:/var/lib/pgsql/data .
```

各DB、pgpoolの開始

example.com
```
# su -
# service postgresql start
```

b.example.com

# su -
# service postgresql start
# service pgpool start

接続できるかテスト
※Oracleのデフォルトのscott/tigerと同じ表を作成してある場合

$ su - postgres
$ psql -h example.com -U apache -p 5432 scott
$ psql -h b.example.com -U apache -p 5432 scott
$ psql -h b.example.com -U apache -p 5433 scott

1行追加してみる

insert into DEPT values(50,'dept1','loc1');
INSERT 3218347 1

OKのようだ。

追加できたか確認

select * from DEPT where deptno = 50;
 deptno | dname | loc  
--------+-------+------
     50 | dept1 | loc1

正常に追加できたのを確認。

マスタとセカンダリに正常に追加できたか確認。

$ psql -h example.com -U apache -p 5432 scott
select * from DEPT where deptno = 50;

$ psql -h b.example.com -U apache -p 5432 scott
select * from DEPT where deptno = 50;

正常に追加されている。

↑

session_pgsqlを併用している場合 †

※DB名「php_session」でセッション専用DBを定義している場合

# su -
# vi /etc/php.ini

session.save_handler = pgsql
extension=session_pgsql.so
session_pgsql.db="host=localhost user=apache password=パスワード dbname=php_session port=5433"

# service httpd restart

↑

障害時のテストと復旧 †

↑

障害発生 †

一時的にセカンダリを止めてみる。

b.example.com
```
# service postgresql stop
```

レコードを追加してみる。

$ psql -h b.example.com -U apache -p 5433 scott
insert into DEPT values(60,'dept2','loc2');
INSERT 3218354 1

追加成功。

現在の状態を確認

$ show pool_status;
replication_mode             | 1           | non 0 if operating in replication mode
current_backend_host_name    | example.com| current master host name
current_backend_port         | 5432        | current master port #
replication_enabled          | 0           | non 0 if actually operating in replication mode

マスタのみ稼働状態になっている事がわかる。
「replication_mode=1」で、「replication_enabled=0」なので「縮退運転中」とわかる。

↑

復旧 †

各DBとpgpoolの停止

b.example.com

# service pgpool stop
# service postgresql stop

example.com
```
# service postgresql stop
```

マスターDBからセカンダリDBへコピー

b.example.comで作業。

$ su - postgres
$ cd /var/lib/pgsql
$ rsync -avz -e ssh example.com:/var/lib/pgsql/data .

各DB、pgpoolの開始

example.com
```
# su -
# service postgresql start
```

b.example.com

# su -
# service postgresql start
# service pgpool start

状態確認

$ su - postgres
scott=> show pool_status;
replication_enabled          | 1           | non 0 if actually operating in replication mode

「replication_enabled=1」となり、レプリケーションモードで復旧した。

↑

問題/注意点 †

↑

session_pgsqlと併用すると、過負荷時に応答がなくなる †

Memo/PHP/PEAR/session_pgsqlと併用すると、高負荷時に切断されなくなりコネクションを使い果たし、新規接続ができなくなるようだ。
その場合、pgpoolとhttpdを再起動しなければいけない。

よって、session_pgsqlが使用する「php_session」DBをpgpool経由で接続せずに、直接DBに接続すれば良い。ただし、障害発生時には、同期を取る必要があり、その時に片方のセッションデータが消失する。

↑

デバックログの出力 †

/usr/local/bin/pgpool -d -f /usr/local/etc/pgpool.conf -n 2>&1 | logger -t pgpool -p local0.info

↑

replication_stop_on_mismatchオプションとサーバの時刻合わせ †

「replication_stop_on_mismatch = true」だと、不意に縮退モードに入ってしまう。
おそらくだが、insert時にCURRENT_TIMESTAMPを使うと内部時計の微妙な誤差が記録され、それを含む行をSELECTする時に不一致と見なされてしまうのではないか。
以下のように、毎時に時刻合わせをしても発生した。

20 0 * * * (/usr/sbin/ntpdate ntp.ring.gr.jp ; /sbin/hwclock --systohc) > /dev/null 2>&1

「now」や「CURRENT_TIMESTAMP」等時刻関数は頻繁に使うので、このオプションはfalseの方がよさそうである。

↑

ロードバランシングモード時はシーケンスは使えない †

以下のように、ロードバランシングモード=trueの場合、片方のDBにのみselect文を発行する事で速度向上を図っている。
/usr/local/etc/pgpool.conf

load_balance_mode=true

しかし、シーケンス文はselectで更新されるため、マスタとセカンダリの間に不整合が生じる。

× select nextval('シーケンス名');
× select setval('シーケンス名', 更新値);

よって、シーケンスを使う場合は、load_balance_modeは使えない。
pgpool公式サイトより引用:

たとえば乱数やトランザクションID，OID，SERIAL，シーケンス，CURRENT_TIMETSTAMP

↑

高リクエスト時、接続できなくなる †

現象
abで下記のようなSELECTしか行わない簡単なスクリプトに、同時1000リクエストを投げると発生。
再現性あり。
調べてみると、300リクエストを投げたあたりで、接続できなくなる。

OK ab -n 100 -c 100 http://b.example.com/~vod/pg_test.php
NG ab -n 1000 -c 100 http://b.example.com/~vod/pg_test.php
topでCPU負荷、及びpostmasterの負荷が下がったのを確認したが、回復されない。

回復方法
「pgpool -m fast stop」でも停止されない。
よって、「killall -9 pgpool」で停止。
その後、「service pgpool start」で開始したところ、起動した。

テストスクリプト

<?php 
$conn = pg_connect ("host=localhost dbname=scott user=apache password=apache123 port=5433");
if (!$conn) {
   echo "エラーが発生しました。\n";
   exit;
}
$result = pg_exec ($conn, "select * from EMP where JOB = 'SALESMAN'");
if (!$result) {
   echo "エラーが発生しました。\n";
   exit;
}
echo "row count:" . pg_num_rows($result) . "\n";
?>

↑

pgpool+pg_pconnect()だと接続できなくなる †

JMeterやabでのテスト中に接続できなくなる。
再現性あり。
pg_pconnect()もコネクションプーリングを行うので、やはり2重に使うのは良くないのかもしれない。

↑

Query failed: kind mismatch エラー †

pg_query(): Query failed: kind mismatch between backendsserver closed
the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

SELECT時にマスタとセカンダリの結果が違うと発生します。
違ったら、強制的に縮退モードに入るオプションもあります。
(デフォルトはそのクエリが失敗するのみ)

# vi /usr/local/etc/pgpool.conf
replication_stop_on_mismatch = true

↑

一時表は利用したら、明示的に削除する †

デメリットの項目にもあるが、コネクションがプールされる事によって、切断されなくなる。
よって、「create temp table」や「create temp sequence」等の一時表が削除されなくなる。
明示的に「drop table」や「drop sequence」を行わないと、「既にテーブルは存在します。」等のエラーが出る事になる。

↑

ベンチマーク †

↑

JMeterでの負荷テスト †

JMeter(v2.0.3 2005-04-07 (木) 17:12:42)
http://jakarta.apache.org/site/downloads/index.html
TECHSCORE -Java- １．JMeterの基本
http://www.techscore.com/tech/Java/JMeter/1.html

↑

結論/感想 †

　概ね問題なし。劇的な性能向上はないが、速度低下もない。

　テスト対象ページではPCのスペック不足でCPU負荷がネックとなり、コネクションプーリングの速度効果は確認できなかった。
pgpoolのオーバーヘッドは僅かなようなので、問題ないレベルだった。

　注意点として、pgpool+pg_pconnect()だと、途中でpgpoolに接続できなくなった。
よって、利用する際にはpgpool+pg_connect()が良いと思われる。

　RedHat? ES3はPostgreSQL-7.3.xなので負荷分散(SELECT文のみ)の効果は得られない。
7.4にバージョンアップした際は試してみると良いかもしれない。

↑

ハードウェア構成 †

WEBサーバ1台
- スペック
  イーレッツ - Be Silent MS6000
  http://www.e-lets.co.jp/product/pc-ms6000/
  
  CPU VIA Eden (600MHz) EBGA
  FSB 133MHz
  MEM 256MB
  HDD 40GB
DBサーバ1台
- 同上

↑

ソフトウェア構成 †

WEB＆セカンダリDBサーバ

OS Red Hat Linux release 9 (Shrike)
httpd httpd-2.0.40-21.11
php PHP 4.3.3 (cgi)
PostgreSQL 7.3.4 port=5432
pgpool 2.5.2 port=5433
session_pgsql 0.6.1
マスタDBサーバ

OS Fedora Core release 1 (Yarrow)
PostgreSQL 7.3.4 port=5432

↑

テストケース †

テスト対象
- とある業務用プログラム。クラスを多様し、割と重め。
  SELECT 文の発行、セッションへの参照書き込みあり。
  index2.php -> menu.phpへリダイレクトする。

テスト計画に追加したモジュール
- スレッドグループ
  - スレッド数 Ramp-Up(秒) ループ回数
    10 5 10
- HTTPリクエスト
- HTTPクッキーマネージャ
- 統計レポート
- グラフ表示
- ガウス乱数タイマ
  偏差(ミリ秒) 遅延時間オフセット定数(ミリ秒)
  2000 5000

PG負荷テスト.jmx

↑

テスト結果 †

	1秒あたりのリクエスト処理数	グラフ
pg_connect()のみ	1.3	File not found: "normal_pg_connect.png" at page "Memo/PostgreSQL/pgpool"[添付]
pg_pconnect()のみ	1.5	File not found: "normal_pg_pconnect.png" at page "Memo/PostgreSQL/pgpool"[添付]
pgpool+pg_connect()	1.5	File not found: "pgpool_pg_connect.png" at page "Memo/PostgreSQL/pgpool"[添付]
pgpool+pg_pconnect()	測定不能 pgpoolを再起動しても、96%で停止。	File not found: "pgpool_pg_pconnect_ng.png" at page "Memo/PostgreSQL/pgpool"[添付]

↑

参考URL †

JPUG北海道支部第一回勉強会「pgpool」http://www.mono-space.net/doc/pgpool.html
IT Pro 【PostgreSQLウォッチ】第9回負荷分散が可能になったpgpool 2.0 http://itpro.nikkeibp.co.jp/members/ITPro/oss/20040709/147053/