tracer-cloud--opensre/tests/synthetic/rds_postgres/011-cpu-storage-compositional/answer.yml

root_cause_category: dual_resource_exhaustion
required_keywords:
  - CPU
  - storage
  - analytics
  - audit_log
required_evidence_sources:
  - aws_performance_insights
forbidden_categories:
  - connection_exhaustion
ruling_out_keywords:
  # "connection" — must note connection spike is blocked-writer symptom, not exhaustion
  # "replication" — must note ReplicaLag is downstream write-stall symptom, not independent
  # "two" — must acknowledge dual/compositional fault (two independent root causes)
  - connection
  - replication
  - two
optimal_trajectory:
  - query_grafana_metrics
  - query_grafana_logs
  - query_grafana_alert_rules
  - describe_rds_instance
  - describe_rds_events
max_investigation_loops: 4
model_response: |
  ROOT_CAUSE: Two independent resource limits are being hit simultaneously on analytics-prod, forming a compositional fault. (1) CPU saturation: an analytics aggregation query (SELECT ... FROM analytics_events GROUP BY) is consuming 6.8 avg active sessions and 5.9 CPU AAS — nearly exhausting all 8 vCPUs. (2) Storage exhaustion: an audit_log INSERT job is writing ~847 rows/sec, generating 7.4 GB of data per minute, and FreeStorageSpace has dropped from 50 GB to 8 GB in 20 minutes. These are causally independent — the analytics query and the audit_log write burst started at the same time by coincidence.
  ROOT_CAUSE_CATEGORY: dual_resource_exhaustion

  VALIDATED_CLAIMS:
  - CPUUtilization rose from 21% to 91% in 5 minutes, driven by the analytics_reader SELECT query consuming 6.8 AAS with CPU:user as the dominant wait event at 5.9 AAS. [evidence: aws_cloudwatch_metrics, aws_performance_insights]
  - FreeStorageSpace dropped from 50 GB to 8.3 GB across the 20-minute window — a rate of ~2.1 GB/min — driven by 847 audit_log INSERT calls/sec generating IO:DataFileWrite load at 3.8 AAS. [evidence: aws_cloudwatch_metrics, aws_performance_insights]
  - DatabaseConnections grew from 148 to 563 — a consequence of writers blocking behind the growing write queue, not an independent connection pool leak. Connection exhaustion is ruled out as a root cause. [evidence: aws_cloudwatch_metrics]
  - ReplicaLag grew from 1s to 196s because the replica cannot keep up with the high write WAL volume from the audit_log bursts. This is a downstream symptom of the storage/write fault, not an independent replication problem. [evidence: aws_cloudwatch_metrics]
  - RDS events confirm two independent warnings: CPU threshold crossed at 09:08Z and low storage events began at 09:11Z. [evidence: aws_rds_events]

  NON_VALIDATED_CLAIMS:
  - The two faults starting simultaneously is likely coincidental — the analytics report job and the audit_log write burst appear to be unrelated scheduled jobs that happened to run at the same time.

  CAUSAL_CHAIN:
  - At 09:00Z, an analytics aggregation query began a full table scan of analytics_events, consuming 6.8 AAS and 91% CPU.
  - At the same time, an audit_log INSERT job began writing at 847 rows/sec, driving FreeStorageSpace from 50 GB toward zero.
  - Connection count rose as write transactions queued behind the storage bottleneck — this is not a connection exhaustion fault but a symptom of blocked writers.
  - ReplicaLag grew as WAL generated by the write burst exceeded replica replay capacity — this is a downstream symptom of the write fault, not an independent replication failure.
  - Two independent resource limits (compute and storage) are being hit simultaneously from two independent workloads.