最小化连接数的最佳方法是什么? [英] What is the best way to minimize number of connections?

问题描述

我的主机对于数据库用户的最大连接数非常非常.这是我的用户收到的错误:

My host has a really, really low number of max connections for a database user. This is the error my users are getting:

用户username_here"已超过max_user_connections"资源(当前值:15).

User 'username_here' has exceeded the 'max_user_connections' resource (current value: 15).

我不认为我有能力提高这个价值，除非我升级到更昂贵的计划，所以我正在寻找一种方法来有效地使用这 15 个连接.我处理连接的方式如下:我在每个页面加载开始时加载的脚本中连接到数据库，然后将该连接传递给运行查询的函数.我想我可以通过在查询函数中打开连接并在 return 语句之后立即关闭它来最小化连接打开的时间，这样可以吗，还是我无缘无故地让事情变得更复杂?

I don't think it's in my power to raise that value, unless I upgrade to a much more expensive plan, so I'm looking for a way to use these 15 connections effectively. My way of handling connections is the following: I connect to the database in a script I load at the start of every page load and then I pass that connection to a function that runs the queries. I thought I could minimize the time a connection is open by opening the connection inside the query function and closing it right after the return statement, is that fine or am I making things more complicated for no reason?

作为最后的手段，我正在考虑将连接放入 try/catch 并尝试每隔几秒重新连接几次.这样做是明智的，还是更糟?

As a last resort, I was thinking of putting the connection inside of a try/catch and attempt to reconnect every few seconds for a few more times. Would that be something wise to do, or is it even worse?

推荐答案

以下是优化连接数的方法:

Here's how you can optimize the number of connections:

• 确保您没有在任何地方使用持久连接.这是忘记打开连接的最简单方法，也是可用连接用完的最常见原因.在 mysqli 中，通过在连接时将 p: 附加到主机名来打开持久连接.
• 确保您只对每个 HTTP 请求打开一个连接.不要打开和关闭它们，因为这会很快失控，并且会对您的应用程序产生不良的性能影响.具有可传递给需要它的函数的单个全局连接.
• 优化您的查询，以便更快地处理它们并更快地释放连接.这也适用于优化索引和摆脱 N+1 问题.(根据经验，我可以说 PDO 在重构代码以避免设计不佳的查询方面有很大帮助.)
• 如果您需要在同一进程中执行其他一些耗时的任务，请先执行所有 SQL 操作，然后关闭连接.这同样适用于打开连接.仅在您知道需要时才打开它.

• Make sure that you are not using persistent connection anywhere. This is the easiest way to lose track of open connections and the most common reason for running out of available connections. In mysqli the persistent connection is opened by prepending p: to the hostname when connecting.
• Make sure that you are only opening a single connection on each HTTP request. Don't open and close them as this can quickly get out of hand and will have bad performance impact on your application. Have single global connection that you pass around to functions that need it.
• Optimize your queries so that they are processed faster and free up the connection quicker. This also applies to optimizing indexes and getting rid of the N+1 problem. (From experience I can say that PDO helps a lot in refactoring your code to avoid poorly designed queries.)
• If you need to perform some other time-demanding task in the same process, do all your SQL operations first and then close the connection. Same applies to opening the connection. Open it only when you know you will need it.

如果您发现自己遇到超出max_user_connections"限制的问题，那么这意味着您的 Web 服务器配置不正确.在理想情况下，MySQL 连接将是无限的，但在共享主机上，必须设置此限制以防止资源滥用(意外或故意).但是，可用的 MySQL 连接数应与可用的服务器线程数相匹配.这可能是一个非常固执的话题，但我想说的是，如果您的应用程序需要对每个请求执行一些 SQL 操作，那么可用服务器连接数不应超过可用 MySQL 连接数.在 apache 上，您可以计算可能的连接数如此链接所示.

If you find yourself running into a problem of exceeding the 'max_user_connections' limit then it means that your web server is not configured properly. In an ideal scenario the MySQL connection would be unlimited, but on shared hosting this limitation has to be put in place to protect against resource abuse (either accidental or on purpose). However, the number of available MySQL connection should match the number of available server threads. This can be a very opinionated topic, but I would say that if your application needs to perform some SQL operation on every request then the number of available server connections should not exceed the number of available MySQL connections. On apache, you can calculate the number of possible connections as shown in this link.

在设计合理的应用程序上，即使有 15 个并发 MySQL 连接，您仍然应该能够处理令人满意的每秒请求量.例如，如果每个请求需要 100 毫秒才能完成，那么您每秒可以处理 150 个请求.

On a reasonably designed application even with 15 concurrent MySQL connections, you should still be able to handle a satisfactory amount of requests per second. For example, if each request takes 100ms to complete, you could handle 150 requests per second.

这篇关于最小化连接数的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！