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
xid-select.sql
Description: application/sql
xid-update.sql
Description: application/sql
xid-tests.sh
Description: application/shellscript
xid-hash.ods
Description: application/vnd.oasis.opendocument.spreadsheet
tests.ods
Description: application/vnd.oasis.opendocument.spreadsheet