最強のOSS DB
PostgreSQLの最新技術情報
SRA OSS, Inc. 日本支社
高塚 遙

2008-10-03 OSC Tokyo

最強のOSS DB
PostgreSQL

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan2

「お使いのデータベース製品は何ですか？」Linux World 2008 アンケート N=1310
2% 1%

1% 1%

複数回答可

2%
4%

27%
14%

PostgreSQL

MySQL

24%

2008/10/03 OSC TokyoSQL Server

Oracle24%

Copyright(c) 2008 SRA OSS, Inc. Japan

PostgreSQL
PowerGres を含む
MySQL
OracleSQL Server
DB2
Symfoware
Sybase
HiRDB
Firebird
その他

もちろん「弊社ブースに近寄ったバイアス」「Linuxバイアス」があります。
3

順調に成長を続けるPostgreSQL

再帰SQL
組み込み
レプリケーションマルチプロセッサ最適化

9 00000
8 00000
7 00000

WALログ
TOAST

6 00000
5 00000

PL/pgSQL
Concurrent
Vacuumトリガ

4 00000

HOT
全文検索Windows対応

3 00000

行ロック
MVCC

2 00000

副問い合わせマルチバイト1 00000
0
6 .1 .1

6 .2 .1

2008/10/03 OSC Tokyo

6 .3 .1

6 .4 .2

6 .5 .3

7 .0.3

7 .1 .3

7 .2 .8 7 .3 .1 9 7 .4 .1 7 8 .0.1 3 8 .1 .9

Copyright(c) 2008 SRA OSS, Inc. Japan

8 .2 .4

8 .3

8 .4

4この５年間でPostgreSQLの性能はどれだけ向上したか？
DBマガジンの記事より●
最新版8.3の更新性能の向上に注目！
●

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan5

検索性能の向上

164%の性能向上

7.4(2003/11)から8.3(2008/2)の間に

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan

6

一括ロード性能の向上
200%の性能向上

7.4(2003/11)から8.3(2008/2)の間に1(15GB)

億件のロード2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan7

更新性能の向上

300%の性能向上

7.4(2003/11)から8.3(2008/2)の間に

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan8

PostgreSQL 8.4の動向●

8.4からは”commitfesta”方式で開発●●

一ヶ月おきに「集中コミット月間」を設け，パッチをレビュー，コミットする

現在のリリース目標は2009年の第一四半期

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan

9

8.4注目の新機能(1)•Synchronous Log Shipping Replication
WALログをスレーブに転送することによってレプリケーションを実現●
warm standbyと違って同期式レプリケーションなので，コミットしたデータの消失がない●

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan10

warm standbyにおけるデータ消失
アーカイブログが満杯になると転送転送前にクラッシュすると，データ消失

未転送

アーカイブ

未アーカイブ
WALログ

未転送

アーカイブ前にクラッシュ
すると，この分のデータが消失

ログ
バッファ
アクティブ側
2008/10/03 OSC Tokyo

スタンバイ側Copyright(c) 2008 SRA OSS, Inc. Japan11

データ消失を最小限にするためには？
アーカイブログセグメントが満杯(16MB)になるまではアーカイブログが転送されないので，最悪16MB分のトランザクションデータが消失●
archive_timeoutを設定することによって，データ消失を最小限にすることが可能●
しかし，DBがアイドルでも，必ず16MBデータが転送されるので非効率●
しかも，archive_timeoutは無限に小さくできない
のでデータ消失の可能性がわずかだが必ず残る●

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan12

Synchronous Log Shipping
Replication による解決
アーカイブログセグメント単位ではなく，ログレコード単位に毎回転送● ログレコードの転送が完了するまではコミットしない●
コミットしたデータはstandby側にも転送されていることが保証されているので，データの消失がない●

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan13

Synchronous Log Shipping
Replication の評価 （NTT-OSSによる）●特長
データ消失なし●
透過的なレプリケーション(レプリケーションすることによる機能上の制限なし)●
性能への影響はわずか(7%)●●

制限事項スタンバイ側のDBは利用できないので，レプリケーションノードを増やしてスケールアップを狙うことはできない●
Slony-Iやpgpoolを必要とするユースケースを完全に置き換えるものではない●
ディザスタリカバリ目的でスタンバイ側を遠隔設置は？
●2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan

14

8.4注目の新機能(2)
再帰SQLのサポート● リスト，木構造などのデータ構造から再帰的にデータを取得できる●●●

住友電工とSRA OSSの協力により開発中●●

今はアプリで処理するか，PL/pgSQLなどの関数を使わなければならなかった
ユーザ企業が積極的にPostgreSQLの開発に関与する新しい形態(エコシステム)

実装はSQL標準の”WITH RECURSIVE”句を採用

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan15

単純な再帰SQL
WITH RECURSIVE t(n) AS (VALUES (1) – 非再帰項
UNION ALLAL
SELECT n+1 FROM t WHERE n < 100 -- 再帰項)
SELECT sum(n) FROM t;sum-----5050

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan16

サンプルデータ
神奈川支部
東日本営業本部

横浜営業所
関内営業所

厚木工場
東京本社

戸塚営業所

製造事業本部
千葉工場
西日本営業本部

大阪支部

梅田営業所
難波営業所

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan17

RDBでの表現
事業所名
東京本社
東日本営業本部
神奈川支部
横浜営業所
関内営業所
戸塚営業所
製造事業本部
厚木工場
千葉工場
西日本営業本部
大阪支部
梅田営業所
難波営業所
2008/10/03 OSC Tokyo

管轄事業所名東京本社
東日本営業本部
神奈川支部
横浜営業所
横浜営業所
東京本社
製造事業本部
製造事業本部
東京本社
西日本営業本部
大阪支部
大阪支部
Copyright(c) 2008 SRA OSS, Inc. Japan

人員数
1 005050201510502002005050202018

東日本営業本部以下の人員数を求める
再帰SQLの定義
WITH RECURSIVE 東日本営業人員(事業所名, 人員数) AS(SELECT 事業所名, 人員数 FROM 人員構成表
WHERE 事業所名 = '東日本営業本部'
UNION ALL
SELECT PEN.事業所名, PEN.人員数 FROM 人員構成表 ASPEN,
東日本営業人員 AS EBP
WHERE PEN.管轄事業所名 = EBP.事業所名)

一つのＳＱＬ

SELECT SUM(人員数) FROM 東日本営業人員 ;SELECT本体
sum----出力145(1 row)
2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan

19

組織階層の「深さ」を表示
WITH RECURSIVE 東日本営業人員 （事業所名, レベル, 人員数） AS （SELECT 事業所名, 1, 人員数 FROM 人員構成表
WHERE 事業所名 = '東日本営業本部'
UNION ALL
SELECT PEN.事業所名, EBP.レベル+1, PEN.人員数
FROM 人員構成表 AS PEN, 東日本営業人員 AS EBP
WHERE PEN.管轄事業所名 = EBP.事業所名）
SELECT * FROM 東日本営業人員;

事業所名 | レベル | 人員数-------------+--------+-------東日本営業本部 |
1 |
50
神奈川支部
|
2 |
50
横浜営業所
|
3 |
20
関内営業所
|
4 |
15
戸塚営業所
|
4 |
10(5 rows)
2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan20

pgpool-II 2.1リリース！
PostgreSQL専用のオープンソースクラスタソフト●PostgreSQL 7.4から8.3まで対応●
レプリケーション，負荷分散，パラレルクエリなどの機能●管理用GUIツールあり●

18万以上のダウンロード実績● 最新版では，主に，オンラインリカバリと，拡張プロト
コル回りの安定性の改善●

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan21

なぜpgpool?
●pgpoolを導入するメリット
レプリケーションにより，データのリアルタイムバックを実現● 同期型レプリケーションなので，複数サーバ間でデータのずれが起きない● 負荷分散機能により，検索系の性能アップが可能●
PostgreSQLアプリケーションの修正は最小限●
DML，ラージオブジェクトもレプリケーション可能●
運用を止めずにDBの切り離し，復帰，追加が可能●●

PostgreSQLの可用性，性能を向上可能

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan22

某地方自治体における
pgpool-IIの導入事例
pgpool-II
JBoss

PostgreSQL

mod_proxy_balancer
mod_proxy_balancer

ApacheApachepgpool-II
JBoss

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan

PostgreSQL

23

pgpool-II デモシステムの構成
pgpoolAdmin
FireFox

PHP 5.1.6

PostgreSQL
8.3(5432)

Apache 2.2.3

pgpool-II(9999)

管理ツール
稼働環境

PostgreSQL
8.3(5433)
サンプル
アプリケーション

psql/pgbenchLinux
2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan24

オンラインリカバリの仕組み
アプリケーション

pgpoolPostgreSQL
5434 (ダウン中)

PostgreSQL
5433

rsync

DBデータ

First stage

アプリケーション

Second stage

First stage中は更新も受付可能

pgpool

一時的にコネクション受付をブロック

DBデータrsync中の更新データ

PostgreSQL
5433

PostgreSQL
5434 (ダウン中)

DBデータ

DBデータ

rsync中の更新データ

rsync

roll forward
トランザクションログ

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan25

デモシナリオ
PostgreSQL
8.3(5432)

pgbenchpgpool-II(9999)

オンライン
リカバリ

X

PostgreSQL
8.3(5433)

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan26

pgpool-IIの関連情報●

pgpool-II開発サイト●●

http://pgfoundry.org/projects/pgpool/「PostgreSQLには絶対pgpool-II」(Think IT)●

http://www.thinkit.co.jp/article/98/1/

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan27

ご清聴ありがとうございました！

2008/10/03 OSC Tokyo

Copyright(c) 2008 SRA OSS, Inc. Japan

28