Ruby China

新手问题 请教连接远程数据库 MySQL 总是间隔 4 分钟左右就断开

renhe1990 · 2016年12月05日 · 最后由 mitchellmarsh820 回复于 2024年04月17日 · 3254 次阅读

在维护 ruby on rails 的一个项目中，有个新需求：将原来本地数据库切换成远程的微软云数据库，原 database.yml 如下：

default: &default
  adapter: mysql2
  encoding: utf8
  username: root
  password: 123456
  pool: 20
  timeout: 5000

切换到远程数据库的 database.yml 如下：

default: &default
  adapter: mysql2
  encoding: utf8
  host: '*********'
  username: '********'
  password: '********'
  pool: 10
  timeout: 5000
  #reconnect: true

切换到微软云数据库后现象为：项目部署后 4 分钟左右再访问，网页卡死等待，后台连接数据库超时，截取日志如下：

F, [2016-11-30T15:33:27.114071 #26108] FATAL -- : 
ActiveRecord::ConnectionTimeoutError (could not obtain a database connection within 5.000 seconds (waited 1902.103 seconds)):
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:190:in `block in wait_poll'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:181:in `loop'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:181:in `wait_poll'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:136:in `block in poll'
  /usr/local/rvm/rubies/ruby-2.1.5/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:146:in `synchronize'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:134:in `poll'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:418:in `acquire_connection'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:351:in `block in checkout'
  /usr/local/rvm/rubies/ruby-2.1.5/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:350:in `checkout'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:265:in `block in connection'
  /usr/local/rvm/rubies/ruby-2.1.5/lib/ruby/2.1.0/monitor.rb:211:in `mon_synchronize'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:264:in `connection'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:541:in `retrieve_connection'
  activerecord (4.1.16) lib/active_record/connection_handling.rb:113:in `retrieve_connection'
  activerecord (4.1.16) lib/active_record/connection_handling.rb:87:in `connection'
  activerecord (4.1.16) lib/active_record/query_cache.rb:32:in `call'
  activerecord (4.1.16) lib/active_record/connection_adapters/abstract/connection_pool.rb:621:in `call`
  actionpack (4.1.16) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
  activesupport (4.1.16) lib/active_support/callbacks.rb:82:in `run_callbacks'
  actionpack (4.1.16) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
  actionpack (4.1.16) lib/action_dispatch/middleware/remote_ip.rb:76:in `call'
  rollbar (2.12.0) lib/rollbar/middleware/rails/rollbar.rb:24:in `block in call'
  rollbar (2.12.0) lib/rollbar.rb:735:in `scoped'
  rollbar (2.12.0) lib/rollbar/middleware/rails/rollbar.rb:22:in `call'
  actionpack (4.1.16) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
  rollbar (2.12.0) lib/rollbar/middleware/rails/show_exceptions.rb:22:in `call_with_rollbar'
  actionpack (4.1.16) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
  railties (4.1.16) lib/rails/rack/logger.rb:38:in `call_app'
  railties (4.1.16) lib/rails/rack/logger.rb:20:in `block in call'
  activesupport (4.1.16) lib/active_support/tagged_logging.rb:68:in `block in tagged'
  activesupport (4.1.16) lib/active_support/tagged_logging.rb:26:in `tagged'
  activesupport (4.1.16) lib/active_support/tagged_logging.rb:68:in `tagged'
  railties (4.1.16) lib/rails/rack/logger.rb:20:in `call'
  actionpack (4.1.16) lib/action_dispatch/middleware/request_id.rb:21:in `call'
  rack (1.5.5) lib/rack/methodoverride.rb:21:in `call'
  rack (1.5.5) lib/rack/runtime.rb:17:in `call'
  activesupport (4.1.16) lib/active_support/cache/strategy/local_cache_middleware.rb:26:in `call'
  rack (1.5.5) lib/rack/sendfile.rb:112:in `call'
  railties (4.1.16) lib/rails/engine.rb:514:in `call'
  railties (4.1.16) lib/rails/application.rb:144:in `call'
  puma (3.6.0) lib/puma/configuration.rb:225:in `call'
  puma (3.6.0) lib/puma/server.rb:578:in `handle_request'
  puma (3.6.0) lib/puma/server.rb:415:in `process_client'
  puma (3.6.0) lib/puma/server.rb:275:in `block in run'
  puma (3.6.0) lib/puma/thread_pool.rb:116:in `call'
  puma (3.6.0) lib/puma/thread_pool.rb:116:in `block in spawn_thread'

个人分析，由于微软云数据库有网络层，负载均衡服务器对其设置了无访问超时时间，导致连接中断，但是并未通知其连接的两端。现在请教前辈该如何配置 rails 的连接池对其中断的连接进行管理或者有其他好的办法保证连接的持续性？ ruby 版本 2.1.5 rails 4.1.16 puma 3.6.0

另有一个问题：现知道微软云的 mysql 数据库的连接总数为 202 个，约 200. puma 工作进程数：2 已知每个进程的线程数为 0~16 个。那个该怎样配置 database.yml 的 pool 数？也就是 pool 连接池数量是配置的每个线程的最大连接数吗？

望不吝赐教！！！！

huacnlee #1 2016年12月05日

pool 要改成最大线程数量，也就是 16

If you are using the Puma web server we recommend setting the pool value to equal ENV['RAILS_MAX_THREADS']. When using multiple processes each process will contain its own pool so as long as no worker process has more than ENV['RAILS_MAX_THREADS'] then this setting should be adequate.

如果你使用 Puma，我们建议你设置 database pool 的值和 Puma 的 max threads 配置相同。而当 Puma 同时使用多进程的时候，每个进程会有它自己的数据库连接池，所以只需要设置 max threads 就足够了。

例如当 Puma 配置

workers 4
threads 16, 64

那么 database.yml 只需要配置 pool: 64 就可以了，而无须乘以进程数 (workers)。

此外，还需要注意，加入你还有 Sidekiq 之类的东西，也需要注意 Sidekiq 的 concurrency 参数，例如当 Sidekiq 的 concurrency 配置大于 64 的时候，应当选择大的数值。

https://devcenter.heroku.com/articles/concurrency-and-database-connections

renhe1990 #2 2016年12月05日

#1 楼 @huacnlee 嗯，了解了，谢谢前辈，还有连接远程数据库的问题遇到过吗？有人提议我做个定时任务，间隔查询数据库解决保持连接的问题，可行吗？

需要 登录 后方可回复, 如果你还没有账号请 注册新账号