mySQL“连接太多”错误受到杂种实​​例数量的影响?

发布于 2024-10-22 00:30:23 字数 586 浏览 4 评论 0原文

最近,我开始在高流量时收到 mySQL“连接过多”错误。我的 Rails 应用程序在一个混合集群上运行,共享主机上有 2 个实例。最近的一些变化可能会推动它:

  • 我网站的流量增加了。我 现在平均大约 4K 页 天。
  • 数据库大小已增加。我最大的表有大约 100K 行。 一些协会可能会回归 中的数百个实例 最坏的情况,尽管大​​多数情况要少得多。
  • 我添加了一些功能 增加了数量和大小 某些操作中的数据库调用。

我进行了代码审查,以减少数据库调用、优化 SQL 查询、添加缺失的索引,并使用 :include 进行急切加载。然而,我的许多方法仍然进行 5-10 个单独的 SQL 调用。我的大多数操作的响应时间约为 100 毫秒,但我最常见的操作之一平均为 300-400 毫秒,有些操作随机峰值超过 1000 毫秒。

日志没有什么帮助,因为错误似乎是随机发生的,或者至少该模式似乎与正在调用的操作或正在访问的数据无关。

我可以通过添加额外的混合实例来减轻错误吗?或者 mySQL 连接是否受到服务器限制,因此与我分配流量的进程数量无关?

这很可能是我的编码问题,还是我应该向我的主机施加更多容量/更少共享服务器上的负载?

Recently I have started getting mySQL "too many connection" errors at times of high traffic. My rails app runs on a mongrel cluster with 2 instances on a shared host. Some recent changes that might be driving it:

  • Traffic to my site has increased. I
    am now averaging about 4K pages a
    day.
  • Database size has increased. My largest table has ~ 100K rows.
    Some associations could return
    several hundred instances in the
    worst case, though most are far less.
  • I have added some features that
    increased the number and size of
    database calls in some actions.

I have done a code review to reduce database calls, optimize SQL queries, add missing indexes, and use :include for eager loading. However, many of my methods still make 5-10 separate SQL calls. Most of my actions have a response time of around 100ms, but one of my most common actions averages 300-400ms, and some actions randomly peak at over 1000ms.

The logs are of little help, as the errors seem to occur randomly, or at least the pattern does not appear related to the actions being called or data being accessed.

Could I alleviate the error by adding additional mongrel instances? Or are the mySQL connections limited by the server, and thus unrelated to the number of processes I divide my traffic across?

Is this most likely a problem with my coding, or should I be pressing my host for more capacity/less load on the shared server?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

記柔刀 2024-10-29 00:30:23

自 Rails 2.2 起,ActiveRecord 就已经汇集了数据库连接,这很可能就是导致此处连接过多的原因。尝试调低该环境的 database.ymlpool 的值(默认为 5)。

可以在此处找到文档。

ActiveRecord has pooled database connections since Rails 2.2, and it's likely that that's what's causing your excess connections here. Try turning down the value of pool in your database.yml for that environment (it defaults to 5).

Docs can be found here.

蓝眸 2024-10-29 00:30:23

你缓存什么东西吗?它是减轻应用程序和数据库负载的重要组成部分。 Rails 指南中有一个关于缓存的部分

Are you caching anything? It's an important part of alleviating application and database load. The Rails Guides have a section on caching.

帅哥哥的热头脑 2024-10-29 00:30:23

有问题。一个 Mongrel 实例一次处理 1 个请求,因此如果您有 2 个 Mongrel 实例,那么您应该不会看到超过 2 个活动 MySQL 连接(至少来自 Mongrel)

您可以记录或绘制 SHOW STATUS LIKE 'Threads_connected' 的输出时间。

PS:这不是很多杂种。如果您希望能够同时处理 2 个以上的请求,那么您会需要更多。 ...如果内存紧张,您可以切换到 Phusion Passenger 和 REE。

Something is wrong. A Mongrel instance processes 1 request at a time so if you have 2 Mongrel instances then you should not be seeing more than 2 active MySQL connections (from the mongrels at least)

You could log or graph the output of SHOW STATUS LIKE 'Threads_connected' over time.

PS: this is not very many Mongrels. if you want to be able to service more than 2 simultaneous requests then you'll want more. ...if memory is tight, you can switch to Phusion Passenger and REE.

入画浅相思 2024-10-29 00:30:23

这个错误是由mysql引起的,而不是rails配置引起的。

  1. 检查变量:max_connections
SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
  1. 增加它:SET GLOBAL max_connections = 1024;

参考:https://www.thegeekdiary.com/mysql-error-too-many-connections-and-how-to-解决它/

This error is caused by mysql, but not rails config.

  1. check variable: max_connections:
SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
  1. increase it : SET GLOBAL max_connections = 1024;

refer to: https://www.thegeekdiary.com/mysql-error-too-many-connections-and-how-to-resolve-it/

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文