PostgreSQL8.3の新機能
SRA OSS, Inc. 日本支社
石井 達夫

データベース製品のシェア

O r a c le
P o st g r eS Q L
M yS Q L
S Q L S er v er
DB2F ir eB ir d
P o w er G r es
その他

Linux World 2007における弊社アンケート結果による(複数回答可能)2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan3

PostgreSQLの歴史
800000750000700000

マルチプロセッサ対応

650000600000

HOT
全文検索

WALログ
TOAST

550000500000450000Windows
対応

PL/pgSQL

400000350000トリガ

Concurrent
Vacuum

300000250000

行ロック
MVCC

2000001 50000

副問い合わせマルチバイト1 00000
50000
06 .1 .1

2007/11/17

6 .2 .1

6 .3 .1

6 .4 .2

6 .5 .3

7 .0 .3

7 .1 .3

7 .2 .8

7 .3 .1
9Copyright(c)2007 SRA OSS, Inc. Japan

7 .4 .1
78 .0 .1
38 .1 .9

8 .2 .4

8 .3

4

トランザクション／秒

Po st g r eSQ L 8 .1 と8 .2 の C PU スケーラビリティの比較
37500
35000
32500
30000
27500
25000
22500
20000
1 75001 50001 25001 0000
7500
5000
2500
0

8 .2
8 .1

12345678CPU 数
2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan

5MySQL

2007/11/17

PostgreSQL

Copyright(c)2007 SRA OSS, Inc. Japan

http://tweakers.net/より引用

6

PostgreSQL 8.3でどこが良くなっている？
●

性能更新性能の改善●
順スキャンの改善●
特定のSQLの高速化
●

●

管理機能
ソート処理のモニタリング● インデックスアドバイザー●
ログ項目の追加
●

●

SQL機能
●

2007/11/17

更新可能カーソル
Copyright(c)2007 SRA OSS, Inc. Japan

7

PostgreSQL 8.3の改良点：性能

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan

8

HOT(1)
従来の更新処理の問題点
更新を繰り返すと更新連鎖(update chain)が長くなる●
更新されないインデックスも追加される●
VACUUMをしないとどんどん遅くなる●●

巨大なテーブルでは頻繁なVACUUMは困難

version 0a100

version 1b100

version 2c100

最新版d100

テーブル
2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan

インデックス9HOT(2): HOTとは
HOT: Heap Only Tupleの略●
更新対象列にインデックスカラムが含まれていない
場合に有効●
VACUUMの必要性を減らす●●●

局所的なVACUUM処理をリアルタイムで実行

UPDATEを繰り返してもテーブル，インデックスが肥大化しない

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan

10

HOT(3): HOTの動作原理
Root tuple
version 0
Heap Only Tuple

100X

version 1
Heap Only Tuple

X

version 2
Heap Only Tuple
最新版
リンク付け代えテーブル
2007/11/17

削除

Copyright(c)2007 SRA OSS, Inc. Japan

インデックス11

HOT(4): HOTの効果
MLに流れた開発者自身によるベンチマーク●
pgbenchによる900万行のデータ●
メモリ2GB，共有バッファ128MB
●
更新＋挿入
●
autovacuumあり●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan

12

HOT(5): トランザクション性能比較
TPS

2200
2000
1 800
1 600
1 400
1 200

H O T ありH O T なし

1 000
800
600400200
2007/11/17

0Copyright(c)2007 SRA OSS, Inc. Japan13

HOT(6): DBサイズの比較
Block数
2200002000001 80000
1 60000
1 40000

オリジナルサイズ
H O T ありH O T なし

1 20000
1 00000
80000
60000
40000
20000
0
2007/11/17

テーブルサイズインデックスサイズ

Copyright(c)2007 SRA OSS, Inc. Japan14

同期スキャン(1)
●

問題点
●

共有バッファに入りきらないような大きなテーブルを複数セッションが順スキャンすると，キャッシュヒット率が低下する
2

345

6

セッション1はブロック6までアクセス済

バッファキャッシュ

345

6

バッファキャッシュにはブロック6までキャッシュ済

1

3456

セッション2はブロック1からスキャンをスタート．キャッシュ
ヒット率01

2007/11/17

2

Copyright(c)2007 SRA OSS, Inc. Japan15

同期スキャン(2)
●

解決策
●

常にテーブルの先頭からスキャンするのではなく，バッファキャッシュにキャッシュされているブロックからスキャン開始

2

3

4

5

6

セッション1はブロック6までアクセス済

バッファキャッシュ

3

4

5

6

バッファキャッシュにはブロック6までキャッシュ済

1

3

4

5

6

セッション2はすでにキャッシュされているブロック3からスキャン開始

1

2007/11/17

2

Copyright(c)2007 SRA OSS, Inc. Japan

16

同期スキャンの効果
平均実行時間（秒）
2200
2000
1 800
1 600
1 400
1 200
1 000
800
600
400
200

PostgreSQL 8.2.5と8.3beta1を比較●
Core 2 Duo+4GBmem, Linux●
データ件数1億5千万(20GB)●
20セッションが15秒間隔で次々に順スキャン●

08 .22007/11/17

8 .3
Copyright(c)2007 SRA OSS, Inc. Japan

17

同期スキャン利用上の注意点
常にテーブルの先頭からスキャンするわけではないので，行の返却順序が一定にならない●
行の返却順序が問題になる場合は，ORDER BYを使って明示的にソートする●●

2007/11/17

実際にregression testの一部に変更が加えられている

Copyright(c)2007 SRA OSS, Inc. Japan18

同期コミット
コミットした後同期書き込みが終わるまで待たずにクライアントにコミット完了を返す●
fsync=offと同等の高速化●
再投入可能なデータやセンサーデータを扱う場合に効果的●
fsync=offとの違い●

GUCの「synchronous_commit」でセッション中にオン・オフ可能● クラッシュしても最近のトランザクションが消えるだけで，データ不整合は起きない●
fsync=offではどこまでデータが消えるか予測できない
2007/11/17
Copyright(c)2007 SRA OSS, Inc. Japan19●

同期コミットと非同期コミットの性能比
pgbenchを使用●
scale factor = 10●
pgbench -N -c 10 -t
100を5回実行した結果の平均
●
1 .6
1 .5
1 .4
1 .3
1 .2
1 .1
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0

2007/11/17

同期コミット

非同期コミット
Copyright(c)2007 SRA OSS, Inc. Japan

20

ORDER BY ... LIMITの高速化
Webアプリケーションに良く使われるクエリ●
入力行全体をソートするのではなく，LIMIT分だけソートする●
場合によっては非常に効果的●“OFFSET”付には適用されないので注意●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan

21

ORDER BY LIMITの高速化の効果
実行時間(秒)1000万件のデータ●
SELECT * FROM
accounts ORDER BY
abalance LIMIT 101 20●

1101 009080706050403020100
P o st g r eS Q L 8 .3
2007/11/17

P o st g r eS Q L 8 .2
Copyright(c)2007 SRA OSS, Inc. Japan22

負荷分散チェックポイント
●

問題点
●

チェックポイント時に一時的に性能が低下する●●

background writerでは代用できない●●

dirty bufferの掃出し
トータルのI/Oが増えてしまうため解決策
チェックポイントを負荷分散し，少しずつdirty bufferを掃出す●
checkpoint_completion_target
●

●

効果

●
2007/11/17

性能が一定にCopyright(c)2007 SRA OSS, Inc. Japan23

WALログの省略●

不必要なWALログの出力がなくなったことにより，ログ領域の削減，処理の高速化●

2007/11/17

pgbenchでは，COPYの前にTRUNCATEを実行することにより，COPYでログ出力されない

Copyright(c)2007 SRA OSS, Inc. Japan24

PostgreSQL 8.3の改良点：
DB運用管理機能

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan25

インデックスアドバイザー●「インデックスアドバイザー」とは？
必要なインデックスがあれば指摘してくれるような機能●
多くの商用DBMSに見られるツール●●

PostgreSQLでの実装●「インデックスアドバイザー」の機能自体はユーザが実装
実際にはベンダーやpgfoundryで開発されることを期待？
●
実装的にはPostgreSQLからダイナミックロードされるモジュールになる●●

PostgreSQLにはそのためのインターフェイス（フック）を設ける●

2007/11/17

EXPLAINコマンドから呼び出されるフックなど
Copyright(c)2007 SRA OSS, Inc. Japan26

インデックスアドバイザーの実装例
ユーザ定義インデックスregression=# load '/home/tgl/pgsql/advisor';
アドバイザーのロードLOAD
regression=# explain select * from fooey order by unique2,unique1;
QUERY PLAN---------------------------------------------------------------Sort (cost=809.39..834.39 rows=10000 width=8)
Sort Key: unique2, unique1-> Seq Scan on fooey (cost=0.00..145.00 rows=10000 width=8)
Plan with hypothetical indexes:
Index Scan using <hypothetical index> on fooey (cost=0.00..376.00 rows=10000 width=8)(6 rows)

ユーザ定義インデックスアドバイザーの出力2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan27

ソート処理のモニタリング

test=# set trace_sort to on;
test=# explain analyze select * from accounts order by abalance;
LOG: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = f
LOG: switching to external sort with 7 tapes: CPU 0.02s/0.00u sec elapsed 0.03 sec
LOG: performsort starting: CPU 0.51s/0.00u sec elapsed 0.52 sec
LOG: finished writing final run 1 to tape 0: CPU 0.52s/0.00u sec elapsed 0.52 sec
LOG: performsort done: CPU 0.52s/0.00u sec elapsed 0.52 sec
LOG: external sort ended, 1331 disk blocks used: CPU 0.96s/0.00u sec elapsed 0.96 sec
QUERY PLAN----------------------------------------------------------------Sort (cost=16191.82..16441.82 rows=100000 width=97) (actual time=527.619..771.426
rows=100000 loops=1)
Sort Key: abalance
Sort Method: external sort Disk: 10648kB-> Seq Scan on accounts (cost=0.00..2588.00 rows=100000 width=97) (actualtime=0.021..213.239 rows=100000 loops=1)
Total runtime: 969.161 ms
2007/11/17
Copyright(c)2007 SRA OSS, Inc. Japan
28(5 rows)

Auto vacuum●

Auto vacuumとは●●

VACUUMを自動実行

8.2まではデフォルトではauto vacuum無効●
統計情報の収集が必要だったため●●

8.3からはデフォルトでauto vacuum有効● 追加された設定項目●

log_autovacuum_min_duration●
track_counts●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan29

ログ項目の追加●

log_lock_waits
ロック待ち状態のトランザクションを表示
LOG: process 12274 still waiting forAccessExclusiveLock on relation 16467 of
database 16384 after 1003.966 ms
STATEMENT: lock table t1;●●

checkpointログ
2007-09-24 21:35:45 JST LOG: checkpoint starting: time

2007-09-24
21:38:15 JST LOG: checkpoint complete: wrote 1693 buffers (55.1%)
log_checkpoints
0 transaction log file(s) added, 0 removed, 0 recycled; write=150.021 s,
sync=0.014 s, total=150.062 s●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan30

PostgreSQL 8.3の改良点：
SQL機能

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan31更新可能カーソル(1)●

従来のカーソルは「更新不可能」だった
BEGIN
DECLARE c CURSOR FOR SELECT * FROM T1;
FETCH 2 FROM c;i| j---+----1 | foo
2 | bar(2 rows)
DELETE FROM t1 WHERE i = 1;
MOVE BACKWARD ALL IN c;
FETCH ALL FROM c;i| j---+----1 | foo
2 | bar(2 rows)

SELECT * FROM t1;
i| j---+----1 | foo
2007/11/17(1 row)

削除されていないように見える

実際には削除されている
Copyright(c)2007 SRA OSS, Inc. Japan32

更新可能カーソル(2)●「FOR UPDATE」を付けて更新可能カーソルへ
BEGIN
DECLARE c CURSOR FOR SELECT * FROM T1 FOR UPDATE;FETCH 2 FROM c;i| j---+----1 | foo
2 | bar(2 rows)DELETE FROM t1 WHERE CURRENT OF c;
MOVE BACKWARD ALL IN c;
FETCH ALL FROM c;i| j---+----削除されている
1 | foo(1 rows)

SELECT * FROM t1;
i| j---+----1 | foo(1 row)
2007/11/17

新しい構文

実際に削除されている
Copyright(c)2007 SRA OSS, Inc. Japan

33

JIS 2004とは？
●従来の日本語文字コード規格(JIS X 0208+JIS X0212)を拡張した文字コード規格(JIS X 0213)の
通称●●

ただし，JIS 2004はJIS X 0208+JIS X 0212の完全上位互換ではない

多数の漢字や符合を追加●

一部の「機種依存文字」も取り込み

Windows Vistaなどが採用し，注目される● 人名などが充実しているため，官公庁や地方公共団体での採用が期待される●

2007/11/17Copyright(c)2007 SRA OSS, Inc. Japan34

JIS 2004で使えるようになった
機種依存文字の一部(NEC特殊文字)①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳
ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ㍉㌔㌢㍍㌘㌧㌃㌶㍑㍗㌍㌦㌣㌫㍊㌻㎜㎝㎞㎎㎏㏄㎡㍻〝〟№㏍℡㊤㊥㊦㊧㊨㈱㈲㈹㍾㍽㍼≒≡∫∮√⊥∠∟⊿∵∩∪

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan35

JIS 2004対応の実装●

JIS 2004対応のEUC(EUC_JIS_2004)，シフトJIS(SHIFT_JIS_2004)をエンコーディングとして追加●

JIS 2004はEUC_JP, SJISの上位互換ではないためUNICODEはそのまま利用可能●
UNICODE <--> EUC_JIS_2004,
SHIFT_JIS_2004の変換が可能●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan36

全文検索機能
contribだったtsearch2が本体に取り込まれ，標準インストールで使えるようになった●
既存のテーブルのテキスト型列にインデックスを定義するだけで，全文検索が可能に●
日本語の場合には「わかち書き」にする前処理が必要●●

2007/11/17

SRAOSSでmecabを使った関数を提供，SQLだけで分かち書きができる

Copyright(c)2007 SRA OSS, Inc. Japan

37

使用例
●

全文検索インデックスの追加●●

CREATE INDEX t1index ON t1 USING
gin(to_tsvector('english',wakachi(text_column)));

全文検索の実行●2007/11/17

SELECT * FROM t1 WHERE '日本語' @@to_tsvector('english',wakachi(t));

Copyright(c)2007 SRA OSS, Inc. Japan38

XML対応SQL:2003のXML対応をサポート●
XMLデータ型●●●

well-formedチェック可能XMLPARSE (DOCUMENT '<?xml

version="1.0"?><book><title>Manual</title><chapter>...</chapter><book>')

テーブル定義のXMLエキスポート●
テーブルデータのXMLエキスポート●
XPath 1.0のサポート●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan39

実行例
test=# select table_to_xmlschema('branches',false,false,'');
table_to_xmlschema------------------------------------------------------------------------------------------------------<xsd:schema
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:simpleType name="INTEGER"><xsd:restriction base='xsd:int'><xsd:maxInclusive value="2147483647"/><xsd:minInclusive value="-2147483648"/></xsd:restriction></xsd:simpleType><xsd:simpleType name="CHAR"></xsd:simpleType><xsd:complexType name="RowType.test.public.branches"><xsd:sequence><xsd:element name="bid" type="INTEGER" minOccurs="0"></xsd:element><xsd:element name="bbalance" type="INTEGER" minOccurs="0"></xsd:element><xsd:element name="filler" type="CHAR" minOccurs="0"></xsd:element></xsd:sequence></xsd:complexType><xsd:complexType name="TableType.test.public.branches"><xsd:sequence><xsd:element name="row" type="RowType.test.public.branches" minOccurs="0" maxOccurs="unbounded"/></xsd:sequence></xsd:complexType><xsd:element name="branches" type="TableType.test.public.branches"/></xsd:schema>
2007/11/17(1 row)

Copyright(c)2007 SRA OSS, Inc. Japan40

PostgreSQL 8.3のリリース時期
現在ベータテスト中●
年内には正式リリース？
●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan41

参考URL●

わかち書き関数●2007/11/17

www.sraoss.co.jp/opensource/

Copyright(c)2007 SRA OSS, Inc. Japan42

pgpool-II 2.0リリース！
●

pgpool-IIとは●●

PostgreSQL専用のクラスタソフト

pgpool-IIの機能●

性能向上
コネクションプーリング●
負荷分散●
パラレルクエリ●●

信頼性向上障害自動検知，フェイルオーバ●
レプリカの作成によるメディア障害への対応●
運用中に障害ノードの復旧可能(オンラインリカバリ)●

2007/11/17

Copyright(c)2007 SRA OSS, Inc. Japan43