Management Consoleを利用したクエリスペシフィックプロジェクションの作成方法

1 はじめに
2 クエリスペシフィックプロジェクションとは
3 テストケース
4 QSP作成手順
5 レスポンス改善状況の確認
6 注意事項
- 6.1 SQLファイル内のクエリの記述
- 6.2 クエリのパフォーマンス
7 検証バージョンについて
8 更新履歴

はじめに

以下の記事でVerticaの大きな特徴であるプロジェクションについて概要をご紹介しました。

プロジェクションの概要

Verticaは最適化スーパープロジェクションのみでも基本的には高速なパフォーマンスを得られます。しかし、SQLによっては性能差が顕著に見られ、期待したパフォーマンスを得られない場合もあります。このような場合に、特定処理のレスポンス向上を目的としたクエリスペシフィックプロジェクション（以降、QSPと表記）の作成を検討します。

本記事では、QSPの特徴や作成手順を解説します。また、全体最適化実施後に期待したパフォーマンスが出ておらず、どうにかしてチューニングを行いたい例として、QSP作成後のパフォーマンス改善例をご紹介します。

クエリスペシフィックプロジェクションとは

特定のクエリのパフォーマンス向上を目的とするプロジェクションです。
該当のクエリの実行に必要な列のみを保持しており、そのクエリに特化したソートや圧縮が行われています。

下記の図は、「日付」列をWHERE句の条件値としたクエリの実行例です。

左側のスーパープロジェクションを見てみると、「エリア」と「店舗」ではソート、圧縮が効いていますが、「日付」列はソートされていないため、SQLの例にある「日付」での条件指定には適していない状態であることがわかります。

このような場合に対象のクエリを読み込ませると、右側のQSPの例のように「日付」列でソート、圧縮されるような状態になります。条件として指定された「日付」列の読み込みに対して効果が発揮されます。

テストケース

今回は以下のようなテストケースを例にQSPの作成手順をご紹介します。

　＜データ＞
　　テーブル数・・・5
　　対象データ量・・・明細テーブル　約7億件

　＜クエリの詳細＞
　　前提・・・スーパープロジェクションの最適化のみ実施済
　　対象処理・・・Star Schema BenchMarkのクエリ7～9
　　各クエリの応答時間
　　

クエリ	処理件数	応答時間（秒）
クエリ7	150	9.5
クエリ8	600	6.7
クエリ9	24	3.6

　　［クエリ7のSQL文］

select c_nation, s_nation, d_year, sum(lo_revenue)
as revenue from customer, lineorder, supplier, date1
where lo_custkey = c_custkey
  and lo_suppkey = s_suppkey
  and lo_orderdate = d_datekey
  and c_region = 'ASIA'  and s_region = 'ASIA'
  and d_year >= 1992 and d_year <= 1997
group by c_nation, s_nation, d_year
order by d_year asc, revenue desc;

select c_nation, s_nation, d_year, sum(lo_revenue)

as revenue from customer, lineorder, supplier, date1

where lo_custkey = c_custkey

and lo_suppkey = s_suppkey

and lo_orderdate = d_datekey

and c_region = 'ASIA' and s_region = 'ASIA'

and d_year >= 1992 and d_year <= 1997

group by c_nation, s_nation, d_year

order by d_year asc, revenue desc;

　　[クエリ8のSQL文]

select c_city, s_city, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date1
where lo_custkey = c_custkey
  and lo_suppkey = s_suppkey
  and lo_orderdate = d_datekey
  and c_nation = 'UNITED STATES'
  and s_nation = 'UNITED STATES'
  and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc;

select c_city, s_city, d_year, sum(lo_revenue) as revenue

from customer, lineorder, supplier, date1

where lo_custkey = c_custkey

and lo_suppkey = s_suppkey

and lo_orderdate = d_datekey

and c_nation = 'UNITED STATES'

and s_nation = 'UNITED STATES'

and d_year >= 1992 and d_year <= 1997

group by c_city, s_city, d_year

order by d_year asc, revenue desc;

　　[クエリ9のSQL文]

select c_city,s_city,d_year,sum(lo_revenue) as revenue
from customer,lineorder,supplier,date1
where lo_custkey = c_custkey
  and lo_suppkey = s_suppkey
  and lo_orderdate = d_datekey
  and (c_city='UNITED KI1' or c_city='UNITED KI5')
  and (s_city='UNITED KI1' or s_city='UNITED KI5')
  and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc,revenue desc;

select c_city,s_city,d_year,sum(lo_revenue) as revenue

from customer,lineorder,supplier,date1

where lo_custkey = c_custkey

and lo_suppkey = s_suppkey

and lo_orderdate = d_datekey

and (c_city='UNITED KI1' or c_city='UNITED KI5')

and (s_city='UNITED KI1' or s_city='UNITED KI5')

and d_year >= 1992 and d_year <= 1997

group by c_city, s_city, d_year

order by d_year asc,revenue desc;

QSP作成手順

QSPはManagement Console（以降、MCと表記）もしくはAdministration Toolsを使って作成します。本記事ではMCを利用した作成手順をご紹介します。Administration Toolsからの手順は、以下の記事を参照してください。

Administration Toolsを利用したクエリスペシフィックプロジェクションの作成方法

Management Consoleにログイン

ブラウザから以下URLにアクセスをし、ユーザ名・パスワードを入力してManagement Consoleにログインします。（URLはご利用中の環境に読み替えてください。）

　https://<導入先ホスト名orIPアドレス>:5450/webui

ログイン後、ターゲットデータベースを選択し、Overviewの画面に移動します。

データベースデザイナ実行ウィザードの起動

画面下段にあるメニューから「Design」を選択します。

デザイン名の入力

任意のデザイン名を入力して次に進みます。

デザインタイプの選択

デザインタイプには「Incremental」を選択します。

デザイン・オプションの指定

デザイン・オプションを指定します。今回は「Propose Unsegmented Projections = ON」、「Analyze Correlations Mode = Ignore」を選択します。
※1ノード構成の場合は、本画面が表示されません。

Propose Unsegmented Projections

ON：レプリケーションとセグメンテーションのプロジェクションを作成します。
OFF：セグメンテーションのみのプロジェクションを作成します。

Analyze Correlations Mode

Ignore：デザインを作成する時に、テーブル内のすべて列の相関関係を無視します。
Consider existing：デザインを作成する時に、テーブル内の既存の相関関係を考慮します。既存の相関関係がない場合は、データベースデザイナーは相関関係を考慮しません。
Analyze missing：以前に相関分析が実行されなかったテーブルの列相関を分析します。デザインを作成する時は、すべての列の相関関係（新規および既存）を考慮します。
Analyze all：デザインを作成する時に、テーブル内のすべての列の相関関係を分析して考慮します。テーブルに相関関係が存在する場合でも、テーブルを再分析します。

SQLファイルの指定

読み込ませるSQLファイルを指定します。
この例ではクエリ7～9を一つに纏めたスクリプトファイルを指定します。
（単一のSQL文を記載したスクリプトファイルを読み込ませることも可能です）

パフォーマンス・オプションの指定

パフォーマンス・オプションを指定します。デプロイまで実行する場合には全てのオプションを選択します。

Analyze Statistics：データベースデザイナーの実行後に統計情報を取得するか指定します。
Auto-build：ウィザード完了後に直ぐにデータベースデザイナーを実行するか指定します。
Auto-deploy：クエリスペシフイックプロジェクションを作成し、自動配置するか指定します。「Auto-deploy」を指定した場合、「Auto-build」も自動的に指定されます。

サマリー画面

ここまでの設定内容がサマリー画面に表示されます。設定内容に問題がない場合には「Submit Design」をクリックして処理を実行します。

ステータス画面

QSPの作成処理を開始すると、以下のようなステータス画面が表示されます。

処理が完了すると、以下の画面のように「Deployment In progress: Deployment completed successfully」と表示されます。

レスポンス改善状況の確認

QSP作成後にクエリ7～9のレスポンスが改善されたか確認します。
以下はQSP作成前後でのそれぞれのクエリのレスポンス比較です。

　　

クエリ	処理件数	QSP作成前（秒）	QSP作成後（秒）	短縮率（%）
クエリ7	150	9.5	6.1	35.8
クエリ8	600	6.7	3.8	43.3
クエリ9	24	3.6	3.0	16.7

通常、汎用的なデータベースであればSQLのチューニングやデータベースのパラメータチューニングを施す必要があります。しかし、Verticaではプロジェクションを活用することで、非常に低コストでチューニングを実施することができます。

注意事項

SQLファイル内のクエリの記述

SQLファイルに書かれたクエリの最後にセミコロン(;)がない場合や間違った構文の場合は、クエリスペシフィックプロジェクションの作成時にエラーが発生します。

忘れずに、セミコロン(;)を追加してください。

$ cat /tmp/test.sql
select sum(lo_extendedprice*lo_discount) as revenue
from lineorder,date1
where lo_orderdate = d_datekey
  and d_year = 1993
  and lo_discount between 1 and 3
  and lo_quantity < 25;　 <-- セミコロンを追加

$ cat /tmp/test.sql

select sum(lo_extendedprice*lo_discount) as revenue

from lineorder,date1

where lo_orderdate = d_datekey

and d_year = 1993

and lo_discount between 1 and 3

and lo_quantity < 25;　 <-- セミコロンを追加

※クエリの記述に関する注意事項は、以下の記事もあわせてご覧ください。

スーパープロジェクション最適化時にクエリを指定する効果と注意事項

クエリのパフォーマンス

クエリスペシフィックプロジェクション作成後は、以下のクエリについてパフォーマンスが変化する可能性があるので、十分にパフォーマンス・テストをおこなってください。

・SQLファイルで指定したクエリ
・クエリスペシフィックプロジェクションが、作成されたテーブルを参照している他のクエリ（SQLファイルで未指定のクエリ）

※パフォーマンスが劣化した場合は、実行計画の確認や、ソート順序を確認するなど、どこに問題があるのか以下の記事を参考にチューニングを行うようにしてください。

SQLの実行計画を確認する方法
vsql上でプロジェクションのソート順を確認する方法
Management Consoleを利用したモニタリング～クエリ解析編～

検証バージョンについて

この記事の内容はVertica 11.0で確認しています。

更新履歴

2021/11/01　11.0用に画面、性能試験結果を更新、
　「はじめに」の他記事リンク方法を変更、
　「注意事項」にクエリの記述, パフォーマンス内容を変更、項目位置を変更、
　「QSP作成手順」にデザイン・オプションの指定画面を追加
2017/05/12　本記事を公開

投稿タグ: クエリスペシフィックプロジェクション, QSP, Incremental

MENU

Management Consoleを利用したクエリスペシフィックプロジェクションの作成方法

はじめに

クエリスペシフィックプロジェクションとは

テストケース

QSP作成手順

Management Consoleにログイン

データベースデザイナ実行ウィザードの起動

デザイン名の入力

デザインタイプの選択

デザイン・オプションの指定

SQLファイルの指定

パフォーマンス・オプションの指定

サマリー画面

ステータス画面

レスポンス改善状況の確認

注意事項

SQLファイル内のクエリの記述

クエリのパフォーマンス

検証バージョンについて

更新履歴

カテゴリー

最近の投稿