GCP Cloud Spanner

Cloud Spanner Skill

Manage and analyze Google Cloud Spanner using gcloud spanner commands.

Discovery-First Rule

ALWAYS discover before acting. Never assume instance names, database names, or table names.

# Discover Spanner instances
gcloud spanner instances list --format=json \
  | jq '[.[] | {name: .name | split("/") | last, displayName: .displayName, config: .config | split("/") | last, nodeCount: .nodeCount, processingUnits: .processingUnits, state: .state}]'

Parallel Execution Requirement

ALL independent operations MUST run in parallel using background jobs (&) and wait.

for instance in $(gcloud spanner instances list --format="value(name)" | xargs -I{} basename {}); do
  {
    gcloud spanner databases list --instance="$instance" --format=json
  } &
done
wait

Helper Functions

# List databases in an instance
list_databases() {
  local instance="$1"
  gcloud spanner databases list --instance="$instance" --format=json \
    | jq '[.[] | {name: .name | split("/") | last, state: .state, versionRetentionPeriod: .versionRetentionPeriod, earliestVersionTime: .earliestVersionTime, encryptionConfig: .encryptionConfig, databaseDialect: .databaseDialect}]'
}

# Get database DDL (schema)
get_schema() {
  local instance="$1" database="$2"
  gcloud spanner databases ddl describe --instance="$instance" --database="$database" --format=json
}

# Execute a read-only query
query_spanner() {
  local instance="$1" database="$2" sql="$3"
  gcloud spanner databases execute-sql "$database" --instance="$instance" --sql="$sql" --format=json
}

# Get instance metrics
get_instance_metrics() {
  local instance="$1"
  gcloud monitoring time-series list \
    --filter="metric.type=starts_with(\"spanner.googleapis.com/\") AND resource.labels.instance_id=\"$instance\"" \
    --interval-start-time="$(date -u -v-1H +%Y-%m-%dT%H:%M:%SZ 2>/dev/null || date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ)" \
    --format=json --limit=50
}

Common Operations

1. Instance and Database Overview

instances=$(gcloud spanner instances list --format="value(name)" | xargs -I{} basename {})
for inst in $instances; do
  {
    echo "=== Instance: $inst ==="
    gcloud spanner instances describe "$inst" --format=json \
      | jq '{name: .name | split("/") | last, config: .config | split("/") | last, processingUnits: .processingUnits, nodeCount: .nodeCount, state: .state}'
    list_databases "$inst"
  } &
done
wait

2. Query Statistics

# Top queries by CPU usage (from SPANNER_SYS)
query_spanner "$INSTANCE" "$DATABASE" "
  SELECT text, execution_count, avg_latency_seconds, avg_cpu_seconds
  FROM SPANNER_SYS.QUERY_STATS_TOP_MINUTE
  ORDER BY avg_cpu_seconds DESC
  LIMIT 10"

# Query statistics over the last hour
query_spanner "$INSTANCE" "$DATABASE" "
  SELECT text, execution_count, avg_latency_seconds, avg_rows_scanned, avg_cpu_seconds
  FROM SPANNER_SYS.QUERY_STATS_TOP_HOUR
  ORDER BY execution_count DESC
  LIMIT 10"

3. Hot Spot Detection

# Read/write statistics by table
query_spanner "$INSTANCE" "$DATABASE" "
  SELECT t.TABLE_NAME, t.ROW_COUNT, t.BYTES
  FROM INFORMATION_SCHEMA.TABLE_STATISTICS AS t
  ORDER BY t.BYTES DESC"

# Lock statistics (detect contention)
query_spanner "$INSTANCE" "$DATABASE" "
  SELECT ROW_RANGE_START_KEY, LOCK_WAIT_SECONDS, SAMPLE_LOCK_REQUESTS
  FROM SPANNER_SYS.LOCK_STATS_TOP_MINUTE
  ORDER BY LOCK_WAIT_SECONDS DESC
  LIMIT 10"

# Transaction statistics
query_spanner "$INSTANCE" "$DATABASE" "
  SELECT FPRINT, READ_COLUMNS, WRITE_CONSTRUCTIVE_COLUMNS, AVG_COMMIT_LATENCY_SECONDS, AVG_TOTAL_LATENCY_SECONDS
  FROM SPANNER_SYS.TXN_STATS_TOP_MINUTE
  ORDER BY AVG_TOTAL_LATENCY_SECONDS DESC
  LIMIT 10"

4. Schema Analysis

# Get full schema DDL
get_schema "$INSTANCE" "$DATABASE"

# Table and index information
query_spanner "$INSTANCE" "$DATABASE" "
  SELECT TABLE_NAME, COLUMN_NAME, SPANNER_TYPE, IS_NULLABLE, ORDINAL_POSITION
  FROM INFORMATION_SCHEMA.COLUMNS
  ORDER BY TABLE_NAME, ORDINAL_POSITION"

# Index usage
query_spanner "$INSTANCE" "$DATABASE" "
  SELECT TABLE_NAME, INDEX_NAME, INDEX_TYPE, IS_UNIQUE, IS_NULL_FILTERED
  FROM INFORMATION_SCHEMA.INDEXES
  ORDER BY TABLE_NAME"

5. Performance Monitoring

# CPU utilization
gcloud monitoring time-series list \
  --filter="metric.type=\"spanner.googleapis.com/instance/cpu/utilization\" AND resource.labels.instance_id=\"$INSTANCE\"" \
  --interval-start-time="$(date -u -v-1H +%Y-%m-%dT%H:%M:%SZ 2>/dev/null || date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ)" \
  --format=json

# Storage usage
gcloud monitoring time-series list \
  --filter="metric.type=\"spanner.googleapis.com/instance/storage/used_bytes\" AND resource.labels.instance_id=\"$INSTANCE\"" \
  --interval-start-time="$(date -u -v-24H +%Y-%m-%dT%H:%M:%SZ 2>/dev/null || date -u -d '24 hours ago' +%Y-%m-%dT%H:%M:%SZ)" \
  --format=json

# Operation latencies
gcloud monitoring time-series list \
  --filter="metric.type=\"spanner.googleapis.com/api/request_latencies\" AND resource.labels.instance_id=\"$INSTANCE\"" \
  --interval-start-time="$(date -u -v-1H +%Y-%m-%dT%H:%M:%SZ 2>/dev/null || date -u -d '1 hour ago' +%Y-%m-%dT%H:%M:%SZ)" \
  --format=json

Output Format

Present results as a structured report:

Gcp Cloud Spanner Report
════════════════════════
Resources discovered: [count]

Resource       Status    Key Metric    Issues
──────────────────────────────────────────────
[name]         [ok/warn] [value]       [findings]

Summary: [total] resources | [ok] healthy | [warn] warnings | [crit] critical
Action Items: [list of prioritized findings]

Target ≤50 lines of output. Use tables for multi-resource comparisons.

Anti-Hallucination Rules

1. NEVER assume resource names — always discover via CLI/API in Phase 1 before referencing in Phase 2.
2. NEVER fabricate metric names or dimensions — verify against the service documentation or --help output.
3. NEVER mix CLI commands between service versions — confirm which version/API you are targeting.
4. ALWAYS use the discovery → verify → analyze chain — every resource referenced must have been discovered first.
5. ALWAYS handle empty results gracefully — an empty response is valid data, not an error to retry.

Counter-Rationalizations

Common Pitfalls

1. Sequential primary keys: Monotonically increasing keys (timestamps, auto-increment) cause hot spots. Use UUIDs or bit-reversed sequences.
2. Node count vs processing units: 1 node = 1000 processing units. Scaling by processing units gives finer granularity (minimum 100 PU for regional, 300 PU for multi-region).
3. Interleaved tables: Interleaved tables co-locate parent and child rows for performance. Deleting a parent row cascades to children if ON DELETE CASCADE is set.
4. SPANNER_SYS tables: System statistics tables are available only for queries from gcloud spanner databases execute-sql, not from client libraries without explicit configuration.
5. Stale reads: Use --read-timestamp or --exact-staleness for stale reads that reduce lock contention. Fresh reads (default) require locks.