持久数据库连接

持久数据库连接是指在脚本结束运行时不关闭的连接。当收到持久连接的请求时,PHP 将检查是否已经存在相同的持久连接(前面已经开启的)——如果存在,将直接使用这个连接。如果不存在,则建立新的连接。所谓“相同”的连接是指用相同的用户名和密码连接到相同主机的连接。

对 web 服务器的工作方式和负载分配方式没有完全理解可能会错误地理解持久连接。特别是持久连接不会在相同的连接上提供建立“用户会话”的能力,也不提供有效建立事务的能力。实际上,要非常清楚的了解持久连接不会提供任何非持久连接无法实现的功能。

为什么?

这和 web 服务器的工作方式有关。web 服务器可以通过三种方法来利用 PHP 生成 web 页面。

第一种方法是将 PHP 自以为 CGI“包装器”用作一个单独运行的语言解释器(CGI Wapper)。当以这种方法运行时,PHP 会为向 web 服务器的每个 PHP 页面请求创建并销毁 PHP 解释器的时候实例。由于其会随每个请求的结束而销毁,因此其获取的任何资源(例如指向 SQL 数据库服务器的链接)都会在销毁时关闭。在这种情况下,不会从使用持久连接中获得任何好处——因为根本不会持久。

第二,也是最流行的方法是把 PHP 用作多进程 web 服务器的一个模块,这种方法目前只适用于 Apache。多进程的服务器通常有一个父进程和一组子进程协调运行,子进程负责提供网页的工作。每当接收达到客户端提出请求时,该请求会传递给尚未给其它客户端提供服务的某个子进程。这也就是说当相同的客户端第二次向服务端发出请求时,它将有可能由与第一次不同的某个子进程提供服务。在开启了一个持久连接后,所有请求 SQL 服务的后继页面都能够重用与 SQL 服务器建立的相同连接。

最后一种方法是将 PHP 用作多线程 web 服务器的插件。目前 PHP 支持 WSAPI 和 NSAPI(在 Windows 上),允许 PHP 作为 Netscape FastTrack(iPlanet)、Microsoft 的 Internet Information Server (IIS) 和 O'Reilly 的 WebSite Pro 等多线程服务器的插件使用。该行为与前面描述的多过程模型相同。

如果持久连接并没有任何附加的功能,那么使用它有什么好处?

答案非常简单——效率。如果创建链接到 SQL 服务器的开销很高,则持久连接是好的。开销高低取决于很多因素。例如,数据库的类型,数据库服务和 web 服务是否在同一台服务器上,SQL 服务器负载状况等。事实是,如果连接开销很高,持久连接将显著的提高效率。它使得子进程在其整个生命周期中只做一次连接操作,而非每次在处理页面时都要向 SQL 服务器提出连接请求。这意味着,每个打开持久连接的子进程将对服务器建立各自的持久连接。例如,如果有 20 个不同的子进程运行与 SQL 服务器建立持久连接的脚本,那么将有 20 个与 SQL 服务器建立的不同连接,每个进程一个。

注意,如果持久连接的数量超过了数据库设定的连接数限制,系统将会产生一些问题。如果数据库的同时连接数限制为 16,而在繁忙会话的情况下,有 17 个线程试图连接,那么有一个线程将无法连接。如果这个时候,在脚本中出现了不允许关闭连接的错误(例如无限循环),则该数据库的 16 个连接将迅速地受到影响。请查阅数据库文档以获取关于如何处理废弃及空闲连接的信息。

警告

在使用持久连接时还有一些特别的问题需要牢记。例如在持久连接中使用数据表锁时,如果脚本由于某种原因无法释放数据表锁,那么随后使用相同连接的脚本将会被持久的阻塞,可能需要重新启动 httpd 服务或者数据库服务。另外,在使用事务时,如果脚本在事务提交前结束,则该事务也会影响到使用相同连接的下一个脚本。不管在什么情况下,都可以通过使用 register_shutdown_function() 函数来注册一个简单的清理函数来释放数据表锁,或者回滚事务。或者更好的处理方法,是不在使用数据表锁或者事务处理的脚本中使用持久连接,这可以从根本上解决这个问题(当然还可以在其它地方使用持久连接)。

以下是一点重要的总结。持久连接与常规的非持久连接应该是可以互相替换的。即将持久连接替换为非持久连接时,脚本的行为不应该发生改变。使用持久连接只应该改变脚本的效率,不应该改变其行为!

参见 ibase_pconnect()ociplogon()odbc_pconnect()oci_pconnect()pfsockopen()pg_pconnect()

add a note add a note

User Contributed Notes 13 notes

up
16
php at alfadog dot net
10 years ago
One additional not regarding odbc_pconnect  and possibly other variations of pconnect:

If the connection encounters an error (bad SQL, incorrect request, etc), that error will return with  be present in odbc_errormsg for every subsequent action on that connection, even if subsequent actions don't cause another error.

For example:

A script connects with odbc_pconnect.
The connection is created on it's first use.
The script calls a query "Select * FROM Table1".
Table1 doesn't exist and odbc_errormsg contains that error.

Later(days, perhaps), a different script is called using the same parameters to odbc_pconnect.
The connection already exists, to it is reused.
The script calls a query "Select * FROM Table0".
The query runs fine, but odbc_errormsg still returns the error about Table1 not existing.

I'm not seeing a way to clear that error using odbc_ functions, so keep your eyes open for this gotcha or use odbc_connect instead.
up
6
ynzhang from lakeheadu canada
15 years ago
It seems that using pg_pconnect() will not persist the temporary views/tables. So if you are trying to create temporary views/tables with the query results and then access them with the next script of the same session, you are out of luck. Those temporary view/tables are gone after each PHP script ended. One way to get around this problem is to create real view/table with session ID as part of the name and record the name&creation time in a common table. Have a garbage collection script to drop the view/table who's session is expired.
up
3
christopher dot jones at oracle dot com
16 years ago
For the oci8 extension it is not true that " [...] when using transactions, a transaction block will also carry over to the next script which uses that connection if script execution ends before the transaction block does.".  The oci8 extension does a rollback at the end scripts using persistent connections, thus ending the transaction.  The rollback also releases locks. However any ALTER SESSION command (e.g. changing the date format) on a persistent connection will be retained over to the next script.
up
1
Tom
14 years ago
There's a third case for PHP: run on a fastCGI interface. In this case, PHP processes are NOT destroyed after each request, and so persistent connections do persist. Set PHP_FCGI_CHILDREN << mysql's max_connections and you'll be fine.
up
0
ambrish at php dot net
13 years ago
In IBM_DB2 extension v1.9.0 or later performs a transaction rollback on persistent connections at the end of a request, thus ending the transaction. This prevents the transaction block from carrying over to the next request which uses that connection if script execution ends before the transaction block does.
up
-2
pacerier at gmail dot com
8 years ago
Did anyone else notice that the last paragraph contradicts everything above it?

( cached page: https://archive.is/ZAOwy )
up
-1
andy at paradigm-reborn dot com
17 years ago
To those using MySQL and finding a lot of leftover sleeping processes, take a look at MySQL's wait_timeout directive. By default it is set to 8 hours, but almost any decent production server will have been lowered to the 60 second range. Even on my testing server, I was having problems with too many connections from leftover persistent connections.
up
-7
fabio
18 years ago
You can in fact provide a port for the connection, take a look at http://de2.php.net/manual/en/function.mysql-pconnect.php#AEN101879

Just use "hostname:port" for the server address.
up
-10
RQuadling at GMail dot com
18 years ago
If you have multiple databases on the same server AND you are using persistent connections, you MUST prefix all the table names with the specific database name.

Changing the database using the xxx_select_db functions alters the database for the connection for all users who are sharing that connection (assuming PHP is running shared and not CGI/CLI).

If you have 2 databases (live and archive) and your script is talking to both, you cannot use 2 persistent connections and change the database for each one.

Internally, persistent connections are used even if you do not specify that you want to use persistent connections. This is why new_link was added to mysql_connect/mssql_connect (PHPV4.2.0+).
up
-11
whatspaz at g NO dot SPAM mail dot c o m
17 years ago
in response to web at nick, have you tried FLUSH PRIVILEGES. this should reload those privileges.
up
-13
jean_christian at myrealbox dot com
21 years ago
If anyone ever wonders why the number of idle db process (open connections) seems to grow even though you are using persistent connections, here's why:

"You are probably using a multi-process web server such as Apache. Since
database connections cannot be shared among different processes a new
one is created if the request happen to come to a different web server
child process."
up
-22
aaryal at foresightint dot com
20 years ago
this one bit quite a bit of chunk out of my you-know-what. seems like if you're running multiple database servers on the same host (for eg. MySQL on a number of ports) you can't use pconnect since the port number isn't part of the key for database connections. especially if you have the same username and password to connect to all the database servers running on different ports. but then it might be php-MySQL specific. you might get a connection for an entirely different port than the one you asked for.
up
-33
jorgeleon85 at gmail dot com
13 years ago
I've been looking everywhere for a benchmark or at least comparison of the overhead used by oci_connect and oci_pconnect.
Just saying "oci_connect is slower because the overhead..." is not enough for me. For than I wrote a couple scripts to compare perfomance.
At the end I found out an average of 34% more time using a oci_connect than oci_pconnect, using a query of 50 rows and 100 columns.
Obviously this wasn't a real benchmark however it gives a simple idea of the efficiency of each function.
To Top