メトリクス用テーブルの選び方

CloudWatch のメトリクスを取るテーブルは、大きく2種類ある。

種類例特徴
リソース別の専用テーブルaws_ec2_instance_metric_cpu_utilization_daily
aws_rds_db_instance_metric_connections_hourly
対象のメトリクスと集計の単位が決まっている。条件なしで、全リソースぶんが返る
汎用のテーブルaws_cloudwatch_metric_statistic_data_point
aws_cloudwatch_metric_data_point
名前空間・メトリクス名・ディメンションを where で指定する。専用テーブルがないメトリクスに使う

専用テーブルは EC2・EBS・RDS・ECS・ElastiCache・Lambda・ALB/NLB などの代表的なメトリクスに用意されていて、名前の末尾で集計の単位と期間が決まる。

テーブル名の末尾集計の単位(Period)取得する期間
(なし)例:..._cpu_utilization5分直近5日
_hourly1時間直近60日
_daily1日(24時間)直近1年

どの専用テーブルも、列は共通で timestamp・average・maximum・minimum・sum・sample_count・unit が並ぶ(ほかにリソースの ID と region・account_id)。boto3 の PART 05 で説明した「名前空間・メトリクス名・ディメンション・統計」のうち、統計は5種類まとめて返ってくるので、SQL 側で必要なものを選べばよい。

⚠️ 期間を where で絞っても、取得量は減らない

専用テーブルは、timestamp の条件に関係なく、上の表の期間ぶんを毎回取得する(timestamp はキー列ではない)。where timestamp >= now() - interval '14 days' は、1年分を取ってから手元で絞り込む。直近だけが欲しいなら、_daily より期間の短い _hourly を選ぶほうが取得量は少ない。

実践:CPU使用率が低いEC2を洗い出す

「動いているが、ほとんど使われていない EC2」は、サイズを下げるか停止を検討する候補になる。boto3 では、インスタンスの一覧を取り、インスタンスごとにメトリクスを取り、結果を突き合わせるスクリプトを書いた。Steampipe では、EC2 の一覧と日次の CPU 使用率を join するだけだ。

~/steampipe-work/low_cpu_ec2.sql
-- 直近14日間、日次の最大CPU使用率がずっと10%未満の、動いているEC2
with cpu as (
  select
    account_id,
    region,
    instance_id,
    round(avg(average)::numeric, 1) as avg_cpu,
    round(max(maximum)::numeric, 1) as max_cpu,
    count(*)                        as days
  from
    aws_ec2_instance_metric_cpu_utilization_daily
  where
    timestamp >= now() - interval '14 days'
  group by
    account_id, region, instance_id
)
select
  i.account_id,
  i.region,
  i.instance_id,
  i.title          as name,
  i.instance_type,
  c.avg_cpu,
  c.max_cpu,
  c.days
from
  aws_ec2_instance as i
  join cpu as c
    on  c.instance_id = i.instance_id
    and c.region      = i.region
    and c.account_id  = i.account_id
where
  i.instance_state = 'running'
  and c.max_cpu < 10
  and c.days >= 14
order by
  c.avg_cpu;
出力例
+--------------+----------------+---------------------+----------+---------------+---------+---------+------+
| account_id   | region         | instance_id         | name     | instance_type | avg_cpu | max_cpu | days |
+--------------+----------------+---------------------+----------+---------------+---------+---------+------+
| 123456789012 | ap-northeast-1 | i-0aaaaaaaaaaaaaaaa | dev-tool | m5.large      | 0.8     | 3.2     | 14   |
| 123456789012 | ap-northeast-1 | i-0bbbbbbbbbbbbbbbb | batch-02 | c5.xlarge     | 1.5     | 7.9     | 14   |
+--------------+----------------+---------------------+----------+---------------+---------+---------+------+

判断の基準を「平均」ではなく「日次の最大値」にしているのは、日中だけ忙しいサーバーを誤って候補にしないためだ。日次の maximum は、その日に記録された5分ごと(詳細モニタリングなら1分ごと)の値の最大値なので、短いピークも拾われる。c.days >= 14 は、起動して間もないインスタンスを除くための条件である。

このクエリでは、メトリクスの取得のために インスタンスごとに GetMetricStatistics が1回呼ばれる(1年分を日次で取るので、1回で収まる)。インスタンスが数百台あっても、API の呼び出しは数百回だ。

💡 時刻は UTC で区切られる

CloudWatch の集計の区切りは UTC が基準で、日次の1日は日本時間の0時〜24時とは一致しない。timestamp を日本時間で表示したいときは timestamp at time zone 'Asia/Tokyo' とする。メトリクスの保持期間(5分値は63日、1時間値は455日など)の考え方は boto3 の PART 05 と同じだ。

専用テーブルがないメトリクスを取る

専用テーブルがないメトリクスは、aws_cloudwatch_metric_statistic_data_point で取る。boto3 の get_metric_statistics に当たるテーブルで、namespace と metric_name の指定が必須になる。特定のリソースのメトリクスを取るときは、dimensions を JSON の配列で指定する。

SQL:RDS の空きストレージ(直近7日、1時間ごと)
select
  timestamp at time zone 'Asia/Tokyo'          as jst,
  round((minimum / 1024 / 1024 / 1024)::numeric, 1) as free_gib_min
from
  aws_cloudwatch_metric_statistic_data_point
where
  namespace   = 'AWS/RDS'
  and metric_name = 'FreeStorageSpace'
  and dimensions  = '[{"Name": "DBInstanceIdentifier", "Value": "app-db-01"}]'
  and period      = 3600
  and timestamp  >= now() - interval '7 days'
order by
  timestamp;

こちらのテーブルは timestamp と period がキー列なので、指定した期間と単位で AWS から取得する(専用テーブルとは逆)。timestamp を省略すると直近24時間になる。1回の取得で返せるデータポイントは 1,440 個までなので、「1分単位で7日分」のような大きな範囲は、期間を分けるか単位を粗くする。

多数のリソースをまとめて集計したい場合は、GetMetricData を使う aws_cloudwatch_metric_data_point がある。id と、Metrics Insights の式(expression)を指定する。

SQL:全 EC2 の CPU 使用率の最大値(直近24時間)
select
  timestamp at time zone 'Asia/Tokyo' as jst,
  round(value::numeric, 1)            as max_cpu
from
  aws_cloudwatch_metric_data_point
where
  id = 'm1'
  and expression = 'SELECT MAX(CPUUtilization) FROM SCHEMA("AWS/EC2", InstanceId)'
order by
  timestamp;

コストを取る前に:料金と権限

コストのテーブルは Cost Explorer の API(GetCostAndUsage など)を呼ぶ。boto3 の PART 05 でも触れたとおり、Cost Explorer の API は1リクエストごとに 0.01 USD かかる。Steampipe でも同じで、テーブルを問い合わせるたびに(ページが分かれればページ数ぶん)課金される。

  • キャッシュが効く:同じクエリを5分以内に繰り返しても、キャッシュから返れば API は呼ばれない。試行錯誤するときは、先に必要な列を広めに取っておき、条件だけ変えるとキャッシュに当たりやすい(PART 03)
  • 集計は SQL 側で:テーブルを変えて何度も問い合わせるより、1つのテーブルの結果を with にまとめて、SQL で切り口を変えるほうが安い
  • 権限:ce:GetCostAndUsage が必要。Organizations の管理アカウントで実行すると、全メンバーアカウントのコストが返る
  • 有効化:Cost Explorer を一度も開いたことがないアカウントでは、コンソールで有効にするまで API が使えない

メトリクスのほうも無料ではない。GetMetricStatistics などの API リクエストは無料枠を超えると 1,000 件あたりで課金され、GetMetricData(aws_cloudwatch_metric_data_point)は取得したメトリクスの数に応じて課金される。数百台規模なら気にするほどの額ではないが、複数アカウント×全リージョンで頻繁に流す場合は意識しておきたい。

実践:サービス別の月額コストを出す

サービス別・月別のコストは aws_cost_by_service_monthly で取れる。直近1年分が返るが、period_start はキー列なので、条件を書けば取得する期間そのものを絞れる。

SQL:直近3か月のサービス別コスト
select
  to_char(period_start, 'YYYY-MM')        as month,
  service,
  round(unblended_cost_amount::numeric, 2) as cost_usd,
  estimated
from
  aws_cost_by_service_monthly
where
  period_start >= date_trunc('month', now()) - interval '2 months'
  and unblended_cost_amount >= 1
order by
  month desc, cost_usd desc;

estimated が true の行は、まだ確定していない当月分などの見込みの値だ。1 USD 未満の細かいサービスは unblended_cost_amount >= 1 で除いている(こちらはキー列ではないので、手元での絞り込みになる)。

前月と比べて増えたサービスを見つけるなら、ウィンドウ関数 lag で前月の値を横に並べる。

SQL:前月からの増減(金額の大きい順)
with monthly as (
  select
    date_trunc('month', period_start) as month,
    service,
    unblended_cost_amount            as cost
  from
    aws_cost_by_service_monthly
  where
    period_start >= date_trunc('month', now()) - interval '2 months'
),
diff as (
  select
    month,
    service,
    cost,
    lag(cost) over (partition by service order by month) as prev_cost
  from monthly
)
select
  to_char(month, 'YYYY-MM')                          as month,
  service,
  round(cost::numeric, 2)                            as cost_usd,
  round(prev_cost::numeric, 2)                       as prev_usd,
  round((cost - coalesce(prev_cost, 0))::numeric, 2) as diff_usd
from diff
where month = date_trunc('month', now()) - interval '1 month'
order by diff_usd desc
limit 10;

このクエリは「前月」と「前々月」を比べている。当月は日割りの途中なので、比べると必ず減って見えるためだ。

ほかにも、目的に応じて次のテーブルが使える。

テーブル内容
aws_cost_by_service_dailyサービス別・日別
aws_cost_by_account_monthlyアカウント別・月別(管理アカウントで使う)
aws_cost_by_service_usage_type_monthlyサービス×使用タイプ別。「EC2 の中でもデータ転送が増えた」などの深掘りに
aws_cost_forecast_monthly今後の予測
aws_cost_usage集計の軸を2つ自由に選べる。granularity・dimension_type_1・dimension_type_2 の指定が必須(例:'LINKED_ACCOUNT' × 'SERVICE')

必要なIAM権限

この回のクエリを読み取り専用で動かすのに必要なアクションをまとめておく。PART 04 のポリシーに足して使う。

テーブル必要なアクション
aws_ec2_instance_metric_* など専用テーブルcloudwatch:GetMetricStatistics、対象リソースの一覧(例:ec2:DescribeInstances)
aws_cloudwatch_metric_statistic_data_pointcloudwatch:GetMetricStatistics
aws_cloudwatch_metric_data_pointcloudwatch:GetMetricData
aws_cost_by_* / aws_cost_usagece:GetCostAndUsage
aws_cost_forecast_*ce:GetCostForecast

✅ 次の記事では…

PART 06 では、複数のアカウントをまとめて問い合わせる aggregator の設定と、結果がおかしいときやエラーが出たときの切り分け方を解説する。

→ PART 06 — 複数アカウントへの展開とトラブルシューティングへ