在 PHP 中从持久连接切换到非持久连接：我能期待什么？
在过去的几周里，我们多次达到 1500 个 mysql 连接限制。突然之间，Threads_connected 就爆炸了（几分钟内从 300 -> 1500）。
我们的前端服务器 (3x) 使用持久连接来连接到数据库服务器 (1x)。即使线程耗尽，我们的数据库服务器在资源方面（CPU、内存、IO）似乎也表现良好。
我正在考虑在我们的应用程序（cakePHP）中从持久连接切换到非持久连接。我能期待什么？
- MySQL 负载更高？
- 前端服务器负载更高？
- 增加前端服务器的响应时间？
这样做是个好主意，还是应该进一步增加连接限制？
In the last weeks we've reached our mysql connection limit of 1500 several times. All of a sudden the Threads_connected just explodes (300 -> 1500 in just a few minutes).
Our front-end servers (3x) use persistent connections to connect to the database server (1x). Even when running out of threads our database server seems to be doing well resource wise (CPU, Memory, IO).
I'm thinking of switching from persistent to non-persistent connections in our application (cakePHP). What can I expect?
- Higher MySQL load?
- Higher load on fron-end servers?
- Increasing response times on fron-end servers?
Is it a good idea to do so, or should I just increase the connections limit even more?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
由于数据库和应用程序服务器位于不同的计算机上，因此您至少可以预期由于打开新连接而导致脚本执行增加延迟。如果两台服务器都在 LAN 中，则这可能可以忽略不计。
显然，额外的任务会产生额外的负载，但我敢说这也几乎不会被注意到。与处理查询或脚本执行的任何其他操作相比，打开连接的开销很小。
现在，解决手头的真正问题。正如 Mikey 所说，您可能想调查问题的原因。如果使用持久连接，则每个 Web 服务器线程都可以打开并维护一个数据库连接。如果负载出现小幅峰值，连接数量就会大幅增加。对于持久连接和非持久连接来说，问题实际上是相同的，只是非持久连接随后就会消失，并且不会因不必要地占用内存而减慢未来的操作。
一般来说，如果您想保证每个数据库连接都成功，则必须确保您的 mySQL Server 可以处理与您的 Web 服务器接受并发连接一样多的连接。然而，在现实世界的设置中，您的 Web 服务器可能会为与数据库无关的连接提供服务。 （图像、CSS、JavaScript 等）。考虑到这一点，您可以提供比数据库连接更多的 Web 服务线程，但有一个问题，这也可能是问题的根源：
如果有人以与预期不同的方式访问您网站上的大量页面在正常使用模式下，上述规则不适用。例如，如果搜索引擎抓取您的网站。它们处理内容的顺序与浏览器不同。他们可以专注于您的 PHP 页面，打破每个客户端都会加载混合数据的假设。如果这是一个问题，取决于您拥有的页面数量与普通用户数量的比较。如果您的数据库服务器在高负载下速度变慢，从而延迟其他连接的处理，则这种影响也可能是累积的。
因此，不使用持久连接并管理数据库服务器上的内存是有意义的，以便它可以以文件缓存和缓冲区为代价支持接近最大连接数，但恢复正常（更快） ）操作一旦尖峰结束。
PS：确保您了解当所有连接都被利用时数据库将实际使用多少内存。 http://www.mysqlperformanceblog.com/2006/05/ 17/mysql-服务器内存使用/。不要在没有确保拥有所需内存的情况下只增加允许的连接数。尝试几次失败的连接比数据库服务器陷入瘫痪要好，因为它的缓冲区正在被换出。
Since the database and application server are on different machines, you can at the very least expect an added delay in script execution due to opening a new connection. If both servers are in a LAN, that is probably negligible.
Obviously an additional task will create additional load, but I dare say this will also be barely noticeable. Opening a connection compared to processing a query or whatever else your script does, is a tiny amount of overhead.
Now, to the real problem at hand. As mikey said, you may want to investigate the cause of the issue. If you use persistent connections, each web server thread can open and maintain one database connection. If there is just a small spike in load, this can drastically increase the amount of connections. The problem is really the same for persistent and non-persistent connections, except that the non-persistent ones will just go away afterwards and won't slow down future operations by needlessly hogging memory.
In general you must make sure your mySQL Server can handle as many connections as your web servers accept concurrent connections if you want to guarantee that every database connection succeeds. However, in a real world setup it is likely that your web server will service connections, which are not database related. (Images, css, javascript, etc.). Taking this into account you can offer more web serving threads than database connections, but there is a catch, which could also be the source of your problem:
If someone accesses a large amount of pages on your site in a different manner than would be expected on a normal use pattern, the above rule does not apply. For example if a search engine crawls your site. They do not process content in the same order as a browser does. They can focus on your PHP pages, breaking the assumption that every client will load a mix of data. If this is a problem depends on the amount of pages you have compared to the amount of regular users. This effect can also be accumulative, if your database server slows down under high load, delaying the processing of other connections.
As such it makes sense not to use persistent connections and manage the memory on the database server so that it can support near the maximum of connections at the cost of file cache and buffers, but returns to normal (faster) operations once the spike is over.
P.S.: Make sure you understand how much memory your database will really use when all connections are utilized. http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/. Don't just increase the amount of allowed connections without making sure you do have the required memory. Better to have a few failed connection attempts than your database server grinding to a halt, because its buffers are being swapped out.
您应该关闭 PHP 中的持久连接，它们不会按您的预期工作。实际上，与不使用它们相比，您最终会拥有更多的开放、空闲连接。手册中的警告实际上是警告连接耗尽。
http://php.net/manual/en/function.mysql-pconnect.php
您还应该阅读以下内容：
http://www.php.net/manual/en/features.persistent -connections.php
底线是持久连接没有按照应有的方式重用。您看到的峰值可能是机器人在爬行您的网站，并且为每个页面加载建立了一个新连接。
您可以预期连接不足的问题将会消失。如果您的 Web 服务器和数据库服务器之间的网络连接快速且可靠，则始终建立新连接所增加的时间和负载将不会很明显。 MySQL 在这方面实际上相当高效。
You should switch off of persistent connections in PHP, they don't work as you would expect. You actually end up having many more open, idle connections than with not using them. The warning in the manual actually warns about running out of connections.
http://php.net/manual/en/function.mysql-pconnect.php
You should also read this:
http://www.php.net/manual/en/features.persistent-connections.php
The bottom line is that the persistent connections do not get reused the way they should. The spikes you are seeing is likely a bot crawling your site and a new connection being established for each page load.
You can expect your running out of connections issues to go away. The added time and load of always establishing a new connection will not be noticeable if the network connection between your web server and database server is fast and reliable. MySQL is actually pretty efficient in this regard.
我发现问题是由 DNS 服务器过载引起的。
请参阅 DNS 是 MySQL 安装中的致命弱点吗？< /a> 和 DNS 问题如何导致您的 Mysql 服务器瘫痪
I found out that the problems were caused by an overloaded DNS server.
See Is DNS the Achilles heel in your MySQL installation? and How a DNS problem can put your Mysql server down