英文:
Why do is my sql pool connection being closed suddenly
问题
我正在使用一个池来在类中对我的数据库进行查询，同时在16个实例中运行此类（每个实例限制为100个连接 - 我不知道这个高数字是否与错误有关）
突然，在运行一段时间后，应用程序崩溃并显示错误消息"池已关闭"，而我唯一使用pool.end()的地方是在应用程序的末尾。
英文:
I'm using a pool to make queires to my db within a class , while i run this class in 16 instances (with limit of 100 connections per each - I don't know if this high number has anything to do with the error)
And suddenly after some time of running the application crashes with the error "Pool Closed"
While the only place is use pool.end() is at the end of the apllciation.
答案1
得分: 0
这是一个罕见的MySQL服务器，可以处理接近1600个连接。如果你有这样的服务器，你知道：你为硬件支付了一大笔钱，而且你可能有一个数据库管理员团队来管理它。
这里有一个一点矛盾：对生产MySQL服务器的更多连接通常会减慢速度。为什么？当许多连接向服务器提供类似的查询时，它们通常会争夺资源。
巨大的连接池也可以隐藏应用程序问题，比如非常慢的查询和连接泄漏。
编辑另一个应用程序问题，也就是你的问题，是在查询仍在活动时重复使用连接。MySQL使用Packets out of order消息报告这个问题。如果你有一个设置如下伪代码的嵌套查询
for each item in SELECT some query
for each detail in SELECT some query WHERE something = (item value from outer query)
每个查询都需要自己的连接。如果你在第一个连接上放置一个新的查询，而它仍在传递先前的结果，MySQL会混淆并报告Packets out of order。
专业提示每个连接只用于一个查询，然后将其释放到连接池中。
你突然遇到的"Pool Closed"错误可能是某种服务器故障的结果。（你没有提供太多细节，所以这只是一个猜测。）
尝试在每个实例上将池的最大大小设置为5。确保你适当地设置了池选项：waitForConnections必须为true，你应该允许一个高（或0）的poolLimit。
如果你有时间和胃口处理复杂性，你可以编写一组小的池事件处理程序来监视池请求在队列中停留的时间。如果发现时间太长，你可以将池的最大大小稍微增加一点（例如，从5增加到6）。
专业提示在你的连接池中使用很少数量的连接进行调试。这样你会更快地找到你的错误。
英文:
It's a rare MySQL server that can handle anything close to 1600 connections. If you have one of those servers, you know it: you're paying a small fortune for the hardware, and you probably have a team of database administrators looking after it.
There's a bit of a paradox here: more connections to production MySQL servers usually slow things down. Why? When many connections present similar queries to the server, they often contend with each other.
Vast pools also can conceal application problems like very slow queries and pool-connection leaks.
Edit Another application problem, your problem, is reusing a connection while a query is still active on it. MySQL reports this using Packets out of order messages. If you have a nested query setup like this pseudocode
for each item in SELECT some query
for each detail in SELECT some query WHERE something = (item value from outer query)
each query needs its own connection. If you put a new query onto your first connection while it's still delivering results from a prior, MySQL gets confused and says Packets out of order.
Pro tip Use each connection for just one query, then .release() it to the pool.
Your sudden Pool Closed may be the result of some kind of server fault. (You didn't give us many details, so that's only a guess.)
Try running your application with a pool maximum size of 5 on each instance. Make sure you set your pool options appropriately: waitForConnections must be true, and you should allow a high (or 0) poolLimit.
If you had the time and the appetite for complexity you could write a little nest of pool event handlers to monitor how long pool requests stay in their queue. If it turned out to be too long you could make the pool maximum size a little larger (6 rather than 5, for example).
Pro tip Debug with a very small number of connections in your pool. You'll find your bugs faster.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论