はじめに
当記事では、Verticaデータベースでパフォーマンス問題に直面した場合に活用されるプロファイルについての概要と使い方を紹介します。
主な使い方として、クエリの実行計画を調べることで、プロジェクション設計の見直しやSQL文の見直しに役立てる事ができます。
プロファイルとは
データベースのパフォーマンスをプロファイルする事で、クエリ処理が効率的に行われているかを確認する事ができます。プロファイルを使う事で以下の情報を確認する事ができます。
・クエリ実行内の異なるタイミングで、各処理でどれくらいのデータがやりとりされたか
・クエリがネットワークバウンドになっていないかどうか
プロファイル情報を確認する事で、クエリのパフォーマンスを評価し、SQLの書き換えが必要かどうかの判断をする事ができます。また、プロジェクションの設計(セグメンテーションやソート順序など)を検討する際にも活用できます。
プロファイルのカテゴリ
プロファイル情報は、以下の3つのカテゴリに分類され、それぞれの情報を各システムテーブルに格納します。
| カテゴリ | システムテーブル | 説明 |
|---|---|---|
| Session | SESSION_PROFILES | 現在のセッションで、各ノードで実行されるクエリについての一般情報 例えば、どれくらいのSQL文が成功し失敗したかや、どれくらいのロックやデッドロックが発生したかなどを確認する事ができます。 |
| Queries | QUERY_PLAN_PROFILES QUERY_PROFILES | SQL文や処理時間などのクエリに特化した情報が2つのテーブルに分割して格納されています。 |
| Execution Engine | EXECUTION_ENGINE_PROFILES | 各クエリの実行計画のリアルタイムなステータスが格納されます。 |
Execution Engineのプロファイルが無効の場合、EXECUTION_ENGINE_PROFILESテーブルには何もデータが保存されません。
クエリプロファイルやセッションプロファイルが無効の場合、QUERY_PROFILESテーブル、QUERY_PLAN_PROFILESテーブル、SESSION_PROFILESテーブルにいくつかのデータを保存します。
各カテゴリのプロファイルを有効にする事で、データベース全体の情報収集やカレントのセッションの情報収集ができます。
プロファイルの有効化と無効化
SHOW_PROFILING_CONFIGを使う事で、現在の設定状態(有効/無効)を確認する事ができます。
このコマンドによって、以下の事を確認する事ができます。
・どのカテゴリでプロファイルが有効/無効か
あるカテゴリでglobalプロファイルが有効の場合、sessionのプロファイルの有効/無効を変更してもglobal側が収集するデータに影響はありません。
あるカテゴリでglobalプロファイルが無効の場合、同一カテゴリ内のsessionプロファイルだけを有効にする事ができます。
現状のクエリプロファイルの設定状態を確認するには、以下のコマンドを使用します。
以下の例では、プロファイルがすべてのカテゴリ(Session、Execution Engine、query)で有効(Global on)になっている事を示しています。
dbadmin=> SELECT SHOW_PROFILING_CONFIG();
SHOW_PROFILING_CONFIG
-------------------------------------------------------------------------------------------------------------------------------------
Session Profiling: Session off, Global on
EE Profiling: Session off, Global on
Query Profiling: Session off, Global on
(1 row)Globalプロファイルの有効化/無効化
Globalプロファイルの設定を変更するには、以下のコマンドを使用します。
profiling-categoryには、以下の引数のいずれかを指定します。
| 引数 | 説明 |
|---|---|
| GlobalSessionProfiling | Sessionプロファイルのデータ |
| GlobalQueryProfiling | クエリのデータ |
| GlobalEEProfiling | Execution Engineのデータ |
値(0と1)は以下のような意味になります。
| 値 | 説明 |
|---|---|
| 0 | 無効 |
| 1 | 有効 |
Sessionプロファイルの有効化/無効化
Sessionプロファイルの設定を変更するには、以下のコマンドを使用します。
DISABLE_PROFILING( profiling-category )
使用例
dbadmin=> SELECT ENABLE_PROFILING('ee');
ENABLE_PROFILING
----------------------
EE Profiling Enabled
(1 row)profiling-categoryには、以下の引数のいずれかを指定します。
| 引数 | 説明 |
|---|---|
| session | Sessionプロファイルのデータ |
| query | クエリのデータ |
| ee | Execution Engineのデータ |
クエリプロファイルの注意点
プロファイルデータはデータが偏る事があります。
例えば、あるノードが他のノードよりも多くのデータを処理した場合に起こる事があります。
各ノードがどれくらい処理をしたかは、システムテーブルの EXECUTION_ENGINE_PROFILES の行数で確認する事ができます。
全ノードを通して行数を比較する事で、処理の偏りを確認する事ができます。
dbadmin=> SELECT
COUNT(*),
node_name
FROM
execution_engine_profiles
GROUP BY
node_name
ORDER BY
node_name
;
count | node_name
--------+-------------------
104288 | v_vmart_node0001
107916 | v_vmart_node0002
109111 | v_vmart_node0003
(3 rows)リアルタイム・プロファイル
リアルタイム・プロファイルを使うと、現在実行中のクエリを監視する事ができます。
実行中のすべてのSQLステートメントや、内部処理(マージアウトやリカバリ、リフレッシュ)が対象になります。
プロファイルを取得するには、特定のSQL文の先頭にPROFILEを付ける、あるいはデータベース全体のプロファイル設定を有効化、あるいはセッションの設定を有効化しない限り、SQL文の実行完了後にプロファイルは取得されません。
※リアルタイム・プロファイルは、特定のSQL文の先頭にPROFILEを付けなくても自動的にプロファイル情報が取得されます。
リアルタイム・プロファイルでクエリを情報を参照するにはトランザクションIDが必要です。
もし1つのトランザクションが複数のSQL文を実行するならば、ステートメントIDも必要になります。
トランザクションIDとステートメントIDは、システムテーブルの SYSTEM_SESSIONS で確認する事ができます。
プロファイリング・カウンター
システムテーブルのEXECUTION_ENGINE_PROFILESには、内部的な動作とユーザが実行するSQL文についてのプロファイリング・カウンターを含んでいます。
主なカウンターには以下のようなものがあります。
Rows produced
Total merge phases
Completed merge phases
Current size of temp files (bytes)
以下に、ノード別に最も実行時間の長い処理を確認する方法を記載します。
ノード1とノード2では結合処理が、ノード3ではネットワーク転送に最も時間を要している事が読み取れます。
dbadmin=> SELECT
node_name,
operator_name,
counter_value execution_time_us
FROM
V_MONITOR.EXECUTION_ENGINE_PROFILES
WHERE
counter_name = 'execution time (us)'
LIMIT 1 OVER(PARTITION BY node_name ORDER BY counter_value DESC)
;
node_name | operator_name | execution_time_us
------------------+---------------+-------------------
v_vmart_node0001 | Join | 131906
v_vmart_node0002 | Join | 227778
v_vmart_node0003 | NetworkSend | 524080
(3 rows)クエリプラン・プロファイル
プロファイルによって、特定クエリのデータをを確認する事ができます。
また、システムテーブルのQUERY_PLAN_PROFILESとEXECUTION_ENGINE_PROFILESで保持しているデータを評価する事ができます。
例えば、QUERY_PLAN_PROFILESを参照する事で、各実行計画にどれくらいの時間を要したかを確認する事ができます。
SQL文単体のプロファイル
1つのSQL文のプロファイルを取得するには、PROFILEをSQL文の先頭に付けます。
SELECT文やDML文(INSERT、UPDATE、COPY、MERGEなど)に付ける事ができます。
プロファイルを付与したSQLの結果には、以下のサマリが表示されます。
・クエリのためのイニシエーターのメモリサイズ
・要求されたメモリサイズの合計
以下に、PROFILEを付与したSELECT文の例を記載します。
PROFILEを付与する事で表示される情報を明確にするために、PROFILEなしとPROFILEありを順に記載します。
PROFILEなし
dbadmin=> 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
;
revenue
--------------
446268068091
(1 row)PROFILEあり
dbadmin=>PROFILE
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
;
NOTICE 4788: Statement is being profiled
HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273705360 and statement_id=4;
NOTICE 3557: Initiator memory for query: [on pool general: 14390663 KB, minimum: 115587 KB]
NOTICE 5077: Total memory required by query: [14390663 KB]
revenue
--------------
446268068091
(1 row)・transaction_id と statement_idが、それぞれ 45035996273705360 と 4 である事が読み取れます。(15行目)
・クエリのためのイニシエーターのメモリサイズが、General Poolから 14390663 KB 割り当てられた事が読み取れます。(16行目)
・要求されたメモリサイズの合計が、14390663 KB であった事が読み取れます。(17行目)
また別の使い方として、特定クエリの transaction_id と statement_id を指定する事で詳細なプロファイル情報を確認する事ができます。
以下に、詳細なプロファイル情報を確認する方法を記載します。
先に実行したSQLの transaction_id と statement_id を使って、以下のSQLのWHERE句にあるtransaction_id と statement_id に値を入れて実行します。(8行目と9行目)
dbadmin=> SELECT
path_id,
path_line::VARCHAR(68),
running_time
FROM
v_monitor.query_plan_profiles
WHERE
transaction_id = 45035996273705360
AND statement_id = 4
ORDER BY
path_id,
path_line_index
;
path_id | path_line | running_time
---------+----------------------------------------------------------------------+-----------------
1 | +-GROUPBY NOTHING [Cost: 29K, Rows: 1 (NO STATISTICS)] (PATH ID: 1) | 00:00:00.044191
1 | | Aggregates: sum((lineorder.lo_extendedprice * lineorder.lo_discou |
2 | | +---> JOIN HASH [Cost: 27K, Rows: 6M (NO STATISTICS)] (PATH ID: 2) | 00:00:00.04947
2 | | | Join Cond: (lineorder.lo_orderdate = date1.d_datekey) |
2 | | | Materialize at Output: lineorder.lo_extendedprice, lineorde |
3 | | | +-- Outer -> STORAGE ACCESS for lineorder [Cost: 16K, Rows: 6M ( | 00:00:00.049302
3 | | | | Projection: public.lineorder_super |
3 | | | | Materialize: lineorder.lo_orderdate |
3 | | | | Filter: (lineorder.lo_quantity < 25) |
3 | | | | Filter: ((lineorder.lo_discount >= 1) AND (lineorder.lo_d |
3 | | | | Runtime Filter: (SIP1(HashJoin): lineorder.lo_orderdate) |
4 | | | +-- Inner -> STORAGE ACCESS for date1 [Cost: 11, Rows: 3K (NO ST | 00:00:00.000136
4 | | | | Projection: public.date1_super |
4 | | | | Materialize: date1.d_datekey |
4 | | | | Filter: (date1.d_year = 1993) |
(15 rows)このようにプロファイルを活用する事で、パフォーマンス問題に直面した際の対策を検討するための情報を得る事ができます。
検証バージョンについて
この記事の内容はVertica 9.1で確認しています。