Postgres connection is slow from PHP

Question

Hello,

I have a little trouble with the Postgres server connection from php. I just started working with Postgres + PHP combo, and I realized that the connection establishment is really slow.
It usually takes 1s or sometimes more than 2 seconds to make a simple connection. And it's only a development server, so there is no real traffic. Ok, the server isn't the best, but MySQL connection is much faster.

After connecting, everything goes well, every query runs how I expect. The application running time is about 10% and the connection is about 90%. Really strange, because with the mysql database layer it is really fast.

What can be the problem?

I've tried with PDO, pg_pconnect, pg_connect, but everytime the result is the same.

It can be a Postgres configuration error? But the queries run fast, only the connection establishment is slow. I have no idea.

PG: PostgreSQL 8.3.9
PHP: 5.2.6

Thank you in advance!

Configuration:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'     # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost', '*' = all
                    # (change requires restart)
port = 5432             # (change requires restart)
max_connections = 100           # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per 
# connection slot, plus lock space (see max_locks_per_transaction).  You might
# also need to raise shared_buffers to support more connections.
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directory = '/var/run/postgresql'       # (change requires restart)
#unix_socket_group = ''         # (change requires restart)
#unix_socket_permissions = 0777     # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour_name = ''          # defaults to the computer name
                    # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min      # 1s-600s
ssl = true              # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'  # allowed SSL ciphers
                    # (change requires restart)
#password_encryption = on
#db_user_namespace = off

# Kerberos and GSSAPI
#krb_server_keyfile = ''        # (change requires restart)
#krb_srvname = 'postgres'       # (change requires restart, Kerberos only)
#krb_server_hostname = ''       # empty string matches any keytab entry
                    # (change requires restart, Kerberos only)
#krb_caseins_users = off        # (change requires restart)
#krb_realm = ''                 # (change requires restart)

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
                    # 0 selects the system default
#tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
                    # 0 selects the system default
#tcp_keepalives_count = 0       # TCP_KEEPCNT;
                    # 0 selects the system default


#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 24MB           # min 128kB or max_connections*16kB
                    # (change requires restart)
#temp_buffers = 8MB         # min 800kB
#max_prepared_transactions = 5      # can be 0 or more
                    # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB             # min 64kB
#maintenance_work_mem = 16MB        # min 1MB
#max_stack_depth = 2MB          # min 100kB

# - Free Space Map -

max_fsm_pages = 153600          # min max_fsm_relations*16, 6 bytes each
                    # (change requires restart)
#max_fsm_relations = 1000       # min 100, ~70 bytes each
                    # (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000       # min 25
                    # (change requires restart)
#shared_preload_libraries = ''      # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0          # 0-1000 milliseconds
#vacuum_cost_page_hit = 1       # 0-10000 credits
#vacuum_cost_page_miss = 10     # 0-10000 credits
#vacuum_cost_page_dirty = 20        # 0-10000 credits
#vacuum_cost_limit = 200        # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms         # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100        # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0      # 0-10.0 multipler on buffers scanned/round

Answer 1

If PostGres is on the same server, try configuring your connection to use Unix sockets instead of TCP stack. Most likely your delay is caused either by reverse DNS resolution or authentication delay in the Postgres server, so if Unix sockets are not an option I would enable debug logging on the postgres and check out what happens there.

Answer 2

Always turn SSL off if you don't use it. And if you want to use SSL, make sure pg_hba.conf has the propper settings to demand a secure connection as well.