Re: [GENERAL] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[Tom Lane]

Vamsi Patchipulusu  writes:
>   Error: psql.bin: could not connect to server: Cannot assign 
> requested address
> Is the server running on host "abchost.corp.xyz.com" 
> (xxx.xxx.xxx.xxx) and accepting
> TCP/IP connections on port 5432?

Googling suggests that this could occur if you're recycling client-side
connections so fast that the old port number assignments haven't timed out
yet.  That's not a Postgres bug, it's inherent in the TCP protocol specs.

> The  shell script does the following:
> a)  Connects to postgres database server using psql.
> b)  Issues a single select statement on table with 200 rows .
> c)   Writes the results to a text file.

There is a large body of evidence to the effect that issuing only one
query per connection attempt is a performance-killer.  Don't do that, at
least not more often than you have to.  This particular symptom is a new
one on me, but there are lots of other reasons not to do it.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[David G. Johnston]

On Monday, May 15, 2017, Vamsi Patchipulusu 
wrote:
>
>   Error: psql.bin: could not connect to server: Cannot assign
> requested address
>
> Is the server running on host "abchost.corp.xyz.com"
> (xxx.xxx.xxx.xxx) and accepting
>
> TCP/IP connections on port 5432?
>
> Jmeter is the tool we are using for orchestrating the tests. It  executes
> a shell script on 50 concurrent  threads.
>
The  shell script does the following:
>
> a)  Connects to postgres database server using psql.
>
> b)  Issues a single select statement on table with 200 rows .
>
> c)   Writes the results to a text file.
>
Jmeter client and database host are sitting in the same data center.
>
Database OS: RedHat 6.x,
>
> DB Version:  Postgres 9.6
>
> Database is of decent config. 16 CPU, 64GB RAM, SAN storage.
>
As you noted this is a client-side error so server specs are immaterial
unless the client and server are the same machine.

> While some calls are failing with the error*:   psql.bin: could not
> connect to server: Cannot assign requested address*
>
> Other calls are successful around the same timeframe may be one to two
> seconds later..
>
This points to an O/S problem, possibly configuration.  Or it might just be
a problem with JMeter.  In any case failing to bind to an address
sporadically isn't really something psql can be blamed for.

Googling jmeter for this turns up a variety of programs and scripts that
seem to have this problem when run within JMeter.  Exploring those
results may prove fruitful.

David J.

[GENERAL] Postgres intermittent connection errors: psql.bin: could not connect to server: Cannot assign requested address

[Vamsi Patchipulusu]

Hi All,

We are receiving below intermittent connection error during performance testing 
on postgres.
Thanks in advance for any suggestion or pointers on how we can troubleshoot 
this issue.

  Error: psql.bin: could not connect to server: Cannot assign requested 
address
Is the server running on host "abchost.corp.xyz.com" (xxx.xxx.xxx.xxx) 
and accepting
TCP/IP connections on port 5432?


Our organization is evaluating the feasibility of using postgres for an 
upcoming project.
As part of the evaluation we are performing  a 50 concurrent user read 
performance testing.
Jmeter is the tool we are using for orchestrating the tests. It  executes a 
shell script on 50 concurrent  threads.

The  shell script does the following:

a)  Connects to postgres database server using psql.

b)  Issues a single select statement on table with 200 rows .

c)   Writes the results to a text file.

Jmeter client and database host are sitting in the same data center.
Database OS: RedHat 6.x,
DB Version:  Postgres 9.6
Database is of decent config. 16 CPU, 64GB RAM, SAN storage.

Our tests are run for 10 to 15min.
During the middle of the run, we are noticing connection errors intermittently. 
(Around 20%  calls are failing due to this).

While some calls are failing with the error:   psql.bin: could not connect to 
server: Cannot assign requested address
Other calls are successful around the same timeframe may be one to two seconds 
later..

For the failed calls we are not able to see anything printed in the database 
server logs, (The exception is received on the client side).
For successful  calls we are able to see the details  of the sql statements, 
user, hostname etc... printed in the logs.

We have verified CPU,Memory,No of open files, netstat connection waits, but 
could not identify anything concrete that could be causing this issue.
The database is setup to accept 500 concurrent open connections and we are not 
using anywhere close to it.



Regards,
Vamsi