Four keys(DORA Four Key metrics)をSQLで集計して、Grafanaで可視化する

こんにちは。プラットフォーム開発部のthorです。

以前の記事で、Four keys(海外では、指標を提唱した組織名からDORA Four Key metricsと呼ばれる事が多いです)についてご紹介しました。このメトリクスを測定するためのオープンソースのプロジェクトにFour Keysプロジェクトがあります。このプロジェクトを使用するには以下の要件を満たす必要があります。

実行環境

Google Cloud

対応データソース

ArgoCD
Circle CI
Cloud Build
GitHub
GitLab
PagerDuty
Tekton

2022年5月時点のロードマップを見ると、GitHub ActionsやJIRAも将来的には対応予定ですが、2023年5月時点では対応していません。また、実行環境がGoogle Cloudのみと、まだ使うための条件が厳しい状態です。

そこで、本記事ではDockerコンテナ上で動作するPostgreSQLでデータを集計しGrafanaで可視化する方法をご紹介します。(ただし、データは手入力で行う必要があります) Four keysの意味については、以下の以前の記事を参照してください。

miraitranslate-tech.hatenablog.jp

Dockerコンテナの準備

作業用のディレクトリを作成し、以下のdocker-compose.ymlファイルを作成します。

version: '3'

services:
  grafana:
    image: grafana/grafana:v8.2.6
    ports:
      - "3000:3000"
    environment:
      - GF_SECURITY_ADMIN_USER=admin
      - GF_SECURITY_ADMIN_PASSWORD=admin  # 初回ログイン時に変更します
    volumes:
      - grafana-data:/var/lib/grafana
    depends_on:
      - db
  db:
    image: postgres:15.3
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - db-store:/var/lib/postgresql/data
      - ./script:/docker-entrypoint-initdb.d  # DB初期化用のSQLファイルをマウント
    environment:
      - POSTGRES_PASSWORD=${PGPASSWORD}
volumes:
  grafana-data:
  db-store:

.envファイルを作成し以下を設定します。(password部分は適宜変更します)

PGPASSWORD='password'

DB初期化用のSQLファイルの作成

scriptディレクトリを作成し、そこに4keys.sqlファイルを作成して、以下の内容を定義します。Four Keysプロジェクトのコードをベースに、月に数回とかデプロイ頻度の少ないプロジェクトでの集計結果が分かりやすいように作成しています。

-- デプロイログ
CREATE TABLE deployments (
    deploy_id text,          -- デプロイのID。バージョン等。 ex) 1.5.7
    time_created timestamp,  -- デプロイした日時
    success boolean          -- デプロイ後障害が発生していなければtrue
);

-- インシデントログ
CREATE TABLE incidents (
    incident_id text,         -- インシデントのID。RedmineのチケットIDやJIRAのISSUE ID等。 ex) ISSUE-1234
    time_created timestamp,   -- 障害を検知した日時
    deploy_id_resolved text   -- 障害を復旧させたデプロイのdeploy_id
);

-- 変更ログ
CREATE TABLE changes (
    change_id text,           -- 変更ID。基本はcommit hash。
    time_created timestamp,   -- 障害を検知した日時
    deploy_id text            -- 変更をデプロイしたdeploy_id
);

-- 三月前から今日までの日付
CREATE VIEW days_from_1st_of_3_months_prior AS
SELECT generate_series as day
FROM GENERATE_SERIES(date_trunc('month', current_date + interval '-3 months'), current_date, '1 days')
;

-- 日毎のデプロイ
CREATE VIEW daily_deployment AS
SELECT days_from_1st_of_3_months_prior.day AS day,    -- 日付
      COUNT(deploy_id) AS day_deployed                -- 1日に何回デプロイしたか
FROM days_from_1st_of_3_months_prior
      LEFT JOIN (SELECT date_trunc('day', time_created) AS day,
                        deploy_id
                  FROM deployments) deployments
                  ON deployments.day = days_from_1st_of_3_months_prior.day
GROUP BY days_from_1st_of_3_months_prior.day
;

-- 週毎のデプロイ
CREATE VIEW weekly_deployment AS
SELECT date_trunc('week', days_from_1st_of_3_months_prior.day)     AS week,           -- 週の開始日
      MAX(CASE WHEN deployments.day is not null THEN 1 ELSE 0 END) AS week_deployed,  -- 週に1回以上デプロイしたか
      COUNT(distinct deployments.day)                              AS days_deployed   -- 週に何日デプロイしたか
FROM days_from_1st_of_3_months_prior
      LEFT JOIN (SELECT date_trunc('day', time_created) AS day,
                        deploy_id
                  FROM deployments) deployments
                  ON deployments.day = days_from_1st_of_3_months_prior.day
      WHERE days_from_1st_of_3_months_prior.day > date_trunc('week', current_date + interval '-12 weeks')
GROUP BY week
;

-- 月毎のデプロイ
CREATE VIEW monthly_deployment AS
SELECT date_trunc('month', days_from_1st_of_3_months_prior.day) AS month,  -- 月の開始日
      COUNT(deployments.day_deployed > 0 OR NULL) AS days_deployed         -- 月に何日デプロイしたか
FROM days_from_1st_of_3_months_prior
      LEFT JOIN (SELECT date_trunc('day', day) AS day,
                        day_deployed
                  FROM daily_deployment) deployments
                  ON deployments.day = days_from_1st_of_3_months_prior.day
GROUP BY month
;

-- 週毎のデプロイ頻度(中央値)
CREATE VIEW median_weekly_deployment_frequency AS
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY days_deployed) AS median_days_deployed_in_week, -- 週に何日デプロイしたかの中央値
      percentile_cont(0.5) WITHIN GROUP (ORDER BY week_deployed) AS median_weekly_deployed         -- 週に1回以上デプロイしたかの中央値
FROM weekly_deployment
;

-- 30日毎のデプロイ頻度
CREATE VIEW median_30days_deployment_frequency AS
WITH last_three_30days AS
    (SELECT generate_series(current_date + interval '-89 days', current_date, '30 days') AS bin_start)
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY deploy_count) AS median_30days_deployment      -- 30日に何回デプロイしたかの中央値
FROM (SELECT COUNT(day_deployed > 0 OR NULL) AS deploy_count
      FROM last_three_30days
      LEFT JOIN (SELECT date_bin('30 days', day, current_date + interval '-89 days') AS bin,
                        day_deployed
                 FROM daily_deployment) AS d
                ON last_three_30days.bin_start = d.bin
      GROUP BY bin_start) AS thirty_days_delploys
;

-- デプロイ頻度のバケット
CREATE VIEW deployment_frequency_bucket AS
SELECT CASE WHEN daily THEN 'Daily'
            WHEN weekly THEN 'Weekly'
            WHEN monthly THEN 'Monthly'
            ELSE 'Yearly'
            END as deployment_frequency
FROM (SELECT median_days_deployed_in_week >= 3 AS daily,
             median_weekly_deployed >= 1 AS weekly
      FROM median_weekly_deployment_frequency) AS median_weekly,
     (SELECT median_30days_deployment >= 1 AS monthly
      FROM median_30days_deployment_frequency) AS median_30days
;


-- 変更のリードタイム
CREATE VIEW median_time_to_changes AS
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY time_to_change) AS median_time_to_change    -- 変更のリードタイムの中央値
FROM (SELECT deployments.deploy_id,
             date_trunc('day', deployments.time_created) AS day,
             deployments.time_created - changes.time_created AS time_to_change
      FROM deployments, changes
      WHERE deployments.deploy_id = changes.deploy_id
            AND deployments.time_created BETWEEN date_trunc('day', now()) + '-3 months' + '1 days'
                                             AND date_trunc('day', now())) AS time_to_changes
;

-- リードタイムのバケット
CREATE VIEW time_to_changes_bucket AS
SELECT CASE WHEN median_time_to_change < interval '24 hours' THEN 'One day'
            WHEN median_time_to_change < interval '1 week'   THEN 'One week'
            WHEN median_time_to_change < interval '1 month'  THEN 'One month'
            WHEN median_time_to_change < interval '6 month'  THEN 'Six month'
            ELSE 'One year'
            END AS lead_time_to_change
FROM median_time_to_changes
;

-- 変更障害率
CREATE VIEW change_failure_rate AS
SELECT count (success = false OR NULL) / cast( count (deploy_id) AS float ) AS rate    -- 変更障害率
FROM deployments
WHERE time_created BETWEEN current_date + interval '-3 months' AND current_date
;

-- 変更障害率バケット
CREATE VIEW change_failure_rate_bucket AS
SELECT CASE WHEN rate < 0.15 THEN '0-15%'
            WHEN rate < 0.46  THEN '16-45%'
            ELSE CAST(rate AS TEXT) || '%'
            END AS change_failure_rate_bucket
FROM change_failure_rate
;

-- サービス復元
CREATE VIEW restore_services AS
    SELECT incidents.incident_id AS incident_id,
           incidents.time_created AS time_failed,
           deployments.time_created AS time_resolved
    FROM deployments, incidents
    WHERE deployments.deploy_id = incidents.deploy_id_resolved
      AND deployments.time_created BETWEEN current_date + interval '-3 months' AND current_date
;

-- サービス復元時間
CREATE VIEW median_time_to_restore_service AS
SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY time_to_resolve) AS med_time_to_resolve
FROM (SELECT date_trunc('hour', time_resolved - time_failed) AS time_to_resolve
      FROM restore_services) AS time_to_restore_services
;

-- サービス復元時間バケット
CREATE VIEW time_to_restore_service_bucket AS
SELECT CASE WHEN med_time_to_resolve < interval '24 hours' THEN 'One day'
            WHEN med_time_to_resolve < interval '1 week'   THEN 'One week'
            WHEN med_time_to_resolve < interval '1 month'  THEN 'One month'
            WHEN med_time_to_resolve < interval '6 months' THEN 'Six month'
            ELSE 'One year'
            END AS time_to_restore_services_bucket
FROM (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY time_to_resolve) AS med_time_to_resolve
      FROM (SELECT date_trunc('hour', time_resolved - time_failed) AS time_to_resolve
            FROM restore_services) AS time_to_restore_services
     ) AS p_resolve
;

Dockerコンテナの起動

以下のコマンドを実行し、コンテナを起動します。

docker-compose up -d

変更・デプロイ・インシデントの履歴の登録

まず、以下のようなコミットの履歴があるとします。

各バージョンのデプロイが以下のようになっていたとします。

バージョン	デプロイ日	障害発生の有無
1.0.0	1/30	なし
1.1.0	2/10	なし
1.2.0	2/22	あり
1.2.1	2/26	なし
1.3.0	3/15	なし
1.4.0	4/20	なし

ここでバージョンの1.2.0はデプロイ後の2月23日に障害が発見されたとします。

上記のような場合、以下のようなCSVファイルを作成します。

まず、コミット履歴のchanges.csvファイルを作成します。

change_id,time_created,deploy_id
28f11953,2023-02-05 12:21:10 +0900,1.1.0
762ed56b,2023-02-07 17:35:23 +0900,1.1.0
c38e5fa1,2023-02-11 10:11:55 +0900,1.2.0
ed2cbc88,2023-02-15 13:27:05 +0900,1.2.0
3eefae6c,2023-02-18 14:48:39 +0900,1.2.0
8eff3ab9,2023-02-24 17:59:18 +0900,1.2.1
1c243e69,2023-03-01 09:27:27 +0900,1.3.0
a70ce5b4,2023-03-11 15:11:44 +0900,1.3.0
96343c50,2023-03-12 16:33:13 +0900,1.3.0
1de889de,2023-03-13 17:25:39 +0900,1.3.0
b1d011ba,2023-03-22 10:03:56 +0900,1.4.0
90e0d745,2023-04-09 18:43:15 +0900,1.4.0
25386e01,2023-04-18 11:55:01 +0900,1.4.0

以下のようにコマンドを実行すると、1.0.0と1.1.0のバージョン間の全コミットの履歴を取得できます。これを各バージョンで実行して追記していきます。

git log 1.0.0..1.1.0 --date=iso --pretty=format:"%h,%cd,1.1.0" >> changes.csv

次に、デプロイ履歴のdeploy.csvファイルを作成します。これは、その組織でのデプロイ作業の記録から作成します。このsuccessは、最初はtですが、障害が発見された場合はfに更新する必要があります。

deploy_id,time_created,success
1.1.0,2023-02-10 20:55:18 +0900,t
1.2.0,2023-02-22 21:18:37 +0900,f
1.2.1,2023-02-26 20:35:25 +0900,t
1.3.0,2023-03-15 22:05:12 +0900,t
1.4.0,2023-04-20 21:19:09 +0900,t

最後に障害の履歴のincidents.csvファイルを作成します。これはチケットの作成日時から作成します。

ticket_id,time_created,deploy_id_resolved
ISSUE-1234,2023-02-23 12:55:18 +0900,1.2.1

ファイルの作成が完了したら、以下のようにDBにcopyコマンドを実行して登録します。

source ./.env
export PGPASSWORD
psql -h localhost -U postgres -c "\copy deployments from ./deployments.csv delimiter ',' header"
psql -h localhost -U postgres -c "\copy incidents from ./incidents.csv delimiter ',' header"
psql -h localhost -U postgres -c "\copy changes from ./changes.csv delimiter ',' header"

Grafanaで可視化

ログインとDB接続設定

http://localhost:3000をブラウザで開きます。

初期パスワードは

username: admin
password: admin

になっています。ログイン後にパスワードの変更を求められるので変更します。

ログイン後、左のメニューでConnect dataを選択します。

Connect dataの検索部分でPostgreSQLと入力し、検索結果のPostgreSQLをクリックします。

Create a PostgreSQL data sourceをクリックします。

data source画面で以下のように接続情報を入力し、Save & testボタンをクリックします。

Host: db:5432
Database: postgres
User: postgres
Password: 設定したパスワード
TLS/SSL Mode: disable

バケットの可視化

Home画面に戻り、右上のAddメニューでVisualizationを選択します。

左下で

Table: deployment_frequency_bucket
Column: deployment_frequency

を選択し、右側で

Stat
Title: デプロイ頻度バケット
Fields: deployment_frequency

を選択し、Run queryボタンをクリックします。

更に、右側の下部の Value mappings, Thresholdsを以下のように設定します。色の設定はチームの目標に合わせて変更します。

設定が完了したら、右上のApplyボタンをクリックします。

同様にリードタイム、変更障害率、サービス復元時間についてもバケットの設定をします。

以上で、Four keysの結果を可視化できました。

詳細データを可視化

時系列データを可視化

次に、時間によって変化する指標を可視化します。ここでは例として、月毎のデプロイをグラフ化します。

左下で、テーブル、カラムを選択した後、Run queryボタンをクリックします。

右上で、

Bar chart
Title: 月毎のデプロイ

としてApplyボタンをクリックします。

ヒストグラム情報を可視化

最後に、変更のリードタイムの分布を可視化します。

左下のクエリの入力ペインでcodeをクリックし、以下のクエリを入力しRun queryボタンをクリックします。

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY lead_time)
FROM (SELECT deployments.deploy_id,
             extract(epoch FROM (deployments.time_created - changes.time_created)) / (60 * 60 * 24) AS lead_time
       FROM deployments, changes
       WHERE deployments.deploy_id = changes.deploy_id
             AND deployments.time_created > date_trunc('month', current_date + interval '-3 months')) AS lead_times
GROUP BY lead_times.deploy_id