From: Потапов Александр <a.potapov@postgrespro.ru>
To: pgsql-hackers@postgresql.org
Subject: Init connection time grows quadratically
Date: Tue, 27 May 2025 13:45:31 +0300
Message-ID: <3bdba-68359800-d-249da480@139845401> (raw)
Hello!
I ran some experiments with pgbench to measure the initialization time and found that the time increases quadratically with the number of clients. It was surprising to me and I would like to understand a reason of such behavior.
Some details on how it was done:
1) I used the branch REL_16_STABLE (commit 2caa85f4).
2) The default system configuration was modified (CPU speed control, memory control, network, ram disk). Briefly:
sudo cpupower frequency-set -g performance
sudo cpupower idle-set -D0
sudo swapoff -a
sudo sh -c 'echo 16384 >/proc/sys/net/core/somaxconn'
sudo sh -c 'echo 16384 >/proc/sys/net/core/netdev_max_backlog'
sudo sh -c ‘echo 16384 >/proc/sys/net/ipv4/tcp_max_syn_backlog’
numactl --membind=0 bash
sudo mount -t tmpfs -o rw,size=512G tmpfs /mnt/ramdisk
exit
Hyperthreading and cpu boost were disabled:
echo 0 | sudo tee /sys/devices/system/cpu/cpufreq/boost
Please note: When testing on a fast multi-core server with a large number of clients, when the speed of creation of new connections becomes very high, even with such kernel parameters an error may occur:
pgbench:pgbench: error: connection to server on socket "/tmp/.s.PGSQL.5114" failed: Resource temporarily unavailable
In such case you need to apply the patch 0001-Fix-fast-connection-rate-issue.patch (attached).
3) The server was configured as:
./configure --enable-debug --with-perl --with-icu --enable-depend --enable-tap-tests
4) Build and install on ramdrive:
make -j$(nproc) -s && make install
5) DB initialization:
/mnt/ramdisk/bin/initdb -k -D /mnt/ramdisk/data -U postgres
Add to the postgresql.conf:
huge_pages = off #for the sake of test stability and reproducibility
shared_buffers = 1GB
max_connections = 16384
6) Start command:
a) Start server (e.g on the first numa socket)
/mnt/ramdisk/bin/pg_ctl -w -D /mnt/ramdisk/data start
b) create test database and stop the server
/mnt/ramdisk/bin/psql -U postgres -c 'create database bench'
/mnt/ramdisk/bin/pg_ctl -w -D /mnt/ramdisk/data stop
7) pgbench commands:
Perform the single test sequence (I've got a dual socket server, so the server was running on the first socket while the clients were running on the second one):
export PATH=/mnt/ramdisk/bin:$PATH
export NUMACTL_CLIENT="--physcpubind=96-191 --membind=1"
export NUMACTL_SERVER="--physcpubind=0-95 --membind=0"
export CLIENTS=1024
numactl $NUMACTL_SERVER pg_ctl -w -D /mnt/ramdisk/data start
numactl $NUMACTL_CLIENT pgbench -U postgres -i -s100 bench
numactl $NUMACTL_CLIENT psql -U postgres -d bench -c "checkpoint"
numactl $NUMACTL_CLIENT pgbench -U postgres -c$CLIENTS -j$CLIENTS -t100 -S bench
numactl $NUMACTL_SERVER pg_ctl -m smart -w -D /mnt/ramdisk/data stop
8) Measurements & Results
Before the measurements I rebooted host machine and configured the host as described above. After that I ran a script that did 30 measurements of init connection time per a given number of clients, average time and standard deviation were also calculated.
The measurements results are presented as graph an in table form:
Number of clientsAverage init time, ms1024~435 +-202048~1062 +-204096~3284 +-408192~11617 +-12016384~43391 +-230
9) The Question
It turned out that the results correspond to a quadratic dependence like y ~ 0.0002x^2 where x is a number of clients and y is init time (ms).
Here there is a question: is it expected behavior or a bug? What do you think? I appreciate any comments and opinions.
--
Best regards,
Alexander Potapov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
[image/png] 3BDBA-68359800-F-249DA480 (33.4K, ../3bdba-68359800-d-249da480@139845401/3-3BDBA-68359800-F-249DA480)
download | view image
[application/octet-stream] 0001-Fix-fast-connection-rate-issue-for-v16.patch (1.2K, ../3bdba-68359800-d-249da480@139845401/4-0001-Fix-fast-connection-rate-issue-for-v16.patch)
download | inline diff:
From 9206625663b2721af888553ee467b8d0f980f9c3 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=E2=80=9CYourUsername=E2=80=9D?= <“YourEmail”>
Date: Tue, 15 Oct 2024 09:32:12 +0300
Subject: [PATCH] Fix-fast-connection-rate-issue-for-v16
---
src/interfaces/libpq/fe-connect.c | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.cindex 6bce6f647b..99cffa06f6 100644--- a/src/interfaces/libpq/fe-connect.c+++ b/src/interfaces/libpq/fe-connect.c@@ -3040,6 +3040,7 @@ keep_going: /* We will come back to here until there is
* Start/make connection. This should not block, since we
* are in nonblock mode. If it does, well, too bad.
*/
+retry_connect:
if (connect(conn->sock, (struct sockaddr *) &addr_cur->addr.addr,
addr_cur->addr.salen) < 0)
{
@@ -3057,6 +3058,12 @@ keep_going: /* We will come back to here until there is
conn->status = CONNECTION_STARTED;
return PGRES_POLLING_WRITING;
}
++ if (SOCK_ERRNO == EAGAIN)+ {+ pg_usleep(100000L); /* wait 0.1 sec */+ goto retry_connect;+ }
/* otherwise, trouble */
}
else
--
2.39.2
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.potapov@postgrespro.ru
Subject: Re: Init connection time grows quadratically
In-Reply-To: <3bdba-68359800-d-249da480@139845401>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox