roachtest: reliability and HA of o11y infrastructure

# Summary

Our current observability infrastructure for testeng has critical single points of failure, scalability bottlenecks, and limited data retention. To improve reliability, scalability, and long-term analytics, we propose a comprehensive revamp spanning metrics, logs, and roachprod orchestration.

This issue outlines a plan to centralize, harden, and scale our observability stack to better support performance testing, regression analysis, and system introspection.

# Plan & Action Items

## Roachprod: Centralized State & Discovery

Problem:
Current roachprod config relies on local caching and CNAME-based discovery, leading to sluggish operations (10s latency), API rate-limiting risks, and inconsistent state.

Solution:
- Build a centralized roachprod state API to consolidate cloud provider syncs and make cluster metadata universally accessible.
- Deprecate CNAME-based discovery in favor of centralized cluster discovery.

Benefits:
- Operation latency drops from ~10s to ms.
- Fewer rate limits from cloud providers.
- Reliable and scalable foundation for future features like Prometheus target registration.

## Metrics: High Availability & Scalability

Problem:
A single Prometheus instance is a SPOF and doesn't scale well with high-cardinality metrics. While migrating to DD would unify the o11y stack with other parts of the organization and would solve the reliability issues, the sheer number of custom metrics scraped during roachtests makes it too expensive.

Solution:
- Migrate to a high-availability, scalable metrics backend (e.g., VictoriaMetrics).
- Deprecate prom-helper-service, replacing it with centralized roachprod discovery for target scraping and load balancing.
- Push a subset of metrics in case of failed roachtests to DD to allow roachtest failures investigations from DD.
- Eliminate local Prometheus nodes on clusters to improve reliability as scraping metrics from the local instance takes up resources.

Bonus Enhancements:
- Longer retention for metrics data.
- Export roachtest metrics and integrate ebpf_exporter for low-level probes.
- Maintain compatibility with Datadog but reduce high-cost ingestion by self-hosting high-cardinality metrics.

## Logs & Artifacts: Long-Term Retention and Search

Problem:
Logs and test artifacts have limited retention (~15 days) and no long-term searchability.

Solution:
- Build a scalable log storage backend that supports developer-friendly CLI interaction, allowing tools like grep, awk, or fzf to be used directly against historical logs (e.g., VictoriaLogs).
- Export raw text and structured logs to a searchable logging backend with 3–6 months of retention.
- Persist .zip test artifacts to long-term object storage (e.g., S3/GCS) for late investigations, auditability, regression tracing.

# Outcome

This initiative will significantly improve:
- Infra stability and operability under load.
- Developer experience for creating, destroying, and debugging test clusters.
- Observability of regressions and performance anomalies across historical windows.
- Flexibility to evolve our tooling (e.g., cloud-hosted roachprod, customer environments).

Jira issue: CRDB-49037