Shows which Cloud SQL DBs might be over-provisioned, or not used at all

db-utilization.sh

#!/bin/sh

# Usage: bash <(curl -sL https://gist.githubusercontent.com/kienstra/c8adeb905bf1aea95a1d7bca421ac057/raw/db-utilization.sh) <project id>

set -eu

trap 'echo "Error on or near line $LINENO" >&2; exit 1' INT TERM HUP ERR

fail() {
  echo "ERROR: $*" >&2
  exit 1
}

command -v gcloud >/dev/null 2>&1 || fail "gcloud is required but not found in PATH."
command -v jq >/dev/null 2>&1 || fail "jq is required but not found in PATH."
command -v curl >/dev/null 2>&1 || fail "curl is required but not found in PATH."
command -v date  >/dev/null 2>&1 || fail "date is required but not found in PATH."

if [ $# -ne 1 ]; then
  echo "Usage: $0 <project id>"
  exit 1
fi
PROJECT_ID=$1

get_date() {
  n_days=$1
  if date -v -1d +%Y-%m-%dT%H:%M:%SZ >/dev/null 2>&1; then
    date -u -v "-${n_days}d" +"%Y-%m-%dT%H:%M:%SZ"
  else
    date -u -d "${n_days} days ago" +"%Y-%m-%dT%H:%M:%SZ"
  fi
}

DAYS_AGO=45
NOW=$(get_date 0)
START=$(get_date "$DAYS_AGO")

TOKEN="$(gcloud auth print-access-token || true)"
[ -n "$TOKEN" ] || fail "Failed to obtain access token via gcloud. Are you logged in and authorized?"

echo "Scanning Cloud SQL instances in project: $PROJECT_ID"

{
  printf "%-20s %-8s %-8s %-8s %-33s %-40s %-25s %-10s\n" \
    "TIER" "MAX_CPU" "P95_CPU" "MEAN_CPU" "PROJECT_ID" "INSTANCE_ID" "CREATED_TIME" "ENGINE"

  gcloud sql instances list --project="$PROJECT_ID" --format='value(name)' |
  while IFS= read -r db_id; do
    [ -z "$db_id" ] && continue

    if ! instance_info=$(gcloud sql instances describe "$db_id" --project="$PROJECT_ID" \
        --format='json(settings.tier,createTime,databaseVersion)' 2>/dev/null); then
      echo "WARN: Failed to describe instance '$db_id' in project '$PROJECT_ID'." >&2
      continue
    fi

    tier=$(echo "$instance_info" | jq -r '.settings.tier // "-"')
    created_time=$(echo "$instance_info" | jq -r '.createTime // "-"')
    db_version=$(echo "$instance_info" | jq -r '.databaseVersion // "-"')

    engine="UNKNOWN"
    echo "$db_version" | grep -iq "POSTGRES" && engine="postgres"
    echo "$db_version" | grep -iq "MYSQL" && engine="mysql"
    [ "$engine" = "UNKNOWN" ] && engine="$db_version"

    response=$(curl -s -H "Authorization: Bearer ${TOKEN}" \
      "https://monitoring.googleapis.com/v3/projects/${PROJECT_ID}/timeSeries?filter=metric.type=\"cloudsql.googleapis.com/database/cpu/utilization\"%20AND%20resource.labels.database_id=\"${PROJECT_ID}:${db_id}\"&interval.startTime=${START}&interval.endTime=${NOW}&aggregation.alignmentPeriod=3600s&aggregation.perSeriesAligner=ALIGN_MEAN" || true)

    if [ -z "$response" ]; then
      echo "WARN: Empty response from Monitoring API for instance '$db_id'." >&2
      max_cpu="-"
      mean_cpu="-"
      p95_cpu="-"
    else
      has_error=$(echo "$response" | jq 'has("error")' 2>/dev/null || echo "false")
      if [ "$has_error" = "true" ]; then
        echo "WARN: Monitoring API returned an error for instance '$db_id':" >&2
        echo "$response" | jq '.error' >&2 || echo "$response" >&2
        max_cpu="-"
        mean_cpu="-"
        p95_cpu="-"
      else
        values=$(echo "$response" | jq '([.timeSeries[]?.points[]?.value.doubleValue] // [])' 2>/dev/null || echo "[]")
        values_count=$(echo "$values" | jq 'length' 2>/dev/null || echo "0")

        if [ "$values_count" -eq 0 ]; then
          max_cpu="-"
          mean_cpu="-"
          p95_cpu="-"
        else
          max_cpu=$(echo "$values" | jq 'max | (. * 10000 | floor / 10000)' 2>/dev/null || echo "-")
          mean_cpu=$(echo "$values" | jq '(add / length) | (. * 10000 | floor / 10000)' 2>/dev/null || echo "-")
          p95_cpu=$(echo "$values" | jq 'sort | .[(length * 0.95 | floor)] | (. * 10000 | floor / 10000)' 2>/dev/null || echo "-")
        fi
      fi
    fi

    printf "%-20s %-8s %-8s %-8s %-33s %-40s %-25s %-10s\n" \
      "$tier" "$max_cpu" "$p95_cpu" "$mean_cpu" "$PROJECT_ID" "$db_id" "$created_time" "$engine"
  done
} | column -t