#!/usr/bin/env python3 """Requires production builds (configure without --enable-cassert): instruction counts are not meaningful with assertions. Needs perf (instructions:u), pgbench, and the builds' bin directories. Example, 6 rounds alternating master (m) and patched (p): python3 subrel-measure.py.txt tpl 100000 m=/path/master/bin python3 subrel-measure.py.txt drop 100000 6 m=/path/master/bin p=/path/patched/bin """ """Real-operation measurements for RemoveSubscriptionRel() on a subscriber with a large pg_subscription_rel. Production builds only (no cassert): instruction counts are meaningless with assertions. Builds are install prefixes 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 clones of the same catalog template, so both builds see byte-identical catalogs. b1.py tpl K BUILD=BINDIR template: K rows in pg_subscription_rel (K/1000 subscriptions x 1000 tables, or one subscription x K tables if K < 1000; state r, added with binary_upgrade_add_sub_rel_state in -b) b1.py census K B=BIN ... pg_subscription_rel scans per DROP of one typical table (text + identity PK) and of one plain table b1.py drop K ROUNDS B=BIN ... DROP SCHEMA ... CASCADE of 1000 typical tables: wall clock (psql \\timing) + instructions:u of the backend b1.py temp K ROUNDS SECS B=BIN ... pgbench, 1 client, BEGIN; CREATE TEMP TABLE ... ON COMMIT DROP; INSERT; COMMIT -> tps b1.py equiv K B=BIN ... same rows removed by every build: DROP TABLE (0, relid), sequence in state i, table in state d (must ERROR), DROP SUBSCRIPTION (subid, 0); with and without ignore_system_indexes; prints the surviving rows' hash b1.py tempi K ROUNDS NTX B=BIN ... one session runs NTX transactions of the same temp table pattern: instructions:u and ms per transaction b1.py refresh K ROUNDS B=BIN ... ALTER SUBSCRIPTION ... REFRESH PUBLICATION that removes 500 relations (local publisher) Output: one TSV line per measurement on stdout: op build K round metric value B1_ROUND0=R starts at round R+1 (keeps the m/p alternation across split runs). Work dir: $B1_WORK (default ./subrel-work). """ import os, re, shutil, signal, subprocess, sys, time W = os.environ.get('B1_WORK', os.path.abspath('subrel-work')) SOCK = os.path.join(W, 'sock') PORT = 55441 SUB = ("CREATE SUBSCRIPTION {} CONNECTION 'dbname=none' PUBLICATION p " "WITH (connect = false, slot_name = NONE, enabled = false, create_slot = false)") def sh(cmd, **kw): return subprocess.run(cmd, check=True, text=True, capture_output=True, **kw).stdout def builds(args): return [tuple(a.split('=', 1)) for a in args] def start(bindir, d, extra=()): os.makedirs(SOCK, exist_ok=True) opts = f"-p {PORT} -k {SOCK} -c listen_addresses= " + ' '.join(extra) sh([f'{bindir}/pg_ctl', '-D', d, '-o', opts, '-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'): 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 single(bindir, d, sql, binary_upgrade=False): cmd = [f'{bindir}/postgres', '--single', '-j', '-D', d, 'postgres'] if binary_upgrade: cmd.insert(2, '-b') r = subprocess.run(cmd, input=sql, text=True, capture_output=True) if 'ERROR' in r.stdout + r.stderr or r.returncode: sys.exit(f'single-user: {(r.stdout + r.stderr)[:800]}') return r.stdout def tpl_dir(k): return os.path.join(W, f'tpl-{k}') def tpl(k, bindir): d = tpl_dir(k) shutil.rmtree(d, ignore_errors=True) os.makedirs(W, exist_ok=True) sh([f'{bindir}/initdb', '-D', d, '-A', 'trust', '--no-sync']) with open(os.path.join(d, 'postgresql.conf'), 'a') as f: f.write('wal_level = logical\nmax_wal_size = 4GB\n') start(bindir, d) nsub = -(-k // 1000) # K < 1000: one subscription with K tables ntab = min(k, 1000) if k else 1000 psql(bindir, ''.join(f'CREATE TABLE ks_t{i} (id int);\n' for i in range(1, 1001)) + ''.join(SUB.format(f'ks_s{j}') + ';\n' for j in range(1, nsub + 1))) stop(bindir, d) if nsub: single(bindir, d, ''.join( f"SELECT count(binary_upgrade_add_sub_rel_state('ks_s{j}', c.oid, 'r', NULL)) " f"FROM pg_class c WHERE c.relname ~ '^ks_t[0-9]+$' " f"AND substr(c.relname, 5)::int <= {ntab};\n" for j in range(1, nsub + 1)), binary_upgrade=True) start(bindir, d) n = psql(bindir, 'VACUUM; CHECKPOINT; SELECT count(*) FROM pg_subscription_rel;').split()[-1] stop(bindir, d) print(f'tpl\t-\t{k}\t-\tsubrel_rows\t{n}', flush=True) def clone(k, tag): d = os.path.join(W, f'cell-{tag}') if os.path.exists(os.path.join(d, 'postmaster.pid')): # left running by an aborted run subprocess.run([os.path.join(os.path.dirname(os.path.abspath(sys.argv[-1].split('=', 1)[1])), 'pg_ctl'), '-D', d, '-m', 'immediate', '-w', 'stop'], capture_output=True) shutil.rmtree(d, ignore_errors=True) sh(['cp', '-a', '--reflink=auto', tpl_dir(k), d]) return d def order(bs, r): return bs if r % 2 == 0 else list(reversed(bs)) TYPICAL = 'CREATE TABLE {}.t{} (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, v text)' def census(k, bs): for name, bindir in bs: d = clone(k, name) start(bindir, d) psql(bindir, 'CREATE SCHEMA c; ' + TYPICAL.format('c', 1) + '; CREATE TABLE c.plain (id int);') for what in ('c.t1', 'c.plain'): out = psql(bindir, f"BEGIN; DROP TABLE {what}; SELECT coalesce(seq_scan, 0) || ' ' || " f"coalesce(idx_scan, 0) FROM pg_stat_xact_sys_tables " f"WHERE relname = 'pg_subscription_rel'; COMMIT;") seq, idx = out.split()[-2:] print(f'census\t{name}\t{k}\t-\t{what}:seq_scan\t{seq}', flush=True) print(f'census\t{name}\t{k}\t-\t{what}:idx_scan\t{idx}', flush=True) stop(bindir, d) class Session: """An interactive psql whose backend can be counted with perf stat -p.""" def __init__(self, bindir): self.p = subprocess.Popen([f'{bindir}/psql', '-X', '-q', '-At', '-v', 'ON_ERROR_STOP=1', '-h', SOCK, '-p', str(PORT), '-d', 'postgres'], 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): """Run sql in a fresh session; return (ms, instructions:u of its backend).""" s = Session(bindir) perf = subprocess.Popen(['perf', 'stat', '-x,', '-e', 'instructions:u', '-p', str(s.pid)], stderr=subprocess.PIPE, text=True) time.sleep(0.5) out = s.run('\\timing on\n' + sql + '\n\\timing off') perf.send_signal(signal.SIGINT) err = perf.communicate()[1] s.close() ms = sum(float(m) for m in re.findall(r'^Time: ([0-9.]+) ms', '\n'.join(out), re.M)) 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 drop(k, rounds, bs): r0 = int(os.environ.get('B1_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(k, name) start(bindir, d) psql(bindir, 'CREATE SCHEMA d;\n' + ''.join(TYPICAL.format('d', i) + ';\n' for i in range(1, 1001))) psql(bindir, 'VACUUM pg_class, pg_attribute, pg_depend, pg_type, pg_constraint, ' 'pg_index, pg_sequence, pg_attrdef, pg_subscription_rel; CHECKPOINT;') ms, ins = counted(bindir, 'DROP SCHEMA d CASCADE;') n = psql(bindir, "SELECT count(*) FROM pg_namespace WHERE nspname = 'd';").split()[-1] if n != '0': sys.exit('drop: schema still there') print(f'drop\t{name}\t{k}\t{r + 1}\tms\t{ms:.1f}', flush=True) print(f'drop\t{name}\t{k}\t{r + 1}\tinstructions_M\t{ins / 1e6:.1f}', flush=True) stop(bindir, d) DUMP = ("SELECT md5(string_agg(s.subname || ' ' || r.srrelid::regclass::text || ' ' || r.srsubstate::text, " "E'\\n' ORDER BY 1)) || ' ' || count(*) FROM pg_subscription_rel r " "JOIN pg_subscription s ON s.oid = r.srsubid;") def equiv(k, bs): for name, bindir in bs: for isi in ('off', 'on'): d = clone(k, f'{name}-eq') start(bindir, d) psql(bindir, ''.join(f'CREATE TABLE e{i} (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ' f'v text);\n' for i in range(1, 7)) + 'CREATE SEQUENCE eseq;\n' + SUB.format('s_a') + ';\n' + SUB.format('s_b') + ';\n') stop(bindir, d) add = ("SELECT binary_upgrade_add_sub_rel_state('{}', '{}'::regclass, '{}', NULL);\n") sql = ''.join(add.format(sb, f'e{i}', 'r') for sb in ('s_a', 's_b') for i in range(1, 6)) sql += add.format('s_a', 'eseq', 'i') + add.format('s_b', 'e6', 'd') single(bindir, d, sql, binary_upgrade=True) start(bindir, d, ['-c', f'ignore_system_indexes={isi}']) before = psql(bindir, DUMP).strip() psql(bindir, 'DROP TABLE e1; DROP TABLE e2, e3; DROP SEQUENCE eseq;') r = subprocess.run([f'{bindir}/psql', '-X', '-q', '-At', '-h', SOCK, '-p', str(PORT), '-d', 'postgres', '-c', 'DROP TABLE e6'], text=True, capture_output=True) err = r.stderr.strip().replace('\n', ' | ') mid = psql(bindir, DUMP).strip() psql(bindir, 'DROP SUBSCRIPTION s_b;') after = psql(bindir, DUMP).strip() print(f'equiv\t{name}\t{k}\tisi={isi}\tbefore\t{before}', flush=True) print(f'equiv\t{name}\t{k}\tisi={isi}\tdrop_e6\t{err}', flush=True) print(f'equiv\t{name}\t{k}\tisi={isi}\tafter_drops\t{mid}', flush=True) print(f'equiv\t{name}\t{k}\tisi={isi}\tafter_dropsub\t{after}', flush=True) stop(bindir, d) TEMP_SQL = """BEGIN; CREATE TEMP TABLE t (id int, v text) ON COMMIT DROP; INSERT INTO t SELECT g, 'x' FROM generate_series(1, 10) g; COMMIT; """ def temp(k, rounds, secs, bs): f = os.path.join(W, 'temp.sql') with open(f, 'w') as fh: fh.write(TEMP_SQL) r0 = int(os.environ.get('B1_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(k, name) start(bindir, d) sh([f'{bindir}/pgbench', '-n', '-h', SOCK, '-p', str(PORT), '-c', '1', '-T', '3', '-f', f, 'postgres']) # warm-up, not counted out = sh([f'{bindir}/pgbench', '-n', '-h', SOCK, '-p', str(PORT), '-c', '1', '-T', str(secs), '-f', f, 'postgres']) tps = re.search(r'tps = ([0-9.]+)', out).group(1) failed = re.search(r'number of failed transactions: (\d+)', out) if failed and failed.group(1) != '0': sys.exit('temp: failed transactions') print(f'temp\t{name}\t{k}\t{r + 1}\ttps\t{tps}', flush=True) stop(bindir, d) def tempi(k, rounds, ntx, bs): one = TEMP_SQL.replace('\n', ' ') for r in range(int(os.environ.get('B1_ROUND0', '0')), int(os.environ.get('B1_ROUND0', '0')) + rounds): for name, bindir in order(bs, r): d = clone(k, name) start(bindir, d) psql(bindir, one * 20) # warm-up, not counted ms, ins = counted(bindir, one * ntx) print(f'tempi\t{name}\t{k}\t{r + 1}\tms_per_tx\t{ms / ntx:.3f}', flush=True) print(f'tempi\t{name}\t{k}\t{r + 1}\tinstructions_M_per_tx\t{ins / 1e6 / ntx:.3f}', flush=True) stop(bindir, d) def refresh(k, rounds, bs, nrel=500): r0 = int(os.environ.get('B1_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(k, name) start(bindir, d, ['-c', 'max_logical_replication_workers=4', '-c', 'max_replication_slots=10']) psql(bindir, 'CREATE DATABASE pub;') tabs = ''.join(f'CREATE TABLE r{i} (id int PRIMARY KEY);\n' for i in range(1, nrel + 1)) psql(bindir, tabs + 'CREATE PUBLICATION pr FOR ALL TABLES;\n' "SELECT pg_create_logical_replication_slot('s_ref', 'pgoutput');", db='pub') psql(bindir, tabs + f"CREATE SUBSCRIPTION s_ref CONNECTION 'host={SOCK} port={PORT} dbname=pub' " "PUBLICATION pr WITH (create_slot = false, slot_name = 's_ref', copy_data = false);") n = psql(bindir, "SELECT count(*) FROM pg_subscription_rel r JOIN pg_subscription s " "ON s.oid = r.srsubid WHERE s.subname = 's_ref';").split()[-1] if n != str(nrel): sys.exit(f'refresh: {n} rows for s_ref') psql(bindir, 'DROP PUBLICATION pr; CREATE PUBLICATION pr;', db='pub') psql(bindir, 'VACUUM pg_subscription_rel; CHECKPOINT;') ms, ins = counted(bindir, 'ALTER SUBSCRIPTION s_ref REFRESH PUBLICATION WITH (copy_data = false);') n = psql(bindir, "SELECT count(*) FROM pg_subscription_rel r JOIN pg_subscription s " "ON s.oid = r.srsubid WHERE s.subname = 's_ref';").split()[-1] if n != '0': sys.exit(f'refresh: {n} rows left') print(f'refresh\t{name}\t{k}\t{r + 1}\tms\t{ms:.1f}', flush=True) print(f'refresh\t{name}\t{k}\t{r + 1}\tinstructions_M\t{ins / 1e6:.1f}', flush=True) psql(bindir, 'ALTER SUBSCRIPTION s_ref DISABLE; ALTER SUBSCRIPTION s_ref SET (slot_name = NONE); ' 'DROP SUBSCRIPTION s_ref;') stop(bindir, d) def main(): a = sys.argv[1:] if not a: sys.exit(__doc__) op, k = a[0], int(a[1]) if op == 'tpl': tpl(k, a[2].split('=', 1)[1]) elif op == 'census': census(k, builds(a[2:])) elif op == 'drop': drop(k, int(a[2]), builds(a[3:])) elif op == 'temp': temp(k, int(a[2]), int(a[3]), builds(a[4:])) elif op == 'equiv': equiv(k, builds(a[2:])) elif op == 'tempi': tempi(k, int(a[2]), int(a[3]), builds(a[4:])) elif op == 'refresh': refresh(k, int(a[2]), builds(a[3:])) else: sys.exit(__doc__) if __name__ == '__main__': main()