年末だしRedashのクエリ棚卸しでもしましょう (調査用サンプルクエリ付き)

これはエムスリー Advent Calendar 2023の9日目の記事です。前日の記事はSREチーム後藤の「共有会をより効果的にするために考えたあれやこれ」でした。

エムスリーエンジニアリンググループデータ基盤チームの木田です。

師走です。12月といえば年末に向けて大掃除の季節です。古来日本では12月に宮中で「煤払い」という行事を行う風習があったことが、今日年末の時期に大掃除をする由来とされているそうです。私の自宅はまだ何も手をつけられていませんが、せめてBIツールの中だけでもということでRedashのクエリ棚卸しをした話*1をご紹介します。

エムスリーにおけるRedashの活用状況
長年の運用の結果起きた性能問題
- 問題の特定と発生原因
- Workerの増設
クエリの棚卸しと改善
まとめ
We are hiring!

エムスリーにおけるRedashの活用状況

RedashはオープンソースのBIツールで、データの分析や可視化を簡単に行うことができるツールです。

BigQueryやRDBはもちろん、Athenaなど幅広いデータソースへの接続に対応しています。また、クエリエディタが付属しており、SQLを記述・実行してその結果を表やグラフで可視化が可能です(商用BIツールでよくある視覚的にクエリを構築するようなUIは提供されていません)。良くも悪くもシンプルなツールですが、SQLの基礎知識さえあれば無償で利用できて、主要機能が揃っているところが個人的には好みです。

エムスリーには、プロダクトマネージャーやディレクターなどエンジニア以外の職種でもSQLを読み書きできる人が多数在籍おり、クエリ共有＋簡易的可視化ツールとして、アクセスログや記事のPV情報の集計・各種KPIの共有などの幅広いユースケースで有効活用されていると感じます。

なお、主に社内向けの分析ツールということもあり、システム基盤はEC2インスタンス1台+クエリ実行のWorker (ECS Fargate spotインスタンス) という必要最低限の構成で運用しています。

長年の運用の結果起きた性能問題

そんなRedashですがかれこれ５年以上運用を続けてきた結果、性能の問題が頻発するようになりました。具体的にはピーク時にダッシュボードのRefreshやクエリの実行が長時間待たされるような状況です。(弊社の場合だと平日の午前中がピークタイムでした)

これまでインスタンスの再起動などでだましだまし対応してきたものの、発生頻度が多くなり本格的な調査とクエリの棚卸しをしました。

問題の特定と発生原因

Redash管理者が、Redashの状況 (例えばクエリキューの実装であるRedisのメモリ使用量やQueueに積まれたジョブ数など)が確認できるSystem Statusという画面が (URLは /admin/status) 提供されており、まずはそこを確認することになります。

今回の性能問題の原因は、クエリ実行待ちのQueueに実行待ちクエリが大量に積まれていて、その結果利用者から見るといつまでもRefreshが終わらないという状態でした。Redashではユーザーが実行したクエリは一度queries, scheduled_queries, periodic等のQueueに登録されて、Workerがそれを逐次消化するという処理になっています。

Workerの増設

即効性のある対応かつ正攻法として、Worker数を増やすという対応が考えられます。まずは滞留解消のためにWorkerの増設をしました。一方で、問題発生の度に野放図に増やしてしまうとコスト増になるので、処理の滞留原因となったクエリについても見直しすることにしました。

クエリの棚卸しと改善

Redashでは、クエリの定義、クエリの実行履歴、ユーザー一覧のようなメタデータが「re:dash metadata」というデータソースでデフォルトで利用できます。今回はこのメタデータを検索することで後述のようなクエリの棚卸しを実施しました。

使われていない定期実行クエリの停止

たくさんありました。Redashには作成したクエリを日次等で定期実行する機能があり、定期実行で登録されたまま放置されたクエリが多々ありました。特に平日の午前中にスケジュールされたものが多く、上記のピーク時に重なってクエリの渋滞を起こしていました。

クエリやダッシュボードからの参照が直近無く、かつ定期実行されているクエリを下記のようなSQLでリストアップして定期実行を停止しました。

サンプル1 直近参照なしかつ定期実行クエリ

select
 q.id,
 q.name,
 u.name as owner,
 u.email as owner_email,
 qe.last_access_time,
 qe.year_uu, -- 直近1年間の利用者数 (クエリ)
 de.year_uu as dashboard_year_uu,　-- 直近1年間の利用者数 (ダッシュボード経由)
 de.related_dashboards, -- クエリを組み込んでいるダッシュボードのリスト
 q.schedule
from queries q
join users u on q.user_id = u.id
left join (
  select
    cast(object_id as integer) as query_id,
        max(case when action in ('create', 'view', 'view_source', 'execute') then events.created_at else null end) as last_access_time,
     count(distinct case when events.created_at > now() - interval '1 year' then events.user_id else null end) as year_uu
    from events
    where
      object_type = 'query' and coalesce(events.object_id, '') <> ''
    group by object_id
) qe  on q.id = qe.query_id
left join (
  select
     visualizations.query_id as query_id,
     string_agg(distinct dashboards.id || '_' || dashboards.name, '\n') as related_dashboards,
     max(case when action in ('create', 'view', 'edit') then events.created_at else null end) as last_access_time,
     count(distinct case when events.created_at > now() - interval '1 year' then events.user_id else null end) as year_uu
  from events
    join dashboards on cast(events.object_id as integer) = dashboards.id and not dashboards.is_archived
    join widgets on dashboards.id = widgets.dashboard_id
    join visualizations on widgets.visualization_id = visualizations.id
  where
    events.object_type = 'dashboard' and coalesce(events.object_id, '') <> ''
  group by visualizations.query_id
) de on q.id = de.query_id
where
 not q.is_archived
 and q.schedule is not null
 and qe.last_access_time < 'yyyy-mm-dd'  -- 最終アクセスが古い (日付部分は適宜変更してください)
order by last_access_time

棚卸し前はおよそ400件のクエリが定期実行されていましたが、うち120件余り(およそ3, 4件に1件)の定期実行を止めました。

クエリの実行時間改善

workerの数が少ない状況下では特定のクエリの実行に時間がかかると、その間後続のクエリ実行が滞留してしまいます。まずは実行時間が長いクエリを洗い出します。

サンプル2 実行時間の長いクエリTop100

with
query_stats as (
  select
    query_hash,
    round(cast(avg(runtime) as numeric), 2) as avg_runtime -- 平均実行時間
  from
    query_results
  where
    retrieved_at between 'yyyy-mm-dd' and 'yyyy-mm-dd' -- 期間指定
  group by
    query_hash
)
select
  q.id,
  q.name,
  st.avg_runtime
from queries q
join query_stats st using (query_hash)
where
  not q.is_archived
order by
  avg_runtime desc
limit 100;

実行時間がかかるクエリは大きく分けると以下の２パターンで発生します。

DBやDWH上での実行に時間がかかるクエリ
必要以上に大量のデータを取得しているクエリ

前者ついてはSQLのチューニング等を個別に検討することになります。個別対応ゆえ、今回は割愛します。

後者について、RedashのWorkerはクエリ実行の結果をJSON形式でDBに保存するため、単純なクエリであっても件数が多いとWorkerがデータを取得してRedashのDBに書き込む部分がボトルネックとなり実行時間がかかります。

Redashで表やグラフ形式でクエリ結果を可視化することを考えたときに、人間が一目で処理できる情報量には限度があります。必要以上にデータを表示したり、グラフ上にプロットするのはブラウザの負荷増にもなるため避けたほうが良いでしょう。適宜集計処理やLimit句で制限をかけることで取得する件数を抑られることが期待されます。

例えば新着数百件のデータを表で参照するユースケースであるにもかかわらず全件(数十万件)のデータを取得・保存しているケースなどがありました。こうしたクエリは実行時間がかかるだけではなく、画面の表示やDBのストレージにも優しくないので件数制限を個別に行いました。

上記長時間実行クエリの発展形で、取得件数の多いクエリをリストアップするサンプルです。クエリ実行結果のJSONから件数を抽出して集計しているのでかなり重たいクエリになります。利用のピーク時を外した時間帯で実行するようにご留意ください。

サンプル3 平均取得件数の多いクエリTop100

with query_dashboard as (
  select
    q.id,
    string_agg(d.id || '_' || d.name, chr(10)) as related_dashboards
  from
    queries q
    left join visualizations viz on viz.query_id = q.id
    left join widgets w on viz.id = w.visualization_id
    left join dashboards d on w.dashboard_id = d.id
    and not d.is_archived
  group by
    q.id
),
query_stats as (
  select
    query_hash,
    count(distinct id) as cnt,
    round(cast(avg(runtime) as numeric), 2) as avg_runtime,
    round(cast(avg(json_array_length(data :: json -> 'rows')) as numeric),2) as avg_rows
  from
    query_results
  where
    retrieved_at between 'yyyy-mm-dd' and 'yyyy-mm-dd' -- 期間指定
  group by
    query_hash
)
select
  q.id,
  q.name,
  u.name as owner,
  st.cnt,
  st.avg_runtime,
  st.avg_rows,
  qd.related_dashboards,
  q.schedule :: json -> 'interval' as interval,
  q.schedule :: json -> 'time' as time
from
  queries q
  join query_stats st using (query_hash)
  join query_dashboard qd on q.id = qd.id
  join users u on q.user_id = u.id
where
  not q.is_archived
order by
  avg_rows desc
limit
  100;