forked from mtusi2026/mtusi2026lab2
345 lines
14 KiB
Python
345 lines
14 KiB
Python
#!/usr/bin/env python3
|
|
"""Reproducible 100k-message plan/index/write-cost and backup-restore experiment."""
|
|
import json
|
|
import os
|
|
import platform
|
|
import secrets
|
|
import statistics
|
|
import subprocess
|
|
import tempfile
|
|
import time
|
|
from datetime import datetime, timezone
|
|
from pathlib import Path
|
|
|
|
ROOT = Path(__file__).resolve().parents[1]
|
|
EVIDENCE = ROOT / "evidence" / "track5_query_restore.json"
|
|
MARKDOWN = ROOT / "evidence" / "track5_query_restore.md"
|
|
COMPOSE = ["docker", "compose", "--env-file", ".env"]
|
|
|
|
|
|
def run_container(*args, input_text=None, stdin=None, stdout=None, check=True):
|
|
result = subprocess.run(
|
|
[*COMPOSE, "exec", "-T", "postgres", *args],
|
|
cwd=ROOT,
|
|
input=input_text,
|
|
stdin=stdin,
|
|
stdout=stdout if stdout is not None else subprocess.PIPE,
|
|
stderr=subprocess.PIPE,
|
|
text=input_text is not None,
|
|
check=False,
|
|
)
|
|
if check and result.returncode:
|
|
detail = result.stderr.decode("utf-8", "replace") if isinstance(result.stderr, bytes) else result.stderr
|
|
raise RuntimeError("PostgreSQL benchmark command failed: " + detail[-1000:].strip())
|
|
return result
|
|
|
|
|
|
def psql(database, sql):
|
|
result = run_container(
|
|
"sh",
|
|
"-c",
|
|
'psql -X -qAt -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" -d "$1"',
|
|
"track5",
|
|
database,
|
|
input_text=sql,
|
|
)
|
|
return result.stdout.strip()
|
|
|
|
|
|
def stdout_text(result):
|
|
if isinstance(result.stdout, bytes):
|
|
return result.stdout.decode("utf-8", "replace").strip()
|
|
return result.stdout.strip()
|
|
|
|
|
|
def admin(command, database):
|
|
return run_container(
|
|
"sh",
|
|
"-c",
|
|
f'{command} -U "$POSTGRES_USER" "$1"',
|
|
"track5",
|
|
database,
|
|
check=command != "dropdb",
|
|
)
|
|
|
|
|
|
def plan(database, table, query):
|
|
raw = psql(database, f"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) {query};")
|
|
document = json.loads(raw)[0]
|
|
root = document["Plan"]
|
|
return {
|
|
"execution_ms": document["Execution Time"],
|
|
"planning_ms": document["Planning Time"],
|
|
"actual_rows": root.get("Actual Rows"),
|
|
"shared_hit_blocks": root.get("Shared Hit Blocks", 0),
|
|
"shared_read_blocks": root.get("Shared Read Blocks", 0),
|
|
"plan": root,
|
|
}
|
|
|
|
|
|
def container_metadata():
|
|
container_id = subprocess.run(
|
|
[*COMPOSE, "ps", "-q", "postgres"],
|
|
cwd=ROOT,
|
|
check=True,
|
|
stdout=subprocess.PIPE,
|
|
text=True,
|
|
).stdout.strip()
|
|
inspected = subprocess.run(
|
|
["docker", "inspect", "--format", "{{.HostConfig.Memory}} {{.HostConfig.NanoCpus}}", container_id],
|
|
check=True,
|
|
stdout=subprocess.PIPE,
|
|
text=True,
|
|
).stdout.strip()
|
|
memory, nano_cpus = (int(value) for value in inspected.split())
|
|
try:
|
|
host_memory = int(
|
|
subprocess.run(
|
|
["sysctl", "-n", "hw.memsize"],
|
|
check=True,
|
|
stdout=subprocess.PIPE,
|
|
text=True,
|
|
).stdout.strip()
|
|
)
|
|
except (OSError, subprocess.CalledProcessError, ValueError):
|
|
host_memory = os.sysconf("SC_PAGE_SIZE") * os.sysconf("SC_PHYS_PAGES")
|
|
pg_version = stdout_text(run_container(
|
|
"sh",
|
|
"-c",
|
|
'psql -X -qAt -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "SELECT current_setting(\'server_version\')"',
|
|
"track5",
|
|
))
|
|
return {
|
|
"host_platform": platform.platform(),
|
|
"host_architecture": platform.machine(),
|
|
"host_logical_cpus": os.cpu_count(),
|
|
"host_memory_bytes": host_memory,
|
|
"postgres_image": "postgres:16.4-alpine",
|
|
"postgres_version": pg_version,
|
|
"postgres_container_memory_limit_bytes": memory,
|
|
"postgres_container_cpu_limit_nanos": nano_cpus,
|
|
"warmup": "initial data load followed by ANALYZE; plan query is executed 5 times",
|
|
}
|
|
|
|
|
|
def dataset_sql():
|
|
return """
|
|
CREATE TABLE messages_chat_only (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
chat_id integer NOT NULL,
|
|
sender_id integer NOT NULL,
|
|
text varchar(200) NOT NULL,
|
|
created_at timestamptz NOT NULL
|
|
);
|
|
CREATE TABLE messages_composite (LIKE messages_chat_only INCLUDING ALL);
|
|
INSERT INTO messages_chat_only (chat_id, sender_id, text, created_at)
|
|
SELECT CASE WHEN n <= 80000 THEN 1 ELSE 2 + ((n - 80001) % 100) END,
|
|
1 + (n % 3),
|
|
'synthetic-message-' || lpad(n::text, 6, '0'),
|
|
timestamptz '2025-01-01 00:00:00+00' + n * interval '1 second'
|
|
FROM generate_series(1, 100000) AS n;
|
|
INSERT INTO messages_composite (chat_id, sender_id, text, created_at)
|
|
SELECT chat_id, sender_id, text, created_at FROM messages_chat_only ORDER BY id;
|
|
CREATE INDEX messages_chat_only_idx ON messages_chat_only (chat_id);
|
|
CREATE INDEX messages_chat_history_idx ON messages_composite (chat_id, created_at, id);
|
|
ANALYZE messages_chat_only;
|
|
ANALYZE messages_composite;
|
|
"""
|
|
|
|
|
|
def history_query(table):
|
|
return (
|
|
f"SELECT id, created_at, text FROM {table} "
|
|
"WHERE chat_id = 2 "
|
|
"AND (created_at, id) > (timestamptz '2025-01-01 23:36:40+00', 0) "
|
|
"ORDER BY created_at, id LIMIT 25"
|
|
)
|
|
|
|
|
|
def write_samples(database, table):
|
|
sql = (
|
|
"BEGIN; "
|
|
"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) "
|
|
f"INSERT INTO {table} (chat_id, sender_id, text, created_at) "
|
|
"SELECT 2 + (n % 100), 1, 'synthetic-write-' || n::text, "
|
|
"timestamptz '2025-01-01 00:00:00+00' + n * interval '1 second' "
|
|
"FROM generate_series(100001, 102000) AS n; "
|
|
"ROLLBACK;"
|
|
)
|
|
samples = []
|
|
for _ in range(5):
|
|
raw = psql(database, sql)
|
|
document = json.loads(raw)[0]
|
|
samples.append(
|
|
{
|
|
"execution_ms": document["Execution Time"],
|
|
"planning_ms": document["Planning Time"],
|
|
"shared_write_blocks": document["Plan"].get("Shared Written Blocks", 0),
|
|
}
|
|
)
|
|
return {
|
|
"samples": samples,
|
|
"median_execution_ms": statistics.median(item["execution_ms"] for item in samples),
|
|
"median_shared_write_blocks": statistics.median(item["shared_write_blocks"] for item in samples),
|
|
"rows_per_sample": 2000,
|
|
"rolled_back_after_each_measurement": True,
|
|
}
|
|
|
|
|
|
def render_markdown(result):
|
|
small = result["query_plans"]
|
|
writes = result["write_cost"]
|
|
restore = result["backup_restore"]
|
|
return f"""# Lab2 level-5 track 2 — query plans and recovery
|
|
|
|
- Run UTC: {result['run_utc']}
|
|
- Host: {result['environment']['host_platform']} ({result['environment']['host_architecture']}), logical CPUs: {result['environment']['host_logical_cpus']}
|
|
- Host physical memory: {result['environment']['host_memory_bytes']} bytes.
|
|
- PostgreSQL: {result['environment']['postgres_version']} in `{result['environment']['postgres_image']}`; container memory limit: {result['environment']['postgres_container_memory_limit_bytes']} bytes; CPU limit: {result['environment']['postgres_container_cpu_limit_nanos']} nanocores (0 means unlimited).
|
|
- Dataset: 100,000 synthetic rows in each comparison table; one chat has 80,000 rows and 100 small chats share the remaining 20,000. Only generated text is used.
|
|
- Query: keyset page from chat 2, identical SELECT and cursor for both indexes; each plan was executed five times after ANALYZE.
|
|
|
|
| Index | Median execution (ms) | Shared hits | Shared reads | Rows |
|
|
|---|---:|---:|---:|---:|
|
|
| `(chat_id)` | {statistics.median(item['execution_ms'] for item in small['chat_id_only']['samples']):.3f} | {small['chat_id_only']['samples'][0]['shared_hit_blocks']} | {small['chat_id_only']['samples'][0]['shared_read_blocks']} | {small['chat_id_only']['samples'][0]['actual_rows']} |
|
|
| `(chat_id, created_at, id)` | {statistics.median(item['execution_ms'] for item in small['composite_history']['samples']):.3f} | {small['composite_history']['samples'][0]['shared_hit_blocks']} | {small['composite_history']['samples'][0]['shared_read_blocks']} | {small['composite_history']['samples'][0]['actual_rows']} |
|
|
|
|
| Index | Median time for 2,000 inserted rows (ms) | Median shared write blocks |
|
|
|---|---:|---:|
|
|
| `(chat_id)` | {writes['chat_id_only']['median_execution_ms']:.3f} | {writes['chat_id_only']['median_shared_write_blocks']} |
|
|
| `(chat_id, created_at, id)` | {writes['composite_history']['median_execution_ms']:.3f} | {writes['composite_history']['median_shared_write_blocks']} |
|
|
|
|
Each write-cost query was measured five times inside a transaction and rolled back. This isolates execution and index-maintenance work but does not measure durable commit/fsync latency.
|
|
|
|
Backup/restore: custom-format dump {restore['dump_bytes']} bytes; dump {restore['dump_seconds']:.3f}s; restore into a separately created empty database {restore['restore_seconds']:.3f}s. Restored row counts and deterministic checksums for the selected messages were verified: **PASS**.
|
|
|
|
Raw JSON plans, samples, environment and restore checksums are in `track5_query_restore.json`. This single-host synthetic run is a reproducible comparison, not a production capacity or HA claim.
|
|
"""
|
|
|
|
|
|
def main():
|
|
tag = secrets.token_hex(5)
|
|
source = "lab2_track5_source_" + tag
|
|
target = "lab2_track5_restore_" + tag
|
|
created = []
|
|
try:
|
|
for database in (source, target):
|
|
admin("createdb", database)
|
|
created.append(database)
|
|
psql(source, dataset_sql())
|
|
select_chat = "SELECT id, chat_id, sender_id, text, created_at FROM {table} WHERE chat_id IN (1, 2) ORDER BY chat_id, created_at, id"
|
|
plans = {
|
|
"chat_id_only": [
|
|
plan(source, "messages_chat_only", history_query("messages_chat_only"))
|
|
for _ in range(5)
|
|
],
|
|
"composite_history": [
|
|
plan(source, "messages_composite", history_query("messages_composite"))
|
|
for _ in range(5)
|
|
],
|
|
}
|
|
writes = {
|
|
"chat_id_only": write_samples(source, "messages_chat_only"),
|
|
"composite_history": write_samples(source, "messages_composite"),
|
|
}
|
|
selected_hash = {}
|
|
for table in ("messages_chat_only", "messages_composite"):
|
|
selected_hash[table] = psql(
|
|
source,
|
|
"SELECT md5(string_agg(md5(row_to_json(r)::text), '' ORDER BY r.chat_id, r.created_at, r.id)) "
|
|
f"FROM ({select_chat.format(table=table)}) AS r;",
|
|
)
|
|
|
|
with tempfile.NamedTemporaryFile(prefix="lab2-track5-", suffix=".dump") as dump_file:
|
|
started = time.monotonic()
|
|
dump = run_container(
|
|
"sh",
|
|
"-c",
|
|
'pg_dump -U "$POSTGRES_USER" -Fc "$1"',
|
|
"track5",
|
|
source,
|
|
stdout=dump_file,
|
|
)
|
|
if dump.returncode:
|
|
raise RuntimeError("PostgreSQL benchmark backup failed")
|
|
dump_seconds = time.monotonic() - started
|
|
dump_file.flush()
|
|
dump_size = os.fstat(dump_file.fileno()).st_size
|
|
started = time.monotonic()
|
|
dump_file.seek(0)
|
|
restore = run_container(
|
|
"sh",
|
|
"-c",
|
|
'pg_restore -U "$POSTGRES_USER" --exit-on-error -d "$1"',
|
|
"track5",
|
|
target,
|
|
stdin=dump_file,
|
|
stdout=subprocess.DEVNULL,
|
|
)
|
|
if restore.returncode:
|
|
raise RuntimeError("PostgreSQL benchmark restore failed")
|
|
restore_seconds = time.monotonic() - started
|
|
|
|
restored_counts = {
|
|
table: int(psql(target, f"SELECT count(*) FROM {table};"))
|
|
for table in ("messages_chat_only", "messages_composite")
|
|
}
|
|
restored_hashes = {
|
|
table: psql(
|
|
target,
|
|
"SELECT md5(string_agg(md5(row_to_json(r)::text), '' ORDER BY r.chat_id, r.created_at, r.id)) "
|
|
f"FROM ({select_chat.format(table=table)}) AS r;",
|
|
)
|
|
for table in ("messages_chat_only", "messages_composite")
|
|
}
|
|
if any(count != 100000 for count in restored_counts.values()):
|
|
raise RuntimeError("Restored message counts do not match the 100,000-row source tables")
|
|
if restored_hashes != selected_hash:
|
|
raise RuntimeError("Restored selected-message checksums differ from the source")
|
|
|
|
env = container_metadata()
|
|
env["postgres_memory_setting"] = psql(source, "SHOW shared_buffers;")
|
|
env["postgres_work_mem"] = psql(source, "SHOW work_mem;")
|
|
result = {
|
|
"run_utc": datetime.now(timezone.utc).isoformat(),
|
|
"environment": env,
|
|
"method": {
|
|
"rows_per_index_variant": 100000,
|
|
"distribution": "80,000 messages in one chat; 20,000 distributed evenly across 100 chats",
|
|
"warmup": "ANALYZE followed by five executed EXPLAIN ANALYZE samples per query",
|
|
"query": history_query("messages"),
|
|
},
|
|
"query_plans": {
|
|
"chat_id_only": {"index": "(chat_id)", "samples": plans["chat_id_only"]},
|
|
"composite_history": {
|
|
"index": "(chat_id, created_at, id)",
|
|
"samples": plans["composite_history"],
|
|
},
|
|
},
|
|
"write_cost": writes,
|
|
"backup_restore": {
|
|
"format": "pg_dump custom format",
|
|
"dump_bytes": dump_size,
|
|
"dump_seconds": dump_seconds,
|
|
"restore_seconds": restore_seconds,
|
|
"restored_counts": restored_counts,
|
|
"source_selected_checksums": selected_hash,
|
|
"restored_selected_checksums": restored_hashes,
|
|
"verification": "PASS",
|
|
},
|
|
}
|
|
EVIDENCE.write_text(json.dumps(result, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
|
|
MARKDOWN.write_text(render_markdown(result), encoding="utf-8")
|
|
print("PASS: level-5 track 2; evidence written to evidence/track5_query_restore.md")
|
|
finally:
|
|
for database in reversed(created):
|
|
admin("dropdb", database)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
try:
|
|
main()
|
|
except (OSError, subprocess.SubprocessError, ValueError, KeyError, RuntimeError) as exc:
|
|
print("FAIL:", str(exc))
|
|
raise SystemExit(1)
|