forked from mtusi2026/mtusi2026lab3
Compare API and PostgreSQL CPU budgets and connection pool limits. Collect repeatable measurements and generate report aggregates and graphs.
40 lines
1.7 KiB
Python
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))
|