29 如实测量

# the lab runs each pgbench in the server's container; $script holds:
pgbench --show-script=tpcb-like 2>&1
prove "the default TPC-B-like script updates pgbench_branches, one row per branch" \
  grep -q "UPDATE pgbench_branches" <<<"$script"
# $tpcb, then $sel (tps_of keeps the tps figure):
pgbench -n -M prepared -c 4 -j 4 -T 5 bench 2>&1 | tps_of
pgbench -n -S -M prepared -c 4 -j 4 -T 5 bench 2>&1 | tps_of
prove "select-only runs at least 5x the transactions per second of TPC-B-like" \
  is "$sel > 5 * $tpcb"

# with synchronous_commit=off, in the server's container
pgbench -n -c 4 -j 4 -t 10000 -f /tmp/picks.sql book
prove "random_zipfian(1, 20000, 1.1) sends 55 to 80% of picks to the first 1% of ids" \
  holds book "select count(*) filter (where z <= 200) * 1.0 / count(*) between 0.55 and 0.80 from picks"
prove "random_exponential(1, 20000, 5) sends about 5% to the first 1% (3 to 7%)" \
  holds book "select count(*) filter (where e <= 200) * 1.0 / count(*) between 0.03 and 0.07 from picks"
prove "random_gaussian(1, 20000, 4) puts about 67% in the middle quarter (62 to 72%)" \
  holds book "select count(*) filter (where g between 7501 and 12500) * 1.0 / count(*) between 0.62 and 0.72 from picks"

for round in 1 2 3; do
  # each round runs modes.sh in the container: the three modes at once
  for m in simple extended prepared; do
    pgbench -n -S -M $m -c 1 -T 8 bench > mode_$m.txt 2>&1 &
  done
  wait
  # then reads each mode's tps
done
prove "the slowest prepared run beats the fastest simple run" is "$pmin > $smax"
prove "prepared is at least 15% faster than simple for a one-row lookup (mean of 3)" \
  is "$pavg > 1.15 * $savg"

prove "in the -R log, every transaction's time includes its schedule lag (time >= lag)" is "$bad == 0"

# sql <db>: psql on the lab's server
sql book -c "select pg_buffercache_evict_relation('users'), pg_buffercache_evict_relation('users_pkey')"
# cold
pgbench -n -M prepared -c 4 -j 4 -T 5 -f /tmp/lookup.sql book
# warm
pgbench -n -M prepared -c 4 -j 4 -T 5 -f /tmp/lookup.sql book
prove "the cold run reads over 1,000 blocks from outside shared buffers" is "$cold_read > 1000"
prove "the warm run of the same command reads under a tenth of that" is "$warm_read * 10 < $cold_read"

PGOPTIONS="-c synchronous_commit=off" pgbench -n -M prepared -c 4 -j 4 -T 60 -P 10 \
  --log --log-prefix=ck_ --aggregate-interval=1 bench
prove "a checkpoint started inside the one-minute run" test -n "$starts"
prove "full-page images jump after the checkpoint starts (5 s after > 5x the 5 s before)" \
  is "$fpi_after > 5 * $fpi_before + 100"
prove "the same transactions write more than twice the WAL each in the 5 s after it starts" \
  is "$wal_after > 2 * $wal_before"
prove "autovacuum processed pgbench_tellers during the run" is "$av_after > $av_before"

# before
pgbench -n -M prepared -c 4 -j 4 -T 10 -r -f /tmp/endpoint.sql book
# sql <db>: psql on the lab's server
sql book -c "create index events_project_id_idx on events (project_id)"
# after
pgbench -n -M prepared -c 4 -j 4 -T 10 -r -f /tmp/endpoint.sql book
prove "before the index the report reads over 30,000 blocks per call (the whole table)" \
  is "$rep_blk_b > 30000"
prove "after the index it reads under 1,000 blocks per call" is "$rep_blk_a < 1000"
prove "the report's mean time in pg_stat_statements fell more than 20x" \
  is "$rep_ms_b > 20 * $rep_ms_a"
prove "the untouched lookup does the same 3 blocks of work before and after" \
  is "$usr_blk_b == $usr_blk_a && $usr_blk_a == 3"
prove "yet its client-side latency (-r) fell over 1.5x, because the box stopped being saturated" \
  is "$lk_b > 1.5 * $lk_a"

prove "zipfian lookups hit shared buffers at least 10 points more often than uniform ones" \
  is "$z_ratio > $u_ratio + 10"
prove "in the seed, the busiest 1% of projects hold under 2% of events" \
  holds book "select sum(c) * 1.0 / 2000000 < 0.02
              from (select count(*) as c from events group by project_id order by c desc limit 200) t"
prove "on uniform data, project 1 plans a scan of the project_id index and a sort (about 25 rows)" \
  grep -q "Index Scan on ev_uniform_project_id_idx" <<<"$u_plan"
prove "on skewed data, the same query walks the created_at index backward and filters (about 35,000 rows)" \
  grep -q "Index Scan Backward using ev_skewed_created_at_idx" <<<"$s_plan"
prove "the aged table holds the same rows in at least 1.5x the pages" is "$aged >= 1.5 * $fresh"

prove "masking kept every email distinct (100,000), so unique constraints and joins still work" \
  holds staging "select count(distinct email) = 100000 from users"
prove "the masked clone still has the original email in a dead row version on disk" \
  holds staging "select exists (select 1 from generate_series(0, (pg_relation_size('users') / 8192)::int - 1) b
                                where position(convert_to('user1000@example.com', 'UTF8')
                                               in get_raw_page('users', 'main', b)) > 0)"
# $hits, in the server's container:
pg_dump -t users staging | grep -c '@example.com' || true
prove "but a pg_dump of the clone contains no original email address" is "$hits == 0"

prove "pg_dump --statistics-only carried relpages and reltuples into the empty copy" \
  holds shape "select relpages = $(sql book -c "select relpages from pg_class where relname = 'events'")
               and reltuples > 1900000 from pg_class where relname = 'events'"
prove "the full database estimates over 200,000 alert rows" is "$est_book > 200000"
prove "the empty copy with the same statistics estimates fewer than 10" is "$est_shape < 10"