Files
mtusi2026lab3-fork/scripts/diagnostics/sql-snapshot.py
qrtx c1fec73ccc perf(bench): add scaling experiments and saturation diagnostics
Compare API and PostgreSQL CPU budgets and connection pool limits. Collect repeatable measurements and generate report aggregates and graphs.
2026-10-29 11:49:51 +03:00

40 lines
1.7 KiB
Python

#!/usr/bin/env python3
"""One read-only snapshot of SQL classes, never SQL text or parameters."""
import argparse
import json
from pathlib import Path
import subprocess
SQL = """
SELECT jsonb_build_object('time_ms',(extract(epoch FROM clock_timestamp())*1000)::bigint,
'activity', (SELECT coalesce(jsonb_agg(a),'[]'::jsonb) FROM (
SELECT state,wait_event_type,wait_event,
CASE
WHEN query LIKE '%INSERT INTO "public"."chat_message_epochs"%' THEN 'message_and_epoch_write'
WHEN query LIKE '%message_idempotency%' THEN 'idempotency'
WHEN lower(ltrim(query)) LIKE 'commit%' THEN 'commit'
WHEN query LIKE '%token_hash%' THEN 'authentication'
WHEN query LIKE '%chat_message_epochs%' THEN 'epoch_read'
WHEN query LIKE '%messages%' THEN 'message_read'
ELSE 'other'
END AS operation,
count(*) AS count,
max(extract(epoch FROM clock_timestamp()-query_start)) AS max_query_seconds,
max(cardinality(pg_blocking_pids(pid))) AS max_blockers
FROM pg_stat_activity
WHERE datname=current_database() AND backend_type='client backend'
AND application_name <> 'scaling-observer' AND state='active'
GROUP BY state,wait_event_type,wait_event,operation
) a));
"""
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument("--output", type=Path, required=True)
args = parser.parse_args()
result = subprocess.run(["docker", "compose", "exec", "-T", "-e", "PGAPPNAME=scaling-observer",
"postgres", "psql", "-X", "-qAt", "-U", "app", "-d", "chat", "-c", SQL],
capture_output=True, text=True, encoding="utf-8", timeout=30, check=True)
data = json.loads(result.stdout)
args.output.write_text(json.dumps(data, indent=2) + "\n", encoding="utf-8")
print(json.dumps(data))