Description:
When the old_snapshot_threshold parameter is set to a value other than the default value of -1 in any minor version of Postgres 12,
query performance on the leader node (or standalone instance) degradation occurs when the threshold is exceeded in 32 threads.
This issue has been reproduced in versions 12.8 and 12.14 on Debian 11 and Oracle Linux/Red Hat 8 operating systems with real load and with pgbench tests.
Steps to Reproduce:
1. Install Postgres version 12.8 or 12.14 on a Debian 11 or Oracle Linux/Red Hat 8 operating system.
2. Set the old_snapshot_threshold parameter to a value other than the default value of -1.
3. Make the load. We used 800GB db with SELECT query for table around 50GB. The greatest affect from seqcan operations.
Our test:
Replica instance:
scaling factor: 1
query mode: simple
number of clients: 104
number of threads: 104
duration: 120 s
number of transactions actually processed: 380955
latency average = 32.755 ms
latency stddev = 5.525 ms
tps = 3173.548711 (including connections establishing)
tps = 3174.330821 (excluding connections establishing)
statement latencies in milliseconds:
33.097 SELECT ***
Leader instance:
scaling factor: 1
query mode: simple
number of clients: 104
number of threads: 104
duration: 10 s
number of transactions actually processed: 1265
latency average = 839.926 ms
latency stddev = 884.810 ms
tps = 121.916353 (including connections establishing)
tps = 122.150878 (excluding connections establishing)
statement latencies in milliseconds:
839.926 SELECT ***
4. Change only the old_snapshot_threshold parameter set to a default value of -1.
5. Repeate the load.
Replica instance:
scaling factor: 1
query mode: simple
number of clients: 104
number of threads: 104
duration: 120 s
number of transactions actually processed: 446795
latency average = 27.926 ms
latency stddev = 16.830 ms
tps = 3722.040652 (including connections establishing)
tps = 3723.239364 (excluding connections establishing)
statement latencies in milliseconds:
28.010 SELECT ***
Leader instance:
scaling factor: 1
query mode: simple
number of clients: 104
number of threads: 104
duration: 120 s
number of transactions actually processed: 437234
latency average = 28.541 ms
latency stddev = 3.798 ms
tps = 3642.509822 (including connections establishing)
tps = 3643.099182 (excluding connections establishing)
statement latencies in milliseconds:
28.723 SELECT ***
Conclusion. Executing queries on more than 32 threads causes query performance degradation.
Impact: This issue may impact the performance of Postgres databases that use the old_snapshot_threshold parameter with a value other than the default value of -1 and execute queries in 32 threads or more.
Note: This issue has been observed in Postgres version 12.* and specific OS and may not apply to other versions.