Files
mtusi2026lab2/scripts/track2/run_track2.py
2026-10-08 14:14:20 +03:00

285 lines
14 KiB
Python

#!/usr/bin/env python3
import argparse
import datetime as dt
import json
import statistics
import subprocess
import sys
import time
from pathlib import Path
ROOT = Path(__file__).resolve().parents[2]
OUT = ROOT / 'evidence' / 'track2'
PROJECT = 'mtusi-lab2'
SRC, DST = 'pg-research', 'pg-restore'
BIG = "md5('chat1')::uuid"
MEDIUM = "md5('chat5')::uuid"
SMALL = "md5('chat500')::uuid"
COLS = 'id, chat_id, sender_id, text, created_at'
INDEX_CONFIGS = {
'pk_only': [],
'chat_id': ['CREATE INDEX t2_ix ON messages (chat_id)'],
'created_at_id': ['CREATE INDEX t2_ix ON messages (created_at, id)'],
'chat_created_id': ['CREATE INDEX messages_chat_history_idx ON messages (chat_id, created_at, id)'],
}
PRODUCTION = 'chat_created_id'
def log(msg):
print('[%s] %s' % (dt.datetime.now().strftime('%H:%M:%S'), msg), flush=True)
def sh(args, input_text=None, check=True):
p = subprocess.run(args, cwd=ROOT, input=input_text, capture_output=True, text=True, encoding='utf-8')
if check and p.returncode != 0:
raise SystemExit('command failed: %s\n%s' % (' '.join(args[:6]), p.stderr[-2000:]))
return p
def compose(*args, **kw):
return sh(['docker', 'compose', '--profile', 'research', *args], **kw)
def psql(service, *sql, input_text=None):
args = ['exec', '-T', service, 'psql', '-U', 'messenger', '-d', 'messenger', '-tAX', '-v', 'ON_ERROR_STOP=1']
for s in sql:
args += ['-c', s]
return compose(*args, input_text=input_text).stdout.strip()
def timed(fn):
t = time.perf_counter()
r = fn()
return r, time.perf_counter() - t
def wait_healthy(service, timeout=90):
deadline = time.monotonic() + timeout
while time.monotonic() < deadline:
p = compose('exec', '-T', service, 'pg_isready', '-U', 'messenger', '-d', 'messenger', '-h', '127.0.0.1', check=False)
if p.returncode == 0:
q = compose('exec', '-T', service, 'psql', '-U', 'messenger', '-d', 'messenger', '-tAc', 'SELECT 1', check=False)
if q.returncode == 0 and q.stdout.strip() == '1':
return
time.sleep(1)
raise SystemExit(service + ' not healthy')
def fresh(service):
compose('rm', '-sf', service)
sh(['docker', 'volume', 'rm', '-f', '%s_%s' % (PROJECT, service.replace('-', ''))], check=False)
compose('up', '-d', service)
wait_healthy(service)
def walk(plan, acc):
name = plan['Node Type']
if plan.get('Index Name'):
name += '(' + plan['Index Name'] + ')'
acc.append(name)
for child in plan.get('Plans', []):
walk(child, acc)
return acc
def explain_many(sql, repeat):
stmt = 'EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + sql
parts = []
for _ in range(repeat):
parts += [stmt, r'\echo @@@']
raw = psql(SRC, *parts)
runs = [json.loads(chunk)[0] for chunk in raw.split('@@@') if chunk.strip()]
measured = runs[1:]
last = measured[-1]
top = last['Plan']
return {
'exec_ms_median': round(statistics.median(r['Execution Time'] for r in measured), 3),
'exec_ms_min': round(min(r['Execution Time'] for r in measured), 3),
'exec_ms_max': round(max(r['Execution Time'] for r in measured), 3),
'planning_ms_median': round(statistics.median(r['Planning Time'] for r in measured), 3),
'rows': top.get('Actual Rows'),
'shared_hit': top.get('Shared Hit Blocks', 0),
'shared_read': top.get('Shared Read Blocks', 0),
'plan': ' -> '.join(walk(top, [])),
'runs': len(measured),
}
def queries(cursor_ts, cursor_id):
return {
'big_first_page': "SELECT %s FROM messages WHERE chat_id = %s ORDER BY created_at, id LIMIT 51" % (COLS, BIG),
'big_deep_keyset': "SELECT %s FROM messages WHERE chat_id = %s AND (created_at, id) > ('%s', '%s') ORDER BY created_at, id LIMIT 51" % (COLS, BIG, cursor_ts, cursor_id),
'big_deep_offset': "SELECT %s FROM messages WHERE chat_id = %s ORDER BY created_at, id OFFSET 55000 LIMIT 51" % (COLS, BIG),
'medium_first_page': "SELECT %s FROM messages WHERE chat_id = %s ORDER BY created_at, id LIMIT 51" % (COLS, MEDIUM),
'small_first_page': "SELECT %s FROM messages WHERE chat_id = %s ORDER BY created_at, id LIMIT 51" % (COLS, SMALL),
}
INSERT_20K = ("INSERT INTO messages (id, chat_id, sender_id, text, created_at) "
"SELECT md5('ins' || j)::uuid, " + BIG + ", md5('user' || ((7 + j % 50) % 2000 + 1))::uuid, "
"md5(j::text), timestamptz '2026-07-01Z' + j * interval '1 ms' FROM generate_series(1, 20000) j")
def apply_config(name):
ddl = ['DROP INDEX IF EXISTS messages_chat_history_idx', 'DROP INDEX IF EXISTS t2_ix'] + INDEX_CONFIGS[name] + ['ANALYZE messages']
psql(SRC, *ddl)
def write_cost(repeat):
parts = []
for _ in range(repeat):
parts += ['BEGIN', 'EXPLAIN (ANALYZE, BUFFERS, WAL, FORMAT JSON) ' + INSERT_20K, 'ROLLBACK', r'\echo @@@']
raw = psql(SRC, *parts)
runs = []
for chunk in raw.split('@@@'):
chunk = chunk.strip()
if not chunk:
continue
body = chunk[chunk.index('['):chunk.rindex(']') + 1]
runs.append(json.loads(body)[0])
measured = runs[1:]
return {
'insert_20k_ms_median': round(statistics.median(r['Execution Time'] for r in measured), 1),
'wal_bytes_median': int(statistics.median(r['Plan'].get('WAL Bytes', 0) for r in measured)),
'runs': len(measured),
}
VERIFY = {
'users': 'SELECT count(*) FROM users',
'chats': 'SELECT count(*) FROM chats',
'chat_members': 'SELECT count(*) FROM chat_members',
'messages': 'SELECT count(*) FROM messages',
'schema_migrations': "SELECT coalesce(string_agg(version::text, ',' ORDER BY version), '') FROM schema_migrations",
'messages_checksum': "SELECT sum(('x' || substr(md5(id::text || chat_id::text || sender_id::text || text || created_at::text), 1, 15))::bit(60)::bigint) FROM messages",
'per_chat_counts_md5': "SELECT md5(string_agg(chat_id::text || ':' || n, ',' ORDER BY chat_id)) FROM (SELECT chat_id, count(*) n FROM messages GROUP BY chat_id) t",
'big_chat_history_md5': "SELECT md5(string_agg(id::text || '|' || text || '|' || created_at::text, E'\\n' ORDER BY created_at, id)) FROM messages WHERE chat_id = " + BIG,
'medium_chat_history_md5': "SELECT md5(string_agg(id::text || '|' || text || '|' || created_at::text, E'\\n' ORDER BY created_at, id)) FROM messages WHERE chat_id = " + MEDIUM,
'small_chat_history_md5': "SELECT md5(string_agg(id::text || '|' || text || '|' || created_at::text, E'\\n' ORDER BY created_at, id)) FROM messages WHERE chat_id = " + SMALL,
'sample_200_md5': "SELECT md5(string_agg(m.id::text || '|' || m.text, ',' ORDER BY m.id)) FROM messages m WHERE m.id IN (SELECT md5('msg1:' || j)::uuid FROM generate_series(300, 60000, 300) j)",
'indexes': "SELECT string_agg(indexname, ',' ORDER BY indexname) FROM pg_indexes WHERE schemaname = 'public'",
}
def verify(service):
return {k: psql(service, q) for k, q in VERIFY.items()}
def main():
p = argparse.ArgumentParser(description='Трек 2: план запроса истории и backup -> restore -> сверка')
p.add_argument('--repeat', type=int, default=7, help='прогонов на замер (первый — прогрев, отбрасывается)')
a = p.parse_args()
if hasattr(sys.stdout, 'reconfigure'):
sys.stdout.reconfigure(encoding='utf-8')
OUT.mkdir(parents=True, exist_ok=True)
result = {'started_at': dt.datetime.now(dt.timezone.utc).isoformat(timespec='seconds')}
info = sh(['docker', 'info', '--format', '{{.NCPU}} {{.MemTotal}} {{.OperatingSystem}}']).stdout.split(maxsplit=2)
result['environment'] = {'docker_cpus': int(info[0]), 'docker_mem_gib': round(int(info[1]) / 2**30, 1), 'docker_os': info[2].strip()}
log('1/6 fresh research database + migrations')
fresh(SRC)
psql(SRC, r'\i /migrations/0001_init.sql',
"CREATE TABLE IF NOT EXISTS schema_migrations (version integer PRIMARY KEY, name text NOT NULL, applied_at timestamptz NOT NULL DEFAULT now())",
"INSERT INTO schema_migrations (version, name) VALUES (1, '0001_init.sql')")
result['environment'].update({
'postgres': psql(SRC, 'SHOW server_version'),
'shared_buffers': psql(SRC, 'SHOW shared_buffers'),
'work_mem': psql(SRC, 'SHOW work_mem'),
})
log('2/6 seeding synthetic data (≈110k messages)')
seed = (ROOT / 'scripts' / 'track2' / 'seed.sql').read_text(encoding='utf-8')
_, seed_s = timed(lambda: psql(SRC, input_text=seed))
dist = psql(SRC, "SELECT json_build_object("
"'messages', (SELECT count(*) FROM messages),"
"'chats', (SELECT count(*) FROM chats),"
"'users', (SELECT count(*) FROM users),"
"'max_chat', (SELECT max(n) FROM (SELECT count(*) n FROM messages GROUP BY chat_id) t),"
"'median_chat', (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY n) FROM (SELECT count(*) n FROM messages GROUP BY chat_id) t),"
"'avg_text_chars', (SELECT round(avg(char_length(text))) FROM messages),"
"'max_text_chars', (SELECT max(char_length(text)) FROM messages),"
"'table_mb', round(pg_total_relation_size('messages') / 1048576.0, 1))")
result['dataset'] = json.loads(dist)
result['dataset']['seed_seconds'] = round(seed_s, 1)
log(' dataset: %s' % result['dataset'])
cur = psql(SRC, "SELECT created_at || '|' || id FROM messages WHERE chat_id = %s ORDER BY created_at, id OFFSET 54999 LIMIT 1" % BIG)
cursor_ts, cursor_id = cur.split('|')
qs = queries(cursor_ts, cursor_id)
log('3/6 index / query comparison')
result['read'] = {}
result['write'] = {}
for name in INDEX_CONFIGS:
apply_config(name)
size = psql(SRC, "SELECT coalesce(sum(pg_relation_size(indexrelid)), 0) FROM pg_index WHERE indrelid = 'messages'::regclass AND NOT indisprimary")
result['read'][name] = {q: explain_many(sql, a.repeat) for q, sql in qs.items()}
result['write'][name] = write_cost(max(4, a.repeat // 2))
result['write'][name]['secondary_index_mb'] = round(int(size) / 1048576, 2)
log(' %-16s deep_keyset=%.3fms offset=%.3fms small=%.3fms insert20k=%.0fms' % (
name, result['read'][name]['big_deep_keyset']['exec_ms_median'],
result['read'][name]['big_deep_offset']['exec_ms_median'],
result['read'][name]['small_first_page']['exec_ms_median'],
result['write'][name]['insert_20k_ms_median']))
apply_config(PRODUCTION)
(OUT / 'explain_production_deep_keyset.txt').write_text(
psql(SRC, 'EXPLAIN (ANALYZE, BUFFERS) ' + qs['big_deep_keyset']) + '\n', encoding='utf-8')
(OUT / 'explain_pk_only_deep_keyset.txt').write_text('see results.json -> read.pk_only\n', encoding='utf-8')
log('4/6 backup (pg_dump -Fc)')
_, dump_s = timed(lambda: compose('exec', '-T', SRC, 'pg_dump', '-U', 'messenger', '-d', 'messenger', '-Fc', '-f', '/tmp/messenger.dump'))
dump_bytes = int(compose('exec', '-T', SRC, 'stat', '-c', '%s', '/tmp/messenger.dump').stdout.strip())
backup_at = psql(SRC, 'SELECT now()')
psql(SRC, "INSERT INTO messages (id, chat_id, sender_id, text) VALUES (md5('after-backup')::uuid, %s, md5('user8')::uuid, 'written after backup')" % BIG)
(ROOT / 'backups').mkdir(exist_ok=True)
_, copy_s = timed(lambda: compose('cp', SRC + ':/tmp/messenger.dump', 'backups/messenger.dump'))
log('5/6 restore into NEW storage (fresh volume)')
fresh(DST)
_, copy2_s = timed(lambda: compose('cp', 'backups/messenger.dump', DST + ':/tmp/messenger.dump'))
_, restore_s = timed(lambda: compose('exec', '-T', DST, 'pg_restore', '-U', 'messenger', '-d', 'messenger',
'--no-owner', '--exit-on-error', '-j', '4', '/tmp/messenger.dump'))
_, analyze_s = timed(lambda: psql(DST, 'ANALYZE'))
result['backup_restore'] = {
'backup_taken_at': backup_at,
'dump_seconds': round(dump_s, 2),
'dump_mb': round(dump_bytes / 1048576, 1),
'copy_out_seconds': round(copy_s, 2),
'copy_in_seconds': round(copy2_s, 2),
'pg_restore_seconds': round(restore_s, 2),
'analyze_seconds': round(analyze_s, 2),
'total_recovery_seconds': round(copy2_s + restore_s + analyze_s, 2),
}
log(' %s' % result['backup_restore'])
log('6/6 verification source vs restored')
dst_v = verify(DST)
after_src = psql(SRC, "SELECT count(*) FROM messages WHERE id = md5('after-backup')::uuid")
after_dst = psql(DST, "SELECT count(*) FROM messages WHERE id = md5('after-backup')::uuid")
psql(SRC, "DELETE FROM messages WHERE id = md5('after-backup')::uuid")
src_v_at_backup = verify(SRC)
checks = {k: {'source': src_v_at_backup[k], 'restored': dst_v[k], 'match': src_v_at_backup[k] == dst_v[k]} for k in VERIFY}
restored_plan = psql(DST, 'EXPLAIN ' + qs['big_deep_keyset']).splitlines()[0:3]
result['verification'] = {
'checks': checks,
'all_match': all(c['match'] for c in checks.values()),
'post_backup_write_in_source': after_src == '1',
'post_backup_write_in_restored': after_dst == '1',
'restored_history_plan': restored_plan,
}
result['finished_at'] = dt.datetime.now(dt.timezone.utc).isoformat(timespec='seconds')
(OUT / 'results.json').write_text(json.dumps(result, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
for k, c in checks.items():
log(' %-24s %s' % (k, 'OK' if c['match'] else 'MISMATCH'))
log('post-backup write: source=%s restored=%s (expected: lost in restore)' % (after_src, after_dst))
log('saved evidence/track2/results.json')
compose('stop', SRC, DST)
sys.exit(0 if result['verification']['all_match'] else 1)
if __name__ == '__main__':
main()