Обсуждение: PostgreSQL local connection is taking 4 seconds
Hi Experts,
We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is taking ~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in postgresql.conf or pg_hba.conf.
PostgreSQL version is 13.12.
RHEL Version: Red Hat Enterprise Linux release 8.8 (Ootpa)
On Tue, Jan 2, 2024 at 8:59 AM pramod kg <pramod11287@gmail.com> wrote:
Hi Experts,We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is taking ~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in postgresql.conf or pg_hba.conf.PostgreSQL version is 13.12.RHEL Version: Red Hat Enterprise Linux release 8.8 (Ootpa)
What authentication method is used?
MD5
On Tue, Jan 2, 2024 at 7:34 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Tue, Jan 2, 2024 at 8:59 AM pramod kg <pramod11287@gmail.com> wrote:Hi Experts,We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is taking ~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in postgresql.conf or pg_hba.conf.PostgreSQL version is 13.12.RHEL Version: Red Hat Enterprise Linux release 8.8 (Ootpa)What authentication method is used?
It's getting slower day by day. Two days back psql was taking 2 seconds to establish a local connection. Today it is taking more than 4 seconds.
On Tue, Jan 2, 2024 at 7:42 PM pramod kg <pramod11287@gmail.com> wrote:
MD5On Tue, Jan 2, 2024 at 7:34 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:On Tue, Jan 2, 2024 at 8:59 AM pramod kg <pramod11287@gmail.com> wrote:Hi Experts,We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is taking ~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in postgresql.conf or pg_hba.conf.PostgreSQL version is 13.12.RHEL Version: Red Hat Enterprise Linux release 8.8 (Ootpa)What authentication method is used?
pramod kg schrieb am 02.01.2024 um 14:58: > Hi Experts, > > We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is taking~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in postgresql.confor pg_hba.conf. > > PostgreSQL version is 13.12. > RHEL Version: Red Hat Enterprise Linux release 8.8 (Ootpa) Did you enable log_hostname? That does a revers DNS lookup which could be a reason for the connection suddenly taking longer.
log_hostname value is off:
postgres=# show log_hostname;
log_hostname
--------------
off
(1 row)
log_hostname
--------------
off
(1 row)
There is no change in postgresql.conf
On Tue, Jan 2, 2024 at 9:25 PM Thomas Kellerer <shammat@gmx.net> wrote:
pramod kg schrieb am 02.01.2024 um 14:58:
> Hi Experts,
>
> We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is taking ~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in postgresql.conf or pg_hba.conf.
>
> PostgreSQL version is 13.12.
> RHEL Version: Red Hat Enterprise Linux release 8.8 (Ootpa)
Did you enable log_hostname?
That does a revers DNS lookup which could be a reason for the connection suddenly taking longer.
Thomas Kellerer <shammat@gmx.net> writes:
> pramod kg schrieb am 02.01.2024 um 14:58:
>> We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is
taking~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in
postgresql.confor pg_hba.conf.
> Did you enable log_hostname?
> That does a revers DNS lookup which could be a reason for the connection suddenly taking longer.
Even without log_hostname, it could be a slow-DNS-lookup problem.
Rather than guessing, though, it might be time to try strace'ing
the psql session to see what it's waiting for.
regards, tom lane
We have made some progress. GSSAPI authentication method is taking time though we have not enabled the GSSAPI authentication method in postgres (but the connection request is spending some time there). We continue troubleshooting on why the GSSAPI authentication method is taking longer time. For now we have exported the following ENV and local connections are faster.
export PGGSSENCMODE="disable"
On Tue, Jan 2, 2024 at 9:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Thomas Kellerer <shammat@gmx.net> writes:
> pramod kg schrieb am 02.01.2024 um 14:58:
>> We have upgraded the OS from RHEL7 to RHEL8 in our testbed machines. After upgrading, PostgrSQL local connection is taking ~4 seconds (Both TLS and non TLS connection). Please advise on what to be verified? There is no change in postgresql.conf or pg_hba.conf.
> Did you enable log_hostname?
> That does a revers DNS lookup which could be a reason for the connection suddenly taking longer.
Even without log_hostname, it could be a slow-DNS-lookup problem.
Rather than guessing, though, it might be time to try strace'ing
the psql session to see what it's waiting for.
regards, tom lane
pramod kg <pramod11287@gmail.com> writes: > We have made some progress. GSSAPI authentication method is taking time > though we have not enabled the GSSAPI authentication method in postgres > (but the connection request is spending some time there). We continue > troubleshooting on why the GSSAPI authentication method is taking longer > time. For now we have exported the following ENV and local connections are > faster. > export PGGSSENCMODE="disable" Ah, that rings a bell. After a bit of searching I found https://www.postgresql.org/message-id/flat/16897-2632c6541c9f8491%40postgresql.org which seems to describe exactly the same symptom. We never got a final answer from that person, but I continue to think that Postgres must have been trying to use GSSAPI authentication, suggesting that the server is being started in an environment where a GSSAPI server appears to be available. regards, tom lane
On Wed, Jan 3, 2024 at 2:05 AM pramod kg <pramod11287@gmail.com> wrote:
We have made some progress. GSSAPI authentication method is taking time though we have not enabled the GSSAPI authentication method in postgres (but the connection request is spending some time there).
When a libpq client sees that krb5 credentials exist, it tries to use them preemptively, without knowing if the server supports them or not. Once given an error, it then tries again without GSS. The server log file should show evidence of these failed connections, and the failure reason given might offer some clues.
But this first try should normally be fast. Is the time taken on the client side, or on the server side? Using strace on a simple psql invocation would be the best way to answer this (on Linux). If the server doesn't support GSS at all, it is hard to see why it should fail slowly on the server side so that would probably be a bug.
Cheers,
Jeff