php mysql_connect VS mysql_pconnect

mysql_connect VS mysql_pconnect

提问by rogeriopvl

I have this doubt, I've searched the web and the answers seem to be diversified. Is it better to use mysql_pconnect over mysql_connect when connecting to a database via PHP? I read that pconnect scales much better, but on the other hand, being a persistent connection... having 10 000 connections at the same time, all persistent, doesn't seem scalable to me.

我有这个疑问，我在网上搜索过，答案似乎多种多样。通过 PHP 连接到数据库时，使用 mysql_pconnect 而不是 mysql_connect 更好吗？我读到 pconnect 的扩展性要好得多，但另一方面，作为一个持久连接......同时拥有 10 000 个连接，所有连接都是持久的，对我来说似乎不可扩展。

Thanks in advance.

提前致谢。

回答by Bill Karwin

Persistent connections should be unnecessary for MySQL. In other databases (such as Oracle), making a connection is expensive and time-consuming, so if you can re-use a connection it's a big win. But those brands of database offer connection pooling, which solves the problem in a better way.

MySQL 应该不需要持久连接。在其他数据库（如 Oracle）中，建立连接既昂贵又耗时，因此如果您可以重用连接，那就是大获全胜。但是那些品牌的数据库提供了连接池，更好的解决了这个问题。

Making a connection to a MySQL database is quick compared to those other brands, so using persistent connections gives proportionally less benefit for MySQL than it would for another brand of database.

与其他品牌相比，连接到 MySQL 数据库的速度更快，因此使用持久连接对 MySQL 的好处比其他品牌的数据库要少。

Persistent connections have a downside too. The database server allocates resources to each connection, whether the connections are needed or not. So you see a lot of wasted resources for no purpose if the connections are idle. I don't know if you'll reach 10,000 idle connections, but even a couple of hundred is costly.

持久连接也有缺点。数据库服务器为每个连接分配资源，而不管这些连接是否需要。因此，如果连接空闲，您会看到大量无用的资源浪费。我不知道您是否会达到 10,000 个空闲连接，但即使是几百个也是昂贵的。

Connections have state, and it would be inappropriate for a PHP request to "inherit" information from a session previously used by another PHP request. For example, temporary tables and user variables are normally cleaned up as a connection closes, but not if you use persistent connections. Likewise session-based settings like character set and collation. Also, LAST_INSERT_ID()would report the id last generated during the session -- even if that was during a prior PHP request.

连接有状态，PHP 请求从先前由另一个 PHP 请求使用的会话中“继承”信息是不合适的。例如，临时表和用户变量通常在连接关闭时被清除，但如果您使用持久连接则不会。同样基于会话的设置，如字符集和排序规则。此外，LAST_INSERT_ID()会报告会话期间最后生成的 id——即使那是在先前的 PHP 请求期间。

For MySQL at least, the downside of persistent connections probably outweighs their benefits. And there are other, better techniques to achieve high scalability.

至少对于 MySQL 而言，持久连接的缺点可能超过了它们的好处。还有其他更好的技术来实现高可扩展性。

---

---

Update March 2014:

2014 年 3 月更新：

MySQL connection speed was always low compared to other brands of RDBMS, but it's getting even better.

MySQL 的连接速度与其他品牌的 RDBMS 相比一直较低，但它正在变得更好。

See http://mysqlserverteam.com/improving-connectdisconnect-performance/

请参阅http://mysqlserverteam.com/improving-connectdisconnect-performance/

In MySQL 5.6 we started working on optimizing the code handling connects and disconnects. And this work has accelerated in MySQL 5.7. In this blog post I will first show the results we have achieved and then describe what we have done to get them.

在 MySQL 5.6 中，我们开始致力于优化代码处理连接和断开连接。而这项工作在 MySQL 5.7 中得到了加速。在这篇博文中，我将首先展示我们取得的成果，然后描述我们为取得这些成果所做的工作。

Read the blog for more details and speed comparisons.

阅读博客了解更多详细信息和速度比较。

回答by staticsan

Basically you have to balance the cost of creating connections versus keeping connections. Even though MySQL is very fast at setting up a new connection, it still costs -- in thread setup time, and in TCP/IP setup time from your web server. This is noticeable on a high-enough traffic site. Unfortunately, PHP does not have any controls on the persistence of connections. So the answer is to lower the idle timeout in MySQL a long way (like down to 20 seconds), and to up the thread cache size. Together, this generally works remarkably well.

基本上，您必须平衡创建连接和保持连接的成本。尽管 MySQL 建立新连接的速度非常快，但它仍然需要花费——在线程设置时间和来自 Web 服务器的 TCP/IP 设置时间上。这在流量足够高的网站上很明显。不幸的是，PHP 对连接的持久性没有任何控制。所以答案是大幅降低 MySQL 中的空闲超时（比如降低到 20 秒），并增加线程缓存大小。总之，这通常非常有效。

On the flip side, your application needs to respect the state of the connection. It is best if it makes no assumptions about what state the session is in. If you use temporary tables, then using CREATE IF NOT EXISTS and TRUNCATE TABLE helps a lot, as does naming them uniquely (such as including as userid). Transactions are bit more problematic; but your code can always do ROLLBACK at the top, just in case.

另一方面，您的应用程序需要尊重连接状态。最好不要假设会话处于什么状态。如果您使用临时表，那么使用 CREATE IF NOT EXISTS 和 TRUNCATE TABLE 会有很大帮助，就像唯一命名它们（例如包含为 userid）一样。事务有点问题；但是您的代码总是可以在顶部执行 ROLLBACK，以防万一。

回答by Phoenix

mysql_connect()and mysql_pconnect()both are working for database connection but with little difference. In mysql_pconnect(), pstands for persistance connection.

mysql_connect()并且mysql_pconnect()两者都适用于数据库连接，但几乎没有区别。在 中mysql_pconnect()，p代表持久连接。

When we are using mysql_connect()function, every time it is opening and closing the database connection, depending on the request.

当我们使用mysql_connect()函数时，每次打开和关闭数据库连接，取决于请求。

But in case of mysql_pconnect()function:

但在mysql_pconnect()功能的情况下：

• First, when connecting, the function would try to find a (persistent) connection that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

• Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the connection will remain open for future use (mysql_close()will not close connection established by mysql_pconnect()).

• 首先，在连接时，该函数会尝试查找已使用相同主机、用户名和密码打开的（持久）连接。如果找到，将返回它的标识符而不是打开新连接。

• 其次，脚本执行结束时不会关闭与 SQL 服务器的连接。相反，该连接将保持打开状态以备将来使用（mysql_close()不会关闭由 建立的连接mysql_pconnect()）。

mysql_pconncet()is useful when you have a lot of traffice on your site. At that time for every request it will not open a connection but will take it from the pool. This will increase the efficiency of your site. But for general use mysql_connect() is best.

mysql_pconncet()当您的网站上有大量流量时非常有用。那时对于每个请求，它不会打开连接，而是从池中获取它。这将提高您网站的效率。但是对于一般使用 mysql_connect() 是最好的。

回答by Vinko Vrsalovic

It's very unlikely that you'll reach 10000 connections. Anyhow, go to the official source. (Emphasis mine).

您不太可能达到 10000 个连接。无论如何，去官方来源。（强调我的）。

If persistent connections don't have any added functionality, what are they good for?

The answer here is extremely simple -- efficiency. Persistent connections are good if the overhead to create a link to your SQL server is high. Whether or not this overhead is really high depends on many factors. Like, what kind of database it is, whether or not it sits on the same computer on which your web server sits, how loaded the machine the SQL server sits on is and so forth. The bottom line is that if that connection overhead is high, persistent connections help you considerably. They cause the child process to simply connect only once for its entire lifespan, instead of every time it processes a page that requires connecting to the SQL server. This means that for every child that opened a persistent connection will have its own open persistent connection to the server. For example, if you had 20 different child processes that ran a script that made a persistent connection to your SQL server, you'd have 20 different connections to the SQL server, one from each child.

Note, however, that this can have some drawbacks if you are using a database with connection limits that are exceeded by persistent child connections. If your database has a limit of 16 simultaneous connections, and in the course of a busy server session, 17 child threads attempt to connect, one will not be able to. If there are bugs in your scripts which do not allow the connections to shut down (such as infinite loops), the database with only 16 connections may be rapidly swamped. Check your database documentation for information on handling abandoned or idle connections.

如果持久连接没有任何附加功能，它们有什么用？

这里的答案非常简单——效率。如果创建到 SQL 服务器的链接的开销很高，则持久连接是很好的。这种开销是否真的很高取决于许多因素。例如，它是什么类型的数据库，它是否位于您的 Web 服务器所在的同一台计算机上，SQL 服务器所在的计算机的负载情况等等。最重要的是，如果连接开销很高，持久连接对你有很大帮助. 它们导致子进程在其整个生命周期内仅连接一次，而不是每次处理需要连接到 SQL 服务器的页面时。这意味着对于每个打开持久连接的子进程，都将拥有自己的到服务器的打开的持久连接。例如，如果您有 20 个不同的子进程运行与 SQL 服务器建立持久连接的脚本，那么您将有 20 个不同的连接到 SQL 服务器，每个子进程一个。

但是请注意，如果您使用的数据库的连接限制超出了持久子连接的限制，则这可能会有一些缺点。如果您的数据库限制为 16 个同时连接，并且在繁忙的服务器会话过程中，有 17 个子线程尝试连接，将无法连接。如果您的脚本中存在不允许关闭连接的错误（例如无限循环），那么只有 16 个连接的数据库可能会迅速被淹没。检查您的数据库文档以获取有关处理废弃或空闲连接的信息。

回答by Pritty M

MYSQL_CONNECT()

MYSQL_CONNECT()

1.mysql_connect can be used to close the connection.Every time it is opening and closing the database connection, depending on the request .

1.mysql_connect 可以用来关闭连接。每次打开和关闭数据库连接，取决于请求。

2.Here database is opened everytime when the page is loaded in MYSQL connect

2.每次在MYSQL connect中加载页面时都会打开这里的数据库

3.When the page is loaded, the database is loaded everytime

3.页面加载时，每次都加载数据库

4.It is used to close the connection

4.用于关闭连接

Example:

例子：

<?php $conn = mysql_connect(‘host', ‘mysql_user', ‘mysql_password'); if(!$conn){ die(‘Could not connect: ‘ . mysql_error()); } echo ‘Connected successfully'; mysql_close($conn); ?>

Description:

描述：

host: Specifies a host name or an IP address like localhost.

host：指定主机名或 IP 地址，如 localhost。

mysql_user: Specifies the MySQL username

mysql_user：指定 MySQL 用户名

mysql_password: Specifies the MySQL password

mysql_password：指定 MySQL 密码

MYSQL_PCONNECT()

MYSQL_PCONNECT()

1.We use the mysql_pconncet(), it initially tries to find an open persistent connection.

1.我们使用mysql_pconncet()，它最初尝试寻找打开的持久连接。

2.The mysql_pconncet() opens persistant connection

2.mysql_pconncet()打开持久连接

3.The mysql_pconnect() does not support the close connection

3.mysql_pconnect()不支持关闭连接

4.mysql_pconnect() cannot close the connection. Here open a persistant connection to the database

4.mysql_pconnect() 无法关闭连接。这里打开一个到数据库的持久连接

5.Here database need not be connected everytime.

5.这里不需要每次都连接数据库。

6.The database need not be connected every time in mysql_pconncet().

6.mysql_pconncet()中不需要每次都连接数据库。

more details:http://prittytimes.com/difference-between-mysql_connect-and-mysql_pconnect/

更多详情：http: //prittytimes.com/difference-between-mysql_connect-and-mysql_pconnect/