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).
在过去的几周里，我们已经多次达到1500的mysql连接限制。突然间，Threads_connected爆炸（仅在几分钟内就会爆炸300 - > 1500）。
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).
我们的前端服务器（3x）使用持久连接来连接数据库服务器（1x）。即使在线程耗尽时，我们的数据库服务器也似乎做得很好（CPU，内存，IO）。
I'm thinking of switching from persistent to non-persistent connections in our application (cakePHP). What can I expect?
我正在考虑在我们的应用程序（cakePHP）中从持久连接切换到非持久连接。我能期待什么？
- Higher MySQL load?
- 更高的MySQL负载？
- 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?
这样做是个好主意，还是我应该更多地增加连接限制？
3 个解决方案
#1
0
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.
现在，到了手头的真正问题。正如mikey所说，您可能想要调查问题的原因。如果使用持久连接，则每个Web服务器线程都可以打开并维护一个数据库连接。如果负载只有很小的峰值，这会大大增加连接量。对于持久性和非持久性连接，问题实际上是相同的，除了非持久性连接之后就会消失，并且不会通过不必要地占用内存来减慢未来的操作。
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:
通常，如果要保证每个数据库连接都成功，则必须确保mySQL Server可以处理与Web服务器接受并发连接一样多的连接。但是，在实际设置中，您的Web服务器可能会为与数据库无关的连接提供服务。 （图片，CSS，JavaScript等）。考虑到这一点，您可以提供比数据库连接更多的Web服务线程，但有一个问题，也可能是您的问题的根源：
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.
如果有人以不同于正常使用模式的方式访问您网站上的大量网页，则上述规则不适用。例如，如果搜索引擎抓取您的网站。它们不像浏览器那样处理内容。他们可以专注于您的PHP页面，打破每个客户端将加载混合数据的假设。如果这是一个问题，则取决于您与常规用户数量相比的页数。如果数据库服务器在高负载下减速，则延迟其他连接的处理，此效果也可以累积。
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.
P.S。：确保您了解在使用所有连接时您的数据库将真正使用多少内存。 http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/。不要只是在不确保拥有所需内存的情况下增加允许的连接数量。最好是连接尝试失败，而不是数据库服务器暂停，因为它的缓冲区正在被换出。
#2
0
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
你应该在PHP中关闭持久连接，它们不能像你期望的那样工作。实际上，与不使用它们相比，实际上有更多开放，空闲的连接。手册中的警告实际上警告连接耗尽。 http://php.net/manual/en/function.mysql-pconnect.php
You should also read this: http://www.php.net/manual/en/features.persistent-connections.php
您还应该阅读：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.
您可以预期您的连接问题将会消失。如果Web服务器和数据库服务器之间的网络连接快速可靠，则始终建立新连接的额外时间和负载将不会明显。在这方面，MySQL实际上非常有效。
#3
0
I found out that the problems were caused by an overloaded DNS server.
我发现问题是由DNS服务器过载引起的。
See Is DNS the Achilles heel in your MySQL installation? and How a DNS problem can put your Mysql server down
请参阅MySQL安装中的DNS是Achilles脚跟吗？以及DNS问题如何导致您的Mysql服务器崩溃
#1
0
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.
现在，到了手头的真正问题。正如mikey所说，您可能想要调查问题的原因。如果使用持久连接，则每个Web服务器线程都可以打开并维护一个数据库连接。如果负载只有很小的峰值，这会大大增加连接量。对于持久性和非持久性连接，问题实际上是相同的，除了非持久性连接之后就会消失，并且不会通过不必要地占用内存来减慢未来的操作。
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:
通常，如果要保证每个数据库连接都成功，则必须确保mySQL Server可以处理与Web服务器接受并发连接一样多的连接。但是，在实际设置中，您的Web服务器可能会为与数据库无关的连接提供服务。 （图片，CSS，JavaScript等）。考虑到这一点，您可以提供比数据库连接更多的Web服务线程，但有一个问题，也可能是您的问题的根源：
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.
如果有人以不同于正常使用模式的方式访问您网站上的大量网页，则上述规则不适用。例如，如果搜索引擎抓取您的网站。它们不像浏览器那样处理内容。他们可以专注于您的PHP页面，打破每个客户端将加载混合数据的假设。如果这是一个问题，则取决于您与常规用户数量相比的页数。如果数据库服务器在高负载下减速，则延迟其他连接的处理，此效果也可以累积。
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.
P.S。：确保您了解在使用所有连接时您的数据库将真正使用多少内存。 http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/。不要只是在不确保拥有所需内存的情况下增加允许的连接数量。最好是连接尝试失败，而不是数据库服务器暂停，因为它的缓冲区正在被换出。
#2
0
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
你应该在PHP中关闭持久连接，它们不能像你期望的那样工作。实际上，与不使用它们相比，实际上有更多开放，空闲的连接。手册中的警告实际上警告连接耗尽。 http://php.net/manual/en/function.mysql-pconnect.php
You should also read this: http://www.php.net/manual/en/features.persistent-connections.php
您还应该阅读：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.
您可以预期您的连接问题将会消失。如果Web服务器和数据库服务器之间的网络连接快速可靠，则始终建立新连接的额外时间和负载将不会明显。在这方面，MySQL实际上非常有效。
#3
0
I found out that the problems were caused by an overloaded DNS server.
我发现问题是由DNS服务器过载引起的。
See Is DNS the Achilles heel in your MySQL installation? and How a DNS problem can put your Mysql server down
请参阅MySQL安装中的DNS是Achilles脚跟吗？以及DNS问题如何导致您的Mysql服务器崩溃