On 6/16/25 11:56, Потапов Александр wrote:
To be more precise I used constant number of threads (128 and 1024) to compare with previous results. The quadratic dependency exists everywhere, see new graph.
> Q: Did you check that pgbench or the OS does not have
> O(n_active_connections) or O(n_active_threads) overhead per worker
> during thread creation or connection establishment, e.g. by varying
> the number of threads used to manage these N clients? I wouldn't be
> surprised if there are inefficiencies in e.g. the threading- or
> synchronization model that cause O(N) per-thread overhead, or O(N^2)
> overall when you have one thread per connection.
Hi, all!
I've investigated slightly different scenario then Alexander and I want share my thoughts in this thread too.
I found that when we run pgbench scenarios sequantially, without postgres restart between iterations, initial time degrades from launch to launch and eventually it stabilizes at the worst values then first run(ICT_degradation.png attached).
Scenario details:
1. postgres rev 1a51ec16db7, 0001-Fix-fast-connection-rate-issue.patch was applied
2. numa was disabled, swap was off, ramdisk was used for binaries and pg_data,Hyperthreading and cpu boost were disabled
3. server was built with -02
4.Add to the postgresql.conf:
huge_pages = off #for the sake of test stability and reproducibility
shared_buffers = 1GB
max_connections = 16384
5. Cycle iteration
psql -U postgres -c 'create database bench'
pgbench -U postgres -i -s100 bench
psql -U postgres -d bench -c "checkpoint"
pgbench -U postgres -c${clients} -t100 -j6 -S bench
psql -U postgres -c "DROP DATABASE bench"
Where ${clients} is one of 512/1024/2048/4096/8192
I paid attention that ICT for the first iteration much better than for next ones. I investigated this behavior a little bit and found a lot of minor page fault events in ProcArrayAdd method(perf_without_patch-j6.txt attached) for code line
allProcs[procno].pgxactoff = index;
So, every proc.pgxactoff access generate page fault, because proc objects accessed in memory randomly and page replacement can occur. I have some ideas how to improve this - it seems we can put array of pgxactoff separately
in shmem to have only few hot pages for them. I've attached appropriate patch(0001-This-patch-reduce-connection-init-close-time.patch). Perf with minor faults for updated version also was attached(perf_with_patch-j6.txt attached),
as we can see, patched version fixes this. I made a series of measurements for all versions and attached comparison chart(ICT_degradation_with_patch.png attached). Also I add the table with results
| without_patch_first_iteration, average ICT, ms | without_patch_after_warmup, average ICT, ms | with_patch_first_iteration, average ICT, ms | with_patch_after_warmup, average ICT, ms |
| 512 | ~480+-15 | ~500+-10 | ~470+-15 | ~215 +-15 |
| 1024 | ~920+-20 | ~1000+-20 | ~900+-15 | ~920 +-20 |
| 2048 | ~1780+-30 | ~2240+-20 | ~1760+-30 | ~1800 +-30 |
| 4096 | ~3570+-50 | ~6140+-40 | ~3450+-130 | ~3740 +-60 |
| 8192 | ~7440+-90 | ~18840+-140 | ~7440+-140 | ~8100+-80 |
As we can see from attached charts, from one side there is no degradation for the first iterations for patched and no patched versions. From another side patched version is much better for next ones.
I've tried the same tests for different values of pgbench -j parameter to avoid effects of multithreading pgbench nature and got the same picture.
I hope it will be interesting and helpful.
Best regards,
Maksim Melnikov
