Thread: PostgreSQL 7.4.1 incredibly slow :-(
Hi, I'm experiencing some really odd behaviour after upgrading to postgresql 7.4.1 We have three different machines: 1) Dual Athlon, 1GB RAM running RedHat 7.3 with all the updates (kernel, glibc, gcc/g++ 2.96-113) 2) Single Pentium 1.7GHz, 512M RAM running RedHat 9 with all the updates. 3) Single Pentium 2GHz, 512M RAM running RedHat 7.3 with all the updates, and the compiler updated from the source to gcc/g++ 3.2.2 We upgraded all of them to postgresql 7.4.1, installed with: ./configure make make install (i.e., all the default options) Everything except local (i.e., non-TCP/IP) connections has md5 authentication (this for the three machines). On the dual Athlon (Murphy had to do it in a way that maximizes the irony, of course! :-)), connections through TCP/IP take forever! Even if they're local connections (i.e., even if they come from 127.0.0.1) If I run psql locally, when executed without switches, it responds instantly (pg_hba.conf has an entry with "ident sameuser" for local connections). When executed with the -h localhost switch, it takes exactly 30 seconds to prompt me for the password, and once I enter the password, it takes another 30 seconds (*exactly* 30 seconds). The pg_hba.conf files are identical (in fact, *really* identical, as I discovered that TCP/IP connections to the same machine are not seen by the server as coming from 127.0.0.1, but coming from the other IP address -- the "public" one; so I had to add each machine's own addresses to the pg_hba.conf). It doesn't look like it is RedHat's version making a difference -- two of the machines have 7.3, and on one of them, everything runs instantly)... Hmmm, the compiler seems to be the only difference between the two RH7.3 machines. I doubt it has to do with firewalls (for one, the connections are passing -- ipchains firewalls do not simply add a delay; but also, it was working without any delay before upgrading to 7.4.1) Any ideas of what this could be? Any extra validations or constraints in the new server-to-client protocol? Those 30 seconds sound like a timeout of some sort, but whatever it may be, it was not affecting the previous setup, so I can't figure out what it could be that I need to change. Thanks for any ideas! Carlos --
Wierd timeouts may be caused by misconfigured or unreachable DNS or LDAP servers. Have you checked them? On Sun, Jan 04, 2004 at 07:58:03PM -0500, Carlos Moreno wrote: > > Hi, > > I'm experiencing some really odd behaviour after > upgrading to postgresql 7.4.1 > > We have three different machines: > > 1) Dual Athlon, 1GB RAM running RedHat 7.3 with > all the updates (kernel, glibc, gcc/g++ 2.96-113) > > 2) Single Pentium 1.7GHz, 512M RAM running RedHat > 9 with all the updates. > > 3) Single Pentium 2GHz, 512M RAM running RedHat 7.3 > with all the updates, and the compiler updated > from the source to gcc/g++ 3.2.2 > > We upgraded all of them to postgresql 7.4.1, installed > with: > > ./configure > make > make install > > (i.e., all the default options) > > Everything except local (i.e., non-TCP/IP) connections > has md5 authentication (this for the three machines). > > On the dual Athlon (Murphy had to do it in a way > that maximizes the irony, of course! :-)), connections > through TCP/IP take forever! Even if they're local > connections (i.e., even if they come from 127.0.0.1) > > If I run psql locally, when executed without switches, > it responds instantly (pg_hba.conf has an entry with > "ident sameuser" for local connections). > > When executed with the -h localhost switch, it takes > exactly 30 seconds to prompt me for the password, > and once I enter the password, it takes another > 30 seconds (*exactly* 30 seconds). > > The pg_hba.conf files are identical (in fact, *really* > identical, as I discovered that TCP/IP connections > to the same machine are not seen by the server as > coming from 127.0.0.1, but coming from the other > IP address -- the "public" one; so I had to add > each machine's own addresses to the pg_hba.conf). > > It doesn't look like it is RedHat's version making a > difference -- two of the machines have 7.3, and on > one of them, everything runs instantly)... Hmmm, > the compiler seems to be the only difference > between the two RH7.3 machines. > > I doubt it has to do with firewalls (for one, the > connections are passing -- ipchains firewalls do not > simply add a delay; but also, it was working without > any delay before upgrading to 7.4.1) > > Any ideas of what this could be? Any extra validations > or constraints in the new server-to-client protocol? > Those 30 seconds sound like a timeout of some sort, > but whatever it may be, it was not affecting the > previous setup, so I can't figure out what it could > be that I need to change. > > > Thanks for any ideas! > > Carlos > -- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > (... have gone from d-i being barely usable even by its developers > anywhere, to being about 20% done. Sweet. And the last 80% usually takes > 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce
Attachment
Carlos Moreno <moreno@mochima.com> writes: > On the dual Athlon (Murphy had to do it in a way > that maximizes the irony, of course! :-)), connections > through TCP/IP take forever! Even if they're local > connections (i.e., even if they come from 127.0.0.1) > > If I run psql locally, when executed without switches, > it responds instantly (pg_hba.conf has an entry with > "ident sameuser" for local connections). > > When executed with the -h localhost switch, it takes > exactly 30 seconds to prompt me for the password, > and once I enter the password, it takes another > 30 seconds (*exactly* 30 seconds). One possibility (though I'm a bit dubious about it in your case) is a reverse DNS lookup on the server side that is timing out. You might do a $ dig -x client.ip.address on the server and see how long it takes. -Doug
> One possibility (though I'm a bit dubious about it in your case) is a > reverse DNS lookup on the server side that is timing out. You might > do a > > $ dig -x client.ip.address > > on the server and see how long it takes. Wow!! You guys are really good! (same goes for Martijn) After doing some more testing, I actually reached this same conclusion. I contacted our hosters to follow-up on this, but the problem was solved (temporarily) by putting the three hosts in the /etc/hosts file of each of the machines. I do find it strange that reverse DNS lookups are required under normal conditions (I mean, I did telnet 1.2.3.4 22 and it would take a while -- after I included the host name and its IP address 1.2.3.4 in the /etc/hosts, the delay just went away). Don't understand why this happens, but in any case it would seem like PostgreSQL's communications protocol has nothing to do with the problem. Thanks! Carlos --
Carlos Moreno <moreno@mochima.com> writes: > I do find it strange that reverse DNS lookups are required > under normal conditions. It's generally either: 1) Desire to log the connection's DNS name as well as the IP address, or 2) A "paranoia check" that the domain name (obtained by reverse lookup) resolves to the same IP address that the connection is coming from. Or both, of course. -Doug
Doug McNaught <doug@mcnaught.org> writes:
> Carlos Moreno <moreno@mochima.com> writes:
>> I do find it strange that reverse DNS lookups are required
>> under normal conditions.
> It's generally either:
> 1) Desire to log the connection's DNS name as well as the IP address, or
AFAIK, PG will not do a reverse DNS lookup unless you have asked it to
by setting "log_hostname" true. If you have not done that, this needs
closer investigation.
regards, tom lane