About could not connect to server: Connection timed out
Hi,
When I use ecpg code to download File from database, error occurred
occasionally as below:
“could not connect to server: Connection timed out Is the server running
on host "DB" and accepting TCP/IP connections on port 5432?”
This my code:
conn = PQsetdbLogin(host, NULL, NULL, NULL, database,
username, "") ;
if (PQstatus (conn) != CONNECTION_BAD){
PQexec(conn, "BEGIN");
if (lo_export(conn,oidImage_Data,chImageName) == -1) {
printf("PQSTATUS=%s \n",PQerrorMessage(conn));
PQfinish (conn) ;
printf("EXPORT %s ERROR! SQL STATE = %s\n
",chImageName,sqlca.sqlstate);
}
printf("PQSTATUS=%s \n",PQstatus(conn));
PQexec(conn, "COMMIT");
This log was found in postgres.log:
Oct 18 10:44:35 SUC02 postgres[10159]: [7-1] LOG: database system is ready
Oct 18 10:44:35 SUC02 postgres[10159]: [8-1] LOG: transaction ID wrap limit
is 3343152371, limited by database "template1"
Oct 18 10:46:11 SUC02 postgres[10997]: [2-1] LOG: unexpected EOF on client
connection
Oct 18 10:46:11 SUC02 postgres[15899]: [2-1] LOG: could not receive data
from client: Connection reset by peer
Oct 18 10:46:11 SUC02 postgres[15899]: [3-1] LOG: unexpected EOF on client
connection
What is the problem ? What should I <javascript:void(0)> investigate ?
Please help ! Thank you very much !
Ray Huang
2009-10-20
On Tue, 2009-10-20 at 12:12 +0800, 黄永卫 wrote:
Oct 18 10:46:11 SUC02 postgres[10997]: [2-1] LOG: unexpected EOF on
client connection
Oct 18 10:46:11 SUC02 postgres[15899]: [2-1] LOG: could not receive
data from client: Connection reset by peer
That looks a lot like lower-level networking issues. Is there any NAT
between client and server? What sort of network connects the two? Is
there any packet loss on the network? Is there any sort of firewall on
or between the client and server?
--
Craig Ringer
Craig Ringer wrote:
On Tue, 2009-10-20 at 12:12 +0800, 黄永卫 wrote:
Oct 18 10:46:11 SUC02 postgres[10997]: [2-1] LOG: unexpected EOF on
client connection
Oct 18 10:46:11 SUC02 postgres[15899]: [2-1] LOG: could not receive
data from client: Connection reset by peerThat looks a lot like lower-level networking issues. Is there any NAT
between client and server? What sort of network connects the two? Is
there any packet loss on the network? Is there any sort of firewall on
or between the client and server?
Agree, what is the timeout value set to in the connection string?
Increase to something Timeout=60 or bigger.
Johan Nel
Pretoria, South Africa.
Thanks for you reply!
Server and the client conect with the same CISCO switch.
We have checked the switch ,but no CRC error occur.
This issue always occur after we reboot the server and the postgres service
just become ready statu for serval several minutes.
It is possible that server's performance cause the issue (server is too busy
on that moment) ?
Thank you!
Ray Huang
-----邮件原件-----
发件人: Craig Ringer [mailto:craig@postnewspapers.com.au]
发送时间: 2009年10月20日 星期二 12:34
收件人: 黄永卫
抄送: pgsql-general@postgresql.org
主题: Re: [GENERAL] About could not connect to server: Connection timed out
On Tue, 2009-10-20 at 12:12 +0800, 黄永卫 wrote:
Oct 18 10:46:11 SUC02 postgres[10997]: [2-1] LOG: unexpected EOF on
client connection
Oct 18 10:46:11 SUC02 postgres[15899]: [2-1] LOG: could not receive
data from client: Connection reset by peer
That looks a lot like lower-level networking issues. Is there any NAT
between client and server? What sort of network connects the two? Is
there any packet loss on the network? Is there any sort of firewall on
or between the client and server?
--
Craig Ringer
On 20/10/2009 3:01 PM, 锟斤拷锟斤拷锟斤拷 wrote:
Thanks for you reply!
Server and the client conect with the same CISCO switch.
OK, so they're both on the same local network segment, with the same
subnet and IP address range, connected via a single Ethernet switch?
Guess it's probably not the network.
This issue always occur after we reboot the server and the postgres service
just become ready statu for serval several minutes.
Hang on. You reboot the server? Why?
Anyway, let me make sure I understand what you are saying. After you
reboot the server, just after the PostgreSQL service has started up,
there are several minutes where some (but not all) client connections
tend to time out. After that initial problem period, things start to
work properly again and the time-outs stop happening. You only have
problems shortly after PostgreSQL (and usually the whole server) has
been re-started.
Right?
If so: Could it just be that a rush of reconnecting clients as the
server comes up causes so much load that it can't process all the
requests before some clients give up? The server would then find, when
it got around to answering the client, that the client had since closed
the connection, which would result in the errors you see in the log.
Try keeping an eye on the number of connected clients, the server load,
and the server response time just after it starts up. I'll bet you'll
see disk I/O and/or CPU load max out and connections to other services
on the same server (say: remote desktop, ssh, file sharing, etc) are
also very slow or time out. Do *not* just ping the server; that'll
usually remain nearly instant no matter how overloaded the server is.
If the problem does turn out to be the server being overloaded: Perhaps
you should rate-limit client reconnection attempts? A typical technique
that's used is to have clients re-try connections after a random delay.
That way, rather than a "thundering herd" of clients all connecting at
once, they connect at random intervals over a short period after the
server comes back up, so the server only has to process a few connection
attempts at once. It's also often a good idea to have that random delay
start out quite short, and increase a bit over time.
A search for the "thundering herd problem" will tell you a bit more
about this, though not in PostgreSQL specific terms.
It is possible that server's performance cause the issue (server is too busy
on that moment) ?
Highly likely given the additional information you've now provided.
--
Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
Anyway, let me make sure I understand what you are saying. After you
reboot the server, just after the PostgreSQL service has started up,
there are several minutes where some (but not all) client connections
tend to time out. After that initial problem period, things start to
work properly again and the time-outs stop happening. You only have
problems shortly after PostgreSQL (and usually the whole server) has
been re-started.
Maybe there's some firewall filtering that takes a while to start up
fully?
What I'd try to determine is whether this behavior occurs when PG itself
is restarted without rebooting the whole machine.
regards, tom lane