Postgres与PHP的连接速度很慢

问题描述：

我从php的Postgres服务器连接有点麻烦。我刚刚开始使用Postgres + PHP组合，并且我意识到连接建立非常慢。
通常需要1秒或有时超过2秒才能进行简单连接。它只是一个开发服务器，所以没有真正的流量。好的，服务器不是最好的，但MySQL连接速度更快。Postgres与PHP的连接速度很慢

连接后，一切顺利，每个查询运行我期望的。应用程序运行时间约为10％，连接约为90％。真的很奇怪，因为使用mysql数据库层的速度非常快。

可能是什么问题？

我试着用PDO，pg_pconnect，pg_connect，但每次结果都是一样的。

它可以是一个Postgres配置错误？但查询运行速度很快，只有连接建立很慢。我不知道。

PG：在PostgreSQL 8.3.9
PHP：5.2.6

预先感谢您！

配置：

#------------------------------------------------------------------------------ 
# 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

你能告诉我们你的集群的连接设置吗？ http://www.postgresql.org/docs/current/static/runtime-config-connection.html SSL连接速度慢得多，这可能是（部分）问题。 – 2010-07-29 07:29:46

嘿，我编辑了我的帖子。正如我所看到的，ssl是真的，所以也许我们应该尝试没有ssl = true？ – 2010-07-29 08:40:11

除非您正在开发SSL，否则肯定会在开发服务器上禁用它。 – 2010-07-29 13:21:27

答

如果您不使用SSL，请务必关闭SSL。如果您想使用SSL，请确保pg_hba.conf具有适当的设置以要求安全连接。

恕我直言，大多数人不需要SSL，应该关闭它。尽管如此，Debian默认它处于开启状态。 – araqnid 2010-07-29 17:45:14

是的，这个dev服务器有debian lenny，默认情况下pg ssl是打开的。 – 2010-07-30 06:28:00

答

如果服务器是由同一个服务器上，尝试配置您的连接使用Unix套接字而非TCP堆栈。很可能您的延迟是由于Postgres服务器中的反向DNS解析或认证延迟导致的，所以如果Unix套接字不是一个选项，我将启用postgres上的调试日志记录并查看发生了什么。

Postgres与PHP的连接速度很慢

相关推荐