0

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
Andrey B. Panfilov
  • 214
  • 8
  • What does the 2x mean? It has two different sockets, each with a 16-core processor? Or just that you have hyperthreading turned on? What is the scale used, and the full command line (particularly the numbers for -c and -j)? – jjanes Oct 24 '23 at 20:46

1 Answers1

1

Don't use old_snapshot_threshold. Its implementation had problems, and it was removed in PostgreSQL v17.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
  • yeap, I have read related topic already: https://www.postgresql.org/message-id/flat/CACG%3DezYV%2BEvO135fLRdVn-ZusfVsTY6cH1OZqWtezuEYH6ciQA%40mail.gmail.com, the problem is the current description of feature in documentation is looking too attractive for former Oracle DBAs. – Andrey B. Panfilov Oct 24 '23 at 05:51