That is actually not a question, just want to share my findings I got when someones were trying to solve non-existing problem.
Below are results of read-only pgbench on 2 x Xeon Gold 5218:
duration: 300 s
number of transactions actually processed: 257001698
latency average = 0.121 ms
latency stddev = 0.220 ms
tps = 856665.854376 (including connections establishing)
tps = 856732.424169 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.121 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
Now, the same with old_snapshot_threshold > 0:
duration: 300 s
number of transactions actually processed: 66537346
latency average = 0.468 ms
latency stddev = 0.775 ms
tps = 221789.233829 (including connections establishing)
tps = 221806.190961 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.471 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
perf output is following:
heap_page_prune_opt
|
--24.72%--TransactionIdLimitedForOldSnapshots
|
|--11.78%--s_lock
| |
| --0.50%--perform_spin_delay
|
--11.69%--GetSnapshotCurrentTimestamp
|
--11.01%--s_lock
|
--0.54%--perform_spin_delay
...
PortalStart
|--23.05%--GetTransactionSnapshot
|
--23.04%--GetSnapshotData
|
|--11.46%--GetSnapshotCurrentTimestamp
| |
| --11.02%--s_lock
| |
| --0.64%--perform_spin_delay
|
--11.25%--MaintainOldSnapshotTimeMapping
|
--10.80%--s_lock
|
--0.61%--perform_spin_delay
Environment:
Architecture: x86_64
CPU(s): 64
Core(s) per socket: 16
Socket(s): 2
Model name: Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz
pgbench -i -s 1000
pgbench -M prepared -n -r -c 100 -j 100 -T 300 -S
