PostgreSQL Discussion - Slow connection to the database

Author

Slow connection to the database

Poul MÃ¸ller Hansen

2005-09-24, 7:23 am

I have a problem with slow connects to a database server.

In a little Java application I am starting 100 threads, each one making
a database connection. The program is running on my laptop.

When connecting to one server (AMD Opteron 2GHz with Solaris &
pgsql version 8.0.3) the 100 connections takes 455 seconds!

Comparing to an old PC (AMD K6-II 500MHz with FreeBSD and pgsql version
8.0.3) the 100 connections takes 6 seconds

When running the client program on the Opteron the 100 connections takes
only 1 second, but in general the network speed to/from the server is ok
and they are on the same LAN.
When they are connected insert and select speed are ok.

Is anyone of you having an idea where to seek the problem ?

Regards, Poul

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Douglas McNaught

2005-09-24, 9:23 am

Poul Møller Hansen <freebsd@pbnet.dk> writes:

> I have a problem with slow connects to a database server.
>
> In a little Java application I am starting 100 threads, each one making
> a database connection. The program is running on my laptop.
>
> When connecting to one server (AMD Opteron 2GHz with Solaris &
> pgsql version 8.0.3) the 100 connections takes 455 seconds!

Is it possible that you've got DNS timeouts happening or something
like that? Though I don't think PG does reverse DNS lookups,
especially not by default.

Watching the connection process with 'tcpdump' to see where the stall
happens might be informative.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Poul Møller Hansen

2005-09-24, 8:23 pm

>
> Is it possible that you've got DNS timeouts happening or something
> like that? Though I don't think PG does reverse DNS lookups,
> especially not by default.
>
> Watching the connection process with 'tcpdump' to see where the stall
> happens might be informative.
>
> -Doug

DNS was also my first thought, but the connection isn't logged so why do
reverse lookup and with my clients ip address in /etc/hosts is same.

I have now also tried on a Sun Ultra Sparc running Solaris 10
and an Intel EMT64 running FreeBSD 6-BETA4 with the same discouraging
result. On the FreeBSD I have tried both compiling by hand and from ports.

All servers where it isn't working properly have in common that it
is PgSQL ver. 8.0.3 compiled with gcc ver. 3.4.4 running on 64-bits
cpu's

It's working well on an old K6-II running FreeBSD 4.11, PgSQL 8.03
compiled with gcc 2.95.4

Poul

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-09-24, 8:23 pm

=?ISO-8859-1?Q? Poul_M=F8ller_Hansen
?= <freebsd@pbnet.dk> writes:
[color=darkred]
> DNS was also my first thought, but the connection isn't logged so why do
> reverse lookup and with my clients ip address in /etc/hosts is same.

How do you know the delay isn't in the client's lookup of the server?

I concur with Doug's suggestion that you need to trace the connection
process to find out what's slow. Personally I'd try strace (or local
equivalent) first; the odds are that you can spot the issue at the
kernel-call level. You'd probably need to trace both the client and
server sides of the operation.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Poul Møller Hansen

2005-09-25, 7:23 am

> How do you know the delay isn't in the client's lookup of the server?
>

You are absolutely right!
I am using the ip address to connect to, so I thougt that was it.
But the client is doing netbios queries .... seen with ethereal.

Thanks to both of you.

Poul

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Csaba Nagy

2005-09-26, 7:23 am

This is a known issue with Java 1.5 networking code, it tries to reverse
look up the IP addresses in certain circumstances (which obviously occur
in the JDBC driver) if you use IPs in your connection addresses.

You're best off to make sure DNS is set up locally on your client
machines for all the known servers you connect to. That would be in your
/etc/hosts on linux, on other OSes I don't know.
This will make sure you have no timeouts whether you use IPs or host
names names, but you have to maintain it.

Cheers,
Csaba.

On Sun, 2005-09-25 at 12:32, Poul MÃ¸ller Hansen wrote:
>
> You are absolutely right!
> I am using the ip address to connect to, so I thougt that was it.
> But the client is doing netbios queries .... seen with ethereal.
>
> Thanks to both of you.
>
>
> Poul
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql
.org so that your
> message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links