Thread: Slow connection to the database

Slow connection to the database

From
Poul Møller Hansen
Date:
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


Re: Slow connection to the database

From
Douglas McNaught
Date:
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

Re: Slow connection to the database

From
Poul Møller Hansen
Date:
>
> 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



Re: Slow connection to the database

From
Tom Lane
Date:
=?ISO-8859-1?Q?Poul_M=F8ller_Hansen?= <freebsd@pbnet.dk> writes:
>> 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.

> 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

Re: Slow connection to the database

From
Poul Møller Hansen
Date:
> 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



Re: Slow connection to the database

From
Csaba Nagy
Date:
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:
> > 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