マルチデータモデルDBのための
SQLクエリ処理効率化
2018年12月12日
富士通株式会社
菅原 久嗣
自己紹介
菅原 久嗣
富士通株式会社でビッグデータ活用製品を開発
Big Data Integration Server
•
NoSQL、Hadoop/Spark、ストリーム処理
Agenda
ビッグデータ活用に必要なマルチデータモデルDB
富士通のマルチデータモデルへの取組み
Sparkクエリアクセラレーション
ビッグデータ活用に必要な
マルチデータモデルDB
「PostgreSQL」の進化
ビッグデータ活用に向けた機能の強化
v6.0 PostgreSQL初回リリース 1986年から続いていたカリフォルニア大学バークレー校におけるPostgresプロジェクトを継承 v9.5 UPSERT v9.4 jsonb型 v9.3 外部データラッパ(FDW)への更新 v9.2 CPUスケール対応 Index Only Scan json型 v9.0 Streaming Replication v9.1 同期レプリケーション v9.6Parallel Seq Scan 複数同期スレーブ 完全同期モード v10 宣言的パーティションニング ロジカルレプリケーション v11 ハッシュパーティショニング Parallel-Append ストアドプロシージャ 運用性 信頼性 データ型の拡充・スケーラビリティ強化 v8.3 SQL/XML
分析アプリケーションから構造化・非構造化データにアクセス
NoSQL ：非構造（センサー/IoT）、リアルタイム活用
RDB
：構造（業務データなど）、バッチ活用
Analysis
ビッグデータ活用のためのデータベース
Ingest Store Process Model
ETL HDFS 多次元分析 モニタリング 機械学習・AI Spark
データモデル別に複数のデータベースを使用
Kafka RDB NoSQL センサー/IoT 業務データ事例：建設機械の稼働データ分析システム
現場の稼働データとマスタデータを活用し、配車最適化・燃費改善を実現
① SQLとNoSQLそれぞれのI/Fを使い、データ検索のアプリケーション開発が必要 ② NoSQL上の稼働データの結合や集計処理ロジックの作り込みが必要 ③ 不要なデータを削除するとき、複数DB上のデータの整合性を担保する必要 アプリ アプリ アプリ NoSQL 稼働データ お客様 代理店 RDB マスタデータ Parse Join non-SQL API SQL API 稼働率・燃費情報 分析アプリ Analyze 不要データ削除 (定期バッチ) Tran Ctrl Sum Avg. Check アプリ開発者 ① ① ② ③① データベース毎にアプリケーションを作成→分析アプリケーションをPostgreSQLに１本化 ② サイロ化したデータの横断的なデータ活用が困難→PostgreSQL側でモデル間の結合・集計 ③ 複数データベース間のデータ整合性の確保→PostgreSQL側でデータの一貫性を保証
PostgreSQLによるマルチデータモデル
アプリ側で実装していた処理をDB機能で実現
RDB 分析アプリケーション NoSQL Document Graph Key-value etc・・・ PostgreSQL 開発コミュニティにて 提案・開発中 マルチデータモデル PostgreSQL アプリ開発者①
② ③
Sparkクエリアクセラレーション
実行計画修正によるJSON処理改善
富士通の取組み
PostgreSQLの進化に合わせてビッグデータ活用領域に拡大
Symfoware Server (Postgres) / Enterprise Postgres
PostgreSQLベースのオープンなRDB。自社カラムナ技術によるOLAP強化
2012
2014
▲
▲ Symfoware Analytics Server
PostgreSQL I/Fでアクセス可能なカラムストア型DWH PostgreSQL採用
2018▲ Big Data Integration ServerOSSと自社AI技術を活用したデータレイク
DB多重化 インメモリカラムナ
DWH対応 FDW改善 In-DB分析 function改善
ビッグデータ対応 透過的暗号化
データモデルの異なるデータベースを仮想的に統合
RDBとNoSQLに対して、同じPostgreSQL構文でアクセス可能
FDWでマルチデータモデルを実現
RDB NoSQL RDB用 FDW NoSQL用 FDW 検索処理 非構造化データ メモリ配置 メモリ配置 構造化データ 検索処理 PostgreSQL I/FCREATE FOREIGN TABLE json_table { id INT options (field ‘$.user.id’),
name TEXT options (field ‘$.name’)
}; SELECT id FROM json_table;
外部DB 外部DB
FDWによるマルチデータモデルの性能課題
外部DBのデータ統合処理部分
インデックス・統計情報等による改善が不可 実データは外部DBが所有、PostgreSQLはデータを仮想統合している 外部DB PostgreSQL 結果返却 SELECT文実行 ソート 結合 集計 PostgreSQL側でデータ統合 抽出 加工 データ統合処理がネック外部DB 外部DB PostgreSQL 外部DB 外部DB
FDWによるマルチデータモデルの性能課題
Sparkにクエリ処理をすべてオフロードして高速化
クエリをSparkSQLに変換し、Sparkクラスタにオフロードしてサーバ並列で処理 Sparkは”パーティション”毎に外部DBを直接スキャンすることでデータを取得→ Sparkオフロード時は外部DBスキャンが性能ネックに
外部DB Spark ソート 結合 加工 集計 結果受領 Sparkにオフロード 抽出 外部DBスキャンがネック Sparkで並列分散
Sparkは細分化されたタスクを各サーバに振り分けることで並列分散
Sparkタスク: 並列分散用に分割されたデータ(レコード群)・処理のセット データ読込みタスクを順次振分け・実行することで、並列な外部DBスキャンを実現 SparkクラスタSparkによる外部DBスキャンの仕組み
Task1 (partition 1) 1 <= id <= 100 Task2 (partition 2) 101 <= id <= 200 Last Task 9901 <= id <= 10000 ・・・ slave server 外部DB id value 1 123 2 456 … … 10000 789 slave server slave server partitionの範囲でselect ※例：idカラムをキーにパーティションを作成し、Sparkが外部DBスキャン slaveが空いたら、 次の検索タスクを再割当て データ読込みタスク
レコード内のカラムをキーにして分割すると、ランダムアクセスが発生
テーブルのフルスキャン時、キーの順にデータが配置されているとは限らない データ更新が高頻度で発生した場合に顕著
シーケンシャルアクセスするパーティショニング機構が必要
格納順に、かつデータ量を平準化することで実現 外部DB内テーブル 外部DB内テーブル Sparkクラスタ Sparkクラスタ外部DBスキャンの性能課題
slave server slave server slave server slave server slave server slave server random accessシーケンシャルアクセスを可能にする自動パーティショニング
外部DBをPostgreSQLとして実装
ctidを基準に自動パーティショニング
1. 外部PostgreSQLから、読込み対象テーブルのctid範囲を取得 2. 1パーティションのレコード数がユーザ指定の行数になるようctid範囲を分割 - ユーザの指定は行数のみ、各パーティションのctid範囲を意識しない 3. 分割したctid範囲を、外部PostgreSQLスキャン時のwhere句に変換 ctid = (0,1) レコード ctid = (0,2) レコード … … ctid = (1,1) レコード … … ctid = (N,1) レコード … … ctid = (N, M) レコード block 0 block 1 block N partition1: ‘(0,1)’ <= ctid <= ‘(10, 2)’ partition 2: ‘(10, 3)’ <= ctid <= ‘(20, 4)’ Last partition : ‘(.., ..)’ <= ctid < ‘(N, M)’ ・・・ 1 partition= 1000 rows 1000 rows 1000 rows ≤1000 rows 読込み対象テーブルSparkクエリアクセラレーションの効果検証
Sparkオフロードなし(FDWのみ)と比較
TPC-H (SF=100), 8core/64GB RAM Server x 4 nodes
10倍～34倍の高速化を達成
5430 2133 3412 225 1123 3203 159 86 162 23 69 175 0 1000 2000 3000 4000 5000 6000 1 2 3 4 5 6 レスポ ン ス時 間 オフロードなし 提案手法実行計画修正によるJSON
処理改善
FDWにおける非構造化データのスキーマ設計
FDW実装の場合、非構造化データのスキーマ設計の改善が必要
外部のJSONデータからfieldを取得するケース RDB NoSQL RDB用 FDW NoSQL用 FDW 検索処理 非構造化データ メモリ配置 メモリ配置 構造化データ 検索処理 PostgreSQL I/FCREATE FOREIGN TABLE json_table { id INT options (field ‘$.user.id’),
name TEXT options (field ‘$.name’)
FDWにおける非構造データのスキーマ設計
従来：外部テーブル定義時にfieldとカラム
をmapping
Schema before read
NoSQLの良さである柔軟さを活かしきれない
Schema on the flyでNoSQLの柔軟性
を確保
スキーマ定義時にカラムmappingを行わない
•クエリ実行時にアクセス先フィールドを指定
データ変更・業務変更時も、アプリ開発者がDML
のみ修正すればよい
CREATE FOREIGN TABLE json_table
( doc json );
CREATE FOREIGN TABLE json_table ( “id” INTEGER, “name” TEXT, … “age” INTEGER )
FDWにおけるSchema on the fly処理の改善
PostgreSQLは外部DBから
JSONデータを抽出
クエリの実行計画は、事前に作成 されたスキーマ情報をもとに生成 JSON内のフィールドはスキーマ で持たないため、反映できない
クエリに指定されたスキーマ情報
を利用して実行計画を修正
クエリ解析結果を検証して外部 DB依頼可能か判断 外部DBにfield抽出を依頼SELECT doc->>’id’::int from json_table 実行計画作成 JSON抽出 id抽出 int型変換 id(int)抽出 実行計画 修正 外部DBに依頼 id(int)抽出処理
既存処理は文字列変換、パース、型キャストの処理コストが発生
提案手法は上記処理が不要
外部DBが自身の格納形式・情報を元に抽出 PostgreSQLはバイナリ値を直接受け取る PostgreSQL側 処理 外部DB側処理実行計画修正による実データ処理削減
SELECT doc->>’id’::int from json_table
PostgreSQL側 処理
外部DB側処理
SELECT doc->>’id’::int from json_table
JSON受領 idパース text → int BSON → JSON 上位処理 BSON → id(int) 上位処理
提案手法の有無による効果を比較
TPC-H (SF=10), 6 core/192GB RAM Server
実行計画修正の効果で、すべてのクエリで性能改善（最大約6倍）
既存のPostgreSQL処理の改善に成功していることが確認できる
Schema on the flyでの性能改善検証
6 5 4 3 2 1 0 Q1 Q3 Q4 Q5 相対速度 Q6 Q7 Q8 Q9 Q10 conventional proposed
おわりに
富士通はビッグデータ活用の現場でPostgreSQL活用を推進
FDWによるマルチデータモデルを実装した製品を2019年4月リリース予定
PostgreSQLそのもののマルチモデルDB化の取組みへと活用
コミュニティにフィードバック
