Reviewing Cluster Health

Reviewing Cluster Health

Performs a comprehensive health check of a CockroachDB cluster. Before running diagnostics, this skill gathers deployment context to provide the right queries and tools for the operator's tier.

When to Use This Skill

• Daily or shift-start operational health checks
• Before starting maintenance (Self-Hosted, Advanced, BYOC)
• After incidents to confirm recovery
• Verifying production readiness
• Monitoring capacity and performance

For live query issues: Use triaging-live-sql-activity. For background jobs: Use monitoring-background-jobs. For range analysis: Use analyzing-range-distribution.

Step 1: Gather Context

Required Context

Additional Context (by tier)

If Self-Hosted:

If Advanced or BYOC:

If Standard:

If Basic: No additional context needed.

Context-Driven Routing

Self-Hosted Health Check

Applies when: Tier = Self-Hosted

Self-Hosted node-level health is read primarily through cockroach node status (CLI) and the DB Console. Cluster settings and jobs are read through public SQL (SHOW ALL CLUSTER SETTINGS, SHOW JOBS). The crdb_internal virtual tables for cluster topology, storage, and certificates are not for production use — see the docs for the production-safe table list.

Check 1: Node Liveness, Version, and Replication

cockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>

Key columns:

• is_live — false requires immediate investigation
• is_draining, is_decommissioning, membership — flag in-progress lifecycle operations
• started_at — compare across runs to spot flapping (node restarts)
• build — version per node; should be a single value (or two during a rolling upgrade)
• ranges_underreplicated — non-zero indicates ranges below the zone's num_replicas

For finer-grained range breakdown, use the DB Console Replication page.

Check 2: Storage Capacity

No production-safe SQL view exposes per-store capacity. Use:

• DB Console Overview → Storage for per-node usage
• The Prometheus metric endpoint on each node: curl -ks https://<node>:8080/_status/vars | grep '^capacity' (capacity, capacity_used, capacity_available)

Check 3: Certificate Expiration

No SQL view exposes node certificate expiration. Use one of:

• cockroach cert list --certs-dir=<certs-dir> to inspect certs locally on each node
• openssl x509 -in <cert.crt> -noout -enddate for a single cert file
• The Prometheus metric endpoint: curl -ks https://<node>:8080/_status/vars | grep '^security_certificate_expiration_' (UNIX-timestamp seconds; node, ca, client_ca, ui_ca)

Treat anything within 90 days as EXPIRING_SOON.

Check 4: Critical Settings

SELECT variable, value FROM [SHOW ALL CLUSTER SETTINGS]
WHERE variable IN (
  'kv.rangefeed.enabled', 'sql.stats.automatic_collection.enabled',
  'server.time_until_store_dead', 'admission.kv.enabled',
  'cluster.preserve_downgrade_option'
) ORDER BY variable;

gc.ttlseconds is a zone-config parameter, not a cluster setting; check the effective value with SHOW ZONE CONFIGURATION FOR ... against the relevant table/database/range.

Check 5: Consolidated Summary

The DB Console Cluster Overview page consolidates live/dead node count, version distribution, range counts, and storage. From the CLI:

cockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>

then aggregate the columns of interest in your shell. The cluster's logical version comes from SQL:

SELECT value AS cluster_version FROM [SHOW CLUSTER SETTING version];

If reason = Pre-maintenance, also check for running jobs:

WITH j AS (SHOW JOBS)
SELECT job_type, COUNT(*) FROM j WHERE status = 'running' GROUP BY job_type;

Check 6: Production Readiness Assessment

Use when verifying a cluster is ready for production workloads or during periodic operational reviews.

# Node count, liveness, and locality diversity
cockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>

In the output, count rows with is_live = true (production wants ≥ 3) and check that locality shows multiple regions/zones.

-- Critical production settings check
SELECT variable, value,
  CASE
    WHEN variable = 'kv.rangefeed.enabled' AND value = 'true' THEN 'OK'
    WHEN variable = 'kv.rangefeed.enabled' AND value = 'false' THEN 'WARN: should be true for CDC'
    WHEN variable = 'sql.stats.automatic_collection.enabled' AND value = 'true' THEN 'OK'
    WHEN variable = 'sql.stats.automatic_collection.enabled' AND value = 'false' THEN 'WARN: should be true'
    WHEN variable = 'admission.kv.enabled' AND value = 'true' THEN 'OK'
    WHEN variable = 'admission.kv.enabled' AND value = 'false' THEN 'WARN: recommended for production'
    WHEN variable = 'cluster.preserve_downgrade_option' AND value != '' THEN 'INFO: finalization pending'
    ELSE 'OK'
  END AS assessment
FROM [SHOW ALL CLUSTER SETTINGS]
WHERE variable IN (
  'kv.rangefeed.enabled', 'sql.stats.automatic_collection.enabled',
  'admission.kv.enabled', 'cluster.preserve_downgrade_option',
  'server.time_until_store_dead'
) ORDER BY variable;

-- Enterprise license status (Self-Hosted only)
SELECT value AS organization FROM [SHOW CLUSTER SETTING cluster.organization];

See production-readiness reference for the full production readiness checklist.

Advanced Health Check

Applies when: Tier = Advanced

Advanced clusters are dedicated single-tenant clusters managed by Cockroach Labs. You have node-level visibility via both Cloud Console and SQL.

Cloud Console Checks

1. Cluster Overview — verify all nodes are live, check node count
2. Metrics — CPU utilization, QPS, P99 latency, storage utilization
3. Alerts — check for active alerts

CLI + SQL Checks

# Node liveness, version, and replication status
cockroach node status --decommission --certs-dir=<certs-dir> --host=<any-live-node>

Look at is_live, build, and ranges_underreplicated per node.

-- Recent failed jobs
WITH j AS (SHOW JOBS)
SELECT job_type, status, COUNT(*) FROM j
WHERE status IN ('running', 'failed') AND created > now() - INTERVAL '24 hours'
GROUP BY job_type, status;

Cloud API

curl -s -H "Authorization: Bearer $COCKROACH_API_KEY" \
  "https://cockroachlabs.cloud/api/v1/clusters/<cluster-id>" | jq '.state, .cockroach_version'

BYOC Health Check

Applies when: Tier = BYOC

BYOC clusters are dedicated and run in your cloud account. You have the same CockroachDB visibility as Advanced, plus direct access to the underlying infrastructure.

CockroachDB Health

Run all Advanced Health Check steps.

Cloud Provider Infrastructure Checks

If AWS:

aws ec2 describe-instance-status --filters "Name=tag:cockroach-cluster,Values=<cluster-name>"

If GCP:

gcloud compute instances list --filter="labels.cockroach-cluster=<cluster-name>"

If Azure:

az vm list --resource-group <rg> --query "[?tags.cockroachCluster=='<cluster-name>']"

Additional BYOC Checks

• Verify VPC/network connectivity (PrivateLink, PSC, VPC Peering)
• Check IAM roles — CRL service account permissions still valid
• Review cloud provider monitoring for infrastructure-level anomalies

Standard Health Check

Applies when: Tier = Standard

Standard is a multi-tenant managed service. There are no individual nodes to monitor — Cockroach Labs manages all infrastructure, replication, and capacity. Health checking focuses on your workload performance and provisioned compute.

Cloud Console Checks

1. Cluster Overview — verify cluster state is RUNNING
2. SQL Activity — statement and transaction latency, error rates
3. Storage — current usage
4. Compute — provisioned vCPU utilization

SQL Checks

-- Verify connectivity
SELECT 1;

-- Current version
SELECT version();

-- Recent failed jobs
WITH j AS (SHOW JOBS)
SELECT job_type, status, description FROM j
WHERE status = 'failed' AND created > now() - INTERVAL '24 hours';

What to Monitor

• P99 SQL latency — track via Cloud Console Metrics
• Error rates — check for spikes in statement errors
• Storage growth — plan based on usage trends
• Compute utilization — increase provisioned vCPUs if utilization is consistently high

Note: Node-level visibility is not available on Standard. Use Cloud Console for all infrastructure health monitoring.

Basic Health Check

Applies when: Tier = Basic

Basic is a serverless offering that auto-scales. There are no nodes or provisioned compute to monitor. Cockroach Labs manages all infrastructure. Health checking focuses on connectivity, consumption, and spending.

Cloud Console Checks

1. Cluster Overview — verify state is RUNNING
2. Request Units — consumption rate and remaining budget
3. Storage — current usage (10 GiB included free)
4. Spending Limits — verify limits are configured to avoid unexpected charges

SQL Checks

-- Verify connectivity
SELECT 1;

-- Current version
SELECT version();

-- Recent failed jobs
WITH j AS (SHOW JOBS)
SELECT job_type, status, description FROM j
WHERE status = 'failed' AND created > now() - INTERVAL '24 hours';

What to Monitor

• Request Unit (RU) consumption — track via Cloud Console to stay within spending limits
• Storage usage — monitor growth relative to the 10 GiB free tier
• Query efficiency — optimize queries that consume excessive RUs
• Cold start latency — Basic clusters may scale to zero during inactivity; first connection after idle may have higher latency

Safety Considerations

All checks in this skill are read-only. No data is modified.

• Self-Hosted: cockroach node status requires CLI access (or admin SQL privilege if you need to fall back to internal tables). Most node-level health queries have no production-safe SQL alternative.
• Advanced/BYOC: cockroach node status works the same way; certificate inspection is managed by Cockroach Labs.
• Standard/Basic: No node-level visibility by design — use the Cloud Console.

Troubleshooting

References

Skill references:

• Production readiness checklist

Related skills:

• upgrading-cluster-version
• managing-cluster-capacity
• performing-cluster-maintenance
• monitoring-background-jobs

Official CockroachDB Documentation:

• Monitoring and Alerting
• cockroach node status
• Production Checklist
• Cloud Console Monitoring
• Export Metrics (Advanced)