タイトル1

Size: px

Start display at page:

Download "タイトル1"

けいしょううみのなか
5 years ago
Views:

1 料金系基幹システムへのPostgreSQL導入事例成功までの道のり 2015年9月11日 NTTコムウェア株式会社朝倉佑貴 NTT OSSセンタ山田達朗

2 目次各社のご紹介 NTT OSSセンタの紹介 NTTコムウェアについてストリーミングの事例 1 システムの概要クエリの性能安定化の事例 2 開発内容まとめ本プロジェクトの最大のミッション性能特性の異なる業務を共存させよ仮想化基盤上で性能要件を満たせ SQL実行時間をコントロールせよ開発を振り返って思うこと 1

3 各社のご紹介 NTTグループ全体で TCO削減のためOSSを積極活用事業会社 SIer NTT OSSセンタが連携して OSSを活用したシステムを開発 NTT コミュニケーションズ NTTコムウェア Global ICT Partner として最新のテクノロジーと安全で信頼性の高いICT サービスを提供開発 Arcstar IP Voice (IP電話サービス) など法人のお客さまにサービスを展開また個人のお客さまにも多くのサービスを展開支援運用保守 Arcstar Universal One (VPNサービス) OCNモバイル ONE for Business (モバイル通信サービス) 品質生産性技術本部プロジェクト開発支援ノウハウ展開ミッションクリティカルシステムの更改実績をベースに高品質なICT基盤を提供運用協力 NTT OSSセンタ開発 OSS活用によるNTT グループ全体のシステムのTCO削減を目的に活動連携 OSS コミュニティ連携研究所等 NTT 2

4 NTTコムウェアにおけるOSSへの取り組み NTTの通信ネットワークや顧客サービス業務を支えてきた技術力を基に NTTグループ内外のお客様へのSIerとして幅広く活動大規模高信頼領域への適用を視野に 2000年代初めから PostgreSQLをはじめとする OSSへの取り組みを積極的に推進 2000年 Linuxセンタ設立 Linuxに関するサポート開始 2004年 PostgreSQLのサポート開始(PostgreSQL 7.4) 2010年 NTT事業会社中規模注文管理システムへ PostgreSQL含むOSSを全面導入 2013年 NTT事業会社大規模履歴管理システムへのPostgreSQL導入ご紹介事例 2015年 NTTコミュニケーションズ料金系基幹システムへのPostgreSQL導入 3

5 ご紹介するシステムの概要 NTTコミュニケーションズにおいて経営基盤ともなる料金系の基幹システム主に数百万契約の法人のお客さまが利用する100種類以上のサービスに関して料金計算や請求情報作成などを行い多様な情報分析も行う法人のお客さま数百万契約請求情報情報分析結果社内利用者ご紹介するシステム情報分析料金計算ご契約ご利用請求情報作成業務データ過去履歴法人のお客さま向けサービス 100種類以上 NTTコミュニケーションズ回収処理支払い情報...など数TB 関連システム群オンライン処理結果 4

6 今回の開発内容商用運用されているレガシーシステムのアーキテクチャを刷新 TCO削減と今後の適用領域拡大に向けた礎として戦略的にPostgreSQLを採用 COBOL 情報分析業務商用 TPモニタ商用 UNIX UNIXサーババッチ業務オンライン業務 JBoss 情報分析業務 PostgreSQL オンライン業務アーキテクチャ刷新バッチ業務商用 DBMS 契約数の伸びに応じた拡張性が必要なため NTT Comの仮想化基盤を採用 Java Linux 仮想化基盤 AP構造も刷新テーブル構成や処理ロジックなど含め完全な作り直しを行った 5

7 開発プロジェクトの最大のミッション最大のミッションそれは性能の担保大量データの計算処理を確実に時間内で完了しつつ処理時間の安定化も図れ大規模な分析処理を行いつつ関連システムからのオンライン処理をさばけ関連システム群バッチ処理AP DBMS オペレータ分析業務利用者オンライン処理 AP 情報分析処理 AP 業務データ過去履歴...など仮想化基盤時間帯処理内容夜間帯バッチ処理日中帯オンライン処理情報分析処理夜... バ... 6

8 開発プロジェクトの最大のミッション最大のミッションそれは性能の担保大量データの計算処理を確実に時間内で完了しつつ処理時間の安定化も図れ大規模な分析処理を行いつつ関連システムからのオンライン処理をさばけ合計数TB 関連システム群 5000弱のジョブが数時間かけてDB中のデータを参照更新オペレータ時間内の完了厳守分析業務利用者終わらないとオンライン業務を始められない時間帯処理内容夜間帯バッチ処理バッチ処理AP 数十テーブルの結合など大規模分析系 SQLが大量に DBMS オンライン処理 AP 情報分析処理 AP 業務データ過去履歴...など性能特性が異なる2つの処理を同時に実行仮想化基盤日中帯オンライン処理情報分析処理仮想化基盤上で実現できるのか? 夜... バ... 7

9 本日のおはなし最大のミッション性能の担保の達成に向け全力で戦い抜いた壮絶(?)なストーリー運用に耐えうるバッチ処理性能特性の異なる業務が互いに影響しないよう設計せよ時間を担保せよ仮想化基盤上で性能要件を満たせ PostgreSQLのSQL実行時間をコントロールせよ 8

10 本日のおはなし最大のミッション性能の担保の達成に向け全力で戦い抜いた壮絶(?)なストーリー運用に耐えうるバッチ処理性能特性の異なる業務が互いに影響しないよう設計せよ時間を担保せよ仮想化基盤上で性能要件を満たせ PostgreSQLのSQL実行時間をコントロールせよ 9

11 性能特性の異なる業務が互いに影響しないよう設計せよ常に最新のデータに対して大規模分析処理を行いたいでもオンライン処理の性能に影響を与えたくないストリーミングレプリケーションで分析処理用のリードレプリカを構築バッチ処理AP オンライン処理 AP 情報分析処理 AP マスタリードレプリカレプリケーション DBが2つに分かれ分析処理がオンライン処理に影響しないレプリケーションで最新データに対する分析処理が可能 10

12 性能特性の異なる業務が互いに影響しないよう設計せよ常に最新のデータに対して大規模分析処理を行いたいでもオンライン処理の性能に影響を与えたくないストリーミングレプリケーションで分析処理用のリードレプリカを構築レプリケーション処理によって分析処理の SQLが実行中に中断されないよう設定(*) する必要ありバッチ処理AP オンライン処理 AP 情報分析処理 AP マスタリードレプリカレプリケーション DBが2つに分かれ分析処理がオンライン処理に影響しないレプリケーションで最新データに対する分析処理が可能 (*) max_standby_streaming_delayを-1としたデフォルトでは WALの再生に30秒以上かかるとレプリカ側のSQLが中断される 11

レプリカ側でレプリケーション処理によって分析処理の SQLが実行中に中断されないよう設定(*)

13 性能特性の異なる業務が互いに影響しないよう設計せよ常に最新のデータに対して大規模分析処理を行いたいでもオンライン処理の性能に影響を与えたくないストリーミングレプリケーションで分析処理用のリードレプリカを構築レプリカ側でレプリケーション処理によって分析処理の SQLが実行中に中断されないよう設定(*) する必要あり AP 情報分析処理 AP リードレプリカレプリケーション DBが2つに分かれ分析処理がオンライン処理に影響しないテーブルへのデータ反映処理が全く進まなくなった!! バッチ処理AP NTT OSSセンタの助力もありレプリケーションで最新データに対する無事解決マスタ分析処理が可能オンライン処理対処策は後半で 12

14 本日のおはなし最大のミッション性能の担保の達成に向け全力で戦い抜いた壮絶(?)なストーリー運用に耐えうるバッチ処理性能特性の異なる業務が互いに影響しないよう設計せよ時間を担保せよ仮想化基盤上で性能要件を満たせ PostgreSQLのSQL実行時間をコントロールせよ 13

15 運用に耐えうるバッチ処理時間を担保せよ - 仮想化基盤上で性能要件を満たせ実機検証の必要性 PostgreSQL 仮想化基盤の採用には性能面の懸念課題があったマシンリソースを数十GBの表同士を限界まで使い切って結合集計するSQLが処理時間を縮めたいが多重で走るけど広範囲のデータにどうしたらいい? 大丈夫? これほど大きなアクセスするバッチ系システムをけど大丈夫? PostgreSQLと仮想化環境で実現するのは初めて実機検証に基づいたハードウェアのサイジングを実施 14

16 運用に耐えうるバッチ処理時間を担保せよ - 仮想化基盤上で性能要件を満たせ実機検証の内容 5000弱にもおよぶジョブ(*)のうち特にCPUとディスクI/O負荷が高い6つのジョブに絞って実機検証を効率化 (*) ジョブ: バッチ処理を構成するプログラムの一単位多数のSQLで構成されているジョブ走行スケジュール負荷状況 ② バッチジョブの走行スケジュールから負荷が高い時間帯の処理を抽出 ① 負荷状況からCPUやディスクI/O負荷が高い時間帯を確認試験に必要な最小限の6つの処理に絞って試験用APを作成試験用AP ジョブA ジョブB 共通処理負荷ジョブ (5000弱) 既存システムの負荷状況を詳細分析ジョブC ジョブD ジョブE ジョブF 時系列 15

17 運用に耐えうるバッチ処理時間を担保せよ - 仮想化基盤上で性能要件を満たせ実機検証の結果実機検証の結果特にディスクI/O リソースが多く必要と判明必要なリソース量が定量的に分かり NTT Comで確保してもらえた WAL出力量削減など今後のPostgreSQLの進化に期待 NTT Comにはマシンリソース確保に尽力してもらった PostgreSQL 仮想化基盤において十分な性能を得られる環境が整った 16

18 本日のおはなし最大のミッション性能の担保の達成に向け全力で戦い抜いた壮絶(?)なストーリー運用に耐えうるバッチ処理性能特性の異なる業務が互いに影響しないよう設計せよ時間を担保せよ仮想化基盤上で性能要件を満たせ PostgreSQLのSQL実行時間をコントロールせよ 17

19 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよバッチ性能への要求運用に耐えうるバッチ性能への要求は2つ 1 時間内で確実にバッチ処理を完了せよ朝までにバッチ処理を確実に完了しなければオンライン業務は開始できない 5000弱の膨大なジョブを抱える本システムではたった一つのジョブの性能遅延が命取りとなる 2 バッチ性能の将来予測を可能にせよ商品契約数の増加によってバッチ処理時間も増加し続けるため将来的にはマシンリソースの増強が必要になるリソース増強時期を見極めるためには安定した性能傾向が必要でありバッチ処理時間をコントロールする必要があるバッチ性能のコントロールが命題 DB処理中心の本システムでは SQL実行時間のコントロールが必須 18

日中帯の処理を開始できなくなる夜間帯ジョブA ジョブB ジョブD ジョブE ジョブF もし処理遅延が発生したら.

20 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ時間内に完了せよ 1 時間内で確実にバッチ処理を完了せよ大規模バッチシステムの特徴は多数の待ち合わせジョブが存在することたった一つのSQL処理遅延により後続ジョブのスタートが遅れバッチ処理時間オーバーに直結し日中帯の処理を開始できなくなる夜間帯ジョブA ジョブB ジョブD ジョブE ジョブF もし処理遅延が発生したら... 日中帯ジョブG ジョブH オンライン処理ジョブI 情報分析処理先行ジョブ全ての完了を待つ待ち合わせ夜間帯ジョブA ジョブB 日中... ジョブD ジョブE ジョブF SQL 処理遅延ジョブG ジョブH オンラ... ジョブI 情報分... 19

21 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ将来予測を可能にせよ 2 バッチ性能の将来予測を可能にせよ商品契約数は常に一定量に増加しかしバッチ処理時間が毎回大きく異なれば将来のバッチ処理時間予測が困難になるバッチ処理時間の変動をコントロールし一定の傾向を持たせ将来予測を可能にする方法を模索した 3時間 300万契約での処理時間が予測困難大きな変動 300万契約での処理時間が予測可能 2時間 1時間 3時間 100万契約 200万契約 300万契約バッチ処理時間をコントロール一定の傾向 2時間 1時間 100万契約 200万契約 300万契約 20

22 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行時間の重要性 DB全体にわたる参照更新処理はもちろんのことデータの集計や計算処理も SQLによって行うこれらSQLの実行時間がバッチ処理性能の鍵を握っていた凡例ジョブx 夜間帯ジョブD ジョブA ジョブE ジョブB ジョブF オンライン処理ジョブI ジョブの実行時間はほぼSQLの実行時間契約情報 SQL2 日中帯バッチ処理は同時に動く多数のジョブで構成顧客情報 SQL1 請求情報履歴情報情報分析処理 SQLが DB全体にわたる参照更新処理やデータ集計計算処理を行う割引情報支払情報商品情報 21

23 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行時間の制御テーブルやインデックスの構成 SQLそのものに問題がなくても完全にはSQL実行時間をコントロールできない SQL実行計画やその元となる統計情報までもコントロールする必要がある SQLやDDLはレビューや試験で問題ないことを確認済み作戦1: 統計情報を制御する最新の状態が反映された統計情報であることを作戦1: 統計情報を制御する保証 SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画統計情報 DDL テーブル構成インデックス構成処理時間テーブルインデックステーブルサイズ行数データの種類偏り... 22

運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行時間の制御テーブルやインデックスの構成 SQLそのものに問題がなくても完全にはSQL実行時間をコントロールできない SQL実行計画やその元となる統計情報までもコントロールする必要がある SQLやDDLはレビューや試験で問題ないことを確認済み SQL SQL SQL SQL

24 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行時間の制御テーブルやインデックスの構成 SQLそのものに問題がなくても完全にはSQL実行時間をコントロールできない SQL実行計画やその元となる統計情報までもコントロールする必要がある SQLやDDLはレビューや試験で問題ないことを確認済み SQL SQL SQL SQL SQL SQL SQL SQL SQL DDL テーブル構成インデックス構成作戦2: SQL実行計画を制御する実行するたびにSQL実行計画が異なり処理時間の変動が大きなものについて SQL実行計画を固定化作戦1: 統計情報を制御する SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画 SQL実行計画統計情報処理時間テーブルインデックステーブルサイズ行数データの種類偏り... 23

PostgreSQLが実行する自動ANALYZEでは適切なタイミングで統計情報が収集されず

SQL1 ジョブB 100万件 100万件 SELECT INSERT 行数統計情報 SQL2

25 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ統計情報の制御作戦 1 自動ANALYZEに頼らないことで統計情報をコントロール PostgreSQLが実行する自動ANALYZEでは適切なタイミングで統計情報が収集されず実データと統計情報にずれが発生したこのため正しいSQL実行計画が選択されなかったジョブA 処理 SQL1 ジョブB 100万件 100万件 SELECT INSERT 行数統計情報 SQL2 テーブル０件 SQL性能劣化 100 万件誤った実行計画を選択自動ANALYZE 0件０件 100万件自動ANALYZE完了までは統計情報は0件のまま時系列 24

バッチ中に適切なタイミングで統計情報を収集するための手動ANALYZE ジョブを作成正しい実行計画が選択されるように

ジョブB ANALYZE SQL1 SQL2 テーブル行数統計情報ジョブ2実行前に０件統計情報を取得

26 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ統計情報の制御作戦 1 自動ANALYZEに頼らないことで統計情報をコントロール正しい実行計画を選択させるために自動ANALYZEの使用をやめバッチ中に適切なタイミングで統計情報を収集するための手動ANALYZE ジョブを作成正しい実行計画が選択されるように統計情報をコントロールしたジョブA 処理 100万件 INSERT 統計情報収集ジョブ 100万件 SELECT ジョブB ANALYZE SQL1 SQL2 テーブル行数統計情報ジョブ2実行前に０件統計情報を取得 100万件手動ANALYZE ０件 100 万件 100 万件 100万件統計情報と実データが一致正しい実行計画を選択時系列 25

特に処理時間の変動が大きかったSQLについて pg_hint_planによって実行計画を固定し処理時間を安定させたテーブル検索方法インデックススキャンテーブル結合順契約

27 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行計画の制御作戦 2 pg_hint_planにより SQL実行計画をコントロール pg_hint_planは SQLヒント句により PostgreSQLのSQL実行計画を思うままにコントロールするツール特に処理時間の変動が大きかったSQLについて pg_hint_planによって実行計画を固定し処理時間を安定させたテーブル検索方法インデックススキャンテーブル結合順契約商品シーケンシャルスキャンどの実行計画が選択されるかは PostgreSQL任せ夜間帯バッチ処理ジョブn 商品契約日中帯オンライン処理情報分析処理夜... バ... 日... オ... 情... 26

28 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行計画の制御作戦 2 pg_hint_planにより SQL実行計画をコントロール pg_hint_planは SQLヒント句により PostgreSQLのSQL実行計画を思うままにコントロールするツール特に処理時間の変動が大きかったSQLについて pg_hint_planによって実行計画を固定し処理時間を安定させたテーブル検索方法インデックススキャンテーブル結合順 pg_hint_plan 契約商品シーケンシャルスキャン常に同じ商品実行計画が選択されるように制御夜間帯バッチ処理ジョブn 契約日中帯処理時間の変動をおさえこみオンライン処理予測できる SQL性能を実現情報分析処理夜... バ... 日... オ... 情... 27

運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行計画の制御作戦 2 pg_hint_planにより SQL実行計画をコントロール pg_hint_planは SQLヒント句により PostgreSQLのSQL実行計画を思うままにコントロールするツール特に処理時間の変動が大きかったSQLについて NTT OSSセンタ

29 運用に耐えうるバッチ処理時間を担保せよ - PostgreSQLのSQL実行時間をコントロールせよ SQL実行計画の制御作戦 2 pg_hint_planにより SQL実行計画をコントロール pg_hint_planは SQLヒント句により PostgreSQLのSQL実行計画を思うままにコントロールするツール特に処理時間の変動が大きかったSQLについて NTT OSSセンタ pg_hint_planによって実行計画を固定し処理時間を安定させた開発ツールテーブル結合順続きは後半で pg_hint_plan 契約テーブル検索方法インデックススキャン商品シーケンシャルスキャン常に同じ商品実行計画が選択されるように制御夜間帯バッチ処理ジョブn 契約日中帯処理時間の変動をおさえこみオンライン処理予測できる SQL性能を実現情報分析処理夜... バ... 日... オ... 情... 28

30 壮絶(?)な戦いを振り返って今思うこと 1 他DBMSでのノウハウをPostgreSQLを使った開発にも活かすべし性能担保のために取り組んだことはすべて他DBMSでの数多の開発で培ったノウハウをベースにしたものだった他DBMSでの当たり前にやるべきことを PostgreSQLを使った開発にも大いに活かすべき 2 PostgreSQLでも SQL性能はコントロールできる SQL性能をコントロールできるかは性能要求が厳しいミッションクリティカルシステムを扱う弊社では DBMS選定の大きなファクターの一つ PostgreSQLは pg_hint_planという武器を手にし適用範囲が大きく広がった 3 今後のPostgreSQLには大規模高負荷システムを意識した進化を期待大規模高負荷なミッションクリティカルシステムへの適用を加速させるにはディスクI/O量削減やパーティショニング機能改善といった PostgreSQLのさらなる進化が必要より強力で使いやすいDBMSへの発展を期待する 29

31 料金系基幹システムへのPostgreSQL導入における技術的チャレンジ NTT OSSセンタ山田達朗

の導入推進 ②OSS適用推進 (製品組合せ検証) プロダクト/ ツール類の開発 ③技術開発 (DBMS,HA等) 技術者育成人材交流サポート

32 NTT OSSセンタの紹介目的 OSS活用によるNTTグループのシステムのTCO削減下記① ④の4つのミッションでグループ事業に貢献 NTT OSSセンタお客様 NTT グループ各社問合せ対応導入支援プロダクト保守 ①OSSトータルサポート技術検証検証済OSS の導入推進 ②OSS適用推進 (製品組合せ検証) プロダクト/ ツール類の開発 ③技術開発 (DBMS,HA等) 技術者育成人材交流サポートサポートベンダ連携 NTT ④ソフトウェア基盤技術力向上研究所等各種開発連携ＯＳＳコミュニティ DBMSはPostgreSQLを推進 31

追いつけ商用DBMS HOT: 更新性能向上 2014 VACUUM自動化黎明期 2013 小中規模構成をターゲット

33 PostgreSQLの進化とOSSセンタの関わり PostgreSQLのエンタープライズ適用に向けた進化を OSSセンタの活動状況と合わせてご紹介赤字 OSSセンタ貢献 Step1. 追いつけ商用DBMS HOT: 更新性能向上 2014 VACUUM自動化黎明期 2013 小中規模構成をターゲット商用DBMSと同等の機能性能向上 (2008/2) (2009/7) NTT参画 OSSｾﾝﾀ設立

1(2011/9) 2010 2009 2008 2005 NTT参画 OSSｾﾝﾀ設立 8.1 8.2 9.0(2010/9) 同期/非同期レプリケーション 8.

34 PostgreSQLの進化とOSSセンタの関わり PostgreSQLのエンタープライズ適用に向けた進化を OSSセンタの活動状況と合わせてご紹介赤字 OSSセンタ貢献 Step1. 追いつけ商用DBMS Step2. 信頼性/可用性移行性の向上 2014 黎明期小中規模構成をターゲット商用DBMSと同等の機能性能向上 9.1(2011/9) NTT参画 OSSｾﾝﾀ設立 (2010/9) 同期/非同期レプリケーション 8.4(2009/7) 発展期 (2008/2) 移行ツール db_syntax_diff 大規模構成適用領域拡大に向けた機能性向上商用DBMSからの移行性向上 33

PostgreSQLの進化とOSSセンタの関わり PostgreSQLのエンタープライズ適用に向けた進化を OSSセンタの活動状況と合わせてご紹介赤字 OSSセンタ貢献 Step1. 追いつけ商用DBMS Step2. 信頼性/可用性移行性の向上外部データラッパー Step3. MCシステムへの導入 9.3(2013/9) (ミッションクリティカルシステム) 9.4 9.

35 PostgreSQLの進化とOSSセンタの関わり PostgreSQLのエンタープライズ適用に向けた進化を OSSセンタの活動状況と合わせてご紹介赤字 OSSセンタ貢献 Step1. 追いつけ商用DBMS Step2. 信頼性/可用性移行性の向上外部データラッパー Step3. MCシステムへの導入 9.3(2013/9) (ミッションクリティカルシステム) (2012/9) 黎明期小中規模構成をターゲット商用DBMSと同等の機能性能向上 9.1(2011/9) (2008/2) (2014/12) 今後 MCシステム適用 9.0(2010/9) (2009/7) 発展期 NTT参画 OSSｾﾝﾀ設立大規模構成適用領域拡大に向けた機能性向上商用DBMSからの移行性向上 34

36 PostgreSQLコミュニティへの貢献 NTT OSSセンタの貢献を一部ご紹介 (2014年度) 年間パッチ採用数 PostgreSQL本体 39件 PostgreSQL周辺ツール 30件講演 PGCon cluster summit PGECons PostgreSQL事例セミナー JPUG PostgreSQLカンファレンス PostgreSQLの開発面や利用面において貢献 35

37 ここからのおはなし最大のミッション性能の担保の達成に向け PostgreSQLエキスパートとして全力で戦い抜いた壮絶(?)なストーリーレプリケーションで DWHを構築せよバッチ処理時間を担保せよ事例(1) レプリカ側でテーブルへのデータ反映処理が全く進まなくなった事例(2) pg_hint_planにより SQL実行計画をコントロール 36

38 最大のミッション性能の担保の達成に向け PostgreSQLエキスパートとして全力で戦い抜いた壮絶(?)なストーリーレプリケーションで DWHを構築せよバッチ処理時間を担保せよ事例(1) レプリカ側でテーブルへのデータ反映処理が全く進まなくなった事例(2) pg_hint_planにより SQL実行計画をコントロール 37

39 レプリケーションの仕組み本案件はマスタとリードレプリカはWAL(更新ログ)を使用し同期データ反映処理スタンバイを DWHとしてマスタの更新毎にリードレプリカスタンバイにWALレコードを転送 ②③のどちらかが有効活用したのが止まっているリードレプリカは受け取ったWALレコードを適用しマスタと同期ポイント DWHシステム OLTPシステムマスタ ②WAL転送リードレプリカ ③WAL適用リカバリ参照クエリ ①更新クエリオンライン処理 AP AP 情報分析処理 AP AP データ反映処理(同期)が全く進まなくなった原因は 38

結果受領 ⑤参照クエリ実行 WAL適用と競合する参照クエリが絶え間なく実行されている

40 データ反映処理が全く進まなかった原因は WALは転送されていない or 適用されていないオンライン処理AP マスタリードレプリカ情報分析処理AP ①参照クエリ実行 ②更新クエリ実行結果受領 ③WAL転送クエリ完了後に WAL適用したいが ④参照クエリ結果受領 ⑤参照クエリ実行 WAL適用と競合する参照クエリが絶え間なく実行されている max_standby_streaming_delay=30秒(デフォルト) -1-1 競合するクエリ完了までWAL適用は行わないクエリ中断しない設定のため WAL適用が見送られていた 39

に対し WALはデータ削除と記載１つの対象に操作が２つこれが競合 ③WAL転送内容はHOGEテーブルの行削除(物理削除)

41 WAL適用と競合する参照クエリとは何か競合の典型的な例クエリ実行中に行削除が行われた場合マスタリードレプリカ ①SELECT * FROM HOGE; ②DELETE FROM HOGE; VACUUM HOGE; 参照中のHOGEテーブルに対し WALはデータ削除と記載１つの対象に操作が２つこれが競合 ③WAL転送内容はHOGEテーブルの行削除(物理削除) ④SELECTのクエリを中断 ⑤WAL適用競合発生の場合 WAL適用が優先されるため実行中の参照クエリは中断される競合は回避できないか 40

競合を回避するにはパラメータはあるか競合の原因である削除(物理削除)のみを遅延させることが可能

実際に採用しても問題無いのか存在を知っている理解している使いこなせるマニュアルには詳細な説明は無い

42 競合を回避するにはパラメータはあるか競合の原因である削除(物理削除)のみを遅延させることが可能 hot_standby_feedback=off(デフォルト)/on マニュアル抜粋現在処理を行っている問合せについてプライマリーにフィードバックを送るか否かレコードの後片付けに起因する問合せの取り消しを排除するために使用できます既知のパラメータではあるが実際に採用しても問題無いのか存在を知っている理解している使いこなせるマニュアルには詳細な説明は無い制約はあるのか次のアプローチで動きや制約等を確認した実機検証ソース解析リードレプリカ側はトランザクションIDをマスタ側にフィードバックマスタ側は競合する可能性があるレコードの物理削除を遅延させる 41

43 パラメータを有効にした場合フローはどうなるかパラメータが有効な場合クエリと競合するWALは生成しない競合しないWALのみが転送されて適用されるオンライン処理AP ②更新クエリ実行結果受領マスタリードレプリカ情報分析処理AP フィードバック参照クエリ情報 ①参照クエリ実行物理削除 ③WAL転送競合しない ④WAL適用フィードバック参照クエリ情報 ⑤参照クエリ結果受領 42 制約や留意点についてはどうか

44 制約や留意点パラメータの採用にあたっては以下に注意制約 1. NW断などによるレプリケーション再開直後はフィードバックが行われておらずクエリの中断が回避できない場合がある 2. XID周回防止のためのFREEZE処理が発生した場合はクエリの中断は回避できない留意点マスタの不要行の物理削除が遅延するため表の肥大化が進む可能性がある本案件においては特に問題無しと判断制約1.問題なし NW断などは発生頻度は低い運用対処で対応制約2.問題なし autovacuum停止手動VACUUMによる運用留意点問題なしクエリ実行時のマスタ側の更新は比較的少ない手動VACUUM 43

45 フィードバックのパラメータを追加し解決へ本案件では以下のパラメータを組み合わせて採用 max_standby_streaming_delay=30秒 -1 WALが到着してから何秒後にWAL適用を開始するかを設定する -1 クエリ完了までWAL適用は行わない hot_standby_feedback=off ON 追加トランザクションIDをマスタに伝え参照する可能性がある行の削除を待ってもらう ON 有効リードレプリカ側はクエリ中断を回避しつつデータ反映処理が行われるようになり課題は解決 44

46 最大のミッション性能の担保の達成に向け PostgreSQLエキスパートとして全力で戦い抜いた壮絶(?)なストーリーレプリケーションで DWHを構築せよバッチ処理時間を担保せよ事例(1) レプリカ側でテーブルへのデータ反映処理が全く進まなくなった事例(2) pg_hint_planにより SQL実行計画をコントロール 45

を制御するためのアドバイスヒント以下を柔軟に制御し目的のプランに誘導することが可能結合順番

47 pg_hint_planの概要 pg_hint_planとは NTT OSSセンタ製のヒント句を利用可能にするツール (githubで公開中) ヒント句とはプラン実行計画を制御するためのアドバイスヒント以下を柔軟に制御し目的のプランに誘導することが可能結合順番結合方法の指定表スキャン方法の指定など多数メリットはなぜ作ったかプランナの見積り誤りによるプラン訂正や性能安定化が可能チューニングの最終手段 46

pg_hint_planの使い方の例ネスティッドループ結合をハッシュ結合に変更するサンプル # EXPLAIN SELECT # FROM pgbench_branches b # JOIN pgbench_accounts a ON b.bid = a.bid # ORDER BY a.

31 rows=100000 width=4) # SeqScan(a) Join Filter: (b.bid = a.bid) # */ -> Index Scan using pgbench_accounts_pkey on pgbench_accounts # EXPLAIN SELECT -> a (cost=0.29..4247.

aid; -------------------------------------------------------------------------------------- Sort (cost=12320.84..12570.84 rows=100000 width=4) Sort Key: a.aid -> Hash Join (cost=1.02..4016.

48 pg_hint_planの使い方の例ネスティッドループ結合をハッシュ結合に変更するサンプル # EXPLAIN SELECT # FROM pgbench_branches b # JOIN pgbench_accounts a ON b.bid = a.bid # ORDER BY a.aid; # /*+ Nested Loop これがヒント句例はハッシュ結合とシーケンシャルスキャンを指定 # HashJoin(a b) (cost= rows= width=4) # SeqScan(a) Join Filter: (b.bid = a.bid) # */ -> Index Scan using pgbench_accounts_pkey on pgbench_accounts # EXPLAIN SELECT -> a (cost= rows= width=8) Materialize # FROM pgbench_branches b (cost= rows=1 width=4) # JOIN pgbench_accounts a ON b.bid = a.bid -> Seq Scan on pgbench_branches b (cost= rows=1 width=4) # ORDER BY a.aid; Sort (cost= rows= width=4) Sort Key: a.aid -> Hash Join (cost= rows= width=4) Hash Cond: (a.bid = b.bid) -> Seq Scan -> Hash on pgbench_accounts a (cost= rows= width=8) (cost= rows=1 width=4) -> Seq Scan on pgbench_branches b (cost= rows=1 width=4) Copyright NTT COMWARE CORPORATION

49 本案件におけるpg_hint_planの必要性本案件ではクエリの実行時間を担保するために様々な営みを実施 SQLコーディングガイドによる机上チェック早期からのプランチェック業務アプリのロジック見直し必要に応じて統計情報の手動実行収集タイミングの管理性能試験による評価商用相当EOL時点のデータ量と分布で性能要件を満たすことを確認それでもプラン変動や非効率なプラン選択が発生し実行時間が変動主な原因は以下あるタイミングでデータ分布が変わるコスト見積りが変動クエリ内の結合数が多いパターン数の上昇選択ミスプランナが不得手なクエリコスト見積り誤り仕様最適なプランでなくてもよいので適切なプランで安定することコストベースのプランナに全てを任せるのではなくが重要自らプランを制御する必要がある 49

NLはNestedLoop HJはHashJoin MJはMergeJoin 元のプランに戻したい A B Cの順 NL HJ

50 具体的にどういうことを行ったのか典型的な例１ : あるタイミングでデータ分布が変わる結合順と結合方法が変動したため実行時間が変動した元々のプラン A NL 変動したプラン実行時間が悪化 B C C B HJ HJ C C A MJ A,B,Cは表内側から四角から結合 NLはNestedLoop HJはHashJoin MJはMergeJoin 元のプランに戻したい A B Cの順 NL HJ PostgreSQLの基本機能では結合順や結合方法を細かく制御できない例 set文で結合方法を指定するとプラン内のすべての結合に影響ヒント句ならば結合順や結合方法を細かく制御可能 50

51 具体的にどういうことを行ったのか典型的な例２ : プランナが不得手なクエリ Nested Loopが選択されたが実はHash Joinの方が適切実際はこのような結果だった見積りでは NL 結合後の行数 10 NL 結合後の行数 1,000,000 NL は大量の処理に向いていない HJ の方が実行時間短縮の見込みがある NLはNestedLoop HJはHashJoin なおヒント句では結合方法だけではなく見積り行やコスト値も変更可能 pg_hint_planによりプランの細部までを制御可能プランの安定によりシステムの安定性が向上 51

52 ここまでのまとめ１２つの課題解決の事例を紹介した事例１レプリカ側でテーブルへのデータ反映処理が全く進まなくなったレプリケーションによるDWH構築はマスタ側の物理削除を遅延させるリードレプリカ側のWAL適用タイミングを変更することが重要再掲最大のミッションそれは性能の担保大量データの計算処理を確実に時間内で完了しつつ処理時間の安定化も図れ大規模な分析処理を行いつつ関連システムからのオンライン処理をさばけ 52

53 ここまでのまとめ２２つの課題解決の事例を紹介した事例２ pg_hint_planにより SQL実行計画をコントロール DB性能の安定化のためにはヒント句によるプラン制御が重要再掲最大のミッションそれは性能の担保大量データの計算処理を確実に時間内で完了しつつ処理時間の安定化も図れ大規模な分析処理を行いつつ関連システムからのオンライン処理をさばけ 53

54 全体のまとめ本案件は難易度が高く PostgreSQL導入における挑戦であった数百万回線百種類以上のサービスを扱う顧客料金システム 24時間365日稼動複雑かつ大規模な夜間バッチ処理の実行時間厳守性能安定化 PostgreSQL Linux 仮想化環境元々は商用DBMS UNIX 物理環境テラバイドオーダーのデータ量 OLTPとOLAPの業務両立それに対し各社の総力を結集し様々な工夫で課題を乗り越え性能の担保を実現現在システムは安定運用中であるミッションクリティカルである本システムへのPostgreSQL 導入を無事成功に導くことができた 54

55 おわりに PostgreSQLは益々進化しておりエンタープライズ利用が進んでいますみなさんもぜひ利用を検討してみてください 55

56 ご清聴ありがとうございました Linux は Linus Torvalds 氏の日本およびその他の国における登録商標または商標ですその他記載されている会社名製品名サービス名は各社の商標または登録商標です "elephants beach walk" by Senorhorst Jahnsen is licensed under CC BY 2.0

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 つでもアクセス方法は複数