The Linux Page

Extremely slow pg_connect() call

Got to setup a new server and first got the firewall to where I wanted it to be:

  • Block everything except ssh, Apache, SMTP, a few other things, but really not much more than that.
  • Block everything with IPv6 since we don't use it.

Then I installed Apache and a couple of websites.

The first one finally started to work, but it was so very slow to show up. I checked the code, the database, nothing wrong... And the database is lightning fast! ( in comparison to our previous server that is.)

So I wondered and thought maybe that my Apache firewall is in the way. I turned it off, tried again, same amount of time to get the page back in my browser (i.e. about 4 minutes, yes, not 4 seconds, I said 4 MINUTES.)

So... if that's not the firewall, what is it?!

I then got the idea that it would be the database, so I decided to try a connection in PHP. I ran something like this:

  php -a
  $p = pg_connect('host=localhost user=foo password=blah dbname=mydb');

And waited about 4 minutes. It worked, but it took 4 minutes!!!

Then, once connected, I could play around and get all the tables dead faster (much faster than our old server!)

This was good, but I thought it would be even better if it were to also connect slightly faster?!

Finally, I thought that the connection using localhost would be culprit:

  psql -h localhost template1

Yes! Gotcha! That was the problem. Using psql as is would be dead fast, but specifying localhost would make it wait for 4 min. just like in PHP.

So?! Why would localhost not respond right? It's is defined in my /etc/hosts as expected...

The fact is, when I looked at the list of servers again, PostgreSQL automatically listen on an IPv6 port if it is available. That was it. It actually wanted to use IPv6 whenever localhost was specified. I guess that can be faster so I decided to authorize the localhost IP on IPv6 instead of closing everything (it is possible to ask PostgreSQL to only listen on IP4.)

Problem solved! Took me over 1 day to get the solution though...