PostgreSQL V9.2 新機能のご紹介

PostgreSQL 9.2 最新動向セミナー（２） ※講演後公開版

2012-06-26 14:40～15:30
SRA OSS, Inc. 日本支社
高塚 遥 harukat@sraoss.co.jp
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

1

PostgreSQL のこれまでと現在

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

2

改めて・・・ PostgreSQL とは
 代表的なオープンソースRDBMS Ingres(1970～ UCB) を先祖に持つ
 PostgreSQL 6.0 (1996 ～) から １5年以上の歴史

 BSDタイプのライセンスで配布
 PostgreSQL Global Development Gruop とUniversity of California が著作権を持つ

 ひとつのオーナー企業、オーナー個人を持たない PostgreSQL開発に時間を割く技術者を提供している企業がいくつかある／その企業群も少しずつ変遷している
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

3

PostgreSQL開発体制
支援企業
Contributors

Major Contributors

Core Team藤井雅雄
板垣貴裕
原田均
石井達夫
：
３０～４０名Josh Berkus
Tom LanePeter Eisentraut
Magnus Hagander
Bruce Momjian
Dave Page

etc...

開発コミュニティ
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

4

PostgreSQLの歩み
PostgreSQL のコードサイズとリリース
同期レプリケーション
外部テーブル

120000

コードサイズ (byte)

100000

プリペアド
ステートメント

80000

60000

外部キー、
JOIN構文

ビットマップ
スキャン

更新の高速化

レプリケーション
Windows 64bit

並列実行
VACUUM

40000

20000

トランザク
ションログ

スキーマ

PITR、
WindowsCPU
スケール

ウィンドウ関数再帰SQL0

PG 7.0 PG 7.1 PG 7.2 PG 7.3 PG 7.4 PG 8.0 PG 8.1 PG 8.2 PG 8.3 PG 8.4 PG 9.0 PG 9.1|
2001年
4月

|
2002年
11月

|
2005年
1月

|
2006年
12月

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

|
2009年
7月

|
2011年
9月
5

PostgreSQL 9.2 の拡張

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

6

CPUスケール対応
 PostgreSQL 9.2 で コア数32～64までスケール⇒ PostgreSQL 8.2 で改良 コア数 8～12 まで
64コアマシンで参照pgbench※PGCon 2012
EnterpriseDB社 Robert Haas の発表資料より引用

・Fast Pathロック・直列化部分を短時間に・WALディスク同期を改善
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

7

書き込みトランザクション改善
 小規模性能サーバ（２コアマシン）
 標準 更新pgbenchでは差は僅か
pgbench 更新テスト (-s 10, -j 2)

多接続で ５～１０％程度の 300改善がみられるのみ。
250

ストレージ律速になるとバージョン差が出ない。
TPS

200
150

9.2beta2
9.1.4

100

・Fast Pathロック

CPU: AMD Turion II Neo
Memory:6GB HDD: SATA500GB
CentOS 6.2 x86_64

50・直列化部分を短時間に・WALディスク同期を改善

0050

100

150

200

250

300

350

400

450

接続数

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

8

COMMIT遅延の改善
 ２コアマシンでもテスト内容によっては大幅性能向上
多接続／遅延付きトランザクションのテスト
900

独自シナリオpgbench
800
間に sleep を入れた更新。

9.2beta2
9.1.4700

従来 COMMIT遅延が目立ったものが解消されている。

600

TPS500400300・Fast Pathロック

200・直列化部分を短時間に100・WALディスク同期を改善

010100200300400500

600

700800

接続数

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

9

Index Only Scan （１） インデックスだけを読んで処理できる 行バージョンがあるため難しかったが Visibility Map を参照して 「可能なときには行う」 ことで対応=# EXPLAIN select min(i) from t;

postgresexecutor

QUERY PLAN------------------------------------Result
VACUUM後、更新なければInitPlan 1 (returns $0)
適用可能-> Limit-> Index Only Scan using tix on tvisibility
Index Cond: (i IS NOT NULL)
map
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

10

Index Only Scan （２） 適用されれば大きな効果
400

インデックスカラムのバルク参照

 読み込みブロック減350
 バッファヒット率向上
300
 min, max, count250などに有効
200《全50万行から30万行を参照》

150=> SELECT aid FROM
pgbench_accounts
WHERE aid < 300000;

100duration(ms)hit_ratio(%)
block(MB)500index-only
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

通常スキャン11

IBM Power780 による測定結果（１）参照pgbench -s 100, -S -c 64 -j 32 -T 300

HWスレッド×4=64なので、16個までCPUスケール

CPU: POWER7 64core × HW4thread
Memory:64GB Storage: Fibre/RAID

64コアスケールを確認。

性能伸びない領域も9.1 より断然速い

9.2 で POWERチップむけ
固有の改修あり接続数でなく、CPUコア数を増やす

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

12

IBM Power780 による測定結果（２）Index Only Scan を pgbench -s 10000, -S -c 64 -j 32 -T 300 でテスト

「CPUスケール」と相まって、30～40倍性能差

Index Only Scan のある 9.2 が圧倒的な性能優位を示す。
《バッファに載らないデータ規模》
テーブル
125 GB
インデックス
42 GB
物理メモリ
64 GB
《参照カラムにインデックス作成》

少コア領域でも５倍以上の差異

CREATE INDEX
ON pgbench_accounts(aid, abalance);

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

13

レプリケーション拡張（１） カスケード構成に対応
 マスターサーバの負荷軽減策に
 オンライン物理バックアップをスタンバイ側から取れる
プライマリサーバ

Postgres

WAL
WAL
データ
データ

スタンバイサーバ

スタンバイサーバ
レプリ
ケーション

レプリ
ケーション

Postgres

レプリ
ケーション

Postgres

スタンバイサーバ
Postgres

非同期のみ
ベースバックアップ
WAL取得

バックアップサーバ
pg_receivexlog

WAL転送

pg_basebackup
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

14

レプリケーション拡張（2）
同期レプリのオプション

 remote_write モードが追加
 より軽量、低信頼な同期レプリケーション
synchronous_commit = 'remove_write'
高速安全# 'off', 'local', 'remote_write' or 'on'

off

COMMIT時のWAL書き込み動作
ディスク同期を待たない

local

自機のディスク同期を待つ

remote_write 自機のディスク同期を待つ
レプリ先への書き込み完了を待つ
自機のディスク同期を待つ
on
レプリ先のディスク同期を待つ
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

プライマリサーバ

Postgrespg_xlog

レプリ
ケーション

WAL
データ

スタンバイサーバ
Postgrespg_xlog15

レプリケーション拡張（３） pg_receivexlog ユーティリティが追加
 接続してWALを受領して保存するユーティリティ
ＤＢサーバ

特性

Postgres
WAL
データ

バックアップサーバ
pg_receivexlog

単位
経路archive_command
プッシュ型

pg_receivexlog
プル型

ファイル単位

ストリーム
ネットワーク経由

cpコマンド等
サーバ側 アーカイブに失敗しの動作ている限り pg_xlogから消えない

pg_xlog 内のWALは構わず消える。要求
した古いWALが無いとエラーになる。

組み合わせ指定例：
archive_command = 'sleep 5 && test -f /mnt/server/archivedir/%f'
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

16

Range data type (1) 範囲を表現するデータ型

データ型

要素データ型

int4range

int

int8range

bigint gist インデックスが利用可能

numrange

numeric

 8.4 で導入された排他制約と組み合わせ

tsrangetimestamp

tstzrange

timestamptz

 重なりがあったら制約違反
 これまでは BOX型くらいしか使える対象がなかった

daterangedate 重なり検出する演算子 && など演算子

意味

<@

含まれる

《排他制約の定義例》&&重なりがある

ALTER TABLE reservation
ADD EXCLUDE
USING gist (during WITH &&);*

共通範囲+合併範囲>=

範囲の大小比較

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

17

Range data type (2) ユーザ定義の範囲型を利用可能

大小比較ができる型=> CREATE TYPE float4range AS RANGE (SUBTYPE = float4);

 境界値を含む／含まないを指定できる=> SELECT '(1.5, 2.1]'::float4range;
float4range------------(1.5,2.1]

空範囲 '(,)'
開範囲 '(0, NULL)'なども表現可能=> SELECT 1.5::float4 <@ '(1.5, 2.1]'::float4range;?column?
---------境界値を含まないfのでCopyright © 2012 SRA OSS, Inc.
JapanFalse
All rights になるreserved.

18

JSONデータ型
 JSON構文チェック 配列や行からJSON型への変換関数がある=> SELECT * FROM t;
これはid | name |tags
文字列の----+------+-----------------配列型
1 | 犬| {従順,賢い}2 | 猫| {自由}
3 | 虎| {強い,恐い,希少}

逆向きの変換手段は乏しい

JSONの配列になっている=> SELECT row_to_json(t) FROM t WHERE id = 3;
row_to_json---------------------------------------------------{"id":3,"name":"虎","tags":["強い","恐い","希少"]}Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

19

セキュリティ関連
 セキュリティバリア ビュー
 関数を通してアクセス不可のカラム内容が漏れることがありえたのをガード=> SELECT * FROM v1 WHERE f_leak(passwd);
NOTICE: f_leak => passwd12345
CREATE VIEW v1 WITH (security_barrier) AS..
ALTER VIEW v1 SET (security_barrier=true)

 LEAKPROOF 関数定義オプション
 「この関数は安全」を宣言

 SE-pgsql の対応範囲拡大

CREATE、DROP なども

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

デフォルト
false

20

その他の拡張（１） - 内部実装
 省電力
 補助プロセスが動作する回数を減らすことで、処理が無い時間帯においてサーバ省電力機能が効果的に働く
動作 11.5回／秒

 各種プランナ、executer改善 Parameterized Plan選択
 高速ソート 配列のコスト見積もり改善 ほか多数...

↓動作 0.4回／秒

プリペアドステートメント
であっても、必ずしもプラン固定しない

Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

21

Parameterized Plan の テスト=> PREPARE p1 (bigint)
AS SELECT * FROM pgbench_accounts WHERE aid < $1;
9.1 では=> EXPLAIN (COSTS OFF) EXECUTE p1(100000);
プラン固定
QUERY PLAN---------------------------------Seq Scan on pgbench_accounts
行数が多ければ
Filter: (aid < 100000::bigint)
Seq Scan=> EXPLAIN (COSTS OFF) EXECUTE p1(10);QUERY PLAN---------------------------------------------------Index Scan using pgbench_accounts_pkey
行数少なければon pgbench_accounts
Index Scan
Index Cond: (aid < 10::bigint)
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

22その他の拡張（2） - 管理機能など
 contrib/pg_stat_statement
 SQL集約が改善、 I/O 情報収集

SQL実行統計を収集する機能

 実行時統計情報の拡充
 pg_stat_bgwriter チェックポイント動作の情報
 pg_stat_database デッドロックカウンタ

 ALTER TABLE .. ALTER TYPE の改善
 必要なければ全レコード書き込みしない DROP INDEX CONCURRENTLY
 強いテーブルロックを取らない
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

インデックス再構築のオンライン実行を助ける機能
23

PostgreSQL 9.2 のリリース予定
HEAD

2010.72012.6.14

2011.6

2010.99.0リリース

2012.69.0.8リリース

2011.92012.6

9.1リリース

9.1.4リリース⇒ 今期は alpha版リリースは無かった
2012-05-14
9.2 beta1 リリース
2012-06-04
9.2 beta2 リリース
⇒ 続いて ＲＣ版リリースがある⇒ 例年通りの流れなら 2012年初秋 正式版リリースか
Copyright © 2012 SRA OSS, Inc. Japan All rights reserved.

24