2016年12月20日火曜日

PostgreSQL 9.6 パラレルクエリに関する小ネタ

よく考えたら当たり前なんだけど、いざ気付くと「えーっ!」となった衝撃的な話。

「パラレルクエリは、マテリアライズド・ビューのリフレッシュに使えない」疑惑

パラレルクエリの制限事項って結構あるんですね。そのうちの1つを9.6.1時点のマニュアルから抜粋すると 

クエリがデータを書き込むか、データベースの行をロックする場合。 クエリがデータ更新操作をトップレベルあるいはCTE内で含むと、そのクエリに対するパラレルプランは生成されません。 これは現在の実装の制限で、将来のリリースでは解除される可能性があります。 

要はDMLはダメと言っているように見える。じゃあマテビュー作るのもダメじゃね?という当然の話ではあるのですが。夜間に巨大なテーブルの加工や集計(そんな処理を数十分とか、数時間かけて)した結果をマテビューにして、なんとか日中はサクサク動くように頑張るわけじゃないですか。
で、パラレルクエリが入ったときに、「くそ重いロックをとるマテビューのリフレッシュが、2時間かかってたやつが1分になったら(キラキラ)」などと夢をいだいたものです。ところがそれが現状のパラレルクエリでは何も変わらないのです。がびーん。

そんなわけで、実際に試してみることにします。 わざわざ実行計画を見るのはめんどいマテビューでは内部で実行されたクエリの実行計画は覆い隠されてしまうので、単に所要時間で(psqlの\timingで)パラったか判断します。

今回使う表(今回、表の中身は重要ではない。件数と所要時間だけ注目)
demo=# \timing
demo=# SELECT count(*) FROM tab1;
  count
----------
 28864000
(1 行)
時間: 669.988 ms   --- パラレルの時は1秒以下、非パラレルだと4秒強かかることを確認済み。

/* 実行計画はこんな感じ */
demo=# EXPLAIN ANALYZE
demo-# SELECT count(*) FROM tab1;
                                                                 QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=333435.96..333435.97 rows=1 width=8) (actual time=1015.604..1015.604 rows=1 loops=1)
   ->  Gather  (cost=333435.33..333435.94 rows=6 width=8) (actual time=1015.510..1015.594 rows=7 loops=1)
         Workers Planned: 6
         Workers Launched: 6
         ->  Partial Aggregate  (cost=332435.33..332435.34 rows=1 width=8) (actual time=1008.983..1008.983 rows=1 loops=7)
               ->  Parallel Seq Scan on tab1  (cost=0.00..320408.67 rows=4810667 width=0) (actual time=0.043..622.811 rows=4123429 loops=7)
 Planning time: 0.126 ms
 Execution time: 1017.730 ms

/* テーブルサイズはこれぐらい */
demo=# SELECT pg_size_pretty(pg_relation_size('tab1'));
 pg_size_pretty
----------------
 2127 MB

マテリアライズド・ビューを作成してリフレッシュ

demo=# CREATE MATERIALIZED VIEW mv1 AS
demo-# SELECT count(*) FROM tab1;
SELECT 1
時間: 4510.077 ms   --- む、この時間は・・・。

demo=# REFRESH MATERIALIZED VIEW mv1;
REFRESH MATERIALIZED VIEW
時間: 4451.020 ms   --- む、この時間は・・・。
うーん、パラられない。残念でならない!
というわけで、何かそれに代わる策がないか試してみます。

VIEWの場合

demo=# CREATE VIEW v2 AS
SELECT count(*) FROM tab1;
CREATE VIEW
時間: 2.151 ms
demo=# SELECT * FROM v2;
  count
----------
 28864000
(1 行)

時間: 682.303 ms --- viewならOKだよねうん。
まあ、これは大丈夫か。SELECTだけだし。でも結果を保存して再利用できない。

普通の表をつくってINSERT SEELCT

demo=# CREATE TABLE t3 (count numeric);
CREATE TABLE

demo=# INSERT INTO t3
demo-# SELECT count(*) FROM tab1;
INSERT 0 1
時間: 4506.088 ms --- だめ
だめ。デスヨネー。

外部テーブルを介してMビュー。

外部テーブルで問合せた先は、リモートのサーバー側でよしなに実行計画を作ってくれるはずなので淡い期待を込めてやってみるが・・・
demo=# CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
demo-# OPTIONS (dbname 'demo',host 'localhost',port '5432'); -- 自分自身を見に行くFDW
CREATE SERVER
時間: 1.331 ms
demo=# CREATE USER MAPPING FOR public SERVER loopback
demo-# OPTIONS (user 'postgres',password 'postgres');
CREATE USER MAPPING
時間: 0.914 ms
demo=# IMPORT FOREIGN SCHEMA public LIMIT TO (tab1)
demo-# FROM SERVER loopback
demo-# INTO loopback;
IMPORT FOREIGN SCHEMA
時間: 7.039 ms
demo=# EXPLAIN ANALYZE
demo-# SELECT * FROM loopback.tab1 WHERE sales_id = 0; -- 一応ループバックしてFOREIGN SCANしてることを確認
                                                   QUERY PLAN

----------------------------------------------------------------------------------------------------------------
 Foreign Scan on tab1  (cost=100.00..117.59 rows=3 width=112) (actual time=396.211..4527.848 rows=1152 loops=1)
 Planning time: 0.286 ms
 Execution time: 4528.583 ms
(3 行)

時間: 4529.705 ms

demo=# CREATE MATERIALIZED VIEW mv2 AS
demo-# SELECT count(*) FROM loopback.tab1;
SELECT 1
時間: 71687.426 ms  ---パラレルかどうかよりaggrigate push downが効いてないせいかな。

demo=# REFRESH MATERIALIZED VIEW mv2;
REFRESH MATERIALIZED VIEW
時間: 68418.958 ms
ダメ。むしろダメ。Aggrigateのpush downはまだ実装されてないんだった。全件もってきて集計してるんだとしたら、仮にリモートでパラレルで動いてくれても、ネットワークが重すぎる。


\copy と COPY

demo=# \copy (SELECT count(*) FROM tab1) TO demo.copy;
COPY 1
時間: 4518.860 ms --ダメ

demo=# COPY (SELECT count(*) FROM tab1) TO '/home/enterprisedb/demo.copy';
COPY 1
時間: 4524.612 ms --ダメ
だめ。うーん、これぐらいはイケると思ったんだが。

\oでファイルに出力

demo=# \o demo2.copy
demo=# SELECT count(*) FROM tab1; -- OK
時間: 699.871 ms
OK!うれしい!

psqlの引数としてクエリを投げ、結果をファイルに出力

[postgres@demo ~]$ time psql -U postgres demo -At -c 'SELECT count(*) FROM tab1' -o demo3.copy

real    0m0.725s -- OK
user    0m0.003s
sys     0m0.009s

[postgres@demo ~]$ psql -U postgres demo -At -c "COPY t3 FROM '/home/enterprisedb/demo3.copy'"
 -- これで格納もできる。(t3は上部検証で作成したやつ)
  -- もちろん、集計ずみの1行だけ格納するので一瞬で終わる。
OK!うれしい!これだ感!これができればシェルとかプログラムで簡単にマテビューチックなことができる、はず。

2016年12月16日金曜日

PostgreSQL 9.6のパラレルが間違う件!

本投稿は、PostgreSQL Advent Calendar 2016 の 12/16 担当です。
昨日は「.NET Core で PostgreSQL を使ってみたら結構いけていたという話」を @creativewebjp さんが書いてくださいました。


■パラレルクエリと戯れて


さて、Ver9.6の登場までポスグレ界の話題を総ざらいしていたパラレルクエリですが、みなさん使ってますか?
大量データの領域で真価を発揮するものあって、なかなか現実的なサイズで試せていないのが現状で、採用に二の足を踏んでしまう、なんて話もちらほら聞いています。私は仕事でEnterpriseDB社が開発するPostgreSQLの商用版、EDB Postgresを担当していますが、そのβテストを兼ねてOSS版PostgreSQLのパラレル動作をみていたのですが、やはり試してみてわかることが結構ありました。
採用するかどうか迷っている方がいたら、とっかかりは非常に簡単なので、是非トライして、自身のアプリケーションでどの程度使えるかを評価してみるべきだと思います。バッチリとハマったときの効果は抜群ですよ。

とは言え、良いときの性能評価はそこそこ出回っていると思うので、今回は、案外うまくいかないパラレルクエリの話を紹介します。


■いろいろ検証して得られた「間違うケース」


間違うケース1 でかい表同士をJOINするとき


前提知識として、PostgreSQLのパラレル・クエリは、表のサイズで並列度が決定されます。
適当な例で言うなら、10GBの表は8並列、500MBの表は4並列ぐらいで動きます。
んまあ、これはあくまでも単一の表をFROM句に書いてSELECTした場合に、これぐらいの並列度が良さそうと判断されたにすぎないです。
10GBを 
1プロセスで読むコスト > 8プロセスで分担して、後でGather(集約)するコスト
と判断したので、そういう実行計画が選択されるということですね。

これが、JOINの場合はどうやら変わってくるようです。
2つの表をパラレルで読んで、Gatherするとなると、 そのオーバーヘッドの方が大きくなってしまって、せっかくパラレルにしたのに所要時間はむしろ長くなってしまいます。

どちらもパラレルが効くぐらい大きい表なので、適切にパラレル処理して欲しいところではありますが、単純に表のサイズだけでは決められず、どちらか一方だけパラレルに動いてくれた方が良い結果になるのです。もちろんそういう目論見で、プランナはどちらか一方の表をパラレルで読もうとします。
問題は、PostgreSQLだと「どちらの表をパラレルで処理するか?」の調整が非常に難しいこと。約10GBのlineoder表と、約500MBのpart表をごく単純に結合してみると、このような実行計画になりました。
 /* 実行したクエリ(ごく単純なJOIN) */
EXPLAIN ANALYZE
SELECT lo_partkey,sum(lo_quantity)
FROM   lineorder
JOIN   part
       ON    lo_partkey = p_partkey 
WHERE  p_name LIKE 'k%'
GROUP BY lo_partkey;
 /* いかにもパラレルが効きそうなlineorderが普通にSeqScanされてる */
->  Hash Join  (cost=28661.34..3682122.56 rows=1111820 width=9) 
               (actual time=67.061..57496.965 rows=1286337 loops=1)
->  Seq Scan on lineorder  (cost=0.00..3214594.56 rows=114066256 width=9)
                         ★(actual time=0.010..27750.208 rows=114066226 loops=1)
      ->  Hash  (cost=28490.76..28490.76 rows=13646 width=4) 
                (actual time=66.904..66.904 rows=15731 loops=1)
            Buckets: 16384  Batches: 1  Memory Usage: 682kB
            ->  Gather  (cost=1000.00..28490.76 rows=13646 width=4) 
                        (actual time=0.342..61.640 rows=15731 loops=1)
                  Workers Planned: 3
                  Workers Launched: 3
                  ->  Parallel Seq Scan on part  (cost=0.00..26126.16 rows=4402 width=4) 
                                               ★(actual time=0.029..59.519 rows=3933 loops=4)
                        Filter: ((p_name)::text ~~ 'k%'::text)
                        Rows Removed by Filter: 346067

今回のケースでは、どうしても小さい方の表をパラレルにしたがる(先に読むからだろうか?)で、大きい方の表をシーケンシャルに読んでしまいました。

効果は未確認ですが、解決策としては、ALTER TABLEでそのテーブルを読むときに使うワーカーの数を指定できるので、それでpartテーブルの方は0に固定してしまうことでしょうか。
ただし、「こっちクエリではpart表をパラレルに読んでほしいんだよね~」という要望に応えるのが難しくなります。


そこでEDB Postgresです。EDB Postgresでは、オプティマイザ・ヒントを指定でき、2017年1月にリリース予定のEDB 9.6ではパラレル・ヒントに対応します。そこで実行計画をいじくるとこんな感じです。

 /* EDB版の実行したクエリ(パラレル・ヒントで各表の並列度を指定) */
EXPLAIN ANALYZE
SELECT /*+ PARALLEL(part 0) PARALLEL(lineorder 4) */ lo_partkey,sum(lo_quantity)
FROM   lineorder
JOIN   part
       ON    lo_partkey = p_partkey 
WHERE  p_name LIKE 'k%'
GROUP BY lo_partkey;
 /* スキャンの並列度が変わり、Hash Joinまでパラレル実行している */
HashAggregate  (cost=2624706.98..2636997.29 rows=983225 width=36) 
               (actual time=17414.085..17421.061 rows=11272 loops=1)
   Group Key: lineorder.lo_partkey
   ->  Gather  (cost=39151.57..2619147.88 rows=1111820 width=9) 
               (actual time=181.809..16260.314 rows=1286337 loops=1)
         Workers Planned: 4
         Workers Launched: 4
         ->  Hash Join  (cost=38151.57..2506965.88 rows=1111820 width=9) 
                      ☆(actual time=233.851..16886.113 rows=257267 loops=5)
               Hash Cond: (lineorder.lo_partkey = part.p_partkey)
               ->  Parallel Seq Scan on lineorder  (cost=0.00..2359097.64 rows=28516564 width=9) 
                                                 ★(actual time=0.034..8187.008 rows=22813245 loops=5)
               ->  Hash  (cost=37981.00..37981.00 rows=13646 width=4) 
                         (actual time=233.525..233.525 rows=15731 loops=5)
                     Buckets: 16384  Batches: 1  Memory Usage: 682kB
                     ->  Seq Scan on part  (cost=0.00..37981.00 rows=13646 width=4) 
                                         ★(actual time=0.026..225.321 rows=15731 loops=5)

狙った表をパラレルスキャンさせることで、PostgreSQLで生じていた問題をバッチリ解決することができました。


間違うケース2 Window関数なのか、ソートなのか。原因不明(ヲイ!)


PostgreSQLでは、Windows関数つかって、それでないと1クエリで得ることがなかなか難しい複雑な集計を任せることができます。
とは言えその複雑さはなかなかなもので、さぞかしプランナは頭を悩ませることでしょう。

以前に本ブログで書いた「PostgreSQLらしい集計クエリ」で紹介しているテーブルで、データを何重にもいれまくって、2GBぐらいの表を用意しました。それを集計してみます。

 /* window関数 */
EXPLAIN ANALYZE
SELECT *,
    rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
    SELECT
        sales_date::date as "売上日"
       ,shop             as "ブランド"
       ,sum(price)       as "売上"
    FROM  tab1
    GROUP BY "売上日","ブランド") AS "集計";

 /* 結果イメージ。ブランド毎に売上の多かった日ランキング */
   売上日   |  ブランド  |   売上    | 順位
------------+------------+-----------+------
 2016-05-11 | DIESEL     | 632141504 |    1
 2016-07-31 | DIESEL     | 622096896 |    2
 2016-11-29 | DIESEL     | 620818688 |    3
  :
 2016-04-06 | Dior       | 962435003 |    1
 2016-03-23 | Dior       | 889015872 |    2
    :
 /* この時の実行計画がこちら。パラレルになってる。約9秒。 */
 WindowAgg  (cost=3430456.39..3454473.19 rows=1200840 width=52) (actual time=9776.175..9777.199 rows=1095 loops=1)
   ->  Sort  (cost=3430456.39..3433458.49 rows=1200840 width=44) (actual time=9776.165..9776.300 rows=1095 loops=1)
         Sort Key: "集計"."ブランド", "集計"."売上" DESC
         Sort Method: quicksort  Memory: 134kB
         ->  Subquery Scan on "集計"  (cost=3115228.89..3235312.89 rows=1200840 width=44)
                                      (actual time=9764.055..9773.283 rows=1095 loops=1)
               ->  Finalize GroupAggregate  (cost=3115228.89..3223304.49 rows=1200840 width=44)
                                            (actual time=9764.054..9773.062 rows=1095 loops=1)
                     Group Key: ((tab1.sales_date)::date), tab1.shop
                     ->  Sort  (cost=3115228.89..3133241.49 rows=7205040 width=44)
                               (actual time=9764.012..9765.509 rows=7665 loops=1)
                           Sort Key: ((tab1.sales_date)::date), tab1.shop
                           Sort Method: quicksort  Memory: 1270kB
                           ->  Gather  (cost=1064650.75..1851272.12 rows=7205040 width=44)
                                       (actual time=6412.121..9757.054 rows=7665 loops=1)
                                 Workers Planned: 6
                                 Workers Launched: 6
                                 ->  Partial GroupAggregate  (cost=1063650.75..1129768.12 rows=1200840 width=44)
                                                             (actual time=6518.572..9655.286 rows=1095 loops=7)
                                       Group Key: ((tab1.sales_date)::date), tab1.shop
                                       ->  Sort  (cost=1063650.75..1075676.95 rows=4810477 width=19)
                                                 (actual time=6516.858..8298.955 rows=4123429 loops=7)
                                             Sort Key: ((tab1.sales_date)::date), tab1.shop
                                           ☆Sort Method: external merge  Disk: 125680kB
                                             ->  Parallel Seq Scan on tab1  (cost=0.00..332432.96 rows=4810477 width=19)
                                                                          ★(actual time=0.071..1195.131 rows=4123429loops=7)

お、パラレルになった。
しかし、これ、Disk Sortになっていますね。メモリソートになればもっと早くなるんじゃ。SET work_mem to '2GB' などとして再実行

あれ?
 /* 実行計画がこちら。メモリソートになったらなぜか非パラレルに。約20秒かかっている。 */
 WindowAgg  (cost=1000838.70..1024855.50 rows=1200840 width=52) (actual time=21083.366..21084.609 rows=1095 loops=1)
   ->  Sort  (cost=1000838.70..1003840.80 rows=1200840 width=44) (actual time=21083.347..21083.521 rows=1095 loops=1)
         Sort Key: "集計"."ブランド", "集計"."売上" DESC
         Sort Method: quicksort  Memory: 134kB
         ->  Subquery Scan on "集計"  (cost=849559.20..879580.20 rows=1200840 width=44)
                                      (actual time=21075.482..21080.224 rows=1095 loops=1)
               ->  HashAggregate  (cost=849559.20..867571.80 rows=1200840 width=44)
                                  (actual time=21075.480..21080.024 rows=1095 loops=1)
                     Group Key: (tab1.sales_date)::date, tab1.shop
                     ->  Seq Scan on tab1  (cost=0.00..633087.75 rows=28862860 width=19)
                                         ★(actual time=0.064..7888.289 rows=28864000 loops=1)
 Planning time: 0.234 ms
 Execution time: 21097.712 ms
なんと、ソートが軽量に済むやいなや、パラレル動くことをやめちゃいました。なんてこった。ディスクソートしていたときのほうが圧倒的に早いので、このケースこそプランナが間違えてるとしか思えません。

しかもこれ、上記のJOINのケースのようにパラレルさせないという選択ではなく、パラレルして欲しい方向なので、これ以上PostgreSQLでは調整のやりようがないです。困った。


そんなときはEDB Postgres
ヒントを使って・・・・
EXPLAIN ANALYZE
SELECT /*+ PARALLEL(tab1 6) */ *,
    rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
    SELECT
        sales_date::date as "売上日"
        ,shop            as "ブランド"
        ,sum(price)      as "売上"
    FROM  tab1
    GROUP BY "売上日","ブランド") AS "集計";
あれ。

悩むこと数十分。サブクエリの側にヒントを入れて、無事解決しました。
 /* EDBでヒントをいれてみる。正解はこの位置 */
EXPLAIN ANALYZE
SELECT *,
    rank() OVER (PARTITION BY "ブランド" ORDER BY "売上" DESC) AS "順位"
FROM (
    SELECT  /*+ PARALLEL(tab1 6) */
        sales_date::date as "売上日"
        ,shop            as "ブランド"
        ,sum(price)      as "売上"
    FROM  tab1
    GROUP BY "売上日","ブランド") AS "集計":
 /* EDBでヒントを使うと、パラレルスキャンし、メモリソートもできた */
 WindowAgg  (cost=1327297.76..1351662.32 rows=1218228 width=56) (actual time=9667.291..10095.048 rows=441334 loops=1)
   ->  Sort  (cost=1327297.76..1330343.33 rows=1218228 width=48) (actual time=9667.270..9746.242 rows=441334 loops=1)
         Sort Key: "集計"."ブランド", "集計"."売上" DESC
         Sort Method: quicksort  Memory: 46768kB
         ->  Subquery Scan on "集計"  (cost=1176747.00..1204157.13 rows=1218228 width=48) (actual time=8986.449..9216.731 rows=441334 loops=1)
               ->  Finalize HashAggregate  (cost=1176747.00..1191974.85 rows=1218228 width=48) (actual time=8986.447..9129.055 rows=441334 loops=1)
                     Group Key: tab1.sales_date, tab1.shop
                     ->  Gather  (cost=357488.67..1103653.32 rows=7309368 width=48) (actual time=3751.793..5775.296 rows=3081355 loops=1)
                           Workers Planned: 6
                           Workers Launched: 6
                           ->  Partial HashAggregate  (cost=356488.67..371716.52 rows=1218228 width=48) (actual time=3736.607..4506.717 rows=440194 loops=7)
                                 Group Key: tab1.sales_date, tab1.shop
                                 ->  Parallel Seq Scan on tab1  (cost=0.00..320408.67 rows=4810667 width=23) (actual time=0.041..870.314 rows=4123429 loops=7)
ぶっちゃけactual timeを見ると元々9秒だったのが10秒と、むしろ若干悪いのだが・・・ディスクソートを排除したいという欲望を満たすべく試したらこうなった、と言う話。たぶんデータ量とかでこの辺はセンシティブに最適解は変わってしまうのだと思います。

間違・・・わないケース3 グループ集計(GROUPING SETS)


結論から言うと、いろいろ頑張ったけど思ったより早くならないのでプランナが正しかった!説があります。以下のクエリを実行してみると、PostgreSQLで一向にパラレルになってくれません。

 /* グループ集計のクエリ */
EXPLAIN ANALYZE
SELECT
    shop            AS "ブランド"
    ,kind           AS "種類"
    ,count(*)       AS "数量"
FROM tab1
GROUP BY GROUPING SETS (("ブランド"),("種類"),());

 /* 結果のイメージ */
  ブランド  |    種類    |   数量
------------+------------+----------
 DIESEL     |            |  8448000
 Paul Smith |            | 15488000
 dior       |            |  4928000
            |            | 28864000
            | シャツ     | 15488000
            | ジャケット |  4224000
            | パンツ     |  9152000
 /* PostgreSQLでの実行計画 パラレルにならない */
 GroupAggregate  (cost=4137591.06..8147200.27 rows=14 width=26)
                 (actual time=27350.631..48641.465 rows=7 loops=1)
   Group Key: shop
   Group Key: ()
   Sort Key: kind
     Group Key: kind
   ->  Sort  (cost=4137591.06..4209751.06 rows=28864000 width=18)
             (actual time=22549.349..26069.469 rows=28864000 loops=1)
         Sort Key: shop
         Sort Method: quicksort  Memory: 2953433kB
         ->  Seq Scan on tab1  (cost=0.00..560942.00 rows=28864000 width=18)
                               (actual time=0.025..5461.728 rows=28864000 loops=1)
 Planning time: 0.160 ms
 Execution time: 49108.929 ms
EDBでヒントをいれてみます。
 GroupAggregate  (cost=6784457.73..10794066.94 rows=14 width=26)
                 (actual time=45959.909..70152.520 rows=7 loops=1)
   Group Key: shop
   Group Key: ()
   Sort Key: kind
     Group Key: kind
   ->  Sort  (cost=6784457.73..6856617.73 rows=28864000 width=18)
             (actual time=39595.620..44537.665 rows=28864000 loops=1)
         Sort Key: shop
         Sort Method: quicksort  Memory: 2953433kB
         ->  Gather  (cost=1000.00..3207808.67 rows=28864000 width=18)
                     (actual time=0.442..18630.270 rows=28864000 loops=1)
               Workers Planned: 6
               Workers Launched: 6
               ->  Parallel Seq Scan on tab1  (cost=0.00..320408.67 rows=4810667 width=18)
                                              (actual time=0.052..1111.439 rows=4123429 loops=7)
 Planning time: 0.130 ms
 Execution time: 70680.047 ms
パラレル度が上がったが早くならないケースもある。ということで。

■総評


パラレルクエリで効果を得づらいケースを3パターン紹介しました。

これはたまたま私が検証する中で気付いたもので、他にもあるかもしれないし、賢い解決策があるのかもしれません。
本投稿では触れていませんが、同じ検証の中で、1クエリ実行するのに50分かかっていた処理が、パラレルクエリとテーブル・パーティショニングの組合せで8秒まで短縮していますから、ハマれば効果は抜群です。
しかしながら、間違うケースもこうやってポンポン出てきたということは、これから採用を考えている人は、自身のアプリケーションでどの程度性能がでるか、どんな書き方をすれば効果がでるか、(そしてEDBにすればどう変わるか。ぐへへ!)是非試してみて欲しいと思います。
策をいろいろ考えるのはパズルみたいで楽しかったです。

明日はぬこ@横浜さんが書いてくれます。楽しみです!

2016年12月3日土曜日

【PGConf.asia】PostgreSQLの初期設定

PostgreSQLのインストールができて、initdbが終わると、PostgreSQLサーバーを起動してデータベースとして使い始めることができますが、当たり前にここまではやっておいたほうが良い、といういくつかの初期設定がありますので、ここではそれを紹介します。

  • パラメータ設定(postgresql.conf)
  • 認証設定(pg_hba.conf)
  • ユーザー作成
  • データベース作成
  • (必要に応じて)スキーマ作成

を扱います。

■パラメータ設定
PostgreSQLの動作設定は、すべてpostgresql.confというテキスト形式のパラメータファイルに記載されています。
その中でも、設定しておかないと使えないものがlisten_addressesです。
デフォルトは「loclhost」となっていて、外部からのアクセスを一切受け付けません。
この値を「*」または、自サーバーのIPアドレスを設定します。NICが二つ以上ある場合に「*」はすべてだし、IPを指定していれば、そのIPアドレスに対するアクセスが来た時だけ受け付けます。

 ※ちなみに、インストーラを使った場合はデフォルトからよしなに変更されているようです。

そのほかにも、現代のサーバースペックを考えるとごく小さいデフォルト値が採用されているメモリ関連の設定などがありますので、実用を考えると5個ぐらい見直したほうが良い値があるのですが、「PostgreSQL データベースチューニング」などで検索すると情報がたくさん公開されています。

■認証設定
PostgreSQLは、ユーザーからの接続要求を受け取ると、pg_hba.confというテキスト形式の認証設定に従って接続の許可・拒否を判断します。
このファイルは、接続元のホスト、接続先のデータベース/ユーザー、認証方法の組み合わせで書かれたリストになっており、上から順に評価されることに注意してください。

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    demo        kkida          192.168.200.0/24     md5



サービスからPstgreSQLサーバーを再起動して、ここまでの設定を反映します。

■ユーザー作成、データベース作成
PostgreSQLを操作するには、psqlというコマンドラインツール、またはpgAdminというGUIツールで対象のデータベースに接続します。

$ psql -U kkida -p 5432 -d demo

このとき、kkidaユーザーをまだ作成していないので当然接続に失敗します。demoデータベースもありません。
デフォルトでは、postgresユーザーとpostgresデータベースが作られているので、それを指定してアクセスし、自分用のユーザーやデータベースを作成します。(これも、インストーラーを使用した場合は、OSユーザーと同名でDBユーザーが作られている場合があります。)

$ psql -U postgres postgres
postgres=#

または、すでにユーザーがいる場合は、以下のように確認できます。セキュリティ的にゆるゆるな初期の今だからこそできることですが。
$ psql postgres (ユーザー名の入力を省略してログイン)
postgres=# \du
名前 |属性
------+---------------
kkida   |  スーパーユーザ


ここでCREATE ROLEやCREATE DATABASEといったSQLを実行し、任意のユーザー、データベースを作成します。

postgres=# CREATE ROLE kkida SUPERUSER LOGIN;
postgres=# \c postgres kkida
postgres=# CREATE DATABASE demo;
postgres=# \c demo kkida
demo=# \q

これで、初期設定は完了です。
$ psql -U kkida demo
demo=# CREATE TABLE demo (no int , val text);
demo=# INSERT INTO demo VALUES (1,'aaa');
demo=# SELECT * FROM demo;
no     | val
-----+----------
1      | aaa

demo=# \q


【PGConf.Asia】Windows版PostgreSQLのインストール

ここでは、PostgreSQLを初めてインストールする方向けに、Windows環境での手順を説明します。

■インストーラーの準備と対象バージョンについて


現時点の最新版、PostgreSQL 9.6.1を対象にします。
Windowsへの対応は、64bitのサーバーOS(最近だとWin 2008以降ぐらいだろうか?)とか、32bitのWindows 7,8とか10では実績があると思います。これといって細かいOS側に対する要件は無くて、PostgreSQLの開発コミュニティでは構築テストをして実績のあるものが公開されているような感じです。

ただし、商用版PostgreSQLであるEDB PostgresはサーバーOSのみに対応し、つまり現行の64bit機のみに対応するように、ここ数年で変わってきています。インストーラーが提供されているかどうかというレベルで、32bit環境では将来を気にしておくべきだと思います。

Windows版のインストーラーは、上記のEDB Postgresを作っていてPostgreSQL自体の機能・性能向上にも大きく貢献しているEnterpriseDB社が作成し、公開しています。ここから自分が該当するインストーラーをダウンロードしておきます。
また、日本PostgreSQLユーザ会のwebサイトでは、各OS向けのインストーラ、yum(rpm)やaptパッケージ、ソースコードへのリンクを記載しており、こちらから辿るのも簡単です。



■インストーラの実行


Windows機では、Administrator権限を持ったOSユーザでインストーラーを実行します。
このとき、UAC(ユーザーアクセス制御:Windowsのセキュリティ機能)は弱くしておかないと起動に失敗することがあります。(正確にどうしたらいいか、試していないので忘れた・・・まあ、失敗したら変更すればよいよね?)

インストーラーを実行すると、以下のように次々値を指定していくことになるのですが・・・



画面キャプチャを次々と載せるのでも良いのですが、各項の解説をしておくほうが大事だと思いますので、PostgreSQL用語満載ですが、ご容赦ください。勉強しましょう!

インストール先 (Installation Directory)

PostgreSQLをインストールするフォルダです。デフォルトはProgram Filesの下。この何階層か下にbinディレクトリがあり、そこにPostgreSQLのコマンドが配置されますので、後でbinに環境変数PATHを通しておくとよいでしょう。

データディレクトリ(Data Directory)

PostgreSQLのデータファイルおよび管理ファイルの配置先です。デフォルトはインストールしたフォルダ配下が使われますが、データ領域であるためディスク容量や性能の問題で専用のストレージ領域を指定することが多いです。

WALについて

これまでのバージョンでは、データディレクトリと併せてWALディレクトリ位置を聞かれていました。
WALはデータベースに対する変更の歴史を記録するファイルで、これをある時点の断面に次々適用することで最新状態まで戻す用途で保持されます。
WALがデータディレクトリと物理的に同じディスクに配置されていると、ディスク障害時の復旧に必要なWALまで失われており泣き寝入りするしかありません。というわけなのですが、最近のディスク領域はRAID構成になっていたりするのが普通なので入力を省いたのでしょうか。
WALの位置を変更するには、最後まで構築が済んだ後にごにょごにょします。(ここでは扱わない。)

ユーザーとパスワード

インストーラで入力を求められるのは、「postgres」というデーベース内に一人必ず作成される管理者ユーザーのパスワードです。
ただし、同名/同パスワードのOSユーザーを勝手につくろうとするので、OS側のセキュリティポリシー設定が厳しかったりすると、パスワードがポリシーに違反してる旨のメッセージが出てしまい、それはOS側の制約だったりするのでインストール中の作業者には心当たりがなく、ハマることがあります。

ポート番号

PostgreSQLがクライアントからの通信を待ち受けるポートを指定します。デフォルトは5432です。
あとからでも変更できるので影響は小さいです。既存のポートとバッティングしないようチェックが入りますので、警告がでなければ進めて問題ありません。

上位設定(Advanced Options)

ロケールはデフォルトではなく、「C」にすることを推奨します。
ロケールで地域を指定すると、その言語の並び順でソートされたりして、特にソート時のパフォーマンスでは大きな差が生じるようです。「C」でのソート順は、UTF-8など使用している格納文字コードのコード順になります。

全て入力して、「Next」を押すとインストールが開始されます。


■データベースクラスタの再作成(必要に応じて)


出来上がったデータベースは、環境によって格納文字コードがASCIIになっている場合があり、日本語を格納できません。その場合、日本語を格納するにはデータベースクラスタ作成時に指定するエンコードを「UTF-8」などにしなければなりません。
インストーラが内部で勝手に実行してくれたデータベースクラスタ作成時のオプション指定がイケてないんですね。というわけで再作成します。

データベースの停止

インストーラーが終了した時点で、データベースが起動していますので、不正なステータスのまま壊してしまわないよう停止しておきます。Windowsでは「サービス」からデータベースの起動・停止を操作します。

データディレクトリの削除

インストーラーで指定したデータディレクトリ配下には、データファイル、WALファイル、各種パラメータ設定ファイル、DBの状態を管理するファイルなどが一式格納されていて、でも再作成したいいまとなってはこれが邪魔な存在なのでフォルダごと一括で消します。

データディレクトリの初期化

同じ意味の言葉で、「initdb」「データベースクラスタの初期化」 などと表現されますが、削除したデータディレクトリをまるっと新たに作り直してくれるinitdbというコマンドがありますので、それを実行してデータディレクトリを作ります。この時に指定するオプションで、問題のエンコードを指定したり、WALファイルの位置を指定したりカスタマイズが効きます。

> initdb -D <データディレクトリ位置> -E UTF8 --no-locale

データベースの起動

手動でinitdbしたあとは、データベースの起動が必要です。
WindowsのサービスからPostgreSQLを探し、起動します。

この時、データディレクトリの位置が最初と変わっていると、サービススクリプトの作り替えも必要になりますが、応用なので今回はそのようなケースは扱いません。


これで、PostgreSQLのインストールは完了です。
テーブルを作ってデータを入れるには、もう少し作業があって、ユーザやデータベースの作成、認証のための設定が必要です。 以降の手順はOSプラットフォームやディストリビューションによらず、PostgreSQLの初期設定一般になりますので、別記事に整理します。

【PGConf.Asia】 チュートリアルセッション序文

今、ユーザーコミュニティたる我々が発信していくべきことは何かと考えると、初めの一歩を踏み出す人を後押しすることではないか。などと唐突に書いてみる。
この投稿は、2016年12月3日 PGConf.asiaチュートリアル枠について書いた非技術的な話です。

■これまでのPostgreSQL勉強会


過去数年、日本PostgreSQLユーザ会(JPUG)で開催した「初級者向け勉強会」は、それでもデータベース経験者というか実務で扱ってる人を対象にした、バックアップ入門、チューニング入門などなど、すでにデータベースに興味を持ってる人が知識を整理し、中上級者になるとか・実務を担当できるようになるという方向性であったように思う。
それはとても良いことで、ある話題の最低限を1コマに凝縮しモジュール化した講義内容は再利用できるし、そのテーマで調べた人がたどり着いて利用するのにもっともよい形になっていると思う。
それらは今でもスライド資料+録画が公開されていて、利用したい人の需要にこたえることができる一つの方法として価値あるものを提供できていると思う。
(コミュニティの場で発表することは、発表者にとっても、苦労に対してメリットが非常に大きく、知識を整理したことで自身がレベルアップできることはもちろん、そこでの経験は仕事の評価につながったり、転職活動に明確に有利になったりする。)

上級者向けは言わずもがな。中国地方DB勉強会とか、Database Loungeでは、新機能やデータベースの新たな領域での活用という話が繰り広げられている。特にこれまでにそのレベルに達している人(≒既にその製品・分野の勉強してきた人)にとってもう一歩先の話はとても興味深く、自慢の機能を作った開発者と、新しいことを得たい受講者の需要と供給が一致して半端ないエネルギーを生んでいるように見える。

■苦労を伴う初心者育成


良い意味で勝手に回っていく上記のような話とは違い、初めの一歩を踏み出す人に向けてスキルを伝えていくことは、双方に努力が必要な話である。どちらかが話を持ち掛けて、相手に努力を強いることになる。

  • 経験者が「○○は大事だから絶対勉強しとくべき」とか、「俺が教えてやるよ」なんて非常に上から目線すぎる話だけど、本当に大事な技術はそうでもして伝承していかないといけない。それにつきあって新たなことを学ぶ初心者の苦労は相当のものかもしれない。
  • 初心者が「わからないので教えてよ」って言うのは、有能で成果を上げてる経験者の時間を消費することで、若気の至りにしたって厚かましいかもしれない。そのために時間を割き、経験値を形に変えて教えてくれる経験者の損失は実は計り知れない。その上、わかりにくいと叩かれたりする。

苦労のデッドロックである。(上手いこと言った。←)


それでも、長い目でみたら製品の利用者が増え、知の総和が増え、製品クオリティの向上にもつながるし、各個人はそこでなければ得られなかった技術領域を習得し、個としてのバリューを発揮できるようになる。
”生み出したい価値” と ”育成に費やすコスト” を天秤にかけて、結果、技術力をものにできたところが最終的に勝つのだと思う。それをお金と上司命令で実現するのが企業だとしたら、我々コミュニティがやるべきことは何か。
お金と苦労の等価交換ではなく、自分がこれまで経験した楽しさを喜んで人に伝えるとか、コミュニティで得たものを還元するとか、そういう気持ちが原料になってデッドロック状態を解放することができるのがコミュニティなのだと思う。

■さて、自分にできることは


ということを意識しはじめた今年の6月、このブログをはじめた。(更新サボってるけど。それでも死んではいない。)自分がコミュニティの中の人として何ができるか、何を発信できるかを考えてきた結果の一つが今回のチュートリアルになった。
この文脈から、「教えてやるよ」と言っちゃってるようなものなんだけど、自分のコンプレックスでもあるプログラミング経験のなさとかは世の技術者と比べたら一目瞭然で、「出来ることは教える、出来ないことは教えてもらう」を体現できる存在として、自分が、JPUGが、誰かが一歩踏み出すお手伝いをするのだ!と決意をもって始めるのである。

っていうか、これまでのJPUGの構成員は何から何まで優秀すぎるっていうか。だからこそ学ばせていただいたことがめちゃくちゃあるんだけど。
このチュートリアルを実現できたことをきっかけに、イチ庶民として、やれることをやっていきたいと思います。

2016年11月27日日曜日

OSC2016 広島に参加しました

秋の広島遠征!
中国地方DB勉強会OSC広島に参加してきました。

本エントリは、11月27日(日)のOSC広島についてです。


■発表資料


PostgreSQLへの移行入門
商用データベースからPostgreSQLへ まず知っておいて欲しいまとめ


その他のことは後日追記します。

2016年11月26日土曜日

第18回 中国地方DB勉強会 に参加しました

秋の広島遠征!
中国地方DB勉強会OSC広島に参加してきました。

本エントリは、11月26日(土)の中国地方DB勉強会についてです。

■はじめに

私はPostgreSQLの人として、Schooさんで講座を持たせてもらった「PostgreSQLで学ぶデータベース技術」を元ネタにツギハギし、2コマしゃべらせてもらいました。(Schooさんでの講座は、Webアプリ開発者向けに、PostgreSQLを使う人に知ってほしいことを全5回にまとめたもので、今回全く触れていない運用管理の話とか、PostgreSQLの使いどころをより詳しく説明しています!)

で、今回のネタ(特に1コマ目)は、データベース初級者に向けて、データベースの勉強をするきっかけになればと思ってつくったもので、初級者の方には純真な気持ちで受け止めてもらい、中上級者には、もっとこうしたらわかりやすい、的なご意見をいただいて、より良いものにして"大切な概念を易しく伝える "講義として息の長いものにしていけたらなーと思っています。

■発表資料

1コマ目:アプリ開発とデータベース、アプリ開発とPostgreSQL

アプリ開発者(プログラムを書く人、SQLを書く人)が、データベースってこういうところが便利、PostgreSQLってこう使える、と理解し、データベースの大事さを理解し、もう一段階成長するためにデータベースの勉強してみよう!と思っていただけることを目指しています。
その中での重要な話題として、「リレーショナルデータベース」「トランザクション」に絞って易しく解説しています。



2コマ目:SQL開発入門 - SELECTとパフォーマンス

仕事でSQLを書くけど、普段からそればっかりというワケじゃないし、ぶっちゃけ得意とは言えないみたいな方へ。
SQLを書く上でのちょっとしたポイントと、中級者になるために索引の構造やJOINのことを知り、パフォーマンスを意識しましょう、という話をしました。


Chugokudb18_2 from Kosuke Kida
 

だからといって、じゃあどうすればパフォーマンス良くなるの?と言われると、実データで実行例なんかを出せなかったのでイメージがしにくいかと思いますが、JPUGでこれまでに開催してきたパフォーマンスチューニング系の勉強会資料なんかを見て行く上で、大前提となるような話ですので、ここからより詳しい話を肉付けしていくのにお役立ていただければなと思っています。