Hi,
I've done a bit of benchmarking on the last patch version (from 22/8),
using a simple workload:
1) 8 clients doing
SELECT SUM(abalance) FROM pgbench_accounts
with the table truncated to only 10k rows
2) variable number (8, 16, 32, ..., 512) of write clients, doing this
\set aid random(1, 10000 * :scale)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid = :aid;
SELECT pg_sleep(0.005);
COMMIT;
The idea is to measure tps of the read-only clients, with many throttled
write clients (idle in transaction for 5ms after each update). This
should generate snapshots with many XIDs. Scripts attached, of course.
The results look really promising (see the attached chart and .ods):
write clients master patched
8 7048 7089
16 6601 6614
32 5862 5944
64 5327 5349
128 4574 4952
256 4132 4956
512 2196 4930
That being said, I think Stephen is right that there's a bug in
CopySnapshot, and I agree with his other comments too. I think the patch
should have been in WoA, so I'll mark it like that.
simplehash is great, but I'm not sure it's a good fit for this use case.
Seems a bit overkill to me in this case, but maybe I'm wrong.
Snapshots are static (we don't really add new XIDs into existing ones,
right?), so why don't we simply sort the XIDs and then use bsearch to
lookup values? That should fix the linear search, without need for any
local hash table.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
