PostgreSQL10 を導入! 大規模データ分析事例からみる DWH としての PostgreSQL 活用のポイント 2017/12/5 株式会社 NTT データ 2017 NTT DATA

Size: px

Start display at page:

Download "PostgreSQL10 を導入! 大規模データ分析事例からみる DWH としての PostgreSQL 活用のポイント 2017/12/5 株式会社 NTT データ 2017 NTT DATA"

たかとしあさま
5 years ago
Views:

1 PostgreSQL10 を導入! 大規模データ分析事例からみる DWH としての PostgreSQL 活用のポイント 2017/12/5 株式会社 NTT データ 2017 NTT DATA

2 はじめに近年の PostgreSQL はパラレルクエリをはじめとして大量データに対して分析クエリを流すような DWH としての用途で活用できる機能が強化されています本講演では DWH として PostgreSQL を扱うときに PostgreSQL エンジニアが知っておくとよいポイントについて NTT データでの事例を踏まえて紹介します 2017 NTT DATA Corporation 2

3 目次はじめにシステムの概要ミッションと課題課題突破のポイント DWH の落とし穴まとめ 2017 NTT DATA Corporation 3

4 自己紹介石井愛弓 ( いしいあゆみ ) NTT データの PostgreSQL チームとして社内プロジェクトへ技術支援を実施 2017 NTT DATA Corporation 4

5 プロジェクト概要ヘルスケア業界大規模データ分析システム BIツールを使ってインタラクティブにデータ分析を行う基盤 2017 NTT DATA Corporation 5

Tableau にて PostgreSQL を自由に参照し表化グラフ化 ( 更新は不可 ) 使用例 2 R

6 プロジェクト概要 BI ツールのバックエンドとして PostgreSQL を選択データサイズ : 約 10TB( 約 2 年分 ) 最大テーブル :20 億件の大規模データを対象に分析を行う使用例 1 Tableau にて PostgreSQL を自由に参照し表化グラフ化 ( 更新は不可 ) 使用例 2 R を用いて ( もしくは直接 ) クエリ実行を行い統計処理を実施し表化グラフ化 2017 NTT DATA Corporation 6

7 Tableau データを直感的操作で可視化する BI ツール PostgreSQL やその他の RDBMS ファイルなど様々なデータソースに対応 2017 NTT DATA Corporation 7

8 なぜ PostgreSQL なのか本件でデータベースに求められる要件 1. Tableau R psql など様々なフロントエンドからアクセスできるデータベースであること 2. オープンソースであること 3. 大規模データセットに耐えられること 2017 NTT DATA Corporation 8

9 ミッション Tableau からのレスポンスを 10 秒で実現せよデータベースサイズは全部合わせて約 10TB 一度のクエリで全体にアクセスすると達成できない目的別データマートを用意 2017 NTT DATA Corporation 9

10 データを取り込むまで Hadoop基盤データ変換目的別データマートデータ変換ＡＰ Spark(scala) マスタ１マスタ２マスタ３点数分解省略部分のデータ反映一連行為の単位でキー付与等 2017 NTT DATA Corporation DWH 生データ名寄せ生データ一部 10 レスポンスを考慮して加工絞込み

11 DWH で高速化するための課題小手先でうまくいかないのが DWH SQL の書き換えで高速化? SQL を作るのは Tableau なので SQL チューニングは不可 pg_hint_plan でコメントを付け実行計画誘導もできないインデックスで高速化? 人の操作次第でどんなクエリがくるか予想が難しいインデックスをどこにはるかが難しいとりあえず絞込みなしで GROUP BY がほとんどパラレルクエリの見せ所! そのためにまずはハードが強くないと闘えない! 2017 NTT DATA Corporation 11

12 ポイント (1) サイジング目的はパラレルクエリ活用 CPU パラレルクエリの並列数同時接続数で使用されるコア数の確保メモリ [ 本件の前提 ] データサイズが大きいため全てがメモリに載りきるのは難しい 256GB 基本は IO で処理される前提で IO 重視ストレージ [ 本件の前提 ] ディスクアクセスが大量に走る SSD IO が弱いとパラレルクエリの恩恵が受けられない RAID6 読み込み性能重視 2017 NTT DATA Corporation 12

13 ポイント (2) パラレルクエリ PostgreSQL9.6 で導入されたパラレルクエリ複数 CPU を活用し複数プロセスが並列で処理をする DWH としての用途で性能面で効果が大きい 9.6 では Seq Scan など一部の Scan/Join 方法のみ利用可 10 では Index Scan や Merge Join など幅が広がったまだリリースされていなかった 10 の採用を前提に設計 Beta 版を使って検証スペアとして 9.6 の設計も行い共存させた 2017 NTT DATA Corporation 13

14 パラレル検証並列数はどれぐらいが最適? 2017 NTT DATA Corporation 14

15 実行時間 ( 秒 ) パラレルクエリ検証 250 SELECT count(*) from テーブルデフォルトパラレル最大値パラレル度各 Parallel 値毎に 10 回計測 / 平均生データ分 160GB / 20 億件 2017 NTT DATA Corporation 15

16 実行時間 ( 秒 ) パラレルクエリ検証 OS と PostgreSQL のキャッシュをクリアした場合 250 SELECT count(*) from テーブルデフォルトパラレル最大値パラレル度各 Parallel 値毎に 10 回計測 / 平均生データ分 160GB / 20 億件 2017 NTT DATA Corporation 16

17 実行時間 ( 秒 ) パラレルクエリ検証 SELECT count(*) from テーブルキャッシュ無デフォルトパラレル最大値キャッシュ有パラレル度 util はほぼ 100% pg_stat_activity は IO/DataFileRead IO 量は基礎性能測定に達していない 2017 NTT DATA Corporation 17

18 補足 :PostgreSQL10 の pg_stat_activity pg_stat_activity で以下のカラムが詳細化された wait_event_type wait_event wait_event_type LWLock Lock BufferPin Activity Extension Client IPC Timeout IO 2017 NTT DATA Corporation 18

19 パラレル検証の結果キャッシュに乗っている場合デフォルトの最大並列数よりも並列数を上げる設定をすると性能向上キャッシュに乗らない場合デフォルトの最大並列数のあたりで性能が伸びなくなった並列度を増やしすぎるとオーバヘッドにより性能が悪化 2017 NTT DATA Corporation 19

20 実行時間 ( 秒 ) ポイント (3) パラメータ shared_buffers 検証 select count(*) Seq Scan 同じクエリを 2 回実行した結果 ( キャッシュ有 ) shared_buffers(gb) サーバ搭載メモリ : 256GB データ量 : 160GB データ件数 : 20 億件 shared_ buffers OS キャッシュサイズ ( 想定 ) NTT DATA Corporation 20

21 なぜ shared_buffers を大きくしてもだめ? PostgreSQL は取得結果が大きく共有バッファの大部分を占めてしまいそうな場合 shared_buffers を全て使わずリングバッファ内にとどめる逆に shared_buffers を小さくして OS キャッシュを大きくしたほうが結果がキャッシュに載りやすくなるリングバッファ共有バッファ 2017 NTT DATA Corporation 21

22 ポイント (3) パラメータ SSD の場合ランダムスキャンが強い random_page_cost は seq_page_cost(1) に近づけるべしデフォルトの 4 で計算するとインデックススキャンのコストが過大に評価されインデックススキャンのほうが速くても選ばれにくい 2017 NTT DATA Corporation 22

23 ポイント (4) インデックス pg_stat_statement で頻出するクエリなどを調査よく使われるカラムに対してインデックスの付与を検討 BRIN(Block Range Index) に注目 PostgreSQL9.5 から追加されたメリット大規模テーブルに対して範囲検索を行う場合高速特にキー値の値が物理的に連続している場合 ( 連番など ) インデックスサイズが小さいインデックス作成時間が短い DWH 用途に向いている 2017 NTT DATA Corporation 23

24 Block Range Index インデックス作成 CREATE INDEX hoge_brin ON hoge USING brin(col); テーブル 128 ブロック 128 ブロック 128 ブロック 128 ブロック : 近接しているブロックの束に対して列の最小値 / 最大値をインデックスに記録 BRIN インデックス Block Range(min/max) ~ ~ 2000 : : 凡例ブロック 2017 NTT DATA Corporation 24

25 Block Range Index 検索 SELECT * FROM hoge WHERE col BETWEEN 1500 AND 1700; テーブル BRIN インデックス検索する範囲を絞り高速に検索 Block Range(min/max) ~ ~ 2000 : : : 凡例ブロック 2017 NTT DATA Corporation 25

26 作成時間 ( 分 ) インデックス検証インデックス作成時間 btree brin 2017 NTT DATA Corporation 26

27 実行時間 ( 秒 ) インデックス検証 select * from table between xx < col1 AND col2 < yy; 全体の 30% 程度を取得 Seq btree brin 処理時間はデータ分布 ( 物理的に連続しているか ) と取得件数の影響大 PostgreSQL のプランナは seqscan を選んだ (random_page_cost = 1 にもかかわらず ) 2017 NTT DATA Corporation 27

28 対処法として 1 つのアイデア pg_hint_plan のテーブルでの指定を使うコメントを使った方法と異なりクエリを書き換えられなくても hint_plan.hints テーブルに実行計画を制御したいクエリとヒントを登録しておくとヒントが効く =# explain analyze select * from test where id = 1; QUERY PLAN Index Only Scan using a on test (cost= rows=1 width=4) Index Cond: (id = 1) Heap Fetches: 1 Planning time: ms Execution time: ms (5 行 ) =# set pg_hint_plan.enable_hint_table to on; SET =# explain analyze select * from test where id = 1; QUERY PLAN Seq Scan on test (cost= rows=1 width=4) Filter: (id = 1) Rows Removed by Filter: 9999 Planning time: ms Execution time: ms (5 行 ) 2017 NTT DATA Corporation 28

29 DWH の落とし穴 2017 NTT DATA Corporation 29

30 パラレルクエリが効かない!? PostgreSQL に psql で接続し直接クエリを実行するとパラレルになるが Tableau 経由でクエリを実行するとパラレルにならないクエリによっては操作後表示まで 10 分くらいかかってしまう log_statement=all にしてサーバログを確認! 2017 NTT DATA Corporation 30

31 パラレルクエリが効かない!?(1) :04:47 JST LOG: statement: BEGIN;declare SQL_CUR cursor for select c.relname, i.indkey, i.indisunique, i.indisclustered, a.amname, c.relhasrules, n.nspname, c.oid, d.relhasoids, i.indoption from pg_catalog.pg_index i, pg_catalog.pg_class c, ( 略 ) Tableau の PostgreSQL 接続ではデフォルトでカーソルが定義されるクライアントに必要なメモリを抑えるため PostgreSQL の仕様上カーソルが使われるとパラレルにならない Tableau の ODBC 接続で DECLARE CURSOR を使わないように設定 2017 NTT DATA Corporation 31

32 パラレルクエリが効かない!?(2) カーソルは使わなくなったがまだ使えないログを眺めていると 2017 NTT DATA Corporation 32

33 パラレルクエリが効かない!?(2) カーソルは使わなくなったがまだ使えないログを眺めていると SET SESSION CHARACTERISTICS AS ISOLATION LEVEL SERIALIZABLE; tableau のデフォルトでシリアライザブルを設定 PostgreSQL の仕様上シリアライザブルの場合パラレルにならない Read committed に変更本件では読み取りのみであるので挙動に差異はない DWH ではシリアライザブルがスタンダード? Teradata, Netezza, redshift もデフォルトシリアライザブル 2017 NTT DATA Corporation 33

34 パラレルクエリが効かない!?(3) 10.0 では Prepared Statement を使っているとパラレルされない場合がある Custom plan バインド変数を考慮した実行計画 Generic plan バインド変数を考慮しない実行計画 C C C C C C C C 10.0 ではパラレルされない (10.1 で修正済み ) G G G Tableau の ODBC 接続パラメータで Prepared を使わないように設定 Prepared Statement 利用によるプランニング時間の短縮はできなくなる 2017 NTT DATA Corporation 34

35 補足 :generic plan であるかどうか? の確認 custom plan Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Seq Scan on tenk1 Filter: (hundred > 1) generic plan Aggregate -> Seq Scan on tenk1 Filter: (hundred > $1) 2017 NTT DATA Corporation 35

36 パラレルクエリが効かない!?(4) EXPLAN ではパラレルプランになったが EXPLAIN ANALYZE ではパラレルにならない例 )max_worker_processes /max_parallel_workers による上限でワーカーが作成できない Finalize Aggregate (cost= rows=1 width=8) (actual time= rows=1 loops=1) -> Gather (cost= rows=4 width=8) (actual time= rows=1 loops=1) Workers Planned: 4 Workers Launched: 0 -> Partial Aggregate (cost= rows=1 width=8) (actual time= rows=1 loops=1) -> Parallel Seq Scan on test (cost= rows= width=0) (actual time= rows= loops=1) Planning time: ms Execution time: ms (8 rows) 2017 NTT DATA Corporation 36

37 パラレル度が増えない!? パラレルプランになったがパラレル度が増えないそもそもパラレル度はどのように決まるのか? (1)PostgreSQL がコストとプロセス数上限を考慮 parallel_setup_cost parallel_tuple_cost max_parallel_workers_per_gather max_worker_processes (2)PostgreSQL がテーブルサイズで上限を計算 min_parallel_table_scan_size=8mb の場合テーブルサイズ 8MB 24MB 72MB 216MB 648MB 1.8GB パラレル度上限 (3) ユーザがテーブルごとのパラメータ設定により上限を調整 parallel_workers ALTER TABLE で parallel workers を設定すればテーブルサイズから計算されるパラレル数上限を突破できる 2017 NTT DATA Corporation 37

38 パラレルクエリが効かなかったらまずはパラメータを確認 max_parallel_workers_per_gather dynamic_shared_memory_type max_worker_processes parallel_workers コストをさげてみる parallel_setup_cost = 0 parallel_tuple_cost = 0 パラレルにならない条件を確認カーソルを使っていないか? シリアライザブルになっていないか? Preapared Statement の generic plan になっていないか?(10.0 のみ ) その他 PostgreSQL のマニュアルを確認 2017 NTT DATA Corporation 38

39 まとめ PostgreSQL10 新しい時代の幕開け DWH 用途としての PostgreSQL には課題も残っているが十分闘える特にパラレルクエリの貢献は大きい BI ツールのバックエンドとして PostgreSQL がスタンダードになるかもしれない BI ツールのカスタマイズに頼らなくてもパラレルクエリが使えるように今後制約が少なくなることに期待 2017 NTT DATA Corporation 39

40 2017 NTT DATA Corporation

PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗

PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗アジェンダ 1.EXPLAIN とは 2. 表アクセスの基本 3. 結合の基本 4. 統計情報とは 5.EXPLAIN コマンド 6. 問題解決例 7. まとめ 2 1.EXPLAIN とは実行計画とは - 目的地は 1 つでもアクセス方法は複数