Azure Postgresql High connection establishment time
Hi
we are writing a python(3.9) azure function hosted on linux which is
connecting to azure postgresql 11. We are using psycopg2 to connect the
azure function to postgresql.
we are noticing the connection establishment is taking around 200-300 ms.
Below is the statment.
conn=psycopg2.connect(conn_string)
Conn_string includes host, user, password, dbname, sslmode=require
The connection establishment time is very high as our functions are written
to be very high performance.
can you please let us know if there is something we should be doing to
reduce the connection establishment time.
Thanks
This is not a Postgres issue. Please reach out to the Azure team.
Show quoted text
Abhay Gupta <gupta.abhay86@gmail.com> writes:
we are noticing the connection establishment is taking around 200-300 ms.
Not sure whether that's out of line or not, since you've provided
no background data (e.g. is the connection across a network link?
how busy is the server? what encryption and authentication are
you using?). However ...
The connection establishment time is very high as our functions are written
to be very high performance.
... if you are looking for good performance, the last thing you
should be doing is making a connection per query. Postgres backend
processes are pretty heavyweight things. Even after the connection
is complete, there's overhead involved in populating caches and so
forth. You'd be well-served to use a connection pooler and/or try
to keep an application's connection open once made.
regards, tom lane
Hello Tom
Thanks for taking time in looking into this for us. Apologies for not
providing any required data.
Yes, Azure postgresql has a private endpoint attached to it so it is going
through a private link. Also the server has 2 vcore and 5gb. Since
currently we are in development phase there is no load on the server at
all. No encryption and in the connection string we are passing the user and
password. It is Ssl enabled. To negate it is Azure issue, we installed
postgresql 11 on our on prem server and there also we are seeing a
connection latency of about 150-200 ms.
We are only opening 1 connection in our application i.e. after all the work
is done for that user session than only we close the connection.
If you could assist in what we can check will be very helpful.
Please let me know if I still missed something as we have just started our
journey with Postgresql.
Thanks
On Mon, Sep 20, 2021 at 1:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Abhay Gupta <gupta.abhay86@gmail.com> writes:
we are noticing the connection establishment is taking around 200-300 ms.
Not sure whether that's out of line or not, since you've provided
no background data (e.g. is the connection across a network link?
how busy is the server? what encryption and authentication are
you using?). However ...The connection establishment time is very high as our functions are
written
to be very high performance.
... if you are looking for good performance, the last thing you
should be doing is making a connection per query. Postgres backend
processes are pretty heavyweight things. Even after the connection
is complete, there's overhead involved in populating caches and so
forth. You'd be well-served to use a connection pooler and/or try
to keep an application's connection open once made.regards, tom lane
On 9/20/21 10:37 AM, Abhay Gupta wrote:
Hello Tom
Thanks for taking time in looking into this for us. Apologies for not
providing any required data.Yes, Azure postgresql has a private endpoint attached to it so it is
going through a private link. Also the server has 2 vcore and 5gb. Since
currently we are in development phase there is no load on the server at
all. No encryption and in the connection string we are passing the user
and password. It is Ssl enabled. To negate it is Azure issue, we
installed postgresql 11 on our on prem server and there also we are
seeing a connection latency of about 150-200 ms.We are only opening 1 connection in our application i.e. after all the
work is done for that user session than only we close the connection.If you could assist in what we can check will be very helpful.
Per upstream comment take a look at connection pooling.
Two solutions that come to mind:
Pgpool-II
https://www.pgpool.net/mediawiki/index.php/Main_Page
PgBouncer
Thanks
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2021-09-20 13:37:52 -0400, Abhay Gupta wrote:
Yes, Azure postgresql has a private endpoint attached to it so it is going
through a private link. Also the server has 2 vcore and 5gb. Since currently we
are in development phase there is no load on the server at all. No encryption
and in the connection string we are passing the user and password. It is Ssl
enabled. To negate it is Azure issue, we installed postgresql 11 on our on prem
server and there also we are seeing a connection latency of about 150-200 ms.
How are you authenticating? Are you using password hashes stored in the
database (MD5 or preferrably SCRAM-SHA-256) or are you authenticating
against an external source (e.g. active directory). If the latter, what
and how? Can you measure how long the authentication takes?
I have seen LDAP authentication against a local AD instance take over
hundred milliseconds, so I wouldn't be surprised if that was the
culprit.
hp
PS: a local connection (ident) takes about 3.5 ms on my (not very fast)
laptop.
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"