Memo/PostgreSQL
pgpoolで可用性*1を高める †
ダウンロード †
紹介 †
特徴 †
開発元サイトより抜粋
- メリット
- アプリケーションの変更の必要がありません
- どの言語でも使えます
- prefork型アーキテクチャ
- PostgreSQLへの接続数の制限が可能
- フェイルオーバー機能を装備
- レプリケーション機能を装備(2台まで)
- SELECT文の負荷分散機能を装備(PostgreSQL 7.4以上)
- デメリット
- オーバヘッドがあります(2%〜17%)
- すべてのlibpqプロトコルがサポートされていません(trust, passwordのみ)
- pgpool自体にpg_hba.confによるアクセス制限はかかりません
(iptablesで制限をかける)
- template1, regressionという名前のデータベースはコネクションプールの対象になりません．
- 一時テーブルについて、フロントエンドがセッションを終了しても削除されません
- レプリケーションにおいて、同じ問い合わせを送っても異なる結果を返すようなデータはレプリケーションされません
インストール †
以下にインストールされました。
/usr/local/bin/pgpool
/usr/local/etc/pgpool.conf.sample
インストール先を変更するには、以下のように指定する。
configure --prefix=path
起動と停止 †
- 起動
# 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停止
- マスター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の開始
- 接続できるかテスト
※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
障害時のテストと復旧 †
障害発生 †
一時的にセカンダリを止めてみる。
レコードを追加してみる。
$ 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の停止
- マスターDBからセカンダリDBへコピー
- 各DB、pgpoolの開始
- 状態確認
$ 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リクエストを投げたあたりで、接続できなくなる。
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での負荷テスト †
結論/感想 †
概ね問題なし。劇的な性能向上はないが、速度低下もない。
テスト対象ページではPCのスペック不足でCPU負荷がネックとなり、コネクションプーリングの速度効果は確認できなかった。
pgpoolのオーバーヘッドは僅かなようなので、問題ないレベルだった。
注意点として、pgpool+pg_pconnect()だと、途中でpgpoolに接続できなくなった。
よって、利用する際にはpgpool+pg_connect()が良いと思われる。
RedHat ES3はPostgreSQL-7.3.xなので負荷分散(SELECT文のみ)の効果は得られない。
7.4にバージョンアップした際は試してみると良いかもしれない。
ハードウェア構成 †
ソフトウェア構成 †
- 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 |
テスト結果 †
| 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 †
![[PukiWiki]](image/owl.png "[PukiWiki]")
![[添付]](https://dexlab.net:443/pukiwiki/index.php?plugin=attachref&attachref_no=2&attachref_opt=normal_pg_connect.png%2Czoom%2C320x256&refer=Memo%2FPostgreSQL%2Fpgpool&digest=635119b461b9cfb6589ca2f4d5250f67){kind=link}
![[添付]](https://dexlab.net:443/pukiwiki/index.php?plugin=attachref&attachref_no=3&attachref_opt=normal_pg_pconnect.png%2Czoom%2C320x256&refer=Memo%2FPostgreSQL%2Fpgpool&digest=635119b461b9cfb6589ca2f4d5250f67){kind=link}
![[添付]](https://dexlab.net:443/pukiwiki/index.php?plugin=attachref&attachref_no=4&attachref_opt=pgpool_pg_connect.png%2Czoom%2C320x256&refer=Memo%2FPostgreSQL%2Fpgpool&digest=635119b461b9cfb6589ca2f4d5250f67){kind=link}
![[添付]](https://dexlab.net:443/pukiwiki/index.php?plugin=attachref&attachref_no=5&attachref_opt=pgpool_pg_pconnect_ng.png%2Czoom%2C320x256&refer=Memo%2FPostgreSQL%2Fpgpool&digest=635119b461b9cfb6589ca2f4d5250f67){kind=link}