PostgreSQL 9.2 の新機能
NTTデータ 笠原 辰仁
最新のメジャーバージョンとなるPostgreSQL 9.2 が2012/9 にリリースされました。本記事では、PostgreSQL9.2の新機能をいくつかピックアップしてご紹介します。
9.2 では、ロックの改善やIndex Only Scanのサポートなどの性能改善をはじめ、レプリケーション機能の強化、JSON型や範囲型のサポート、ログメッセージや監視機能の充実など、多方面に渡る改良がバランスよく行われています。
性能強化
9.2ではIndex Only Scanやスケーラビリティの向上など、様々な性能強化が図られています。特に、Index Only Scanは9.2の目玉機能の一つとなっています。
Index Only Scan
読んで字の如く、インデックスのみを読むスキャン機能です。この機能により、インデックスキーの付与されている列だけを参照するSELECT処理速度がぐっと向上しています。従来のPostgreSQLでは、インデックスデータのスキャン->目的のテーブルの該当行の読み出し、という流れで処理を行う必要がありました。テーブルデータにそのレコードが可視かどうかの情報が付与されていたからです。このテーブルデータのスキャンを回避できるよう、バージョン8.4から実装されたVisibility Mapを活用したのです。もともとVisibility Mapは更新処理等で発生したガベージ箇所を記録し、VACUUM処理を必要最低限の箇所にのみ実施するための目的で使われていました。このVisibitiy Mapを活用することで、ガベージが無い、つまり可視である領域を判別し、テーブルスキャンをスキップすることができます。
なお、Visibility MapはPostgreSQLのクラッシュ後の再起動でも内容が維持されるように9.2で改良されており、再起動後にVacuumをかけ直さなくてもIndex Only Scanは正しく動作します。
Index Only Scanが実現されたため、以下の様にインデックスのみへのアクセスが実施されていることが分かります。
-- インデックスキーである aid 列のみを参照
=# EXPLAIN (ANALYZE on, COSTS off)
SELECT aid FROM pgbench_accounts WHERE aid < 100;
QUERY PLAN
-----------------------------------------------------------------
Index Only Scan using pgbench_accounts_pkey on pgbench_accounts
(actual time=0.021..0.049 rows=99 loops=1)
Index Cond: (aid < 100)
Heap Fetches: 0
Total runtime: 0.092 ms
その他のIndex Only Scanの特長を列挙します。
=# EXPLAIN (COSTS off, ANALYZE on) SELECT count(*) FROM pgbench_accounts;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (actual time=48.043..48.043 rows=1 loops=1)
-> Index Only Scan using pgbench_accounts_pkey on ★<-Index Only Scan
pgbench_accounts (actual time=0.024..31.020 rows=200000 loops=1)
Heap Fetches: 0
Total runtime: 48.100 ms
=# SET enable_indexonlyscan = off;
=# EXPLAIN (COSTS off, ANALYZE on) SELECT count(*) FROM pgbench_accounts;
QUERY PLAN
---------------------------------------------------------------------
Aggregate (actual time=82.246..82.246 rows=1 loops=1)
-> Seq Scan on pgbench_accounts
(actual time=0.009..45.124 rows=200000 loops=1)
Total runtime: 82.296 ms
★9.1まで
=# explain (COSTS off, ANALYZE ON)
SELECT c1 FROM test WHERE c1 = ANY (ARRAY['cffb','hdad','294d','12ja']);
QUERY PLAN
----------------------------------------------------------------
Bitmap Heap Scan on test
(actual time=0.065..0.089 rows=22 loops=1)
Recheck Cond: (c1 = ANY ('{cffb,hdad,294d,12ja}'::text[]))
-> Bitmap Index Scan on test_c1_idx
(actual time=0.054..0.054 rows=22 loops=1)
Index Cond: (c1 = ANY ('{cffb,hdad,294d,12ja}'::text[]))
★9.2から
=# explain (COSTS off, ANALYZE ON)
SELECT c1 FROM test WHERE c1 = ANY (ARRAY['cffb','hdad','294d','12ja']);
QUERY PLAN
-----------------------------------------------------------------
Index Only Scan using test_c1_idx on test
(actual time=0.025..0.056 rows=22 loops=1)
Index Cond: (c1 = ANY ('{cffb,hdad,294d,12ja}'::text[]))
Heap Fetches: 22
- count(*)の高速化も可能
- インデックスの付与された列検索だけでなく、件数カウントも高速に行えます。
- ANY(ARRAY[…])処理が高速化
- ANY(ARRAY[…])による検索処理で、Index ScanおよびIndex Only Scanが使用できるようになりました。従来はBitMap Heap Scanでの検索のみサポートされていました。一部の配列に対する検索性能の向上が期待できます。
- 性能がVisibility Mapの状態に左右される
- 更新が進むとVisibility Map上で可視と判断できるデータが減っていきます。Visibility MapはVACUUMで最新化されるため、Index Only Scanが十分に機能が機能しない場合は、適切に自動VACUUMや手動VACUUMが実行されているかを確認しましょう。
ロック改善によるCPUスケーラビリティの向上
本バージョンでは、ロック競合に関する多くの改善がなされました。メインは、ロックのFast-path機構の追加です。従来、特に競合が発生しない単純なSELECT処理であっても、各プロセス間で共有しているロック情報へアクセスを行う必要があったため、これがネックとなっていました。本バージョンでは、競合が発生しないと保証できる処理については、各プロセスでロック情報を保持するのみとしました。そして、競合が発生する場合に初めて共有のロック情報へアクセスするようになっています。この改善により、特に多数のプロセスが並列して参照処理を行うワークロードにおいて、格段にCPUスケーラビリティが向上しています。
WAL書き込みの改善による更新処理の性能向上
本バージョンでは、WAL書き込みの競合を低減する改善がなされました。従来は複数のプロセスがWALを(バッファからディスクへ)書き出す際、全てのプロセスがロック取得(待ち)、ロックの獲得、書き込み状況確認、(自分が書くべきログがまだ書かれていなければ)ログの書き込みを行っていました。本バージョンからは、ロック取得が失敗したら、ロックを取得せずに書き込み状況確認に移るようになりました。ロック取得の機会を減らすことで、WAL書き込みの並列度があがり、性能向上が図られています。
上記のロック改善によるCPUスケーラビリティ向上やWAL書き込み処理の改善による性能向上はSRA OSS様の講演資料で検証結果を見ることができます。
プランナの改善
プランナが大きく改良され、いくつかのケースにおける性能向上を期待できます。
パーティションテーブルに対する準備文の逐次プランニング
以前まで、分割キーをバインド値とするようなPrepared Statementは、汎用的な実行計画が作成されてしまうため、性能的に問題となることがありました。本バージョンから、Prepared Statementでは実行計画が固定化されず、実行時のパラメータ値を考慮した最適な実行計画を立てることができます。
下記は、PostgreSQL9.1にて、タイムスタンプを分割キーとした3つの子テーブルを持つ親テーブルに対する、分割キーをバインド値としたPrepared Statementの実行計画です。
=# PREPARE pf AS SELECT * FROM p_t WHERE c3 BETWEEN $1 AND $2;
=# EXPLAIN (COSTS off, ANALYZE on)
EXECUTE pf('2012-04-01 00:00:00', '2012-04-12 00:00:00');
QUERY PLAN
------------------------------------------------------------------------
Result (actual time=0.036..0.419 rows=265 loops=1)
-> Append (actual time=0.034..0.299 rows=265 loops=1)
-> Seq Scan on p_t (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((c3 >= $1) AND (c3 <= $2))
-> Index Scan using c_t_201204_c3_idx on c_t_201204 p_t
(actual time=0.031..0.172 rows=265 loops=1)
Index Cond: ((c3 >= $1) AND (c3 <= $2))
-> Index Scan using c_t_201205_c3_idx on c_t_201205 p_t
(actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((c3 >= $1) AND (c3 <= $2))
-> Index Scan using c_t_201206_c3_idx on c_t_201206 p_t
(actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((c3 >= $1) AND (c3 <= $2))
上記の通り、全ての子テーブルのインデックスを見ていることが分かります。一方、9.2では以下の様に該当のデータが格納されている子テーブルのインデックスのみ検索していることが分かります。
=# EXPLAIN (COSTS off, ANALYZE on)
EXECUTE pf('2012-04-01 00:00:00', '2012-04-12 00:00:00');
QUERY PLAN
-----------------------------------------------------------------------------------------
Result (actual time=0.019..0.416 rows=265 loops=1)
-> Append (actual time=0.018..0.267 rows=265 loops=1)
-> Seq Scan on p_t
(actual time=0.001..0.001 rows=0 loops=1)
Filter: ((c3 >= '2012-04-01 00:00:00'::timestamp without time zone) AND
(c3 <= '2012-04-12 00:00:00'::timestamp without time zone))
-> Index Scan using c_t_201204_c3_idx on c_t_201204 p_t
(actual time=0.016..0.159 rows=265 loops=1)
Index Cond: ((c3 >= '2012-04-01 00:00:00'::timestamp without time zone) AND
(c3 <= '2012-04-12 00:00:00'::timestamp without time zone))
一部のネストループ結合を伴うプランの改善
Parameterized Pathにより、特定の条件におけるネストループ結合の実行計画が最適化されるようになりました。ネストループ結合前のインデックス条件に、別テーブルの条件をプッシュダウンできるようになったため、効率の良い実行計画を作成できるようになっています。汎用的なケースではないものの、OUTER JOINを多用しているアプリケーションではそこそこ恩恵を受けることができそうです。
★9.1
=# EXPLAIN (COSTS off, ANALYZE on)
SELECT * FROM tbl t left join
(pgbench_accounts a join pgbench_branches b ON a.bid = b.bid)
on a.aid = t.id + 1 ;
QUERY PLAN
--------------------------------------------------------------------------
Hash Right Join (actual time=0.096..119.403 rows=100 loops=1)
Hash Cond: (a.aid = (t.id + 1))
-> Hash Join (actual time=0.015..99.442 rows=200000 loops=1)
Hash Cond: (a.bid = b.bid)
-> Seq Scan on pgbench_accounts a
(actual time=0.002..28.437 rows=200000 loops=1)
-> Hash (actual time=0.003..0.003 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pgbench_branches b
(actual time=0.001..0.002 rows=2 loops=1)
-> Hash (actual time=0.070..0.070 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 4kB
-> Seq Scan on tbl t (actual time=0.008..0.029 rows=100 loops=1)
Total runtime: 119.478 ms
★9.2
=# EXPLAIN (COSTS off, ANALYZE on)
SELECT * FROM tbl t left join
(pgbench_accounts a join pgbench_branches b ON a.bid = b.bid)
on a.aid = t.id + 1 ;
QUERY PLAN
--------------------------------------------------------------------------
Nested Loop Left Join (actual time=0.037..1.688 rows=100 loops=1)
-> Seq Scan on tbl t (actual time=0.009..0.071 rows=100 loops=1)
-> Nested Loop (actual time=0.010..0.013 rows=1 loops=100)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts a
(actual time=0.004..0.005 rows=1 loops=100)
Index Cond: (aid = (t.id + 1))
-> Index Scan using pgbench_branches_pkey on pgbench_branches b
(actual time=0.002..0.003 rows=1 loops=100)
Index Cond: (bid = a.bid)
Total runtime: 1.807 ms
レプリケーションの改良
カスケードレプリケーション
本バージョンから、レプリケーションをカスケードで組めるようになりました。従来はシングルマスタ・マルチスタンバイの構成しかできませんでしたが、スタンバイにさらにスタンバイを繋げられるようになっています。このため、スタンバイの増加に伴うマスタへの負荷の集中を回避できるようになりました。なお、カスケードする際のスタンバイへの接続は非同期モードでしか許されていません。性能や運用面を考えると、現状では妥当な仕様かもしれません。
新しい同期モードの追加
バージョン9.1までは、レプリケーションは同期モードと非同期モードの2種類がサポートされていましたが、その中間に位置づけられるモードが新たに追加されました。synchronous_commitパラメータにてremote_writeを設定することで、スタンバイに転送したログをメモリ上に書き込むところ(Write完了)までを保証するモードとなります。これにより、信頼性を多少下げることで、性能を向上させることができるようになっています。スタンバイにてログを送ることが保証できれば良い場合が多いには、性能と信頼性のバランスがとれたモードとなるでしょう。
9.2のレプリケーションのモードをsynchronous_commitパラメータの観点でまとめると以下の様になります。
- synchronous_commit=on : デフォルト。マスタとスタンバイのディスクへのログ同期書き込みまで待つ。9.1までの同期モードと同じ。
- synchronous_commit=remote_write: マスタのディスクへのログ同期書き込みとスタンバイのメモリへのログ書き込みまで待つ。
- synchronous_commit=local: マスタのディスクへのログ同期書き込みまで待つ。スタンバイに関しては一切待たない。9.1までの非同期モードと同じ。
- synchronous_commit=off: マスタとスタンバイに関してログ書き込みを一切待たない。
pg_xlog_location_diff関数の追加
レプリケーション状態にあるスタンバイが、マスタからどれくらいログの受け取りや再生が遅れているかを把握するために、従来はpg_stat_replicationビューの*localtion情報の差分を使いました。ただし、*location情報は16進で表現されるログの位置であるため、バイト数に変換する処理を挟み込む必要がありました。本バージョンではpg_xlog_location_diff関数により、16進数の差分をバイト数として分かりやすく把握することができます。
=# SELECT sent_location, write_location, flush_location, replay_location, pg_xlog_location_diff(sent_location, write_location) as send_diff_byte, pg_xlog_location_diff(sent_location, flush_location) as flush_diff_byte, pg_xlog_location_diff(sent_location, replay_location) as replay_diff_byte FROM pg_stat_replication; -[ RECORD 1 ]----+---------- sent_location | 0/C6A6248 write_location | 0/C6A5F48 flush_location | 0/C6A5DE0 replay_location | 0/C6A5B38 send_diff_byte | 768 flush_diff_byte | 1128 replay_diff_byte | 1808
pg_receivexlog
pg_receivexlogが、9.2のクライアントコマンド(psqlなど)に加えられました。pg_receivexlogは、稼働中のPostgreSQLインスタンスに接続し、ストリーム的にトランザクションログを受け取るためのツールです。レプリケーション時にマスタからトランザクションログを受け取る、スタンバイのwal receiverプロセスの動きをそのまま切り出したものです。コマンドを実行すると、接続オプションで指定されたホストのPostgreSQLに接続し、ログを受け取り、指定したディレクトリにWALファイルを生成してくれます。
■ pg_receivexlog 実行例 $ pg_receivexlog -D /var/lib/pgsql/my_xlog -h hostname -U postgres
pg_receivexlogは有用なツールですが、以下の特徴に注意しましょう。
- デフォルトではログ受信を永久に継続する
- 受信元への接続失敗や受信元のPostgreSQL停止が発生してもreceivexlogは接続をtryし続けます。オプションにより、エラー発生時にコマンドをexitすることができます。
- 非同期での受信となる
- 受信元からは非同期でログを受信するため、受信元のクラッシュのタイミングによっては、receivexlogでログの取りこぼしが発生します。
- バックアップラベルファイルやHistoryファイルなどは受信できない
- オンラインバックアップの実施や、PITRによるリカバリの処理において、pg_xlogディレクトリやアーカイブログに生成されるいくつかのファイルがあります。それらはreceivexlogでは受信できないため、WALのアーカイブ手段としての利用を検討する場合は注意しましょう。
SQL構文の強化
JSON型のサポート
新たにJSON型がサポートされました。内部的には構文チェックを伴ったテキスト型として扱われます。また、array_to_json、row_to_jsonという配列や行をJSON型へ変換を行う関数も用意されています。ただし、9.2ではJSON型のための演算子などは用意されていないため、JSON型のキーや要素に対する条件絞込みなどは行えません(※)。
=# SELECT * FROM j_t;
id | name | address
----+------+---------
1 | 鈴木 | 東京
=# SELECT row_to_json(t) from j_t t;
row_to_json
-----------------------------------------
{"id":1,"name":"鈴木","address":"東京"}
ちなみに、テーブルの特定の列のみ抽出し、列と値の組をJSON型として表現したい場合には、下記の様にサブクエリを使用する必要があります。
=# SELECT row_to_json(t) from (SELECT id, name FROM j_t) t;
row_to_json
------------------------
{"id":1,"name":"鈴木"}
=# SELECT row_to_json(row(id, name)) from j_t t;
row_to_json
----------------------
{"f1":1,"f2":"鈴木"} ★直接テーブル結果の列を射影した場合は列名部分がfnに変換される。
(※) json_out関数で一旦cstring型に変換し、さらにテキスト型にすることでLIKE文などを適用させることはできますが、複雑なSQLになってしまうため推奨はできません。9.2では、JSON型に対する便利なAPIが備わっているplv8などを利用し、演算子や処理を規定する方法が現実的です。
RANGE(範囲)型のサポート
もう一つの新しいデータ型として範囲型が追加されました。数値型と日付型の範囲を表すデータ型と、それらに対する演算子が利用可能です。範囲は[begin, end)といった形で表現され、`['と`]'はそれぞれ`以上'と`以下'、`('と`)'は`?より大きい'と`?未満'を表します。
従来はline型などの幾何データ型を使うことで実現可能でしたが、より一般的なデータ型をベースに使うことができます。既にバージョン8.4から利用可能である排他制約と組み合わせることで、重なりを禁じる列などの定義が可能です。
=# CREATE TABLE r_t ( id int, t_range tsrange, EXCLUDE USING gist(t_range WITH&&) ★排他制約 ); =# INSERT INTO r_t VALUES (1, '[2012-01-01 00:00:00, 2012-02-1 00:00:00)'); INSERT 0 1 =# INSERT INTO r_t VALUES (2, '[2012-01-31 00:00:00, 2012-02-1 00:00:00)'); ERROR: conflicting key value violates exclusion constraint "r_t_t_range_excl"★排他制約違反 (略)
範囲型には便利な演算子がいくつか用意されており、包含や重なり部分を抽出することも可能です。ただし、結果が複数の非連続的な範囲に分割されてしまう場合はERRORとなりますので注意しましょう。
★1-100までと30-120までの共通部分 =# SELECT int4range(1,100) * int4range(30, 120); ?column? ---------- [30,100) ★1-100までと30-100までの差分 =# SELECT int4range(1,100) - int4range(30, 100); ?column? ---------- [1,30) ★1-100までと30-50までの差分。この場合[1-30)と[50-100)に分断されるためERROR =# SELECT int4range(1,100) - int4range(30, 50); ERROR: result of range difference would not be contiguous
NOT VALID CHECK
9.2では、NOT VALID CHECK制約を利用できるようになりました。これは、現在のテーブルに後付けでCHECK制約を付与し、新規にINSERT/UPDATEされたデータに対してのみ制約を行使できる機能です。通常のCHECK制約は、付与する際に既存データに対して全件確認が行われますが、それをスキップすることができます。あらかじめユーザ側で制約違反がないことを保証できる、もしくはある時点以降に挿入・更新されるデータにだけ制約を設けたい場合にはとても便利な機能です。
=# CREATE TABLE c_t (c1 int); =# INSERT INTO c_t VALUES(1), (2), (3); =# ALTER TABLE c_t ADD CHECK ( c1 < 3 ) NOT VALID; =# INSERT INTO c_t VALUES(3); ERROR: new row for relation "c_t" violates check constraint "c_t_c1_check" DETAIL: Failing row contains (3).
ちなみに、NOT VALID CHECK制約を設けたテーブルの既存データに対する確認は、ALTER TABLE VALIDATE CONSTRAINTを使い、NOT VALID CHECK制約を指定することで可能です。
=# ALTER TABLE c_t VALIDATE CONSTRAINT c_t_c1_check; ERROR: check constraint "c_t_c1_check" is violated by some row
NOT INHERIT CHECK
従来、継承(INHERITED)した子テーブルは継承元の親テーブルの制約も継承していました。そのため、子テーブルと親テーブルで異なる制約を設けることが手軽に行えませんでした。例えば、パーティショニングを行ったテーブルに対するINSERT処理において、子テーブルの制約に合致しないものは、基本的に親テーブルに差し込まれていました。ただ、親テーブルには何もINSERTさせたくない場合には、子テーブルと独立した制約を親テーブルに設定する必要があります。これを可能にしたのがNOT INHERITです。親テーブルの作成時にCHECK制約にNO INHERITオプションを付与することで本機能が有効になります。
=# CREATE TABLE p_t (
id serial,
body int not null
CHECK (body IS NULL) NO INHERIT
);
=# CREATE TABLE c_t1 (
CHECK ( body > 0 AND body <= 100 )
) INHERITS (p_t);
=# CREATE TABLE c_t2 (
CHECK ( body > 100 AND body <= 200 )
) INHERITS (p_t);
★親にも子にも入れたくないデータ
=# INSERT INTO p_t(body) VALUES(300);
ERROR: new row for relation "p_t" violates
check constraint "p_t_body_check"
DETAIL: Failing row contains (1, 300).
運用やメンテナンス機能の強化
スタンバイノードからのバックアップ取得
レプリケーションを構成しているスタンバイからオンラインバックアップを取得できるようになりました。マスタに負荷をかけず、スタンバイからバックアップを取得したいケースが多かったと思われますので、嬉しい改良ですね。ただし、おなじみのpg_start_backup()関数などはやはりスタンバイでは実施することができません。pg_basebackupコマンドでバックアップを行いましょう。
autovacuum処理におけるI/O統計値の記録
特に、autovacuum の遅延に関するチューニングに有用な改善です。今回の改善により、autovacuumのログにautovacuum処理で発生したReadとWriteのI/Oの統計値(MiB/s)が記録されるようになりました。ユーザは、各autovacuumでのI/O帯域消費量を確認できるため、autovacuumの遅延が必要か否か、また遅延設定が十分に効果をなしているかを確認することができます。下記はログの出力例です。
LOG: automatic vacuum of table "postgres.public.test": index scans: 1
pages: 0 removed, 167 remain
tuples: 10000 removed, 10000 remain
buffer usage: 538 hits, 2 misses, 1 dirtied
avg read rate: 0.341 MiB/s, avg write rate: 0.170 MiB/s ★ <- 新規のI/O情報
system usage: CPU 0.00s/0.00u sec elapsed 0.04 sec
I/O時間の追跡
9.2から、いくつかのコマンド、システムビューにてI/Oに要した時間を取得できるようになりました。取得できるI/O情報ですが、これはread(共有バッファへブロックを読み込む)もしくはwrite(共有バッファ外へブロックを書き出す)にかかる処理にかかった時間を表します。track_io_timingパラメータを有効にすることで、以下の情報を取得できるようになります。
=# EXPLAIN (ANALYZE on, BUFFERS on, COSTS off)
SELECT * FROM pgbench_accounts ;
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on pgbench_accounts (actual time=0.035..26.740 rows=100000 loops=1)
Buffers: shared hit=672 read=968
Total runtime: 47.995 ms
=# SET track_io_timing TO on;
=# EXPLAIN (ANALYZE on, BUFFERS on, COSTS off)
SELECT * FROM pgbench_accounts ;
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on pgbench_accounts (actual time=0.043..26.823 rows=100000 loops=1)
Buffers: shared hit=704 read=936
I/O Timings: read=2.312 ★ readに2.312msec消費
Total runtime: 48.214 ms
=# SELECT datname, blk_read_time, blk_write_time FROM pg_stat_database; datname | blk_read_time | blk_write_time -----------+---------------+---------------- template1 | 0 | 0 template0 | 0 | 0 postgres | 135.563 | 155.845 test | 31.021 | 24.59
=# SELECT calls, total_time::numeric(10,2), substring(query, 1, 20),
blk_read_time::numeric(10,2), blk_write_time::numeric(10,2)
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
calls | total_time | substring | blk_read_time | blk_write_time
-------+------------+----------------------+---------------+----------------
22692 | 575301.64 | UPDATE pgbench_branc | 0.06 | 0.00
22692 | 127133.35 | UPDATE pgbench_telle | 0.03 | 0.00
22692 | 1574.61 | UPDATE pgbench_accou | 193.46 | 0.12
- EXPLAIN ANALYZEコマンドによるSQL実行時I/O時間
- EXPLAIN ANALYZEコマンド実施時にBUFFERS onとすることで、対象SQLの実行時に発生したI/O時間(ミリ秒)を確認できます。
- pg_stat_databaseシステムビューによるDB単位で発生したI/O時間
- track_io_timingが有効な状態であれば、pg_stat_databaseにてDB単位でのI/O時間を確認できます。DB単位でI/O傾向を俯瞰するのに便利です。
- pg_stat_statementsシステムビューによるSQL単位で発生したI/O時間
- track_io_timingが有効な状態であれば、pg_stat_statementsにてSQL単位でのI/O時間を確認できます。SQL単位でI/O傾向を俯瞰するのに便利です。
なお、track_io_timingを有効にすると内部的にI/Oの前後で時間計測の処理が入るため、そのオーバーヘッドに注意が必要です。オーバーヘッドは、その仕組み上プラットホームで異なります。 pg_test_timingコマンドを使うことで、該当のシステムでのオーバーヘッドを推測することができます。
サーバクラッシュ時の実行クエリロギング
PostgreSQLでは、一つのバックエンドが異常終了(SIGKILLで終了させられたなど)した場合、PostgreSQL全てのバックエンドプロセスが一旦再起動されます。今回の改善では、そのようなクラッシュが引き起こされた場合に、引き金となったプロセスが実行していた処理をログに記録するようになりました。クラッシュの原因が外部要因(killコマンドなどで落とされた) or 内部要因(処理内容にSegmentation faultなどがある)なのかを切り分けしやすくなっています。下記はログの出力例です。
LOG: server process (PID 11997) was terminated by signal 11: Segmentation fault DETAIL: Failed process was running: SELECT pg_crash_function(); LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process
pg_stat_statementsのSQL文の正規化
SQLの監視に有用なpg_stat_statementsモジュールですが、準備文を使用しないプレインなSQLの場合、パラメータ値が違うだけで異なるSQLと見なされてしまい、有用な統計値を取得することが困難でした。本バージョンからは、パラメータ値の部分を正規化することで、プレインなSQLでも準備文の使用時と同様の統計値を得ることができます。
以下は、9.1と9.2のそれぞれでpgbench で負荷をかけた後のpg_stat_statements結果の一部です。 9.2では条件の実値の部分が"?"に置き換えられ、正規化されていることが分かります。これにより、効率的な性能監視が可能です。
★9.1
=# SELECT query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
query
------------------------------------------------------------------------
UPDATE pgbench_branches SET bbalance = bbalance + -3728 WHERE bid = 1;
BEGIN;
END;
★9.2
=# SELECT query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 3;
query
--------------------------------------------------------------------
UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?;
互換性のあるデータ型変換時のテーブル再構築の回避
従来はALTER TABLEによるデータ型変換時には、テーブルの再構築が実施されていましたが、互換性のあるデータ型であればメタデータの変更だけが実施されるようになり、ほぼ瞬時にALTER TABLEが終了するようになっています。
ビューのSECURITY_BARRIERオプションと関数のLEAKPROOFオプション追加
セキュリティや権限の関係上、一般ユーザに対してテーブルの一部分のみを参照させるため、ビューを活用することがあります。しかし、PostgreSQLの内部処理と関数の特性を利用することで、本来は見えてはならないデータをのぞき見ることができました。 例えば以下の様なテーブルがあるとします。
=# SELECT * from all_t; id | data ----+------------ 1 | secret 2 | non_secret
このテーブルのnon_secretのみを参照させるビューを次の様に定義します。
=# CREATE VIEW non_secret AS SELECT * FROM all_t WHERE id = 2; =# SELECT * from non_secret; id | data ----+------------ 2 | non_secret
このビューに対して次の様な関数を定義して実施してみます。
CREATE OR REPLACE FUNCTION bad(text) RETURNS boolean AS $$ BEGIN RAISE NOTICE '%',$1; RETURN true; END $$ COST 0.9 LANGUAGE plpgsql; =# SELECT * FROM non_secret WHERE bad(non_secret.data); NOTICE: secret NOTICE: non_secret id | data ----+------------ 2 | non_secret
このように、ビューのスコープでは見えないはずのsecretデータがNOTICE経由で見えています。本来であれば、ビューの処理におけるid=2が評価された結果が関数でフィルタリングされるはずですが、関数のコストが=演算子より低いコストで定義されているため、先にbad関数がテーブルに対して実施されてしまった結果です。このような形でスコープ外のデータを見えてしまうことを防ぐため、9.2ではビューにSECURITY_BARRIERオプションが追加されました。
=# CREATE VIEW non_secret2 WITH (security_barrier) AS SELECT * FROM all_t WHERE id = 2; =# SELECT * FROM non_secret2 WHERE bad(non_secret2.data); NOTICE: non_secret id | data ----+------------ 2 | non_secret
SECURITY_BARRIERオプションにより、一旦ビューとして正しい結果をスキャンした後、関数を通す仕組みになっているため、先のようなリークは発生していません。実行計画を見ると分かりやすいです。
=# explain SELECT * FROM non_secret2 WHERE bad(non_secret2.data);
QUERY PLAN
------------------------------------------------------------------
Subquery Scan on non_secret2 (cost=0.00..25.45 rows=2 width=36)
Filter: bad(non_secret2.data)
-> Seq Scan on all_t (cost=0.00..25.38 rows=6 width=36)
Filter: (id = 2)
ただし、この作用により性能が幾分か犠牲になっています。これを回避するため、信頼できる関数にはLEAKPROOFオプションを付与することで、SECURITY_BARRIERオプションを付与したビューに対しても、従来と同様な処理方法を行わせることが可能です。このLEAKPROOF付きの関数はスーパーユーザのみが定義できます。
=# CREATE OR REPLACE FUNCTION bad(text) RETURNS boolean AS
$$
BEGIN
RAISE NOTICE '%',$1;
RETURN true;
END
$$
COST 0.9 LEAKPROOF LANGUAGE plpgsql;
=# SELECT * FROM non_secret2 WHERE bad(non_secret2.data);
NOTICE: secret
NOTICE: non_secret
id | data
----+------------
2 | non_secret
psqlのメタコマンド改良
psqlで使用できるメタコマンドに便利な機能が追加されています。
- \x auto による自動での列行表示入れ替え
- 従来、\x コマンドにより、長大な列を持つテーブルを縦に表示できました。本バージョンからは \x auto とすることで、画面に行が納まらない場合に自動的に縦表示にしてくれます。
- \setenv による環境変数の動的設定
- \setenvにより環境変数がその場で指定できるようになりました。
- \timing によるエラー処理の時間表示
- 従来は成功した処理のみの時間が表示されていましたが、エラーとなった処理も時間が表示されます。タイムアウトのテストなどで有用です。
プロセスの省力化
バックグランドプロセスの活動のSleep/Wakeupなどが、ラッチ機構の実装により効率的に行われるようになりました。その結果、無駄なプロセス活動が削減されています。性能や機能に直接は寄与しませんが、ホスティング事業やクラウド事業にとっては僅かな省力化も大きな節電等に繋がるため、経費削減となります。
互換性に関する注意
本バージョンを使用するにあたり、注意すべき点を挙げます。
システムカタログ/システムビューの一部変更
システムカタログやシステムビューの列が一部変更されています。システムビューは監視等で使用することが多いので注意してください。
- pg_tablespace.spclocationが削除
- うっかりテーブルスペース本体のディレクトリを不正に削除/移動した場合、即座にPostgreSQLが検知できるようになっています。また、PostgreSQL停止時に手動でテーブルスペース本体のディレクトリを移動できるようになりました。pg_tblspace.spclocationの代わりに、本バージョンからpg_tablespace_location() 関数が提供されています。
- tsvector型の統計情報値列の追加
- 特に全文検索用途で使用されるtevector型の統計情報値を表す列(most_common_elemsとmost_common_elem_freqs)がpg_statsシステムテーブルに追加されています。
- pg_stat_activity.procpidとcurrent_queryの列名変更
- それぞれpidとqueryいう名称になりました。pg_stat_activityビューは監視用のスクリプトなどで使用することが多いため、注意が必要な変更です。
EXTRACT (epoch from timestamp) の結果が一部変更
EXTRACT(epoch from timestamp)の基準とする開始点が、UTCではなくローカルのタイムゾーンになりました。振る舞いが大きく変わるので注意してください。timestamp with timezoneにを対象にした処理については変更ありません。
★9.1の結果 =# SELECT EXTRACT (epoch FROM '2012-01-01 00:00:00'::timestamp); date_part ------------ 1325343600 ★9.2の結果 =# SELECT EXTRACT (epoch FROM '2012-01-01 00:00:00'::timestamp); date_part ------------ 1325376000
まとめ
いかがでしたでしょうか？性能改善もさることながら、レプリケーションの強化あり、新しいデータ型の追加あり、そして監視やユーティリティの強化ありと、様々な領域にバランス良く伸びを見せているバージョンだということを感じていただけたでしょうか？現在では、すでに9.3の開発が進められており、着々と魅力的な機能が搭載されつつあります。今後もPostgreSQLの進化から目が離せませんね。
外部リンク
PostgreSQL 9.2 に関する情報の収集には、以下のサイトが参考になります。
- Let's PostgreSQL 9.2リリースニュース(プレスキットやリリースノートへのリンクを含みます)
- [SlideShare] PostgreSQL 9.2の新機能
- PostGreSQL 9.2 最新動向セミナー(SRA OSS)
(2012年10月31日公開)