Close Rails ActiveRecord Connection Pool-CodePudding

I am using a second database with datasets within my API.

Every API request can have up to 3 queries on that Database so I am splitting them in three Threads. To keep it Thread safe I am using a connection pool.

But after the whole code is run the ConnectionPool thread is not terminated. So basically every time a request is made, we will have a new Thread on the server until basically there is no memory left.

Is there a way to close the connection pool thread? Or am I doing wrong on creating a connection pool per request?

I setup the Connection Pool this way:

begin
  full_db = YAML::load(ERB.new(File.read(Rails.root.join("config","full_datasets_database.yml"))).result)
  resolver = ActiveRecord::ConnectionAdapters::ConnectionSpecification::Resolver.new(full_db)
  spec = resolver.spec(Rails.env.to_sym)
  pool = ActiveRecord::ConnectionAdapters::ConnectionPool.new(spec)

Then I am running through the queries array and getting the results to the query

returned_responses = []
queries_array.each do |query|
  threads << Thread.new do
    pool.with_connection do |conn|
     returned_responses << conn.execute(query).to_a
    end
  end
end

threads.map(&:join)

returned_responses

Finally I close the connections inside the connection pool:

ensure
 pool.disconnect!
end

CodePudding user response：

Follow the official way of handling multiple databases in Rails:

https://guides.rubyonrails.org/active_record_multiple_databases.html

I can't give you an accurate answer as I do not have your source code to fully understand the whole context. If the setup that I sent above is not applicable to your use case, you might have missed some background clean up tasks. You can refer to this doc:

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html

CodePudding user response：

Since you want to make SQL queries directly without taking advantage of ActiveRecord as the ORM, but you do want to take advantage of ActiveRecord connection pooling, I suggest you create a new abstract class like ApplicationRecord:

# app/models/full_datasets.rb

class FullDatasets < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: {
    writing: :full_datasets_database,
    reading: :full_datasets_database
  }
end

You'll need to configure the database full_datasets_database in database.yml so that connects_to is able to connect to it.

Then you'll be able to connect directly to that database and make direct SQL queries against it by referencing that class instead of ActiveRecord::Base:

FullDatasets.connection.execute(query)

The connection pooling will happen transparently with different pools:

FullDatasets.connection_pool.object_id
=> 22620

ActiveRecord::Base.connection_pool.object_id
=> 9000

You may have to do additional configuration, like dumping the schema to db/full_datasets_schema.rb, but any additional troubleshooting or configuration you'll have to do will be in described in https://guides.rubyonrails.org/active_record_multiple_databases.html.

The short version of this explanation is that you should attempt to take advantage of ActiveRecord as much as possible so that your implementation is clean and straightforward while still allowing you to drop directly to raw SQL.