By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,852 Members | 2,198 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,852 IT Pros & Developers. It's quick & easy.

PostgreSQL 7.4.1 incredibly slow :-(

P: n/a

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

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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 <kl*****@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


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/+LlcY5Twig3Ge+YRAlykAJ9EkjFOJAgBVj8uHXpWgvhxb6LAJA CfQ2jG
aoUx3iRtagBrQq6oYDR5vMY=
=eFQn
-----END PGP SIGNATURE-----

Nov 12 '05 #2

P: n/a
Carlos Moreno <mo****@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3

P: n/a
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
--
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #4

P: n/a
Carlos Moreno <mo****@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #5

P: n/a
Doug McNaught <do**@mcnaught.org> writes:
Carlos Moreno <mo****@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

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

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.