クラウドワークス エンジニアブログ

日本最大級のクラウドソーシング「クラウドワークス」の開発の裏側をお届けするエンジニアブログ

定期的にSQLを実行した結果をDatadogに送信するcyqldogというツールを作った

cyqldog Datadog SQL Redshift

日々Datadogのダッシュボードを眺めながらニヤニヤしている @minamijoyo です。

定期的にSQLを実行した結果をDatadog送信するcyqldogというツールを作ったので紹介します。

f:id:minamijoyo:20171002184919p:plain

はじめに

クラウドワークスでは日々のデータ分析にAWSのRedshiftを利用しています。 Redshiftはサービスの主要なKPI集計から、ちょっとしたアドホックな分析まで、 利用者はエンジニアだけにとどまらず、プロダクトオーナーやマーケティングのメンバーも含め広く利用されており、 ひとたびシステム障害などでRedshiftが利用できないと業務に大きな影響が出ます。

そんなRedshiftを安定稼働させるためには、日々の監視が大切です。 AWSで監視といえば、まずはCloudWatchが思い浮かびますが、RedshiftのCPU使用率やディスク使用率などの基本的なメトリクスであれば取得できるもの、 ちょっと複雑なこと、例えばユーザごとのクエリ実行時間やキューの滞留数などを取得しようとすると、Redshiftのシステムビューにクエリを投げる必要があります。

クラウドワークスではインフラレイヤの監視ツールとしてDatadogを使っているので、 Redshiftのシステムビューに定期的にクエリを投げて、Datadogにメトリクスを投げつけられると幸せになれそうだなぁと思ったのがこのツールを作り始めたきっかけです。

ちなみにDatadogの公式インテグレーションにRedshiftがありますが、これはCloudWatchのRedshift関連のメトリクスを取得できるもので、システムビューまでは見てくれません。

また、RedshiftのSQLのインターフェースはPostgreSQL風なので、DatadogのPostgreSQLインテグレーションが使えそうな気がしましたが、RedshiftのシステムビューはPostgreSQLとは異なるので、Redshiftのメトリクスを取得するのにはそのままでは使えなさそうでした。

雑なシェルスクリプトを書いてもよかったのですが、監視対象のクエリが増える度にスクリプトをメンテするのやだなーというのと、 Redshiftにあんまり監視自体で負荷をかけないように、重いクエリと軽いクエリで監視間隔をコントロールしたいなぁと思って、 設定ファイルにSQLと監視間隔を書けばいいかんじにDatadogにメトリクスを送信してくれるcyqldogというツールを作ってみました。

github.com

元々Redshiftの監視のために作りましたが、データソースとしてはもう少し汎用的にPostgreSQLやMySQLでも使えるようにしてあります。 実装は個人的な趣味でGo言語です。

というわけで、cyqldogの使い方を簡単に紹介します。本稿執筆時点のcyqldogのバージョンはv0.1.3です。最新情報については上記のリポジトリのREADMEを参照して下さい。

事前準備

cyqldogはDatadogへのメトリクスの送信に内部的にdogstatsdを使用しています。

dogstatsdとはメトリクスのアグリゲーションサービスでDatadogエージェント(dd-agent)に含まれます。 Datadogにメトリクスを送信したい人であれば、おそらく既に何かしらdd-agentをインストールしてると思いますが、 もしdd-agentをインストールしていない or 別に用意したい場合は、dd-agentをインストールして下さい。 dd-agentは実行するプラットフォームごとにインストール手順が異なるので、dd-agentのドキュメントを参照して下さい。

または、Dockerを使いたい人は、dd-agentをDockerで動かすこともできます。

もしくは、dogstatsd単体をDockerで動かすこともできます。

dd-agentやdogstatsdをDockerで動かす場合は、docker runのオプションで -p 8125:8125/udp を指定し、UDP 8125番ポートを公開する必要があるのでご注意下さい。

Datadogそのものの使い方の説明はここでは割愛しますが、Datadogを使うのがはじめての人は、以下の記事も参考にしてみて下さい。

Dockerで始めるDatadog

インストール

cyqldog自体のインストールは簡単で、ビルド済のバイナリを以下のリリースページからダウンロードして、パスが通ってるところに置くだけです。

https://github.com/crowdworks/cyqldog/releases

Dockerイメージも用意してあるので、Dockerがよい人はこちらをご利用下さい。

https://hub.docker.com/r/crowdworks/cyqldog/tags/

使い方

使い方は簡単で、cyqldogコマンドに -C オプションで設定ファイルのパスを指定して起動するだけです。

$ cyqldog -C /path/to/cyqldog.yml

監視設定は設定ファイルに書いていきます。

設定

設定ファイルのサンプルは以下のとおりです。

各項目の細かい説明はREADMEに書いてあるのですが、ポイントを簡単に説明します。

data_source:
  driver: postgres
  options:
    host: db.example.com
    port: 5432
    user: cyqldog
    password: {{ .DB_PASSWORD }}
    dbname: cyqldogdb
    sslmode: disable

notifiers:
  dogstatsd:
    host: {{ .DD_HOST }}
    port: 8125
    namespace: playground.cyqldog
    tags:
      - "env:local"
      - "source:db.example.com"

rules:
  - name: test1
    interval: 5s
    query: "SELECT COUNT(*) AS count FROM table1"
    notifier: dogstatsd
    value_cols:
      - count
  - name: test2
    interval: 10s
    query: "SELECT tag1, val1, tag2, val2 FROM table1"
    notifier: dogstatsd
    tag_cols:
      - tag1
      - tag2
    value_cols:
      - val1
      - val2

上から順に説明していきます。

データソースの設定

data_source:
  driver: postgres
  options:
    host: db.example.com
    port: 5432
    user: cyqldog
    password: {{ .DB_PASSWORD }}
    dbname: cyqldogdb
    sslmode: disable

data_source は監視対象のデータベースを指定します。 driver にデータベースの種別を設定します。 現在サポートしているのは postgres と mysql です。 Redshiftで使用する場合は postgres を指定して下さい。 options に接続先ホスト名などを指定します。 パスワードなど設定ファイルにハードコードしたくない値は、 {{ .DB_PASSWORD }} というような記法で環境変数 DB_PASSWORD を参照することが可能です。

sslmode などpostgres用のDB接続のその他のオプションも指定できます。 指定可能なオプションの一覧は内部的に使用している lib/pq というライブラリと同じなので、追加の接続オプションで指定できるものは、以下のドキュメントを参照して下さい。

https://godoc.org/github.com/lib/pq

ちなみにmysqlの場合は、host/port/user/password/dbname 以外のその他接続パラメータは go-sql-driver/mysql のパラメータ名と同じになっています。mysqlで指定可能な接続パラメータは、以下を参照して下さい。

https://github.com/go-sql-driver/mysql#parameters

メトリクス送信先の設定

notifiers:
  dogstatsd:
    host: {{ .DD_HOST }}
    port: 8125
    namespace: playground.cyqldog
    tags:
      - "env:local"
      - "source:db.example.com"

notifiers は収集したメトリクスを送信する先です。 接続するdogstatsdのホストやポート番号などを指定します。 namespace と tags はメトリクスに共通で付与するプレフィックスとタグです。

監視ルールの設定

rules:
  - name: test1
    interval: 5s
    query: "SELECT COUNT(*) AS count FROM table1"
    notifier: dogstatsd
    value_cols:
      - count
  - name: test2
    interval: 10s
    query: "SELECT tag1, val1, tag2, val2 FROM table1"
    notifier: dogstatsd
    tag_cols:
      - tag1
      - tag2
    value_cols:
      - val1
      - val2

rules は監視ルールを記載します。 この例ではtest1とtest2の2つのルールが定義されています。ルールは配列でいくつでも指定可能です。

test1は5秒間隔で SELECT COUNT(*) AS count FROM table1 した結果のメトリクスが送信されます。 実際に送信されるメトリクスは、 playground.cyqldog.test1.count でタグ ["env:local", "source:db.example.com"] が付与されます。

interval には監視間隔を記載します。 5s は5秒です。1m で1分や 1h で1時間なども指定可能です。

notifier にわざわざ dogstatsd を指定しているのは、作ってるときにそのうちdogstatsd以外にもslackとかにも投げたくなるかなと思ったものの、今のところdogstatsdしか実装されていないというあるあるw

test2では10秒間隔で SELECT tag1, val1, tag2, val2 FROM table1 した結果以下のメトリクスが送信されます。

  • playground.cyqldog.test2.value1 (タグ ["env:local", "source:db.example.com", "tag1:(tag1カラムの値)", "tag2:(tag2カラムの値)"])
  • playground.cyqldog.test2.value2 (タグ ["env:local", "source:db.example.com", "tag1:(tag1カラムの値)", "tag2:(tag2カラムの値)"])

Redshiftの監視例

抽象的な例だと若干分かりにくいと思うので、もう少し具体的な例を挙げてみます。

RedshiftでWLMキューごとのクエリ処理数は以下のSQLで取得できます。

SELECT 
  TRANSLATE(TRIM(TRANSLATE(c.condition, '():', '')), ' ', '_') AS name, 
  s.num_queued_queries AS queued, 
  s.num_executing_queries AS executing, 
  s.num_executed_queries AS executed 
FROM 
  stv_wlm_classification_config c, 
  stv_wlm_service_class_state s 
WHERE 
  c.action_service_class = s.service_class

結果は以下のようになります。

                 name                 | queued | executing | executed
--------------------------------------+--------+-----------+----------
 querytype_any                        |      0 |         2 |   142534
 user_group_hoge                      |      0 |         1 |  1365805

これをcyqldogで1分間隔で取得するには、以下のような監視ルールを書きます。

rules:
  - name: stv_wlm_service_class_state
    interval: 1m
    query: "SELECT TRANSLATE(TRIM(TRANSLATE(c.condition, '():', '')), ' ', '_') AS name, s.num_queued_queries AS queued, s.num_executing_queries AS executing, s.num_executed_queries AS executed FROM stv_wlm_classification_config c, stv_wlm_service_class_state s WHERE c.action_service_class = s.service_class"
    notifier: dogstatsd
    tag_cols:
      - name
    value_cols:
      - queued
      - executing
      - executed

すると、1分間隔で、以下のような (表の行数2) ✕ (value_colsの数3) = 6メトリクスがnameカラムのタグ付きで送信されます。

  • playground.cyqldog.stv_wlm_service_class_state.queued = 0 (タグ ["env:local", "source:db.example.com", "name:querytype_any"] )
  • playground.cyqldog.stv_wlm_service_class_state.executing = 2 (タグ ["env:local", "source:db.example.com", "name:querytype_any"] )
  • playground.cyqldog.stv_wlm_service_class_state.executed = 142534 (タグ ["env:local", "source:db.example.com", "name:querytype_any"] )
  • playground.cyqldog.stv_wlm_service_class_state.queued = 0 (タグ ["env:local", "source:db.example.com", "name:user_group_hoge"] )
  • playground.cyqldog.stv_wlm_service_class_state.executing = 1 (タグ ["env:local", "source:db.example.com", "name:user_group_hoge"] )
  • playground.cyqldog.stv_wlm_service_class_state.executed = 1365805 (タグ ["env:local", "source:db.example.com", "name:user_group_hoge"] )

SELECTした表をそのままDatadogのメトリクスやタグとして送りつけるイメージです。 こんなかんじでメトリクスをタグを付けて送信しておくとDatadogでグラフ化するときにタグでGROUP BYしたりできます。

監視条件のSQLは簡単に追加できるので、これでデータベースの監視が捗りますね。 というわけで、cyqldogでいろいろRedshiftに監視クエリを書いてみました。

rules:
  - name: stv_wlm_query_state
    interval: 1m
    query: "SELECT TRANSLATE(TRIM(TRANSLATE(c.condition, '():', '')), ' ', '_') AS name, s.state, COUNT(*) AS count, NVL(MAX(s.queue_time), 0) AS max_queue_time, NVL(MAX(s.exec_time), 0) AS max_exec_time FROM stv_wlm_classification_config c, stv_wlm_query_state s WHERE c.action_service_class = s.service_class GROUP BY s.state, c.condition"
    notifier: dogstatsd
    tag_cols:
      - name
      - state
    value_cols:
      - max_queue_time
      - max_exec_time
  - name: stv_wlm_service_class_state
    interval: 1m
    query: "SELECT TRANSLATE(TRIM(TRANSLATE(c.condition, '():', '')), ' ', '_') AS name, s.num_queued_queries AS queued, s.num_executing_queries AS executing, s.num_executed_queries AS executed FROM stv_wlm_classification_config c, stv_wlm_service_class_state s WHERE c.action_service_class = s.service_class"
    notifier: dogstatsd
    tag_cols:
      - name
    value_cols:
      - queued
      - executing
      - executed
  - name: stl_query
    interval: 5m
    query: "SELECT TRIM(u.usename) AS username, (CASE q.aborted WHEN 0 THEN 'no' WHEN 1 THEN 'yes' END) AS aborted, COUNT(*) AS count, NVL(MIN(DATEDIFF(us, q.starttime, q.endtime)), 0) AS min, NVL(MAX(DATEDIFF(us, q.starttime, q.endtime)), 0) AS max, NVL(AVG(DATEDIFF(us, q.starttime, q.endtime)), 0) AS avg, NVL(MEDIAN(DATEDIFF(us, q.starttime, q.endtime))::integer, 0) AS median FROM stl_query q, pg_user u WHERE q.userid = u.usesysid AND q.endtime > (SYSDATE - interval '5m') GROUP BY q.userid, q.aborted, u.usename"
    notifier: dogstatsd
    tag_cols:
      - username
      - aborted
    value_cols:
      - count
      - min
      - max
      - avg
      - median
  - name: stl_alert_event_log
    interval: 5m
    query: "SELECT COUNT(*) AS count FROM stl_alert_event_log WHERE event_time > (SYSDATE - interval '5m')"
    notifier: dogstatsd
    value_cols:
      - count
  - name: system_timestamps
    interval: 1m
    query: "SELECT DATEDIFF(sec, updated_at, SYSDATE) AS delay_sec FROM crowdworks_masked.system_timestamps"
    notifier: dogstatsd
    value_cols:
      - delay_sec

Redshiftの監視内容の解説は本題から逸れるので省略しますが、興味ある人はRedshiftのシステムテーブルリファレンスを参照して下さい。

できたDatadogダッシュボードの様子（一部）はこんなかんじ。

f:id:minamijoyo:20171002184919p:plain

クエリの実行状況とかキューのつまり具合がいいかんじに見える化されました。よさげ。

余談ですが、このへんのRedshiftの運用周りの話とかは、2017/10/11のBigData-JAWS#9 でもお話させていただく予定です。

おわりに

定期的にSQLを実行した結果をDatadogに送信するcyqldogというツールを作りました。

とりあえず必要なものを作ったものの、まだまだ荒削りなところも多々あると思いますが、 使ってみた感想や、使い方が分からないなどの質問、エラー出たんだけどという苦情など、なんかあればGitHubでIssueを立てていただくなり、Twitterで@minamijoyoまでフィードバックしただけると喜びます。

クラウドソーシングのクラウドワークス では、監視が好きなエンジニアや、Goでインフラ周りのツールを作りたいエンジニアを募集しています。

www.wantedly.com

© 2016 CrowdWorks, Inc., All rights reserved.