Thread: Slow connection to the database
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
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
> > 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
=?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
> 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
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