はじめに

下記Quickstartを参考に、Snowflakeでパフォーマンス改善の余地があるクエリやリソースを調べる方法について見ていきます。

概要

このQuickstartを実施することで下記を学べます。

パフォーマンスを改善できる部分を特定する方法
リソース過剰消費の原因となっている低パフォーマンスのワークロードを分析する方法
スケールアップやスケールアウトが有効なウェアハウスを特定する方法

また下記のように重要度ごとに3段階で分類わけしています。

段階1: Snowflakeのリソース最適化に必要不可欠で、全員すべきなもの
段階2: 段階1より重要度は下がるが、より深いレベルでリソース最適化ができるもの
段階3: 段階2より重要度は下がるが、Snowflakeの使用量を最適化するためにこだわりたいならすべきこと

これから紹介するクエリは、SNOWFLAKEデータベースのACCOUNT_USAGEスキーマ内のビューに対して実行します。

ACCOUNT_USAGE: アカウントのオブジェクトメタデータと使用状況を表示するビュー。

（見やすいようにQuickstartに記載のものからは一部クエリを書き換えています）

段階1

SnowpipeやCOPYによるデータ取り込みについて

このクエリを実行することで、テーブルごとのデータ取り込みのファイルサイズや行数などの集計情報が取得できます。

SELECT
    TO_DATE(LAST_LOAD_TIME) as LOAD_DATE
    ,STATUS
    ,TABLE_CATALOG_NAME as DATABASE_NAME
    ,TABLE_SCHEMA_NAME as SCHEMA_NAME
    ,TABLE_NAME
    ,CASE WHEN PIPE_NAME IS NULL THEN 'COPY' ELSE 'SNOWPIPE' END AS INGEST_METHOD
    ,SUM(ROW_COUNT) as ROW_COUNT
    ,SUM(ROW_PARSED) as ROWS_PARSED
    ,AVG(FILE_SIZE) as AVG_FILE_SIZE_BYTES
    ,SUM(FILE_SIZE) as TOTAL_FILE_SIZE_BYTES
    ,SUM(FILE_SIZE) / POWER(1024,1) as TOTAL_FILE_SIZE_KB
    ,SUM(FILE_SIZE) / POWER(1024,2) as TOTAL_FILE_SIZE_MB
    ,SUM(FILE_SIZE) / POWER(1024,3) as TOTAL_FILE_SIZE_GB
    ,SUM(FILE_SIZE) / POWER(1024,4) as TOTAL_FILE_SIZE_TB
FROM SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY
GROUP BY LOAD_DATE, STATUS, DATABASE_NAME, SCHEMA_NAME, TABLE_NAME, INGEST_METHOD
ORDER BY DATABASE_NAME, SCHEMA_NAME, TABLE_NAME, LOAD_DATE, STATUS
;

この結果から、ロードするファイルサイズが大き過ぎたり小さ過ぎたりしないかなどを判断できます。
ドキュメントによると、一般的にロードするデータファイルとしては圧縮後のサイズでおよそ100〜250 MB（またはそれ以上）にすることが推奨されており、非常に大きなファイル（例: 100 GB 以上）をロードすることは推奨されていません。

段階2

ウェハウスのスケールアウト / スケールアップ

このクエリを実行することで、キューに入れられたクエリ負荷値の大きなウェアハウスを特定できます。

SELECT
    TO_DATE(START_TIME) as DATE
    ,WAREHOUSE_NAME
    ,SUM(AVG_RUNNING) AS SUM_RUNNING
    ,SUM(AVG_QUEUED_LOAD) AS SUM_QUEUED
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE TO_DATE(START_TIME) >= DATEADD(month, -1, CURRENT_TIMESTAMP())
GROUP BY DATE, WAREHOUSE_NAME
HAVING SUM(AVG_QUEUED_LOAD) > 0
ORDER BY SUM_QUEUED DESC
;

キューに入れられたクエリ負荷値が大きいということは、その時間帯はウェアハウスのリソースが枯渇しているということなので、マルチクラスターウェアハウスの設定（スタンダードエディションであればウェアハウスの分割など）を行うべきだということが分かります。

また下記クエリを実行することで、リモートディスクにスピルした（溢れ出た）メモリバイト数が多いウェアハウスを特定できます。

SELECT
    QUERY_ID
    ,USER_NAME
    ,WAREHOUSE_NAME
    ,WAREHOUSE_SIZE
    ,BYTES_SCANNED
    ,BYTES_SPILLED_TO_REMOTE_STORAGE
    ,BYTES_SPILLED_TO_REMOTE_STORAGE / BYTES_SCANNED AS SPILLING_READ_RATIO
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE BYTES_SPILLED_TO_REMOTE_STORAGE > BYTES_SCANNED * 5  -- Each byte read was spilled 5x on average
ORDER BY SPILLING_READ_RATIO DESC
;

リモートディスクへスピルしたバイト数が多いとリモートディスクI/Oに処理時間がかかることになり、パフォーマンス低下に繋がります。
これに対処して処理速度を上げるためには、より大量のメモリを持っている大きなサイズのウェアハウスに変更する必要があります。

段階3

キャッシュ利用

このクエリを実行することで、ウェアハウスキャッシュからスキャンされたデータの割合を取得できます。

SELECT
    WAREHOUSE_NAME
    ,COUNT(*) AS QUERY_COUNT
    ,SUM(BYTES_SCANNED) AS BYTES_SCANNED
    ,SUM(BYTES_SCANNED*PERCENTAGE_SCANNED_FROM_CACHE) AS BYTES_SCANNED_FROM_CACHE
    ,SUM(BYTES_SCANNED*PERCENTAGE_SCANNED_FROM_CACHE) / SUM(BYTES_SCANNED) AS PERCENT_SCANNED_FROM_CACHE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
    START_TIME >= dateadd(month, -1, current_timestamp())
    AND BYTES_SCANNED > 0
GROUP BY WAREHOUSE_NAME
ORDER BY PERCENT_SCANNED_FROM_CACHE
;

この割合が低いウェアハウスはウェアハウスキャッシュを十分に活用できていない可能性があるため、サスペンドまでの時間が早すぎるのではないか？と考えることができます。

[参考] Snowflakeのキャッシュについて

ヘビースキャン

このクエリを実行することで、大量のデータをスキャンするクエリを実行しているユーザーを特定できます。

SELECT 
    USER_NAME
    ,WAREHOUSE_NAME
    ,AVG(CASE WHEN PARTITIONS_TOTAL > 0 THEN PARTITIONS_SCANNED / PARTITIONS_TOTAL ELSE 0 END) AVG_PCT_SCANNED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME::date > dateadd('days', -45, current_date)
GROUP BY USER_NAME, WAREHOUSE_NAME
ORDER BY AVG_PCT_SCANNED DESC
;

これで抽出されるのは大量のマイクロパーティションをスキャンしているユーザーなので、
クエリに問題がある場合は参照するデータの絞り込みなどの改善ができるかもしれないですし、クラスタリングを活用してスキャンするデータ量を減らせないかを検討することもできます。

テーブルのフルスキャン

このクエリを実行することで、テーブルフルスキャンに近いクエリを多く実行したユーザーを特定できます。

SELECT
    USER_NAME
    ,COUNT(*) as COUNT_OF_QUERIES
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
    START_TIME >= dateadd(month, -1, current_timestamp())
    AND PARTITIONS_SCANNED > (PARTITIONS_TOTAL*0.95)
    AND QUERY_TYPE NOT LIKE 'CREATE%'
GROUP BY USER_NAME
ORDER BY COUNT_OF_QUERIES DESC
;

また次のクエリは上記と似ていますが、ユーザー単位ではなく、テーブルフルスキャンに近いクエリを全て取得します。

SELECT * 
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
    START_TIME >= dateadd(month, -1, current_timestamp())
    AND PARTITIONS_SCANNED > (PARTITIONS_TOTAL*0.95)
    AND QUERY_TYPE NOT LIKE 'CREATE%'
ORDER BY PARTITIONS_SCANNED DESC
LIMIT 50  -- Configurable threshold that defines "TOP N=50"
;

これらで抽出されたクエリへの改善策としては、先ほどのヘビースキャンの時と同様に、参照するデータの絞り込みやクラスタリングの活用が考えられます。

リモートディスクへの流出

このクエリを実行することで、リモートディスクに流出したメモリバイト数の多いクエリを取得できます。

SELECT
    QUERY_ID,
    substr(QUERY_TEXT, 1, 50) PARTIAL_QUERY_TEXT,
    USER_NAME,
    WAREHOUSE_NAME,
    WAREHOUSE_SIZE, 
    BYTES_SPILLED_TO_REMOTE_STORAGE,
    START_TIME,
    END_TIME,
    TOTAL_ELAPSED_TIME / 1000 TOTAL_ELAPSED_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE
    BYTES_SPILLED_TO_REMOTE_STORAGE > 0
    AND START_TIME::date > dateadd('days', -45, current_date)
ORDER BY BYTES_SPILLED_TO_REMOTE_STORAGE DESC
LIMIT 10
;

ウェアハウスのスケールアップで説明したのとほぼ同じ内容ですが、より大量のローカルストレージやメモリを持つ、サイズの大きいウェアハウスに変更した方が速度が向上することが見込めます。

自動クラスタリングで消費されたクレジット

このクエリを実行することで、自動クラスタリングで消費された1日あたりの平均クレジットを取得できます。

WITH CREDITS_BY_DAY AS (
    SELECT
        TO_DATE(START_TIME) as DATE
        ,SUM(CREDITS_USED) as CREDITS_USED
    FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
    WHERE START_TIME >= dateadd(year, -1, current_timestamp()) 
    GROUP BY DATE
    ORDER BY CREDITS_USED DESC 
)
SELECT
    DATE_TRUNC('week', DATE) WEEK
    ,AVG(CREDITS_USED) as AVG_DAILY_CREDITS
FROM CREDITS_BY_DAY
GROUP BY WEEK
ORDER BY WEEK
;

時系列に見て、消費量が急増している期間を特定するためにも利用できます。
このクエリはFROM句で指定するACCOUNT_USAGEのビューを下記のように変えることで、それぞれのリソースで消費されたクレジット平均を同じように算出することができます。

ビュー	リソース
MATERIALIZED_VIEW_REFRESH_HISTORY	マテリアライズドビュー
SEARCH_OPTIMIZATION_HISTORY	検索最適化
PIPE_USAGE_HISTORY	Snowpipe
REPLICATION_USAGE_HISTORY	複製

まとめ

SnowflakeのACCOUNT_USAGEを用いたパフォーマンス改善のための調査方法について見てきました。
単にクエリの実行時間ではなく、Snowflakeが提供する履歴データを活用することで様々な観点からパフォーマンス改善の余地を見出せるのは面白いと思いました。