数据库持久连接

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

对 web 服务器的工作和分布负载没有完全理解的读者可能会错误地理解持久连接的作用。特别的,持久连接不会在相同的连接上提供建立“用户会话”的能力,也不提供有效建立事务的能力。实际上,从严格意义上来讲,持久连接不会提供任何非持久连接无法提供的特殊功能。

为什么?

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

第一种方法是将 PHP 用作一个单独运行的语言解释器(CGI Wapper)。以这种方法运行,PHP 会为向 web 服务器提出的每个 PHP 页面请求生成并结束一个 PHP解释器线程。由于该线程会随每个请求的结束而结束,因此任何在这个线程中利用的任何资源(例如指向SQL 数据库服务器的连接)都会随线程的结束而关闭。在这种情况下,使用持久连接和非持久连接没有任何区别——因为PHP脚本本身的执行不是持久的。

第二,也是最常用的方法,是把 PHP 用作多进程 web 服务器的一个模块,这种方法目前只适用于 Apache。对于一个多进程的服务器,其典型特征是有一个父进程和一组子进程协调运行,其中实际生成 web 页面的是子进程。每当客户端向父进程提出请求时,该请求会被传递给还没有被其它的客户端请求占用的子进程。这也就是说当相同的客户端第二次向服务端提出请求时,它将有可能被一个不同的子进程来处理。在开启了一个持久连接后,所有请求 SQL 服务的后继页面都能够重用这个已经建立的 SQL Server 连接。

最后一种方法是将 PHP 用作多线程 web 服务器的一个插件。目前 PHP 4 已经支持 ISAPI、WSAPI 和 NSAPI(在非Windows 环境下),这些使得 PHP 可以被用作诸如 Netscape FastTrack (iPlanet)、Microsoft's Internet Information Server (IIS) 和 O'Reilly's WebSite Pro 等多线程 web 服务器的插件。持久连接的行为和前面所描述的多过程模型在本质上是相同的。注意 PHP 3 不支持 SAPI。

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

答案非常简单——效率。当Web Server创建到SQL服务器的连接耗费(Overhead)较高(如耗时较久,消耗临时内存较多)时,持久连接将更加高效。Overhead高低取决于很多因素。例如,数据库的种类,数据库服务和web 服务是否在同一台服务器上,SQL 服务器负载状况等。当Overhead较高,每次创建数据库连接成本较高时,持久连接将显著的提高效率。它使得每个子进程在其生命周期中只做一次连接操作,而非每次在处理一个页面时都要向SQL 服务器提出连接请求。这也就是说,每个子进程将对服务器建立各自独立的持久连接。例如,如果有 20 个不同的子进程运行某脚本建立了持久的 SQL 服务器持久连接,那么实际上向该 SQL 服务器建立了 20 个不同的持久连接,每个进程占有一个。

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

Warning

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

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

参见 fbsql_pconnect()ibase_pconnect()ifx_pconnect()ingres_pconnect()msql_pconnect()mssql_pconnect()mysql_pconnect()ociplogon()odbc_pconnect()ora_plogon()pfsockopen()pg_pconnect()sybase_pconnect()

add a note

User Contributed Notes 13 notes

up
6
php at alfadog dot net
2 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
5
christopher dot jones at oracle dot com
8 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
3
Tom
6 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
2
ambrish at php dot net
5 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
0
pacerier at gmail dot com
10 months ago
Did anyone else notice that the last paragraph contradicts everything above it?

( cached page: //archive.is/ZAOwy )
up
0
ynzhang from lakeheadu canada
7 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
0
fabio
10 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
-2
RQuadling at GMail dot com
10 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
-4
andy at paradigm-reborn dot com
9 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
whatspaz at g NO dot SPAM mail dot c o m
9 years ago
in response to web at nick, have you tried FLUSH PRIVILEGES. this should reload those privileges.
up
-11
jean_christian at myrealbox dot com
13 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
-19
jorgeleon85 at gmail dot com
5 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.
up
-19
aaryal at foresightint dot com
12 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.