#!/usr/bin/env python3 """Write measurements.txt from results/c-*.txt (the output of counts.sh). mk_measurements.py Medians of the 3 runs per cell; every number comes from the result files.""" import glob, os, re, statistics as st, sys, textwrap H = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..') R = os.path.join(H, 'results') FILES = sorted(glob.glob(os.path.join(R, 'c-*.txt'))) TEXT = ''.join(open(f).read() for f in FILES) OVERLAP = [] NAMES = {'p': 'patched', 'n': 'probes', 'a': '0001 only'} def heads(): seen = {} for f in FILES: for l in open(f).read().splitlines()[1:5]: m = re.match(r'\s+(\w): (.*)', l) if m: seen.setdefault(m.group(1), m.group(2)) return [f' {k}: {seen[k]}' for k in ('m', 'p', 'a', 'n') if k in seen] def cells(kre, vre): out = {} for line in TEXT.splitlines(): k, v = re.search(kre, line), re.search(vre, line) if k and v: out.setdefault(k.group(2), {}).setdefault(k.group(1), []).append(int(v.group(1))) return out def mm(x): return f'{x / 1e6:.2f}M' def rr(x): return f'{x / 1e6:.1f}' if x >= 1e9 else f'{x / 1e6:.2f}' def block(title, desc, kre, vre, label, pb='p'): pname = NAMES[pb] o = ([title, '-' * len(title)] if title else []) + desc o += ['', f'{label:16} {"master":>10} {pname:>10} {"change":>7} runs: master / {pname}'] for cell, d in cells(kre, vre).items(): m, p = d['m'], d[pb] assert len(m) == 3 and len(p) == 3, (title, cell, pb) a, b = st.median(m), st.median(p) if max(min(m), min(p)) <= min(max(m), max(p)): OVERLAP.append(f'{label} {cell}' + ('' if pb == 'p' else f' ({pname})')) o.append(f'{cell:16} {mm(a):>10} {mm(b):>10} {100 * (b - a) / a:+6.1f}% ' f'{rr(min(m))}-{rr(max(m))} / {rr(min(p))}-{rr(max(p))}') return o + [''] OC = (r'^oncommit (\w) (N=\d+ K=\d+) run=\d', r'per commit=(\d+)') SP = (r'^spread (\w) (K=\d+) run=\d', r'lookup per TRUNCATE=(\d+)') L = ['heap_truncate_find_FKs() with confrelid = ANY: measurements', '=' * 59, '', 'CPU instructions in user space (perf stat) of a single-user backend,', 'fsync off; 3 runs per build in rotated order; median, and the min-max', 'of the runs (in M) at the right. Builds: gcc -O2 -g, no cassert.'] + heads() + [ 'm = master; p = m + v1-0001..0003 ("patched"); a = m + v1-0001 only', '("0001 only", the CASCADE change); n = m + v1-0001 + one confrelid', 'index probe per relation in heap_truncate_find_FKs() instead of', '0002+0003 ("probes"; the alternative that needs no genam.c change,', 'not proposed, measured for comparison).', 'Every figure is net of a control: sections 1-2 subtract a run with', 'fewer repetitions or with FK-less tables (below); sections 3-4', 'subtract a session that runs SELECT 1 the same number of times', '(backend start-up, stats flush and counter queries).', 'Scripts: build.sh.txt, counts.sh.txt (driver), fk-lookup-counts.sh.txt', '(setups and measuring stages), mk_measurements.py.txt (this file);', 'drop the .txt suffix to run them.', '', ''] L += block('1. Commit with ON COMMIT DELETE ROWS tables', [ 'N temporary tables, each with a trigger (without one, the FK lookup', 'does not run at all); every commit truncates them. Per commit:', '(100 commits - 10 commits) / 90. K = 0 is a fresh database (194', 'pg_constraint rows); with K, the catalog of section 2 (K + 2 FKs).'], *OC, 'cell') L += block('', [], *OC, 'cell', 'n') + [''] L += block('2. TRUNCATE in a catalog with many other FKs', [ 'old_pk/old_fk, then K FKs between other tables, then new_pk/new_fk;', '100 x TRUNCATE old_pk, old_fk, new_pk, new_fk, minus 100 x TRUNCATE', 'of four plain tables (no index, no trigger), per TRUNCATE. So it is', 'the FK lookup plus truncating two PK indexes; the lookup is what', 'changes.'], *SP, 'cell') L += block('', [], *SP, 'cell', 'n') + [''] L += block('3. TRUNCATE ... CASCADE', [ 'Fresh database, per TRUNCATE. chain: c1 <- c2 <- ... <- cN, TRUNCATE', 'c1 CASCADE. wide: N tables reference w0, TRUNCATE w0 CASCADE.', 'pp: partitioned table with 100 partitions, referenced by 10 tables;', 'TRUNCATE pp CASCADE, and TRUNCATE pp_1 CASCADE (one partition).'], r'^cascade (\w) (N=\d+) run=\d', r'net per TRUNCATE=(\d+)', 'chain') L += block('', [], r'^cascade (\w) (N=\d+) run=\d', r'net per TRUNCATE=(\d+)', 'chain', 'a') for lab, kre, vre in (('wide', r'^wide (\w) (N=\d+) run=\d', r'net per TRUNCATE=(\d+)'), ('pp', r'^part (\w) (P=\d+ Q=\d+) run=\d', r'net per TRUNCATE pp=(\d+)'), ('pp_1', r'^part (\w) (P=\d+ Q=\d+) run=\d', r' pp_1=(\d+)')): L += block('', [], kre, vre, lab) + block('', [], kre, vre, lab, 'a') L += [''] L += block('4. Other shapes, fresh database, per statement', [ 'list: TRUNCATE of N tables with a trigger in one statement.', 'err: TRUNCATE of 100 tables that fails on the last one (error path).'], r'^list (\w) (N=\d+) run=\d', r'net per TRUNCATE=(\d+)', 'list') L += block('', [], r'^list (\w) (N=\d+) run=\d', r'net per TRUNCATE=(\d+)', 'list', 'n') L += block('', [], r'^errpath (\w) (N=\d+) run=\d', r'net per TRUNCATE=(\d+)', 'err') L += block('', [], r'^errpath (\w) (N=\d+) run=\d', r'net per TRUNCATE=(\d+)', 'err', 'n') + [''] L += ['5. Run-to-run variation', '-' * 23, 'Cells whose min-max ranges overlap with master, read as no change:', *textwrap.wrap((', '.join(OVERLAP) if OVERLAP else 'none') + '.', 72), 'In every other cell the ranges do not overlap.'] out = '\n'.join(L).rstrip() + '\n' while '\n\n\n' in out: out = out.replace('\n\n\n', '\n\n') out = re.sub(r'\n\n(\d\. )', r'\n\n\n\1', out) open(sys.argv[1], 'w').write(out)