php 在 PDO 中使用持久连接的缺点是什么

What are the disadvantages of using persistent connection in PDO

提问by MD Sayem Ahmed

In PDO, a connection can be made persistent using the PDO::ATTR_PERSISTENTattribute. According to the php manual -

在 PDO 中，可以使用该PDO::ATTR_PERSISTENT属性使连接持久化。根据 php 手册 -

Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

持久连接不会在脚本结束时关闭，而是在另一个脚本使用相同凭据请求连接时被缓存和重新使用。持久连接缓存允许您避免每次脚本需要与数据库通信时建立新连接的开销，从而使 Web 应用程序更快。

The manual also recommends not to use persistent connection while using PDO ODBC driver, because it may hamper the ODBC Connection Pooling process.

该手册还建议在使用 PDO ODBC 驱动程序时不要使用持久连接，因为它可能会妨碍 ODBC 连接池过程。

So apparently there seems to be no drawbacks of using persistent connection in PDO, except in the last case. However., I would like to know if there is any other disadvantages of using this mechanism, i.e., a situation where this mechanism results in performance degradation or something like that.

显然，在 PDO 中使用持久连接似乎没有任何缺点，除了最后一种情况。但是，我想知道使用这种机制是否还有其他缺点，即这种机制导致性能下降或类似的情况。

回答by Charles

Please be sure to read this answer below, which details ways to mitigate the problems outlined here.

请务必阅读下面的答案，其中详细介绍了缓解此处概述的问题的方法。

---

---

The same drawbacks exist using PDO as with any other PHP database interface that does persistent connections: if your script terminates unexpectedly in the middle of database operations, the next request that gets the left over connection will pick up where the dead script left off. The connection is held open at the process manager level (Apache for mod_php, the current FastCGI process if you're using FastCGI, etc), not at the PHP level, and PHP doesn't tell the parent process to let the connection die when the script terminates abnormally.

使用 PDO 与使用任何其他执行持久连接的 PHP 数据库接口存在相同的缺点：如果您的脚本在数据库操作中间意外终止，获得剩余连接的下一个请求将在死脚本停止的地方继续。连接在进程管理器级别（用于 mod_php 的 Apache，如果您使用的是 FastCGI 的当前 FastCGI 进程等）保持打开状态，而不是在 PHP 级别，并且 PHP 不会告诉父进程让连接在脚本异常终止。

If the dead script locked tables, those tables will remain locked until the connection dies or the next script that gets the connection unlocks the tables itself.

如果死脚本锁定了表，则这些表将保持锁定状态，直到连接终止或下一个获得连接的脚本自行解锁表。

If the dead script was in the middle of a transaction, that can block a multitude of tables until the deadlock timer kicks in, and even then, the deadlock timer can kill the newer request instead of the older request that's causing the problem.

如果死脚本在事务中间，则可能会阻塞多个表，直到死锁计时器启动，即使如此，死锁计时器也会终止较新的请求，而不是导致问题的较旧的请求。

If the dead script was in the middle of a transaction, the next script that gets that connection also gets the transaction state. It's very possible (depending on your application design) that the next script might not actually ever try to commit the existing transaction, or will commit when it should not have, or roll back when it should not have.

如果死脚本在事务中间，则获取该连接的下一个脚本也会获取事务状态。很有可能（取决于您的应用程序设计）下一个脚本实际上可能不会尝试提交现有事务，或者在不应该提交的时候提交，或者在不应该提交的时候回滚。

This is only the tip of the iceberg. It can all be mitigated to an extent by always trying to clean up after a dirty connection on every single script request, but that can be a pain depending on the database. Unless you have identified creating database connections as the one thing that is a bottleneckin your script (this means you've done code profiling using xdebugand/or xhprof), you should notconsider persistent connections as a solution to anything.

这只是冰山一角。通过始终尝试在每个脚本请求的脏连接后进行清理，都可以在一定程度上缓解这种情况，但这取决于数据库，这可能会很痛苦。除非你已经确定了创建数据库连接为一两件事，是一个瓶颈在您的脚本（这意味着你所做的代码中使用剖析了XDebug和/或XHProf的），你应该不考虑作为解决任何持久连接。

Further, most modern databases (including PostgreSQL) have their own preferred ways of performing connection pooling that don't have the immediate drawbacks that plain vanilla PHP-based persistent connections do.

此外，大多数现代数据库（包括 PostgreSQL）都有自己的首选方式来执行连接池，这些方式没有基于普通 PHP 的持久连接的直接缺点。

---

---

To clarify a point, we use persistent connections at my workplace, but not by choice. We were encountering weirdconnection behavior, where the initial connection from our app server to our database server was taking exactlythree seconds, when it should have taken a fraction of a fraction of a second. We think it's a kernel bug. We gave up trying to troubleshoot it because it happened randomly and could not be reproduced on demand, and our outsourced IT didn't have the concrete ability to track it down.

为了澄清一点，我们在我的工作场所使用持久连接，但不是自愿的。我们遇到了奇怪的连接行为，从我们的应用程序服务器到我们的数据库服务器的初始连接只用了三秒钟，而本应只用了几分之一秒。我们认为这是一个内核错误。我们放弃了对它进行故障排除的尝试，因为它是随机发生的，无法按需重现，而且我们的外包 IT 没有具体的追踪能力。

Regardless, when the folks in the warehouse are processing a few hundred incoming parts, and each part is taking three and a half seconds instead of a half second, we had to take action before they kidnapped us all and made us help them. So, we flipped a few bits on in our home-grown ERP/CRM/CMS monstrosity and experienced all of the horrors of persistent connections first-hand. It took us weeksto track down all the subtle little problems and bizarre behavior that happened seemingly at random. It turned out that those once-a-week fatal errors that our users diligently squeezed out of our app were leaving locked tables, abandoned transactions and other unfortunate wonky states.

无论如何，当仓库里的人正在处理几百个进货零件时，每个零件都需要三秒半而不是半秒，我们必须在他们绑架我们所有人并让我们帮助他们之前采取行动。因此，我们在我们自己开发的 ERP/CRM/CMS 怪物中翻转了一些，并亲身体验了持久连接的所有恐怖。我们花了数周时间来追踪看似随机发生的所有微妙的小问题和奇怪的行为。事实证明，我们的用户努力从我们的应用程序中挤出的那些每周一次的致命错误正在离开锁定的表、被放弃的交易和其他不幸的不稳定状态。

This sob-story has a point: It broke things that we never expected to break, all in the name of performance.The tradeoff wasn't worth it, and we're eagerly awaiting the day we can switch back to normal connections without a riot from our users.

这个悲伤的故事有一个观点：它以性能的名义打破了我们从未预料到的东西。这种权衡是不值得的，我们热切地等待着我们可以在没有用户骚动的情况下切换回正常连接的那一天。

回答by Prashant

In response to Charles' problem above,

针对上面查尔斯的问题，

From : http://www.php.net/manual/en/mysqli.quickstart.connections.php-

来自：http: //www.php.net/manual/en/mysqli.quickstart.connections.php-

A common complain about persistent connections is that their state is not reset before reuse. For example, open and unfinished transactions are not automatically rolled back. But also, authorization changes which happened in the time between putting the connection into the pool and reusing it are not reflected. This may be seen as an unwanted side-effect. On the contrary, the name persistent may be understood as a promise that the state is persisted.

关于持久连接的一个常见抱怨是它们的状态在重用之前没有重置。例如，未完成和未完成的事务不会自动回滚。而且，在将连接放入池中和重用它之间的时间内发生的授权更改也没有反映。这可能被视为不需要的副作用。相反，名称persistent 可以理解为状态被持久化的承诺。

The mysqli extension supports both interpretations of a persistent connection: state persisted, and state reset before reuse. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli_change_user()to reset the state. The persistent connection appears to the user as if it was just opened. No artifacts from previous usages are visible.

mysqli 扩展支持持久连接的两种解释：状态持久化和重用前状态重置。默认为重置。在重用持久连接之前，mysqli 扩展会隐式调用mysqli_change_user()重置状态。持久连接在用户看来就像刚打开一样。看不到以前使用的任何工件。

The mysqli_change_user()function is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECTbeing set.

该mysqli_change_user()功能是一项昂贵的操作。为了获得最佳性能，用户可能希望MYSQLI_NO_CHANGE_USER_ON_PCONNECT在设置了编译标志的情况下重新编译扩展。

It is left to the user to choose between safe behavior and best performance. Both are valid optimization goals. For ease of use, the safe behavior has been made the default at the expense of maximum performance.

由用户在安全行为和最佳性能之间进行选择。两者都是有效的优化目标。为了便于使用，以牺牲最大性能为代价将安全行为设为默认值。

回答by Jhonathan H.

Persistent connections are a good idea only when it takes a (relatively) long time to connect to your database. Nowadays that's almost never the case. The biggest drawback to persistent connections is that it limits the number of users you can have browsing your site: if MySQL is configured to only allow 10 concurrent connections at once then when an 11th person tries to browse your site it won't work for them.

仅当连接到数据库需要（相对）较长的时间时，持久连接才是一个好主意。如今，这种情况几乎从未发生过。持久连接的最大缺点是它限制了您可以浏览站点的用户数量：如果 MySQL 配置为一次只允许 10 个并发连接，那么当第 11 个人尝试浏览您的站点时，它对他们不起作用.

PDO does not manage the persistence. The MySQL driver does. It reuses connections when a) they are available and the host/user/password/database match. If any change then it will not reuse a connection. The best case net effect is that these connections you have will be started and stopped so often because you have different users on the site and making them persistent doesn't do any good.

PDO 不管理持久性。MySQL 驱动程序可以。当 a) 连接可用并且主机/用户/密码/数据库匹配时，它会重用连接。如果有任何更改，则不会重用连接。最好的情况是，您拥有的这些连接会经常启动和停止，因为站点上有不同的用户，让他们持久化并没有任何好处。

The key thing to understand about persistent connections is that you should NOT use them in most web applications. They sound enticing but they are dangerous and pretty much useless.

理解持久连接的关键是你不应该在大多数 Web 应用程序中使用它们。它们听起来很诱人，但它们很危险，而且几乎毫无用处。

I'm sure there are other threads on this but a persistent connection is dangerous because it persists between requests. If, for example, you lock a table during a request and then fail to unlock then that table is going to stay locked indefinitely. Persistent connections are also pretty much useless for 99% of your apps because you have no way of knowing if the same connection will be used between different requests. Each web thread will have it's own set of persistent connections and you have no way of controlling which thread will handle which requests.

我确信在这方面还有其他线程，但是持久连接很危险，因为它在请求之间持续存在。例如，如果您在请求期间锁定一个表，然后无法解锁，那么该表将无限期地保持锁定状态。对于 99% 的应用程序来说，持久连接也几乎毫无用处，因为您无法知道不同请求之间是否会使用相同的连接。每个 Web 线程都有自己的一组持久连接，您无法控制哪个线程将处理哪些请求。

The procedural mysql library of PHP, has a feature whereby subsequent calls to mysql_connect will return the same link, rather than open a different connection (As one might expect). This has nothing to do with persistent connections and is specific to the mysql library. PDO does not exhibit such behaviour

PHP 的程序 mysql 库具有一项功能，即对 mysql_connect 的后续调用将返回相同的链接，而不是打开不同的连接（正如人们所期望的）。这与持久连接无关，并且特定于 mysql 库。PDO 不会表现出这种行为

---

---

Resource Link : link

资源链接：链接

In General you could use this as a rough "ruleset"::

一般来说，您可以将其用作粗略的“规则集”::

YES, use persistent connections, if:

是，使用持久连接，如果：

• There are only few applications/users accessing the database, i.e. you will not result in 200 open (but probably idle) connections, because there are 200 different users shared on the same host.

• The database is running on another server that you are accessing over the network

• An (one) application accesses the database very often

• 只有少数应用程序/用户访问数据库，即不会导致 200 个打开（但可能空闲）连接，因为在同一主机上共享 200 个不同的用户。

• 数据库正在您通过网络访问的另一台服务器上运行

• 一个（一个）应用程序经常访问数据库

NO, don't use persistent connections, if:

NO，不要使用持久连接，如果：

• Your application only needs to access the database 100 times an hour.

• You have many, many webservers accessing one database server

• 您的应用程序每小时只需访问数据库 100 次。

• 您有许多网络服务器访问一台数据库服务器

Using persistent connections is considerable faster, especially if you are accessing the database over a network. It doesn't make so much difference if the database is running on the same machine, but it is still a little bit faster. However - as the name says - the connection is persistent, i.e. it stays open, even if it is not used.

使用持久连接要快得多，尤其是当您通过网络访问数据库时。如果数据库在同一台机器上运行并没有太大区别，但它仍然要快一点。然而 - 正如名称所说 - 连接是持久的，即它保持打开状态，即使它没有被使用。

The problem with that is, that in "default configuration", MySQL only allows 1000 parallel "open channels". After that, new connections are refused (You can tweak this setting). So if you have - say - 20 Webservers with each 100 Clients on them, and every one of them has just one page access per hour, simple math will show you that you'll need 2000 parallel connections to the database. That won't work.

问题在于，在“默认配置”中，MySQL 只允许 1000 个并行“开放通道”。之后，新连接将被拒绝（您可以调整此设置）。因此，如果您有 - 比如说 - 20 个 Web 服务器，每个服务器上有 100 个客户端，并且每个服务器每小时只能访问一个页面，那么简单的数学计算就会告诉您，您需要与数据库建立 2000 个并行连接。那行不通。

Ergo: Only use it for applications with lots of requests.

Ergo：仅将其用于有大量请求的应用程序。

回答by Gunnar Bernstein

On my tests I had a connection time of over a second to my localhost, thus assuming I should use a persistent connection. Further tests showed it was a problem with 'localhost':

在我的测试中，我与本地主机的连接时间超过一秒，因此假设我应该使用持久连接。进一步的测试表明这是'localhost'的问题：

Test results in seconds (measured by php microtime):

以秒为单位的测试结果（由 php microtime 测量）：

• hosted web: connectDB: 0.0038912296295166
• localhost: connectDB: 1.0214691162109 (over one second: do not use localhost!)
• 127.0.0.1: connectDB: 0.00097203254699707

• 托管网站：connectDB：0.0038912296295166
• 本地主机：connectDB：1.0214691162109（超过一秒：不要使用本地主机！）
• 127.0.0.1：连接数据库：0.00097203254699707

Interestingly: The following code is just as fast as using 127.0.0.1:

有趣的是：以下代码与使用 127.0.0.1 一样快：

$host = gethostbyname('localhost');
// echo "<p>$host</p>";
$db = new PDO("mysql:host=$host;dbname=" . DATABASE . ';charset=utf8', $username, $password,
    array(PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

回答by Stephen

Persistent connections should give a sizable performance boost. I disagree with the assement that you should "Avoid" persistence..

持久连接应该会带来相当大的性能提升。我不同意你应该“避免”坚持的说法。

It sounds like the complaints above are driven by someone using MyIASM tables and hacking in their own versions of transactions by grabbing table locks.. Well of course you're going to deadlock! Use PDO's beginTransaction() and move your tables over to InnoDB..

听起来上面的抱怨是由某人使用 MyIASM 表并通过抓取表锁来破解他们自己的事务版本所引起的。当然，你会陷入僵局！使用 PDO 的 beginTransaction() 并将您的表移动到 InnoDB ..

回答by Crayon Violent

seems to me having a persistent connection would eat up more system resources. Maybe a trivial amount, but still...

在我看来，持久连接会消耗更多系统资源。也许是微不足道的数量，但仍然......

回答by Tony Stark

The explanation for using persistent connections is obviously reducing quantity of connects that are rather costly, despite the fact that they're considerably faster with MySQL compared to other databases.

使用持久连接的解释显然是减少了相当昂贵的连接数量，尽管与其他数据库相比，它们在 MySQL 中要快得多。

The very first trouble with persistent connections...

持久连接的第一个麻烦...

If you are creating 1000's of connections per second you normally don't ensure that it stays open for very long time, but Operation System does. Based on TCP/IP protocol Ports can't be recycled instantly and also have to invest a while in “FIN” stage waiting before they may be recycled.

如果您每秒创建 1000 个连接，您通常不会确保它保持打开很长时间，但操作系统可以。基于 TCP/IP 协议的端口不能立即回收，还必须在“FIN”阶段投入一段时间等待才能回收。

The 2nd problem... using a lot of MySQL server connections.

第二个问题...使用大量 MySQL 服务器连接。

Many people simply don't realize you are able to increase *max_connections* variable and obtain over 100 concurrent connections with MySQL others were beaten by older Linux problems of the inability to convey more than 1024 connections with MySQL.

许多人根本没有意识到您可以增加 *max_connections* 变量并获得超过 100 个与 MySQL 的并发连接，其他人被旧的 Linux 问题打败，即无法与 MySQL 传送超过 1024 个连接。

Allows talk now about why Persistent connections were disabled in mysqli extension. Despite the fact that you can misuse persistent connections and obtain poor performance which was not the main reason. The actual reason is – you can get a lot more issues with it.

现在允许讨论为什么在 mysqli 扩展中禁用持久连接。尽管您可能会滥用持久连接并获得较差的性能，但这并不是主要原因。真正的原因是——你可能会遇到更多问题。

Persistent connections were put into PHP throughout occasions of MySQL 3.22/3.23 when MySQL was not so difficult which means you could recycle connections easily with no problems. In later versions quantity of problems however came about – Should you recycle connection that has uncommitted transactions you take into trouble. If you recycle connections with custom character set configurations you're in danger again, as well as about possibly transformed per session variables.

在 MySQL 3.22/3.23 期间，当 MySQL 不是那么困难时，持久连接被放入 PHP，这意味着您可以轻松地回收连接，没有问题。然而，在以后的版本中出现了大量问题——如果您回收具有未提交事务的连接，您就会遇到麻烦。如果您使用自定义字符集配置回收连接，您将再次处于危险之中，并且每个会话变量可能会发生转换。

One trouble with using persistent connections is it does not really scale that well. For those who have 5000 people connected, you'll need 5000 persistent connections. For away the requirement for persistence, you may have the ability to serve 10000 people with similar quantity of connections because they are in a position to share individuals connections when they are not with them.

使用持久连接的一个问题是它不能很好地扩展。对于有 5000 人连接的用户，您需要 5000 个持久连接。对于持久性的要求，您可能有能力为 10000 人提供相似数量的联系，因为当他们不与他们在一起时，他们能够分享个人联系。

回答by James

I was just wondering whether a partial solution would be to have a pool of use-once connections. You could spend time creating a connection pool when the system is at low usage, up to a limit, hand them out and kill them when either they've completed or timed out. In the background you're creating new connections as they're being taken. At worst case this should only be as slow as creating the connection without the pool, assuming that establishing the link is the limiting factor?

我只是想知道部分解决方案是否是拥有一个一次性连接池。您可以花时间在系统使用率低时创建一个连接池，达到限制，将它们分发出去并在它们完成或超时时终止它们。在后台，您正在创建新的连接。在最坏的情况下，这应该只与在没有池的情况下创建连接一样慢，假设建立链接是限制因素？