PostgreSQL 9.1でのパーティション全体のソートの最適化

「PostgreSQL Advent Calendar」23日目です。

PostgreSQLにはパーティショニングテーブルというものがあります。今日はそのお話。
「カラム定義が同じ複数のテーブルに、データを分割して格納できる」機能といいますか、「テーブルに親子関係を持たせられる」といいますか、詳しくは、Let's PostgreSQLを参照。（丸投げ）

拙作「ふぁぼるっく」では、バックエンドのデータベースとしてPostgreSQLを採用しています。Twitterのつぶやきデータが大量に貯まり、SSDに載りきらないため、新しいデータはSSD上のテーブルスペース、古いデータはHDD上のテーブルスペースと分けて保存しています。
そこで検索は横断的にやりたいということで、パーティショニングテーブルを使っています。
しかし、どうにも重たい。要求は新しいもの順で表示するので、SSDだけにアクセスし、データが無ければHDDを見に行くような動きをしてほしいのです。

Twitterでぼやいてみたら、9.1からは「パーティション全体のソートの最適化」が行われていることを教えてもらいました。そういえば、9.1を使っていたんだった。ということで、追試してみました。

PostgreSQLで期間ごとに区切ってパーティショニングテーブルを使っているんだけど、その期間をORDER BYに入れた場合、小さい部分の受け持ちテーブルから順に見に行ってくれたりするんだろうか？いやー、この遅さなら、見てくれてないんだろうな。
— おさ (@osapon) 12月 15, 2011

たとえば、Twitterのstatus_idの大きい順に指定件数だけ取ってきたいんだけど、古いテーブルも見てるんならやっぱり遅いよな。アプリ側で各テーブルを順に見て、指定件数分取得できるまで古いテーブルを掘り返す方が速いんだろうか。
— おさ (@osapon) 12月 15, 2011

それってアプリ側でやることじゃないよなぁ。せっかく制約も掛けて、そのテーブルに含まれるstatus_idが分かってるんだからさ。
— おさ (@osapon) 12月 15, 2011

EXPLAINの結果はどうでしょうか？ 9.1ではパーティションのORDER BYが強化されています→ bit.ly/q5jnOV RT @osapon: 期間ごとに区切ってパーティショニングテーブルを使っているんだけど、その期間をORDER BYに入れた場合…
— 日本PostgreSQLユーザ会 (@PostgreSQL_JP) 12月 15, 2011

@PostgreSQL_JP 9.1を使っているんですが、他のテーブルとJOINしたりしてるので、そのせいか全テーブルを見に行っちゃってるんですよね。条件減らして、JOINしなければうまくソートしてくれました。もう少しSQLを調整してみます。
— おさ (@osapon) 12月 15, 2011

@osapon なるほどなるほど。他のテーブルも含めた絞り方がキーなんですね。
— 日本PostgreSQLユーザ会 (@PostgreSQL_JP) 12月 15, 2011

PostgreSQL.1の「パーティション全体のソートの最適化」が無効になる条件判明。SELECT a.*,b.* FROM A JOIN B ON ～ WHERE B.hoge ORDER BY A.idでB.hogeにインデックスが無い時。うー、その項2値しか入らないんよ。
— おさ (@osapon) 12月 15, 2011

試してみた環境はPostgreSQL9.1.2、テーブル構成はこんな感じ。

CREATE TABLE t_parent
(
id smallint NOT NULL, -- 社員番号
kind smallint NOT NULL, -- 役職
fullname text NOT NULL, -- 名前
CONSTRAINT t_parent_pkey PRIMARY KEY (id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE t_parent
OWNER TO postgres;
COMMENT ON COLUMN t_parent.id IS '社員番号';
COMMENT ON COLUMN t_parent.kind IS '役職';
COMMENT ON COLUMN t_parent.fullname IS '名前';

CREATE TABLE t_child10
(
-- 継承 from table t_parent:  id smallint NOT NULL,
-- 継承 from table t_parent:  kind smallint NOT NULL,
-- 継承 from table t_parent:  fullname text NOT NULL,
CONSTRAINT t_child10_pkey PRIMARY KEY (id ),
CONSTRAINT id_chk_1 CHECK (id >= 10 AND id < 20)
)
INHERITS (t_parent)
WITH (
OIDS=FALSE
);
ALTER TABLE t_child10
OWNER TO postgres;

CREATE INDEX t_child10_kind_idx
ON t_child10
USING btree
(kind );

CREATE TABLE t_child20
(
-- 継承 from table t_parent:  id smallint NOT NULL,
-- 継承 from table t_parent:  kind smallint NOT NULL,
-- 継承 from table t_parent:  fullname text NOT NULL,
CONSTRAINT t_child20_pkey PRIMARY KEY (id ),
CONSTRAINT id_chk_2 CHECK (id >= 20 AND id < 30)
)
INHERITS (t_parent)
WITH (
OIDS=FALSE
);
ALTER TABLE t_child20
OWNER TO postgres;

CREATE INDEX t_child20_kind_idx
ON t_child20
USING btree
(kind );

INSERT INTO t_child10 VALUES (10, 1, '社長');
INSERT INTO t_child10 VALUES (11, 2, '部長A');
INSERT INTO t_child10 VALUES (12, 2, '部長B');
INSERT INTO t_child10 VALUES (16, 3, '課長A');
INSERT INTO t_child10 VALUES (19, 4, '社員A');
INSERT INTO t_child20 VALUES (25, 2, '部長C');
INSERT INTO t_child20 VALUES (23, 3, '課長B');
INSERT INTO t_child20 VALUES (27, 3, '課長C');
INSERT INTO t_child20 VALUES (24, 4, '社員B');
INSERT INTO t_child20 VALUES (26, 4, '社員C');
INSERT INTO t_child20 VALUES (28, 4, '社員D');

親テーブルt_parent、子テーブルt_child10、t_child20。
主キーはそれぞれidの社員番号。t_child10はidが10代、t_child20は20代という制約が付いている。
そのほか、kindの役職にインデックスを張っている。

このとき

EXPLAIN (COSTS OFF) SELECT *
FROM t_parent
WHERE id >= 15
ORDER BY id
LIMIT 2

とidが15以上の者、id順で上位2件を取得すると
Limit
  ->  Result
        ->  Merge Append
              Sort Key: public.t_parent.id
              ->  Index Scan using t_parent_pkey on t_parent
                    Index Cond: (id >= 15)
              ->  Index Scan using t_child10_pkey on t_child10 t_parent
                    Index Cond: (id >= 15)
              ->  Index Scan using t_child20_pkey on t_child20 t_parent
                    Index Cond: (id >= 15)
となる。このMerge Appendが最適化が行われた状態。

つぎに

EXPLAIN (COSTS OFF) SELECT *
FROM t_parent
WHERE kind = 2
ORDER BY id
LIMIT 2

とkindが2の者、id順で上位2件を取得すると
Limit
  ->  Sort
        Sort Key: public.t_parent.id
        ->  Result
              ->  Append
                    ->  Index Scan using t_parent_pkey on t_parent
                          Filter: (kind = 2)
                    ->  Index Scan using t_child10_kind_idx on t_child10 t_parent
                          Index Cond: (kind = 2)
                    ->  Index Scan using t_child20_kind_idx on t_child20 t_parent
                          Index Cond: (kind = 2)
となり、全テーブルを見に行くようになってしまいました。

（追記9:53 ANALYZEかけたらMerge Appendになりました。）

いろいろ試してみたのですが、テーブルの設計がかなり物を言うようです。あとは頻繁にANALYZEをかける必要がありそう。
実際に使用している環境では、一つのテーブルが数千万行、また正規化しまくっていてJOINでつないでいるテーブルも数百万行あり、 「パーティション全体のソートの最適化」が行われませんでした。

プランナが賢くなるまで、もう少し辛抱のようです。

24日目はt_motookさんの「SQLFeatureNotSupportedException - JDBC4.0 API の中で未実装のもの」です。

zabbixで外部チェックアイテムを作る

（この記事はzabbix 1.8を対象としたものです。2.0以降では、下記パラメータの1番目にホスト名というのがありません）
サーバの状態監視にはzabbix！
便利すぎます。結構負荷高いのが難点だけど、余裕があったら監視用サーバを立てるのもありかなと思う。

普段はテンプレートをそのまま適用して、動いていないサービスのアイテムだけ解除していく使い方をしているのだけど、テンプレートで用意されていない物を監視したいときに、外部チェックアイテムを作成して対応した。

　アイテムのタイプ：外部チェック
　キー：get_queue_count.sh[fav_queue]
などとする。

このとき、zabbix_server.confのExternalScriptsに、キーで指定したスクリプトのパスを書いておく。

そしてスクリプト側。[ ]で括られた部分がパラメータとして渡されるのだけど、パラメータの1番目にホスト名が自動的に付けられる。
上の例だと、実際に呼ばれるコマンドは
　get_queue_count.sh hostname fav_queue
みたいに。

なので、スクリプト側では、それを考慮しておく必要がある。
　#/bin/bash
　/usr/bin/mongo --quiet --eval="db.$2.count()" favlook
という感じで、ホスト名は無視した。

PostgreSQL9.1betaでpg_upgradeしてみた。

PostgreSQL9.1のベータを試用しているんだけど、beta2からCATALOG_VERSION_NOが変わっていてバイナリ差し替えが出来なかったので、pg_upgradeを試してみた。今回はbeta1からbeta3への移行。
（9.0から9.1betaのときは、9.0のファイルが壊れまくっていてpg_upgradeが動かなかった）

まずpg_upgradeを-cオプション付きで実行。
最低限の環境が合っているか確認する。
このとき、移行元のシステムは動作していて構わない。移行先のシステムは停止させておく。

[postgres@srv ~]$ /usr/local/pgsql91b3/bin/pg_upgrade -c -b /usr/local/pgsql91b1/bin -B /usr/local/pgsql91b3/bin -d /usr/local/pgsql91b1/data -D /usr/local/pgsql91b3/data -p 50911 -P 50913
（pg_upgradeは移行先のものを使うこと）
環境が異なっているとカレントディレクトリにログが吐き出されるので、それを見てライブラリの差違をなくす。

チェックが終わったら、移行元、移行先の両システムを停止させて-cオプション無しでpg_upgradeを実行。
このときはまったのが、ログインロールにdefault_tablespaceの設定がある場合。
環境としてはroleAというロールがspaceBのオーナーであり、default_tablespace='spaceB'となっている。

pg_upgradeは
1.移行先にロールを作成
2.ロールに付属するALTERを実行
3.テーブルスペースを作成
の流れて進んでいく。
このとき、2番の処理で ALTER ROLE ロール名 SET default_tablespace='テーブルスペース'; が実行されて「そんなテーブルスペースはない」と怒られてしまう。
では先にテーブルスペースを作れば良いのかと、移行先にロールとテーブルスペースを作ってしまうと、1番の処理で「既にロールがある」と怒られてしまう。
ロールは作らず、テーブルスペースのオーナーをpostgresにしておいても同様に「既にテーブルスペース（以下略）。

結局、一時的にdefault_tablespaceを解除して実行した。
pg_dumpのときみたいに、CREATE系を先に実行して、ALTER系をあとで実行するとか出来ませんかね？

あと、pg_upgradeがエラーで中止されると、移行元システムの/data/global/pg_controlがpg_control.oldにリネームされて再実行できなくなるので、面倒だがpg_controlにリネームする必要がある。
これは再実行させないためなんでしょうかね？　どうせ移行先にロールがあったら失敗するのに、と思ってみたり。

移行中にpg_upgradeの進捗が分からないのも難点。
残り時間までは必要無いので、対象ファイルがいくつあって、現在どこまで進んだのかが分かるだけでも気が楽だと思う。
停止時間を長くしたくない場合は、アプリ側で更新処理を停めて、pg_dump | psqlを流した方が参照だけでも出来るのでpg_upgradeよりマシかも。