Sequel and ActiveRecord Connection Pool Conflict #553

Closed
cgilbert56 opened this Issue Sep 17, 2012 · 7 comments

Comments

Projects
None yet
2 participants
@cgilbert56

We are wanting to connect to multiple databases in same Rails application, Sybase and Oracle. We find Sequel gem very useful and flexible for sql executed against Sybase - stored procs, etc... But we already have number of sql queries going against Oracle using ActiveRecord and prefer that gem for Oracle.

I cannot get Sequel to use it's own connection pool code. But instead it ignores my options below such as max_connections and pool_timeout. This only happens when running with Oracle as the default database using ActiveRecord. Another test where I eliminated ActiveRecord proved to work fine with sequel conn pool and sequel configs.

When testing the multiple DB scenario, I actually ran the sequel connection logic in debug and see that sequel is setting my new options correctly on it's own database and threaded connection pool objects. However a load test to try and increase the connections beyond 5 always fails with below stacktrace error. That shows that my sequel connections are somehow using the ActiveRecord defaults of 5 max connections and 5 second pool timeout. Also in stacktrace you see the sybase connections/checkout, etc... happen from ActiveRecord objects, not Sequel objects.

Is there any way Sequel and ActiveRecord can coexist in same Rails application, to where I can get them each to use their own connection pools?

Thanks,
Chuck Gilbert

config:

jruby 1.6.7
tomcat 7
jdk 1.6
Rails 3.2.8 (ActiveRecord 3.2.8)
sequel 3.39.0

Singleton sequel sybase connection helper:

require 'sequel'

class SybaseSupport
class << self

#only done once upon rails startup to establish connection pool
def create_connection_pool()

    begin
        Rails.logger.info "Initiating Sybase Connection Pool via Sequel.connect()"

        @sequel_db = Sequel.connect(
        user: "myuser",
        password: "mypassword",
        url: "jdbc:jtds:sybase://myhost:3333/web_db;charset=iso_1",
        adapter: "jdbc",
        max_connections: 10,
        pool_timeout: 20
        )

    rescue Exception => exc
        Rails.logger.error("Could not establish Sybase DB Connection Pool" + exc.inspect + "\n" + exc.backtrace.join("\n"))
        raise
    end

end

#This returns the Sequel::Database instance object (reused) to make use of conn pool
def get_connection()
  begin
    Rails.logger.info "Getting Sybase DB Connection from pool"
    if @sequel_db.nil?
        #1st time if sql error it ruins it for subsequent calls, so sequel connect again
        create_connection_pool()
        if @sequel_db.nil?
            raise TypeError, "Sybase Sequel Database conn pool object is nil"
        end
    end
  rescue Exception => exc
    Rails.logger.error("Could not obtain Sybase DB Connection" + exc.inspect + "\n" + exc.backtrace.join("\n"))
    raise
  end
  @sequel_db
end

end
end

typical call from another object, to execute sequel sql:

class AccountDataAccess
def simple
conn = SybaseSupport.get_connection()
conn["SELECT * from people WHERE area = 'west'"].all
end
end

database.yml (Oracle default activerecord)

development:
adapter: oracle_enhanced
host: myoracle
port: 4444
database: otherdb
username: me
password: apassword
url: jdbc:oracle:thin:@//myoracle:4444/instance

stacktrace:

14-Sep-2012 08:50:13.159 INFO [tomcat-http--25] org.apache.catalina.core.ApplicationContext.log
ActiveRecord::ConnectionTimeoutError (could not obtain a database connection within 5 seconds (waited 5.0 seconds). The max pool size is currently 5; consider increasing it.):
gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:252:in checkout' org/jruby/RubyKernel.java:1410:inloop'
gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:236:in checkout' file:lib/gems-gems-jruby-jars-1.6.7-lib-jruby-stdlib-1.6.7.jar!/META-INF/jruby.home/lib/ruby/1.9/monitor.rb:201:inmon_synchronize'
file:lib/gems-gems-jruby-jars-1.6.7-lib-jruby-stdlib-1.6.7.jar!/META-INF/jruby.home/lib/ruby/1.9/monitor.rb:200:in mon_synchronize' gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:233:incheckout'
gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:96:in connection' file:lib/gems-gems-jruby-jars-1.6.7-lib-jruby-stdlib-1.6.7.jar!/META-INF/jruby.home/lib/ruby/1.9/monitor.rb:201:inmon_synchronize'
file:lib/gems-gems-jruby-jars-1.6.7-lib-jruby-stdlib-1.6.7.jar!/META-INF/jruby.home/lib/ruby/1.9/monitor.rb:200:in mon_synchronize' gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:95:inconnection'
gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:404:in retrieve_connection' gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:170:inretrieve_connection'
gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:144:in connection' gems/gems/activerecord-3.2.8/lib/active_record/query_cache.rb:61:incall'
gems/gems/activerecord-3.2.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:473:in call' gems/gems/actionpack-3.2.8/lib/action_dispatch/middleware/callbacks.rb:28:incall'
gems/gems/activesupport-3.2.8/lib/active_support/callbacks.rb:408:in _run__1303186173__call__1311521036__callbacks' org/jruby/RubyBasicObject.java:1698:insend'
org/jruby/RubyKernel.java:2097:in send' gems/gems/activesupport-3.2.8/lib/active_support/callbacks.rb:405:inrun_callback'
gems/gems/activesupport-3.2.8/lib/active_support/callbacks.rb:390:in _run_call_callbacks' org/jruby/RubyBasicObject.java:1698:in__send
'
org/jruby/RubyKernel.java:2097:in send' gems/gems/activesupport-3.2.8/lib/active_support/callbacks.rb:81:inrun_callbacks'
gems/gems/actionpack-3.2.8/lib/action_dispatch/middleware/callbacks.rb:27:in call' gems/gems/actionpack-3.2.8/lib/action_dispatch/middleware/remote_ip.rb:31:incall'
gems/gems/actionpack-3.2.8/lib/action_dispatch/middleware/debug_exceptions.rb:16:in call' gems/gems/actionpack-3.2.8/lib/action_dispatch/middleware/show_exceptions.rb:56:incall'
gems/gems/railties-3.2.8/lib/rails/rack/logger.rb:26:in call_app' gems/gems/railties-3.2.8/lib/rails/rack/logger.rb:16:incall'
gems/gems/actionpack-3.2.8/lib/action_dispatch/middleware/request_id.rb:22:in call' gems/gems/rack-1.4.1/lib/rack/methodoverride.rb:21:incall'
gems/gems/rack-1.4.1/lib/rack/runtime.rb:17:in call' gems/gems/activesupport-3.2.8/lib/active_support/cache/strategy/local_cache.rb:72:incall'
gems/gems/actionpack-3.2.8/lib/action_dispatch/middleware/static.rb:62:in call' gems/gems/rack-cache-1.2/lib/rack/cache/context.rb:136:inforward'

@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Sep 17, 2012

Owner

The backtrace you have provided is an ActiveRecord backtrace, not a Sequel backtrace, showing that ActiveRecord could not obtain a connection. Why do you think this is a Sequel issue?

Owner

jeremyevans commented Sep 17, 2012

The backtrace you have provided is an ActiveRecord backtrace, not a Sequel backtrace, showing that ActiveRecord could not obtain a connection. Why do you think this is a Sequel issue?

@cgilbert56

This comment has been minimized.

Show comment Hide comment
@cgilbert56

cgilbert56 Sep 18, 2012

Hi Jeremy. I am not saying it is necessarily a Sequel issue. Some folks use Sequel on Rails. Does Sequel make use of Rails ActiveRecord objects for anything? Wouldn't the 2 want to co-exist in Rails?

Thanks,
Chuck

Hi Jeremy. I am not saying it is necessarily a Sequel issue. Some folks use Sequel on Rails. Does Sequel make use of Rails ActiveRecord objects for anything? Wouldn't the 2 want to co-exist in Rails?

Thanks,
Chuck

@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Sep 18, 2012

Owner

Sequel does not use ActiveRecord for anything. Many people use Sequel alongside ActiveRecord in the same process and it works fine. The backtrace you provided doesn't touch Sequel or any of the code you posted. All it does is show that you've hit the ActiveRecord connection limit (which looking at database.yml you didn't change). It looks like you may want to increase your ActiveRecord connection limit in database.yml, but nothing you've posted shows any issue with Sequel, so I'm closing this now.

Owner

jeremyevans commented Sep 18, 2012

Sequel does not use ActiveRecord for anything. Many people use Sequel alongside ActiveRecord in the same process and it works fine. The backtrace you provided doesn't touch Sequel or any of the code you posted. All it does is show that you've hit the ActiveRecord connection limit (which looking at database.yml you didn't change). It looks like you may want to increase your ActiveRecord connection limit in database.yml, but nothing you've posted shows any issue with Sequel, so I'm closing this now.

@cgilbert56

This comment has been minimized.

Show comment Hide comment
@cgilbert56

cgilbert56 Sep 19, 2012

Hi Jeremy, Do you know if Sequel can work with multiple databases. So I'm going to try that and strip out ActiveRecord from my rails app. Thanks for your help. btw-I did try putting in higher limit on the activerecord pool, but still ignored it for sequel, and kept hitting the default AR pool limit of 5.

Thanks,
Chuck

Hi Jeremy, Do you know if Sequel can work with multiple databases. So I'm going to try that and strip out ActiveRecord from my rails app. Thanks for your help. btw-I did try putting in higher limit on the activerecord pool, but still ignored it for sequel, and kept hitting the default AR pool limit of 5.

Thanks,
Chuck

@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Sep 19, 2012

Owner

Yes, Sequel should work just fine with multiple databases. If you are only using two databases, I recommending assigning them to two separate constants:

SYBASE_DB = Sequel.connect('jdbc:jtds:sybase://myhost:3333/web_db;charset=iso_1')
ORACLE_DB = Sequel.connect('jdbc:oracle:thin:@//myoracle:4444/instance', :user=>'me', :password=>'apassword')

If you want to keep using database.yml so you don't store your connection info in your repository, you can still do so:

db_config = YAML.load(File.read('config/database.yml'))[Rails.env]
SYBASE_DB = Sequel.connect(db_config['sybase'])
ORACLE_DB = Sequel.connect(db_config['oracle'])

With a database.yml file like:

development:
  sybase:
    url: jdbc:jtds:sybase://myhost:3333/web_db;charset=iso_1
  oracle:
    url: jdbc:oracle:thin:@//myoracle:4444/instance
    user: me
    password: apassword

That's untested, but hopefully something like that should work.

Owner

jeremyevans commented Sep 19, 2012

Yes, Sequel should work just fine with multiple databases. If you are only using two databases, I recommending assigning them to two separate constants:

SYBASE_DB = Sequel.connect('jdbc:jtds:sybase://myhost:3333/web_db;charset=iso_1')
ORACLE_DB = Sequel.connect('jdbc:oracle:thin:@//myoracle:4444/instance', :user=>'me', :password=>'apassword')

If you want to keep using database.yml so you don't store your connection info in your repository, you can still do so:

db_config = YAML.load(File.read('config/database.yml'))[Rails.env]
SYBASE_DB = Sequel.connect(db_config['sybase'])
ORACLE_DB = Sequel.connect(db_config['oracle'])

With a database.yml file like:

development:
  sybase:
    url: jdbc:jtds:sybase://myhost:3333/web_db;charset=iso_1
  oracle:
    url: jdbc:oracle:thin:@//myoracle:4444/instance
    user: me
    password: apassword

That's untested, but hopefully something like that should work.

@cgilbert56

This comment has been minimized.

Show comment Hide comment
@cgilbert56

cgilbert56 Sep 19, 2012

Jeremy,

Thanks for the response. We have just reconfigured our Rails app to strip out ActiveRecord in application.rb, and went with multiple database connections and pools. We just ran a load test hitting both databases, and it works really well!! Sequel pools and max connections/timeouts worked as expected.

I did the separate database assignment you suggested. Thanks for the tip on database.yml, and for your support!

Have a good day,
Chuck

Jeremy,

Thanks for the response. We have just reconfigured our Rails app to strip out ActiveRecord in application.rb, and went with multiple database connections and pools. We just ran a load test hitting both databases, and it works really well!! Sequel pools and max connections/timeouts worked as expected.

I did the separate database assignment you suggested. Thanks for the tip on database.yml, and for your support!

Have a good day,
Chuck

@jeremyevans

This comment has been minimized.

Show comment Hide comment
@jeremyevans

jeremyevans Sep 19, 2012

Owner

Glad to hear it is working. Let me know if you need additional help. While I test jdbc/oracle regularly, I don't test on Sybase at all (I only test jdbc/jtds on Microsoft SQL Server), so there may be issues there. In fact, I think the jdbc/jtds adapter is hard coded to use Microsoft SQL Server syntax, so in the cases where that differs from Sybase syntax, you may have issues. The jdbc/jtds can probably be split to support them separately if you run into issues.

Owner

jeremyevans commented Sep 19, 2012

Glad to hear it is working. Let me know if you need additional help. While I test jdbc/oracle regularly, I don't test on Sybase at all (I only test jdbc/jtds on Microsoft SQL Server), so there may be issues there. In fact, I think the jdbc/jtds adapter is hard coded to use Microsoft SQL Server syntax, so in the cases where that differs from Sybase syntax, you may have issues. The jdbc/jtds can probably be split to support them separately if you run into issues.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment