Re: Hash index inserts serialise on the metapage tuple counter

Hi David, all,

v2 attached (d2adf339b06). TL;DR: ~parity with btree performance.

Changes since v1:

- No metapage pin on the common path. Every insert used to pin the
metapage, and a failed try-lock also writes to its buffer header.
Either way the metapage's cache line moved between CPUs on every
insert.

- Batching once contended. After a backend has done 1024 inserts into an
index and then finds the lock taken, it tries the lock only every 32nd
insert, or when it added an overflow page. It waits for the lock if
256 tuples are pending. Sweeps showed that waiting at 256 beats
waiting at 32 by about 25%.

- Short sessions keep v1 behaviour. That's what the 1024-insert
threshold is for. Without it, sessions of 10 inserts left 30% of the
count uncounted. With it, the worst case is 1.4% low.

Numbers are from a c7i.24xlarge (48 cores, one socket), with the
server pinned to 64 vCPUs and pgbench to the other 32, tmpfs, -O2, best
of 3. Single-row INSERT into a table with a 2M-row index:

clients 16 32 64 128 256
master 388045 139803 81312 60753 49640
v2 528484 638372 684513 674565 686102
btree 514299 648486 681106 733686 719012

Here v2 is level with btree; across runs it varied by about 5% at 128
and 256 clients (700-716k). With 10 rows per INSERT the index work
dominates:

clients 16 32 64 128 256
master 14552 13312 11665 10206 8732
v2 202884 227378 194543 215502 206641
btree 174719 243469 241316 237434 219777

Most of what's left between v2 and btree is the index growing during
the run, at about 6400 bucket splits a second. Every split, and every
overflow page allocated or freed, still takes the metapage and the
bitmap page exclusively, and in the profile those waits are what's
left; the tuple counter no longer shows up. On an index that already
has its buckets (built on 64M rows, all but 1/64 deleted, vacuumed),
10-row inserts give v2 240k/218k/168k TPS at 32/64/128 clients, against
244k/194k/173k for btree - within "spitting distance" even before
targeting this latest point of contention identified.

Reducing the split and overflow allocation traffic on the metapage would
be a separate patch. Wait for v3 with that before deep review please.

Against master on identical data:

- Mixed load (8 writers, unique keys plus a heavy duplicate, delete a
third, VACUUM, insert more): same row count, index probes match
seqscan for the unique and the duplicate key, maxbucket 3039
against 3040.
- 500k inserts of one key: maxbucket 1628 on both.
- Delete-heavy (2M, delete 90%, VACUUM, 2M more): 9369 pages and
7167 buckets on both.
- High-duplicate (2M rows, 50 keys, 8 writers): 6515 buckets on both.
- A streaming standby replaying 8 concurrent inserters into two hash
indexes ends with the same rows, the same metapage ntuples and
maxbucket, and matching probes.

The wal_consistency_checking=hash checksum failure I mentioned earlier
still happens on unpatched master, with or without this patch. That's
covered by [0], we can't overlook the importance of that fix.

In addition, I've added a new regression test in src/test/modules/hash
that drives the deferred path through a new injection point,
hash-insert-defer-count, and checks the counting rules, the cap, the
per-index state, and that the index still splits. It also gets a TAP
test with concurrent long and short sessions on a primary and a standby.
meson test passes on current master.

This changes the XLOG_HASH_INSERT record: the metapage block is now
optional and can carry data. So it's master-only, and whoever commits
it should bump XLOG_PAGE_MAGIC.

Correctness:
- The mixed-load, single-key, delete-heavy and high-duplicate
adversarial runs give the same results as master.
- The standby's metapage and row counts are identical to the primary's.
- The full meson test suite passes (387 ok, 0 fail), including on
current upstream (d2adf339b06).

more to come, best.

-greg

[0] https://postgr.es/m/055bb33c-bccc-bc1d-c2f8-8598534448ac@gmail.com