#!/usr/bin/env python3 """Requires production builds (configure without --enable-cassert): instruction counts are not meaningful with assertions. Needs perf (instructions:u) and the builds' bin directories. Example, 6 rounds alternating master (m) and patched (p): python3 fk-names-measure.py.txt addfk 10000 6 m=/path/master/bin p=/path/patched/bin """ """Real-operation measurements for foreign keys that reference a partitioned table with N partitions. Production builds only (no cassert). Builds are given as NAME=BINDIR (e.g. m=/x/i-m/bin p=/x/i-p/bin). Every measurement alternates the builds by round (m p, p m, ...) on reflink clones of one template, so both builds start from byte-identical clusters. The templates are made with the first build given (the patch does not change the catalog format). b2.py addfk N ROUNDS B=BIN ... ALTER TABLE f ADD FOREIGN KEY (a) REFERENCES p: wall clock + instructions:u of the backend b2.py restore N ROWS ROUNDS B=BIN ... pg_dump -Fc of p (N partitions, ROWS rows each) and f (FK to p); restore into an empty cluster section by section: pre-data and data with pg_restore, post-data (the FK) as SQL in a counted session; total = the sum b2.py postdata N ROWS ROUNDS B=BIN .. as restore, but pre-data and data are restored once into a template; each round clones it and runs only post-data (for sizes where pre-data takes minutes) b2.py upgrade N ROUNDS B=BIN ... pg_upgrade of a cluster holding p and f (no data) into a fresh cluster of the same build: wall clock b2.py partof N NEW ROUNDS B=BIN ... with the FK in place, CREATE TABLE ... PARTITION OF p for NEW more partitions: ms + instructions per op Output: one TSV line per measurement on stdout: op build N round metric value B2_ROUND0=R starts at round R+1 (keeps the m/p alternation across split runs). Work dir: $B2_WORK (default ./fk-names-work). """ import os, re, shutil, signal, subprocess, sys, time W = os.environ.get('B2_WORK', os.path.abspath('fk-names-work')) SOCK = os.path.join(W, 'sock') PORT = 55451 def sh(cmd, **kw): r = subprocess.run(cmd, text=True, capture_output=True, **kw) if r.returncode: sys.exit(f'{cmd[0]}: {(r.stdout + r.stderr)[-1500:]}') return r.stdout def builds(args): return [tuple(a.split('=', 1)) for a in args] def order(bs, r): return bs if r % 2 == 0 else list(reversed(bs)) def start(bindir, d, port=PORT): os.makedirs(SOCK, exist_ok=True) sh([f'{bindir}/pg_ctl', '-D', d, '-o', f'-p {port} -k {SOCK} -c listen_addresses=', '-l', d + '.log', '-w', 'start']) def stop(bindir, d): sh([f'{bindir}/pg_ctl', '-D', d, '-m', 'fast', '-w', 'stop']) def psql(bindir, sql, db='postgres', port=PORT): r = subprocess.run([f'{bindir}/psql', '-X', '-q', '-At', '-v', 'ON_ERROR_STOP=1', '-h', SOCK, '-p', str(port), '-d', db], input=sql, text=True, capture_output=True) if r.returncode: sys.exit(f'psql: {r.stderr[:800]}') return r.stdout def initdb(bindir, d): shutil.rmtree(d, ignore_errors=True) sh([f'{bindir}/initdb', '-D', d, '-A', 'trust', '--no-sync']) with open(os.path.join(d, 'postgresql.conf'), 'a') as f: f.write('max_wal_size = 8GB\nmax_locks_per_transaction = 1024\n') def clone(src, tag, bindir): d = os.path.join(W, f'cell-{tag}') if os.path.exists(os.path.join(d, 'postmaster.pid')): subprocess.run([f'{bindir}/pg_ctl', '-D', d, '-m', 'immediate', '-w', 'stop'], capture_output=True) shutil.rmtree(d, ignore_errors=True) sh(['cp', '-a', '--reflink=auto', src, d]) return d def schema_sql(n, rows=0, fk=False): st = ['CREATE TABLE p (id int PRIMARY KEY) PARTITION BY RANGE (id);'] st += [f'CREATE TABLE p{i} PARTITION OF p FOR VALUES FROM ({i * 1000}) TO ({(i + 1) * 1000});' for i in range(n)] st.append('CREATE TABLE f (a int);') if rows: st.append(f'INSERT INTO p SELECT i * 1000 + j FROM generate_series(0, {n - 1}) i, ' f'generate_series(0, {rows - 1}) j;') st.append('INSERT INTO f SELECT id FROM p;') if fk: st.append('ALTER TABLE f ADD FOREIGN KEY (a) REFERENCES p;') return '\n'.join(st) + '\n' def template(name, bindir, sql): d = os.path.join(W, f'tpl-{name}') if os.path.exists(os.path.join(d, 'PG_VERSION')): return d os.makedirs(W, exist_ok=True) initdb(bindir, d) if sql: start(bindir, d) psql(bindir, sql) psql(bindir, 'VACUUM ANALYZE; CHECKPOINT;') stop(bindir, d) return d class Session: """An interactive psql whose backend can be counted with perf stat -p.""" def __init__(self, bindir, db='postgres'): self.p = subprocess.Popen([f'{bindir}/psql', '-X', '-q', '-At', '-v', 'ON_ERROR_STOP=1', '-h', SOCK, '-p', str(PORT), '-d', db], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True, bufsize=1) self.pid = int(self.run('SELECT pg_backend_pid();')[-1]) def run(self, sql): self.p.stdin.write(sql + '\n\\echo __done__\n') self.p.stdin.flush() out = [] for line in self.p.stdout: line = line.rstrip('\n') if line == '__done__': return out if 'ERROR' in line: sys.exit(f'session: {line}') out.append(line) sys.exit('session: psql exited') def close(self): self.p.stdin.close() self.p.wait() def counted(bindir, sql, db='postgres'): """Run sql in a fresh session; return (ms, instructions:u of its backend).""" s = Session(bindir, db) perf = subprocess.Popen(['perf', 'stat', '-x,', '-e', 'instructions:u', '-p', str(s.pid)], stderr=subprocess.PIPE, text=True) time.sleep(0.5) t0 = time.monotonic() s.run(sql) ms = (time.monotonic() - t0) * 1000 perf.send_signal(signal.SIGINT) err = perf.communicate()[1] s.close() ins = [int(l.split(',')[0]) for l in err.splitlines() if 'instructions' in l and l[0].isdigit()] if not ins: sys.exit(f'perf: {err[:400]}') return ms, ins[0] def fk_rows(bindir, db='postgres'): return int(psql(bindir, "SELECT count(*) FROM pg_constraint WHERE contype = 'f';", db).split()[-1]) def addfk(n, rounds, bs): tpl = template(f'addfk-{n}', bs[0][1], schema_sql(n)) r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to split long runs) for r in range(r0, r0 + rounds): for name, bindir in order(bs, r): d = clone(tpl, name, bindir) start(bindir, d) ms, ins = counted(bindir, 'ALTER TABLE f ADD FOREIGN KEY (a) REFERENCES p;') if fk_rows(bindir) != n + 1: sys.exit('addfk: wrong number of FK rows') print(f'addfk\t{name}\t{n}\t{r + 1}\tms\t{ms:.1f}', flush=True) print(f'addfk\t{name}\t{n}\t{r + 1}\tinstructions_M\t{ins / 1e6:.1f}', flush=True) stop(bindir, d) def restore(n, rows, rounds, bs): src = template(f'dumpsrc-{n}-{rows}', bs[0][1], schema_sql(n, rows, fk=True)) empty = template('empty', bs[0][1], '') dump = os.path.join(W, f'dump-{n}-{rows}.custom') post = os.path.join(W, f'dump-{n}-{rows}.post.sql') if not os.path.exists(post): d = clone(src, 'dumper', bs[0][1]) start(bs[0][1], d) sh([f'{bs[0][1]}/pg_dump', '-Fc', '-h', SOCK, '-p', str(PORT), '-f', dump, 'postgres']) stop(bs[0][1], d) sh([f'{bs[0][1]}/pg_restore', '--section=post-data', '-f', post, dump]) r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to split long runs) for r in range(r0, r0 + rounds): for name, bindir in order(bs, r): d = clone(empty, name, bindir) start(bindir, d) psql(bindir, 'CREATE DATABASE r;') t = {} for sec in ('pre-data', 'data'): t0 = time.monotonic() sh([f'{bindir}/pg_restore', '-h', SOCK, '-p', str(PORT), '-d', 'r', '--exit-on-error', f'--section={sec}', dump]) t[sec] = (time.monotonic() - t0) * 1000 with open(post) as fh: ms, ins = counted(bindir, fh.read(), db='r') if fk_rows(bindir, 'r') != n + 1: sys.exit('restore: wrong number of FK rows') total = t['pre-data'] + t['data'] + ms print(f'restore\t{name}\t{n}x{rows}\t{r + 1}\tpredata_ms\t{t["pre-data"]:.1f}', flush=True) print(f'restore\t{name}\t{n}x{rows}\t{r + 1}\tdata_ms\t{t["data"]:.1f}', flush=True) print(f'restore\t{name}\t{n}x{rows}\t{r + 1}\tpostdata_ms\t{ms:.1f}', flush=True) print(f'restore\t{name}\t{n}x{rows}\t{r + 1}\tpostdata_instructions_M\t{ins / 1e6:.1f}', flush=True) print(f'restore\t{name}\t{n}x{rows}\t{r + 1}\ttotal_ms\t{total:.1f}', flush=True) stop(bindir, d) def postdata(n, rows, rounds, bs): restore(n, rows, 0, bs) # make the dump and post.sql dump = os.path.join(W, f'dump-{n}-{rows}.custom') post = os.path.join(W, f'dump-{n}-{rows}.post.sql') tpl = os.path.join(W, f'tpl-predata-{n}-{rows}') if not os.path.exists(os.path.join(tpl, 'PG_VERSION')): empty = template('empty', bs[0][1], '') d = clone(empty, 'predata', bs[0][1]) start(bs[0][1], d) psql(bs[0][1], 'CREATE DATABASE r;') for sec in ('pre-data', 'data'): sh([f'{bs[0][1]}/pg_restore', '-h', SOCK, '-p', str(PORT), '-d', 'r', '--exit-on-error', f'--section={sec}', dump]) psql(bs[0][1], 'VACUUM ANALYZE; CHECKPOINT;', db='r') stop(bs[0][1], d) shutil.rmtree(tpl, ignore_errors=True) os.rename(d, tpl) r0 = int(os.environ.get('B2_ROUND0', '0')) for r in range(r0, r0 + rounds): for name, bindir in order(bs, r): d = clone(tpl, name, bindir) start(bindir, d) with open(post) as fh: ms, ins = counted(bindir, fh.read(), db='r') if fk_rows(bindir, 'r') != n + 1: sys.exit('postdata: wrong number of FK rows') print(f'postdata\t{name}\t{n}x{rows}\t{r + 1}\tpostdata_ms\t{ms:.1f}', flush=True) print(f'postdata\t{name}\t{n}x{rows}\t{r + 1}\tpostdata_instructions_M\t{ins / 1e6:.1f}', flush=True) stop(bindir, d) def upgrade(n, rounds, bs): old = template(f'upgsrc-{n}', bs[0][1], schema_sql(n, 0, fk=True)) empty = template('empty', bs[0][1], '') r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to split long runs) for r in range(r0, r0 + rounds): for name, bindir in order(bs, r): o = clone(old, f'{name}-old', bindir) nw = clone(empty, f'{name}-new', bindir) wd = os.path.join(W, f'upg-{name}') shutil.rmtree(wd, ignore_errors=True) os.makedirs(wd) t0 = time.monotonic() sh([f'{bindir}/pg_upgrade', '-b', bindir, '-B', bindir, '-d', o, '-D', nw, '-p', str(PORT), '-P', str(PORT + 1), '-s', SOCK, '--no-sync', '--link'], cwd=wd) ms = (time.monotonic() - t0) * 1000 start(bindir, nw) if fk_rows(bindir) != n + 1: sys.exit('upgrade: wrong number of FK rows') stop(bindir, nw) print(f'upgrade\t{name}\t{n}\t{r + 1}\tms\t{ms:.1f}', flush=True) def partof(n, new, rounds, bs): tpl = template(f'partof-{n}', bs[0][1], schema_sql(n, 0, fk=True)) sql = '\n'.join(f'CREATE TABLE q{i} PARTITION OF p FOR VALUES FROM ({i * 1000}) TO ({(i + 1) * 1000});' for i in range(n, n + new)) r0 = int(os.environ.get('B2_ROUND0', '0')) # first round index (to split long runs) for r in range(r0, r0 + rounds): for name, bindir in order(bs, r): d = clone(tpl, name, bindir) start(bindir, d) ms, ins = counted(bindir, sql) if fk_rows(bindir) != n + new + 1: sys.exit('partof: wrong number of FK rows') print(f'partof\t{name}\t{n}\t{r + 1}\tms_per_op\t{ms / new:.2f}', flush=True) print(f'partof\t{name}\t{n}\t{r + 1}\tinstructions_M_per_op\t{ins / 1e6 / new:.2f}', flush=True) stop(bindir, d) def main(): a = sys.argv[1:] if not a: sys.exit(__doc__) op, n = a[0], int(a[1]) if op == 'addfk': addfk(n, int(a[2]), builds(a[3:])) elif op == 'restore': restore(n, int(a[2]), int(a[3]), builds(a[4:])) elif op == 'postdata': postdata(n, int(a[2]), int(a[3]), builds(a[4:])) elif op == 'upgrade': upgrade(n, int(a[2]), builds(a[3:])) elif op == 'partof': partof(n, int(a[2]), int(a[3]), builds(a[4:])) else: sys.exit(__doc__) if __name__ == '__main__': main()