随机简单查询的查询过程中与MySQL服务器的连接断开 [英] Lost connection to MySQL server during query on random simple queries

问题描述

最终更新:我们通过找到一种无需分叉即可实现目标的方法来解决此问题.但是分叉是问题的原因.

FINAL UPDATE: We fixed this problem by finding a way to accomplish our goals without forking. But forking was the cause of the problem.

---原始帖子---

---Original Post---

我在rails stack上运行ruby，我们的mysql服务器是单独的，但是与我们的应用程序服务器位于同一站点. (我们尝试将其换成规格翻倍的另一台mysql服务器，但未见改善.

I'm running a ruby on rails stack, our mysql server is separate, but housed at the same site as our app servers. (we've tried swapping it out for a different mysql server with double the specs, but no improvement was seen.

在工作时间内，我们可以从特定查询中获取少量信息.

during business hours we get a handful of these from no particular query.

ActiveRecord::StatementInvalid: Mysql2::Error: Lost connection to MySQL server during query

大多数失败的查询确实非常简单，并且在一个查询和另一个查询之间似乎没有任何模式.当我从Rails 4.1升级到4.2时，一切就开始了.

most of the queries that fail are really simple, and there seems to be no pattern between one query and another. This all started when I upgraded from Rails 4.1 to 4.2.

我不知所措.我们的数据库服务器全天不足5％的CPU.我确实从随机交互的用户那里收到了错误报告，由于这个原因，因此，不是运行了几个小时或类似时间的查询，当然是当他们重试它完全相同的工作时.

I'm at a loss as to what to try. Our database server is less than 5% CPU throughout the day. I do get bug reports from users who have random interactions fail due to this, so it's not queries that have been running for hours or anything like that, of course when they retry the exact same thing it works.

我们的服务器由cloud66配置.

Our servers are configured by cloud66.

简而言之:我们的mysql服务器由于某种原因而消失了，但这并不是因为资源不足，它还是一台全新的服务器，因为当此问题开始时我们从另一台服务器迁移了.

So in short: our mysql server is going away for some reason, but it's not because of lack of resources, it's also a brand new server as we migrated from another server when this problem started.

有时在开发功能时，在本地主机上也会发生这种情况，所以我不认为这是负载问题.

this also happens to me on localhost while developing features sometimes, so I don't believe it's a load issue.

我们正在运行以下内容:

We're running the following:

• 红宝石2.2.5
• rails 4.2.6
• mysql2 0.4.8

更新:根据下面的第一个答案，我昨晚将max_connections变量增加到500，并通过确认增加 show global variables like 'max_connections';

UPDATE: per the first answer below I increased our max_connections variable to 500 last night, and confirmed the increase via show global variables like 'max_connections';

我仍然断开连接，今天的第一个连接是在几分钟前断开的. ActiveRecord::StatementInvalid: Mysql2::Error: Lost connection to MySQL server during query

I'm still getting dropped connection, the first one today was dropped only a few minutes ago.... ActiveRecord::StatementInvalid: Mysql2::Error: Lost connection to MySQL server during query

我跑了select * from information_schema.processlist;，我得到了36行.这是否意味着我的应用服务器当时正在运行36个连接?或一个进程可以是多个连接?

I ran select * from information_schema.processlist; and I got 36 rows back. Does this mean my app servers were running 36 connections at that moment? or can a process be multiple connections?

更新:我只是将net_read_timeout设置为60(之前是30)，我会看看是否有帮助

UPDATE: I just set net_read_timeout = 60 (it was 30 before) I'll see if that helps

更新:它没有帮助，我仍在寻找解决方案...

UPDATE: It didn't help, I'm still looking for a solution...

在这里删除了凭据的我的Database.yml.

Heres my Database.yml with credentials removed.

production:
  adapter: mysql2
  encoding: utf8
  host: localhost
  database:
  username: 
  password: 
  port: 3306
  reconnect: true

推荐答案

与MySQL的连接可能会通过多种方式中断，但我建议您重新考虑一下Mario Carrion的答案，因为这是一个非常明智的答案.

The connection to MySQL can be disrupted by a number of means, but I would recommend revisiting Mario Carrion's answer since it's a very wise answer.

连接可能正在中断，因为它正在与其他进程共享，从而导致通信协议错误...

It seems likely that connection is disrupted because it's being shared with the other processes, causing communication protocol errors...

...如果连接池是进程绑定的，则很容易发生这种情况，我相信它是在ActiveRecord中，这意味着同一连接可以在不同的进程中同时检出"多次.

...this could easily happen if the connection pool is process bound, which I believe it is, in ActiveRecord, meaning that the same connection could be "checked-out" a number of times simultaneously in different processes.

解决方案是仅在应用程序服务器中的fork语句之后才能建立数据库连接.

The solution is that database connections must be established only AFTER the fork statement in the application server.

我不确定您使用的是哪个服务器，但是如果您使用的是warmup功能，请不要.

I'm not sure which server you're using, but if you're using a warmup feature - don't.

如果在第一个网络请求之前正在运行任何数据库调用，请不要.

If you're running any database calls before the first network request - don't.

这两个操作中的任何一个都可能在fork ing发生之前初始化连接池，从而导致在没有锁定系统的情况下在进程之间共享MySQL连接池.

Either of these actions could potentially initialize the connection pool before forking occurs, causing the MySQL connection pool to be shared between processes while the locking system isn't.

我并不是说这是导致此问题的唯一可能原因，正如@ sloth-jr所说，还有其他选择……但是根据您的描述，其中大多数似乎不太可能.

I'm not saying this is the only possible reason for the issue, as stated by @sloth-jr, there are other options... but most of them seem less likely according to your description.

旁注:

我从information_schema.processlist中选择*；我得到了36行.这是否意味着我的应用服务器当时正在运行36个连接?或一个进程可以是多个连接?

I ran select * from information_schema.processlist; and I got 36 rows back. Does this mean my app servers were running 36 connections at that moment? or can a process be multiple connections?

每个进程可以容纳多个连接.就您而言，您最多可以有500X36个连接. (请参见编辑)

通常，池中的连接数通常可以与每个进程中的线程数相同(它不应小于线程数，否则争用会使您变慢).有时，根据您的应用程序再添加一些是很好的.

In general, the number of connections in the pool can often be the same as the number of threads in each process (it shouldn't be less than the number of thread, or contention will slow you down). Sometimes it's good to add a few more depending on your application.

我很抱歉忽略进程计数是在引用MySQL数据而不是应用程序数据这一事实.

I apologize for ignoring the fact that the process count was referencing the MySQL data and not the application data.

您显示的进程计数是MySQL服务器数据，该数据过程"数据实际上计数活动连接，并且不是实际的进程或线程(尽管它也应该转换为线程数).

The process count you showed is the MySQL server data, which seems to use a thread per connection IO scheme. The "Process" data actually counts active connections and not actual processes or threads (although it should translate to the number of threads as well).

这意味着在每个应用程序进程可能的500个连接中(即，如果您为应用程序使用8个进程，那将是8X500 = 4,000个允许的连接)，您的应用程序到目前为止仅打开了36个连接.

This means that out of possible 500 connections per application processes (i.e., if you're using 8 processes for your application, that would be 8X500=4,000 allowed connections) your application only opened 36 connections so far.

这篇关于随机简单查询的查询过程中与MySQL服务器的连接断开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！