Performance — baseline, comparison & Redshift sizing

AI-facing knowledge: how to extract a representative Teradata workload, compare it against Redshift after migration, and size the target cluster from the source profile. The AI generates the extraction/replay runner; this doc is the knowledge.

Purpose

Establish a Teradata performance baseline (workload + resource profile) → output/performance/result/perf_baseline.json.
Compare the same queries on Redshift after migration → output/performance/result/perf_compare.json.
Feed Redshift sizing from the source inventory.json (see Sizing below).

Inputs come from references/teradata/discovery-queries.md (inventory.json) plus the two workload sources here. Both are read-only.

Source of truth: the DBQL/ResUsage extraction queries in this doc ARE the source of truth. The skill ships no executable collector — the AI generates the runner at run time (BTEQ driver template in references/teradata/discovery-queries.md → Execution modes).

Workload extraction (DBQL)

DBC.DBQLogTbl holds query history. It is often disabled — probe first (SELECT COUNT(*) FROM DBC.DBQLogTbl); 0 rows → mark query_log unavailable and skip the rich stats (the baseline still produces from AMPUsage + ResUsage).

-- Overall stats (one row; empty when logging off).
SELECT TRIM(CAST(COUNT(*) AS BIGINT)) || '|' ||
       TRIM(CAST(COUNT(DISTINCT UserName) AS INTEGER)) || '|' ||
       TRIM(CAST(CAST(AVG(AMPCPUTime) AS DECIMAL(18,4)) AS VARCHAR(40))) || '|' ||
       TRIM(CAST(CAST(MAX(AMPCPUTime) AS DECIMAL(18,4)) AS VARCHAR(40))) || '|' ||
       TRIM(CAST(SUM(TotalIOCount) AS BIGINT)) AS rec
FROM DBC.DBQLogTbl
HAVING COUNT(*) > 0;

-- Mix by StatementType (ETL vs BI → WLM queues).
SELECT TRIM(StatementType) || '|' || TRIM(CAST(COUNT(*) AS BIGINT)) AS rec
FROM DBC.DBQLogTbl GROUP BY StatementType ORDER BY COUNT(*) DESC;

-- Top query-issuing users.
SELECT TRIM(UserName) || '|' || TRIM(CAST(COUNT(*) AS BIGINT)) || '|' ||
       TRIM(CAST(CAST(SUM(AMPCPUTime) AS DECIMAL(18,4)) AS VARCHAR(40))) AS rec
FROM DBC.DBQLogTbl GROUP BY UserName ORDER BY COUNT(*) DESC;

-- Concurrency/peak proxy: query count by hour-of-day.
SELECT TRIM(CAST(EXTRACT(HOUR FROM StartTime) AS INTEGER)) || '|' ||
       TRIM(CAST(COUNT(*) AS BIGINT)) AS rec
FROM DBC.DBQLogTbl
GROUP BY EXTRACT(HOUR FROM StartTime)
ORDER BY EXTRACT(HOUR FROM StartTime);

peak_hour = the hour bucket with the max count; it sizes Concurrency Scaling / WLM slots.

Resource extraction (ResUsage)

DBC.ResUsageSpma is the per-node system resource time series — node-level CPU/memory. It is enabled at node level (ctl/dbscontrol), not via SQL.

The shared test cluster now has ResUsageSpma logging ON at a 60s interval, so this query was validated against real samples (see the validated note below).

-- System resource summary (one row; empty when logging off).
--   cpu_busy% = (CPUUExec + CPUUServ) / (CPUUExec + CPUUServ + CPUIdle) * 100   [VALIDATED]
-- NOTE: ResUsageSpma.MemSize is NOT physical node RAM (see validated note below) — do not
-- derive total RAM from it. Source RAM for sizing comes from node spec / operator.
SELECT TRIM(CAST(COUNT(*) AS BIGINT)) || '|' ||
       TRIM(CAST(COUNT(DISTINCT NodeID) AS INTEGER)) || '|' ||
       TRIM(CAST(CAST(AVG((CPUUExec + CPUUServ) * 100.0
            / NULLIFZERO(CPUUExec + CPUUServ + CPUIdle)) AS DECIMAL(6,2)) AS VARCHAR(20))) || '|' ||
       TRIM(CAST(CAST(MAX((CPUUExec + CPUUServ) * 100.0
            / NULLIFZERO(CPUUExec + CPUUServ + CPUIdle)) AS DECIMAL(6,2)) AS VARCHAR(20))) AS rec
FROM DBC.ResUsageSpma
HAVING COUNT(*) > 0;

Gotcha: EXTRACT(HOUR FROM ResUsageSpma.TheTimestamp) fails (Failure 5326) — a per-hour ResUsage breakdown needs a different time column/derivation than the DBQL StartTime form above. Keep ResUsage to the summary until the time grain is validated.

Validated live (2026-06-24, 12,272 samples / 2 nodes): the cpu-busy formula is correct; the dev cluster is near-idle (avg 0.05%, max 2.13%). MemSize is not physical RAM (~126,844 per sample — not bytes), so the RAM-from-MemSize derivation was dropped. Source RAM for memory-bound sizing must come from node spec / operator — see references/teradata/sizing.md.

Baseline → compare

Representative-query selection — take the top queries by frequency × cost from DBQL (by_user / statement mix), capture each query's TD runtime and AMPCPUTime/IO.
Replay — run the converted queries (converted per references/teradata/conversion-rules.md) on Redshift; capture runtime/cost from SVL_QUERY_REPORT / STL_QUERY.
Compare — per-query and aggregate deltas; flag regressions.

Fairness notes: control for warm vs cold cache (run twice, report warm), match concurrency (the dev cluster has a system concurrency limit of 2), and document WLM/queue settings on both sides. Compare like-for-like result sets.

`perf_baseline.json` (Teradata)

{
  "source": "teradata",
  "window": { "from": null, "to": null, "note": "DBQL retention window" },
  "query_stats": {
    "total_queries": 0, "distinct_users": 0,
    "avg_amp_cpu": 0.0, "max_amp_cpu": 0.0, "total_io": 0,
    "by_statement": [{ "statement_type": "Select", "count": 0 }],
    "by_hour": [{ "hour": 0, "count": 0 }],
    "top_users": [{ "user": "tester", "count": 0, "cpu": 0.0 }],
    "peak_hour": null, "peak_hour_count": 0
  },
  "resource_stats": {
    "sample_count": 0, "node_count": 2,
    "avg_cpu_busy_pct": null, "max_cpu_busy_pct": null
  },
  "representative_queries": [
    { "id": "q1", "sql": "...", "td_runtime_s": 0.0, "amp_cpu": 0.0, "io": 0 }
  ]
}

`perf_compare.json` (Redshift vs Teradata)

{
  "pairs": [
    { "id": "q1", "td_runtime_s": 0.0, "rs_runtime_s": 0.0, "delta_pct": 0.0, "status": "ok|regression" }
  ],
  "aggregate": { "queries": 0, "median_delta_pct": 0.0, "regressions": 0 },
  "notes": "warm-cache, concurrency-matched"
}

query_stats / resource_stats reuse the inventory.json workload shape (references/teradata/discovery-queries.md) — populated only when DBQL / ResUsage are available.

Redshift sizing

Sizing the Redshift target from this profile (RG node type + count, CPU- vs memory-bound decision, concurrency/WLM) is covered in references/teradata/sizing.md. Performance feeds it the workload signals: resource_stats.avg_cpu_busy_pct (bottleneck) and query_stats (peak-hour concurrency + statement mix for WLM).

Source endpoints come from migration-config.yaml — operator-provided, never hard-coded in the skill.

/migrating-to-amazon-redshift

referencesteradataperformance.md