![? 2017 NTT DATA Corporation 12
目的は、「パラレルクエリ活用」
CPU
? パラレルクエリの並列数×同時接続数で使用されるコア数の確保
メモリ
[本件の前提]データサイズが大きいため、全てがメモリに載りきるのは難しい
? 256GB
? 基本はIOで処理される前提で、IO重視
ストレージ
[本件の前提]ディスクアクセスが大量に走る
? SSD
? IOが弱いと、パラレルクエリの恩恵が受けられない
? RAID6
? 読み込み性能重視
ポイント(1) サイジング](https://image.slidesharecdn.com/postgresql10dwhcasestudy-171208044557/85/PostgreSQL10-DWH-PostgreSQL-12-320.jpg)
More Related Content
What's hot (20)
Similar to 笔辞蝉迟驳谤别厂蚕尝10を导入！大规模データ分析事例からみる顿奥贬としての笔辞蝉迟驳谤别厂蚕尝活用のポイント (20)
![[db tech showcase Tokyo 2015] C17:MySQL Cluster ユーザー事例紹介～ＪＲ東日本情報システム様における導入事例...](https://cdn.slidesharecdn.com/ss_thumbnails/dbts-tokyo-2015c17mysql-clusterhp-150619091220-lva1-app6891-thumbnail.jpg?width=560&fit=bounds)
More from NTT DATA OSS Professional Services (20)
Recently uploaded (11)
笔辞蝉迟驳谤别厂蚕尝10を导入！大规模データ分析事例からみる顿奥贬としての笔辞蝉迟驳谤别厂蚕尝活用のポイント
- 1. ? 2017 NTT DATA PostgreSQL10を導入！大規模データ分析事例 からみるDWHとしてのPostgreSQL活用のポイント 2017/12/5 株式会社NTTデータ
- 2. ? 2017 NTT DATA Corporation 2 ? 近年のPostgreSQLは、パラレルクエリをはじめとして、大量 データに対して分析クエリを流すようなDWHとしての用途で活 用できる機能が強化されています。 ? 本講演では、DWHとしてPostgreSQLを扱うときに、 PostgreSQLエンジニアが知っておくとよいポイントについて NTTデータでの事例を踏まえて紹介します。 はじめに
- 3. ? 2017 NTT DATA Corporation 3 ? はじめに ? システムの概要 ? ミッションと課題 ? 課題突破のポイント ? DWHの落とし穴 ? まとめ 目次
- 4. ? 2017 NTT DATA Corporation 4 ? 石井愛弓（いしいあゆみ） ? NTT データのPostgreSQL チームとして、社内プロジェクトへ 技術支援を実施。 自己紹介
- 5. ? 2017 NTT DATA Corporation 5 プロジェクト概要 ヘルスケア業界、大規模データ分析システム BIツールを使ってインタラクティブにデータ分析を行う基盤
- 6. ? 2017 NTT DATA Corporation 6 ? BIツールのバックエンドとしてPostgreSQLを選択 ? データサイズ：約10TB（約2年分）、 ? 最大テーブル：20億件の大規模データを対象に分析を行う プロジェクト概要 使用例1 TableauにてPostgreSQLを自由 に参照し、表化、グラフ化。（更新は 不可。） 使用例2 Rを用いて（もしくは直接）クエ リ実行を行い、統計処理を実施し、 表化、グラフ化。
- 7. ? 2017 NTT DATA Corporation 7 ? データを直感的操作で可視化するBIツール ? PostgreSQLやその他のRDBMS、ファイルなど様々なデータ ソースに対応 Tableau
- 8. ? 2017 NTT DATA Corporation 8 本件でデータベースに求められる要件 1. Tableau、R、psqlなど様々なフロントエンドからアクセスでき るデータベースであること 2. オープンソースであること 3. 大規模データセットに耐えられること なぜ、PostgreSQLなのか
- 9. ? 2017 NTT DATA Corporation 9 ? Tableauからのレスポンスを10秒で実現せよ ? データベースサイズは全部合わせて約10TB ? 一度のクエリで全体にアクセスすると、達成できない →目的別データマートを用意 ミッション
- 10. ? 2017 NTT DATA Corporation 10 データを取り込むまで Hadoop基盤 データ変換 ＡＰ Spark(scala) マスタ １ データ変換 DWH（生データ） ?点数分解、省略部 分のデータ反映、一 連行為の単位でキー 付与等。 ?名寄せ マスタ ２ マスタ ３ 生データ（一部） 目的別 データマート レスポンスを考慮して 加工?絞込み
- 11. ? 2017 NTT DATA Corporation 11 ? 小手先でうまくいかないのがDWH ? SQLの書き換えで高速化？ ? SQLを作るのはTableauなので、SQLチューニングは不可。 ? pg_hint_planでコメントを付け実行計画誘導もできない。 ? インデックスで高速化？ ? 人の操作次第で、どんなクエリがくるか予想が難しい ? インデックスをどこにはるかが難しい ? とりあえず絞込みなしでGROUP BYがほとんど →パラレルクエリの見せ所！ そのために、まずはハードが強くないと闘えない！ DWHで高速化するための課題
- 12. ? 2017 NTT DATA Corporation 12 目的は、「パラレルクエリ活用」 CPU ? パラレルクエリの並列数×同時接続数で使用されるコア数の確保 メモリ [本件の前提]データサイズが大きいため、全てがメモリに載りきるのは難しい ? 256GB ? 基本はIOで処理される前提で、IO重視 ストレージ [本件の前提]ディスクアクセスが大量に走る ? SSD ? IOが弱いと、パラレルクエリの恩恵が受けられない ? RAID6 ? 読み込み性能重視 ポイント(1) サイジング
- 13. ? 2017 NTT DATA Corporation 13 ? PostgreSQL9.6で導入されたパラレルクエリ ? 複数CPUを活用し複数プロセスが並列で処理をする ? DWHとしての用途で性能面で効果が大きい ? 9.6では、Seq Scanなど一部のScan/Join方法のみ利用 可 ? 10ではIndex ScanやMerge Joinなど幅が広がった ? まだリリースされていなかった10の採用を前提に設計 ? Beta版を使って検証 ? スペアとして9.6の設計も行い共存させた ポイント(2) パラレルクエリ
- 14. ? 2017 NTT DATA Corporation 14 パラレル検証 並列数はどれぐらいが最適？
- 15. ? 2017 NTT DATA Corporation 15 パラレルクエリ検証 ?各Parallel値毎に10回計測 /平均 ?生データ分 160GB / 20億件 0 50 100 150 200 250 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 実行時間（秒） パラレル度 デフォルト パラレル最大値 SELECT count(*) from テーブル
- 16. ? 2017 NTT DATA Corporation 16 パラレルクエリ検証 ?各Parallel値毎に10回計測 /平均 ?生データ分 160GB / 20億件 0 50 100 150 200 250 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 実行時間（秒） パラレル度 SELECT count(*) from テーブル デフォルト パラレル最大値 OSとPostgreSQLのキャッシュをクリアした場合
- 17. ? 2017 NTT DATA Corporation 17 パラレルクエリ検証 0 50 100 150 200 250 1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 実行時間（秒） パラレル度 キャッシュ無 キャッシュ有 SELECT count(*) from テーブル デフォルト パラレル最大値 ? utilはほぼ100% ? pg_stat_activityはIO/DataFileRead ? IO量は基礎性能測定に達していない
- 18. ? 2017 NTT DATA Corporation 18 ? pg_stat_activityで以下のカラムが詳細化された ? wait_event_type ? wait_event ? wait_event_type ? LWLock ? Lock ? BufferPin ? Activity ? Extension ? Client ? IPC ? Timeout ? IO 補足：PostgreSQL10のpg_stat_activity
- 19. ? 2017 NTT DATA Corporation 19 ? キャッシュに乗っている場合、デフォルトの最大並列数よりも、 並列数を上げる設定をすると性能向上 ? キャッシュに乗らない場合、デフォルトの最大並列数のあたりで 性能が伸びなくなった ? 並列度を増やしすぎるとオーバヘッドにより性能が悪化 パラレル検証の結果
- 20. ? 2017 NTT DATA Corporation 20 ? shared_buffers検証 ポイント(3) パラメータ サーバ搭載メモリ： 256GB データ量： 160GB データ件数： 20億件 shared_ buffers OSキャッシュサイ ズ(想定) 20 236 40 216 60 196 80 176 100 156 120 136 140 116 160 96 180 76 200 56 0 10 20 30 40 50 60 0 50 100 150 200 実行時間（秒） shared_buffers(GB) select count(*) Seq Scan 同じクエリを2回実行した結果 （キャッシュ有）
- 21. ? 2017 NTT DATA Corporation 21 ? PostgreSQLは、取得結果が大きく、共有バッファの大部分を 占めてしまいそうな場合、shared_buffersを全て使わず、リ ングバッファ内にとどめる ? 逆に、shared_buffersを小さくして、OSキャッシュを大きくし たほうが、結果がキャッシュに載りやすくなる なぜshared_buffersを大きくしてもだめ？ 共有バッファ リングバッファ
- 22. ? 2017 NTT DATA Corporation 22 ? SSDの場合、ランダムスキャンが強い ? random_page_costはseq_page_cost（1）に近づけ るべし ? デフォルトの4で計算するとインデックススキャンのコストが過大に 評価され、インデックススキャンのほうが速くても選ばれにくい ポイント(3) パラメータ
- 23. ? 2017 NTT DATA Corporation 23 ? pg_stat_statementで頻出するクエリなどを調査 ? よく使われるカラムに対して、インデックスの付与を検討 ? BRIN(Block Range Index)に注目 ? PostgreSQL9.5から追加された メリット ? 大規模テーブルに対して、範囲検索を行う場合高速 ? 特に、キー値の値が物理的に連続している場合（連番など） ? インデックスサイズが小さい ? インデックス作成時間が短い →DWH用途に向いている ポイント(4) インデックス
- 24. ? 2017 NTT DATA Corporation 24 Block Range Index Block Range(min/max) 1 - 128 1 ~ 1000 129 - 256 1001 ~ 2000 : : インデックス作成 CREATE INDEX hoge_brin ON hoge USING brin(col); 近接している ブロックの束に対して 列の最小値/最大値 をインデックスに記録 ： テーブル BRINインデックス128 ブロック 128 ブロック 128 ブロック 128 ブロック ブロック 凡例
- 25. ? 2017 NTT DATA Corporation 25 Block Range Index Block Range(min/max) 1 - 128 1 ~ 1000 129 - 256 1001 ~ 2000 : : 検索する範囲を 絞り高速に検索 ： テーブル BRINインデックス 検索 SELECT * FROM hoge WHERE col BETWEEN 1500 AND 1700; ブロック 凡例
- 26. ? 2017 NTT DATA Corporation 26 インデックス検証 37 11 0 5 10 15 20 25 30 35 40 btree brin 作成時間（分） インデックス作成時間
- 27. ? 2017 NTT DATA Corporation 27 ? 処理時間は、データ分布（物理的に連続しているか）と取得 件数の影響大 ? PostgreSQLのプランナはseqscanを選んだ (random_page_cost = 1にもかかわらず） インデックス検証 49 5 5 0 10 20 30 40 50 60 Seq btree brin 実行時間（秒） select * from table between xx < col1 AND col2 < yy; ※全体の30%程度を取得
- 28. ? 2017 NTT DATA Corporation 28 ? pg_hint_planの「テーブルでの指定」を使う ? コメントを使った方法と異なり、クエリを書き換えられなくても、 「hint_plan.hints」テーブルに、実行計画を制御したいクエ リとヒントを登録しておくと、ヒントが効く 対処法として1つのアイデア =# explain analyze select * from test where id = 1; QUERY PLAN ------------------------------------------------------------------------- Index Only Scan using a on test (cost=0.29..8.30 rows=1 width=4) … Index Cond: (id = 1) Heap Fetches: 1 Planning time: 0.054 ms Execution time: 0.027 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=0.00..170.00 rows=1 width=4) … Filter: (id = 1) Rows Removed by Filter: 9999 Planning time: 0.031 ms Execution time: 0.808 ms (5 行)
- 29. ? 2017 NTT DATA Corporation 29 DWHの落とし穴
- 30. ? 2017 NTT DATA Corporation 30 ? PostgreSQLにpsqlで接続し、直接クエリを実行するとパラレ ルになる ? がTableau経由でクエリを実行するとパラレルにならない ? クエリによっては、操作後、表示まで10分くらいかかってしまう。 →log_statement=allにしてサーバログを確認！ パラレルクエリが効かない!?
- 31. ? 2017 NTT DATA Corporation 31 ? TableauのPostgreSQL接続では、デフォルトでカーソルが定 義される ? クライアントに必要なメモリを抑えるため ? PostgreSQLの仕様上、カーソルが使われると、パラレルに ならない パラレルクエリが効かない!?(1) 2017-05-12 17:04:47 JST LOG: statement: BEGIN;declare “SQL_CUR0000000006524770” 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のODBC接続で、DECLARE CURSORを使わないように設定
- 32. ? 2017 NTT DATA Corporation 32 ? カーソルは使わなくなったが、まだ使えない ? ログを眺めていると。。 パラレルクエリが効かない!?(2)
- 33. ? 2017 NTT DATA Corporation 33 ? カーソルは使わなくなったが、まだ使えない ? ログを眺めていると。。 ? tableauのデフォルトでシリアライザブルを設定。 ? PostgreSQLの仕様上、シリアライザブルの場合、パラレル にならない ? DWHではシリアライザブルがスタンダード？ ? Teradata, Netezza, redshiftもデフォルトシリアライザブル。 パラレルクエリが効かない!?(2) SET SESSION CHARACTERISTICS AS ISOLATION LEVEL SERIALIZABLE; Read committedに変更。 本件では、読み取りのみであるので、挙動に差異はない。
- 34. ? 2017 NTT DATA Corporation 34 ? 10.0では、Prepared Statementを使っていると、パラレル されない場合がある。 ? Custom plan …バインド変数を考慮した実行計画 ? Generic plan …バインド変数を考慮しない実行計画 パラレルクエリが効かない!?(3) C C C C C C G C C G G10.0ではパラレルされない (10.1で修正済み） TableauのODBC接続パラメータでPreparedを使わないように設定。 Prepared Statement利用によるプランニング時間の短縮はできなくなる。
- 35. ? 2017 NTT DATA Corporation 35 ? custom plan 補足：generic planであるかどうか？の確認 ? generic plan Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Seq Scan on tenk1 Filter: (hundred > 1) Aggregate -> Seq Scan on tenk1 Filter: (hundred > $1)
- 36. ? 2017 NTT DATA Corporation 36 ? EXPLANではパラレルプランになったが、EXPLAIN ANALYZEではパラレルにならない 例）max_worker_processes/max_parallel_workers による上限でワーカーが作成できない パラレルクエリが効かない!?(4) Finalize Aggregate (cost=75498.35..75498.36 rows=1 width=8) (actual time=2778.768..2778.768 rows=1 loops=1) -> Gather (cost=75497.93..75498.34 rows=4 width=8) (actual time=2778.761..2778.762 rows=1 loops=1) Workers Planned: 4 Workers Launched: 0 -> Partial Aggregate (cost=75497.93..75497.94 rows=1 width=8) (actual time=2778.595..2778.596 rows=1 loops=1) -> Parallel Seq Scan on test (cost=0.00..69247.94 rows=2499994 width=0) (actual time=0.015..1598.895 rows=10000000 loops=1) Planning time: 0.100 ms Execution time: 2778.937 ms (8 rows)
- 37. ? 2017 NTT DATA Corporation 37 ? パラレルプランになったが、パラレル度が増えない ? そもそも、パラレル度はどのように決まるのか？ (1)PostgreSQLがコストとプロセス数上限を考慮 ? parallel_setup_cost ? parallel_tuple_cost ? max_parallel_workers_per_gather ? max_worker_processes (2)PostgreSQLがテーブルサイズで上限を計算 (3)ユーザがテーブルごとのパラメータ設定により上限を調整 ? parallel_workers ? ALTER TABLEでparallel workersを設定すれば、テーブルサイズから 計算されるパラレル数上限を突破できる。 パラレル度が増えない!? テーブルサイズ 8MB 24MB 72MB 216MB 648MB 1.8GB パラレル度上限 1 2 3 4 5 6 ※min_parallel_table_scan_size=8MBの場合
- 38. ? 2017 NTT DATA Corporation 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のマニュアルを確認 パラレルクエリが効かなかったら
- 39. ? 2017 NTT DATA Corporation 39 ? PostgreSQL10、新しい時代の幕開け ? DWH用途としてのPostgreSQLには、課題も残っているが、 十分闘える ? 特にパラレルクエリの貢献は大きい ? BIツールのバックエンドとして、PostgreSQLがスタンダードにな るかもしれない ? BIツールのカスタマイズに頼らなくてもパラレルクエリが使えるよう に、今後、制約が少なくなることに期待 まとめ
- 40. ? 2017 NTT DATA Corporation