Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Custom ActiveRecord Connection Pool Size #503

Closed
Ragmaanir opened this Issue · 84 comments
@Ragmaanir

Hi,

is it possible to specify an ActiveRecord Pool Size for Sidekiq that differs from the rails environment settings? We have the default database pool size of 5 for our unicorn workers, but since we want to use ~20 sidekiq workers we would like to customize the pool size for sidekiq.

Some worker threads fail with an "could not obtain a database connection within 5 seconds [...] The max pool size is currently 5". We are not 100% sure wether this is due to the small connection pool, but decreasing the worker count to 5 made the error rate drop significantly.

Thanks

@jc00ke
Collaborator

You could incorporate @mperham's connection_pool gem so you could have your own pool outside of Rails.

@mperham
Owner

@jc00ke Unfortunately that won't solve his problem.

You can programmatically change the value in Sidekiq.configure_server, something like:

ActiveRecord::Base.configurations['production']['pool'] = 10

and that might work. My understanding is that in the AR pool, connections are lazy created; are you sure that using a size of 20 is actually a problem for you?

@Ragmaanir

Your are right, it seems that connections are lazily created:

Initially, the database connection pool is empty and it will create additional connections 
as the demand for them increases, until it reaches the connection pool limit.

(Rails guide)

So, we will try out setting the pool size to 20 in the database config file (I can remember mysql connection issues some time ago, but i cant recall wether those had to do with a big connection pool size). If that doesnt work we will try your first suggestion.

Thanks

@Ragmaanir

Ok, first we set the worker count to ~20 and the connection pool size to the same value, but we still received the error. Additionally we received some SMTP errors (too many SMTP connections, probably due to the workers sending mails too fast). So we reduced the worker count but left the connection pool size so that we can have more connections than workers. And this seems to work fine so far.

@mperham
Owner

What numbers finally worked for you?

@Ragmaanir

We are still testing and the numbers were picked willy-nilly:

workers/concurrency: 15
pool: 25

I think we process about 40k jobs (50% mails, 50% AR records) a day. Occasionally bigger bulks of jobs are inserted at once into the sidekiq-queue (~500 - 1000 jobs).

@fenelon

I have set workers to 20 and pool to 30 and still in some cases my MySQL DB (hosted on Amazon RDS) fails with "too many connections" which causes it to collapse until I restart it manually. I've checked MAX_CONNECTIONS on the DB server and it's over 130... It seems to me that it is somehow connected to the time I redeploy as if the killed workers refuse to release their connections. Can someone explain how do my processes (5xUnicorns + Sidekiq) distribute the pool number? Do they use the same pool or separate? What could possibly cause my problem?

@mperham
Owner

The connection pool does not explicitly close connections. I bet they are lingering open on the server.

@fenelon

@mperham so how do I actually trace/fight that? Does Sidekiq close them? What happens if an exception occurs?

@mperham
Owner

Sidekiq does not close connections within the pool, ever. If an exception occurs, the connection is assumed to repair itself at some point in the future when the network is healed.

@fenelon

@mperham could you possibly explain your idea further? How is it possible that if I have 30 connections in my pool and MySQL allows 135 the database still complains about 'too many connections' error?

Does Sidekiq shares the same AR pool as my app or every process (every Unicorn + Sidekiq) has it's own pool?

Also, I'm using RMagick gem. Could it possibly be caused by it?

I apologize for bothering the community, but I'm just puzzled about where to start digging. Thanks a lot!

@Ragmaanir

@fenelon As i understand it:

Each process has its own pool (pool size is set in the database.yml). So each unicorn process is able to use maximally n connections if the pool size is n. Sidekiq, since it is one process, should also be able to use maximally n connections (shared between its threads/workers, so n should be bigger than the worker count configured via the concurrency option since the worker do their work concurrently and use different connections).

Since the unicorn processes are single-threaded and AR creates connections lazily, each unicorn process should only create one connection. So for a pool size of 30 and 5 unicorn workers and sidekiq running, only 5*1 + 30 = 35 connections should be created.

Does your "too many connections" error only occur during or shortly after a deploy? I am very interested in an analysis of this problem because it might occur on our server too (at the moment everything works fine, but who knows for how long ;-) ).

@mperham
Owner

@Ragmaanir That's exactly my understanding too. People have reported the pool using too many connections but I don't understand why this would be.

@fenelon

Thanks, @Ragmaanir, for the info. Today I had to reboot my DB instance twice because of this error. Can you or @mperham suggest a way to debug this? Something I could test my system with and get back with my findings.

Here's my setup for reference:

deployer@ip-xx-xx-xx-xx:~/apps/XXX/current$ bundle exec rails c
/home/deployer/apps/XXX/shared/bundle/ruby/1.9.1/gems/mysql2-0.3.11/lib/mysql2/client.rb:44:in `connect': Too many connections (Mysql2::Error)

sidekiq.yml

---
:verbose: false
:pidfile: ./tmp/pids/sidekiq.pid
:namespace: sidekiq
:concurrency: 15
:queues:
  - [high, 100]
  - [normal, 50]
  - [default, 10]
  - [low, 1]

database.yml

production:
  adapter: mysql2
  encoding: utf8
  database: XXX_production
  username: xyz
  password: **************
  host: my.db.host.com
  port: 3306
  pool: 33
@Ragmaanir

The "Too many connections" error occurred again. The connection errors were only in the sidekiq-log. The unicorn log and the production log did not contain any similar errors. Noticeable was that right before the first connection error happened all the workers took a really long time to finish (30-40 seconds, even though the sidekiq timeout is 30).

I am really unsure about what could be the cause. Maybe some transactions (SQL inserts) were really slow due to much site activity and the workers got killed (though the sidekiq log only contains e.g. 'done: 30.015' or 'done: 43.94' for the slow workers). And when they got killed they did not have the chance to release their connection to the pool. So the pool got drained after several workers got killed and the new workers could not acquire a new connection.

@mperham
Owner

The timeout middleware comes after the activerecord middleware so connections should be returned, even if the job times out. That certainly is an interesting bit of data though.

@fenelon

I have the same "stuck workers" issue. And the timeout option for workers is set. There must be some connection between those things.

class ImageResizer
    include Sidekiq::Worker
    sidekiq_options queue: "low"
    sidekiq_options timeout: 60
    sidekiq_options :retry => 5

    # ...
end

Stuck Workers

The process indicates 15/15 busy...

$ ps ax | grep [s]idekiq
29931 ?        Sl    17:57 sidekiq 2.5.2 current [15 of 15 busy]  

...but it's only 10 in the web interface

Stuck Workers

@fenelon

That might be also interesting - I have 4 unicorn workers with no Sidekiq running and look what I've got in my database:

Processes

$ ps ax | grep [u]ni
21618 ?        Sl     0:15 unicorn master -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                           
21632 ?        Sl     0:20 unicorn worker[0] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                        
21636 ?        Sl     0:14 unicorn worker[1] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                        
21640 ?        Sl     0:20 unicorn worker[2] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production                                                        
21645 ?        Sl     0:12 unicorn worker[3] -D -c /home/deployer/apps/XXX/shared/config/unicorn.rb -E production  

Unicorn config

working_directory "/home/deployer/apps/XXX/current"
pid "/home/deployer/apps/XXX/shared/pids/unicorn.pid"
stderr_path "/home/deployer/apps/XXX/shared/log/unicorn.log"
stdout_path "/home/deployer/apps/XXX/shared/log/unicorn.log"

listen "/tmp/unicorn.XXX.sock"
worker_processes 4
timeout 100

preload_app true

before_fork do |server, worker|
  # Disconnect since the database connection will not carry over
  if defined? ActiveRecord::Base
    ActiveRecord::Base.connection.disconnect!
  end

  # Quit the old unicorn process
  old_pid = "#{server.config[:pid]}.oldbin"
  if File.exists?(old_pid) && server.pid != old_pid
    begin
      Process.kill("QUIT", File.read(old_pid).to_i)
    rescue Errno::ENOENT, Errno::ESRCH
      # someone else did our job for us
    end
  end
end

after_fork do |server, worker|
  # Start up the database connection again in the worker
  if defined?(ActiveRecord::Base)
    ActiveRecord::Base.establish_connection
  end
  child_pid = server.config[:pid].sub(".pid", ".#{worker.nr}.pid")
  system("echo #{Process.pid} > #{child_pid}")
end

DB

mysql> show full processlist;
+-----+----------+--------------------------------------------------+------------------------+---------+------+-------+-----------------------+
| Id  | User     | Host                                             | db                     | Command | Time | State | Info                  |
+-----+----------+--------------------------------------------------+------------------------+---------+------+-------+-----------------------+
|   2 | rdsadmin | localhost:31383                                  | NULL                   | Sleep   |    9 |       | NULL                  |
|  52 | level    | 212.100.140.42:50683                             | leveltravel_production | Query   |    0 | NULL  | show full processlist |
|  74 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38197 | leveltravel_production | Sleep   |    5 |       | NULL                  |
|  75 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38199 | leveltravel_production | Sleep   |    8 |       | NULL                  |
|  76 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38201 | leveltravel_production | Sleep   |    8 |       | NULL                  |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CUT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

| 157 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38321 | leveltravel_production | Sleep   |  154 |       | NULL                  |
| 158 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38322 | leveltravel_production | Sleep   |   17 |       | NULL                  |
| 159 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38325 | leveltravel_production | Sleep   |   54 |       | NULL                  |
| 160 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38326 | leveltravel_production | Sleep   |   54 |       | NULL                  |
| 161 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38327 | leveltravel_production | Sleep   |   54 |       | NULL                  |
| 162 | level    | ip-10-55-10-151.eu-west-1.compute.internal:38329 | leveltravel_production | Sleep   |   42 |       | NULL                  |
+-----+----------+--------------------------------------------------+------------------------+---------+------+-------+-----------------------+
90 rows in set (0.15 sec)

90 connections!!! On only four unicorns! Any ideas what could be the cause of the problem?

@Ragmaanir

@fenelon Did your unicorn workers get reaped? I am not sure but if workers got reaped frequently they all would try to establish new connections after each fork. I have no idea what the timout for a SQL connection on the DB side is but if it is some high value a lot of the connections shown in the processlist might be dead. Though your unicorn-worker timout is pretty high (we use 30 seconds), so dead connections through reaped workers could only build up with a speed of 4 connections per 100 seconds. Really weird ...
Do those connections just build up during regular request processing or only under certain circumstances (like during deploy or under heavy load or so)?

@fenelon

@Ragmaanir, unicorns seem to be ok. This all happens in the normal flow. But now I'm 90% sure I've found what may be causing it. I use threads to process third-party services requests. Seems they do not properly use connection pool out of the box. The next step I think I need to take is taking those operations under Celluloid's control. I'll let you know if it works out.

@mperham, could I have a bit of your expertise up there? Does the Celluloid handle connections for me or I still gonna need to explicitly check them back and forth on each Actor?

The only question still remains. 90 connections on four unicorns is equal to 22 (max connection pool) * 4 (unicorns) + 1 (my shell) + 1 (aws monitoring). Add to this 15 Sidekiq workers (in my tests they took only as much as 12) and you end up with 105 connections open at max. How could it possibly cause 'too many connections' on MySQL when it allows 135..?

Thank you all, guys! Nothing is more valuable then the community!

@mperham
Owner

Celluloid does nothing out of the box to help with connections. You can have all your actors share a connection pool, exactly like sidekiq, and that should limit your connection count to the configured pool size.

@fenelon

@mperham, where can I check out at the way you do that in Sidekiq?

I do not have any connection-pool related code in my Sidekiq workers code, but they seem to work fine. Do I need to wrap my worker code in with_connection call?

Thank you so much!

@mperham
Owner

@fenelon The Sidekiq.redis call is a wrapper around the Redis connection pool. It's used everywhere inside Sidekiq.

@fenelon

@mperham and the AR pool? Does it work well with threaded code by itself?

@mperham
Owner

Yes, they have an internal connection pool that all AR queries go through.

@fenelon

@mperham, As I understood from Johnathan's article (http://bibwild.wordpress.com/2011/11/14/multi-threading-in-rails-activerecord-3-0-3-1/) the AR connection pool sometimes shows unpredictable behaviour when used with threads. In his case the connections was not returned to the pool. He advises to wrap threaded code in with_connection block to make the AR explicitly check out and return the connection when it's finished. Sidekiq has no problems with that, but I can't say so about my code.

@mperham
Owner

If you are using an older version, I suppose that's possible but take info from random blogs with a grain of salt. I think it's a bad idea to explicitly check out connections with AR; it's just not idiomatic Rails code. If you are rolling your own threads, I'd bet a lot of money it's your bug.

Sidekiq does include a middleware by default to clean up any checked out AR connections for the worker.

@fenelon

@mperham, so you mean that ActiveRecord::Base.clear_active_connections! piece in run after each worker and makes sure they return the connections they used to the pool? Does this mean that if I include this call to my actors I can probably avoid the connection bloat?

Michael, I feel like getting you a beer :)

@stevenharman

@mperham

I wonder if we should update the Concurrency section of the Advanced Options wiki page to advise programmatically changing the ActiveRecord connection pool size via

ActiveRecord::Base.configurations['production']['pool'] = 25

rather than the hack I suggested. It seems sensible that you'd want a small connection pool for your Unicorn workers, or threads, etc., but a substantially larger pool for the Sidekiq process.

For instance, we have an app on Heroku running 2 Unicorn workers per dyno, at 75+ dynos, and a single Sidekiq worker with concurrency of 50. The hacky method of setting the connection pool size means that each Unicorn worker could soak up that many connections, but the default (5) is enough for them. But we'd like to have a connection pool of 50-ish on the Sidekiq dyno.

Does that all make sense?

@mperham
Owner

@stevenharman My understanding is that AR's pool is lazy created so setting the pool size to something large doesn't matter. My connection pool is the opposite - you have to be careful as connections are eager created.

@stevenharman

@mperham I'd buy that.

Even so, I'm wondering if it would be better to explicitly set Sidekiq's connection pool to a larger one via the initializer rather than the database.yml in general. Especially in the case of running on Heroku where you have to hack the database URL to tack the pool=xxx parameter on.

Thoughts?

@sjahandideh

@stevenharman
i add this to my config/initializers/sidekiq.rb file:

Sidekiq.configure_server do |config|
  config.redis = { :url => ENV['redis'] }
  #config.poll_interval = 15
  config.options[:concurrency] = ENV['concurrency'].to_i - 5
  config.options[:timeout] = 8
end

and read the env variables from application.yml ( using figaro gem: https://github.com/laserlemon/figaro )
and in my database.yml:

development:
  <<: *common
  database: sample_development
  pool:     <%= ENV['concurrency'] %>
  timeout:  5000
@stevenharman

@sjahandideh database.yml has no effect on Heroku as they inject their own database config. Furthermore, the programatic approach suggested by @mperham doesn't work on Heroku as ActiveRecord::Base.configurations is empty.

I still don't understand exactly why it's empty on Heroku only, but it seems to be causing other folks heartache as well: https://www.google.com/search?q=ActiveRecord%3A%3ABase.configurations+is+empty+on+heroku

Maybe @wuputah or one of the @heroku folks can chime in?

@wuputah

Add ?pool=25 to the end of your DATABASE_URL. (Don't do this, please see #503 (comment))

This works due to URL parsing present in our injected database.yml. You can view the source code in this gist or by running heroku run cat config/database.yml.

Keep in mind that there is a connection limit on many database services on Heroku, including the Postgres service by Heroku (20 for dev & basic, 500 for production databases) as well as most other database services provided by add-on providers.

(I'm unwatching here, but feel free to ping me again if you have further questions.)

@stevenharman

@wuputah that is precisely the work-around we were talking about working around. :smiley:

Specifically we want to set the connection pool size for ONLY the worker, not the web processes (letting them default to 5). However, ActiveRecord::Base.configurations being empty on Heroku leaves us in a bind. Thoughts?

@TheCorp

You might want to write some custom code in your sidekiq workers to establish a separate connection to the same db but with a different pool size and let the default be 5 for your unicorn workers. This also might be possible via middleware @mperham?

We used to inject entries into 'ActiveRecord::Base.configurations' so we could manage our db sharding/multiplexing with certain gems that required that all of the db entries be in the configurations hash so you could probably do something similar and make explicit connections to that 2nd manually added entry which has a higher pool size.

@wuputah

I see. I can confirm in production but have no idea why Rails is not populating ActiveRecord::Base.configurations - perhaps that is a bug in Rails? This is the only thing we modify in this regard. It should be possible to replicate this behavior locally by setting DATABASE_URL and using the config/database.yml I posted.

Edit: Actually I have an app set up this way already, so I did what I posted above:

% foreman run script/rails console
Loading development environment (Rails 3.2.9)
>> ActiveRecord::Base.configurations
Connecting to database specified by DATABASE_URL
=> {}

It seems that latest versions of Rails is now ignoring config/database.yml if DATABASE_URL is present and going straight to reading DATABASE_URL from the environment. I don't know how it sets or interprets extra parameters in this case, though. You might try modifying your DATABASE_URL before starting your workers or before Rails connections. A way to do this would be to create a bin/worker script (this assumes DATABASE_URL has not already been modified with a pool setting):

#!/bin/bash
export DATABASE_URL="$DATABASE_URL?pool=25"
exec bundle exec sidekiq
@TheCorp

@wuputah - I assume you are talking about the buildpack process here:

https://github.com/heroku/heroku-buildpack-ruby/blob/master/lib/language_pack/ruby.rb

In the create_database_yml function right?

I guess I assumed something about this being dynamically generated causes the configurations hash to never get initialized.

@wuputah

@TheCorp Please see above comment / edits. The fact it's ERB is not affecting AR::Base.configurations.

@stevenharman

Sounds like we need to dig into Rails to see how the ActiveRecord::Base.configurations is being initialized/populated and why this particular approach DOESN'T work.

Something else of note, Rails.configuration.database_configuration is populated on Heroku. So how/why is that populated, but not ActiveRecord::Base.configurations...

@TheCorp

Oh my bad didn't see the edit before I posted. Sorry dynamically generated may have been not the correct terminology, I just meant that the database.yml is generated based on the DATABASE_URL in your heroku env variables. But I guess that's interesting that Rails is ignoring the yml entirely.

And @stevenharman that's really interesting, I never even knew that existed, good find.

@stevenharman

@wuputah You're right. In edge Rails ActiveRecord::Base.connections is not set when we have an ENV['DATABASE_URL']. (NOTE: self is in this case ActiveRecord::Base.)

So when establish_connection is called a few lines later we end up using a ConnectionAdapters::ConnectionSpecification::Resolver to parse out the spec for the connection. That parsing handles adding any extra query string arguments as well.

This all looks similar for Rails 3.2-stable.

The "bug" to me is that having a DATABASE_URL present results in not ever setting the ActiveSupport::Base.configurations hash - which otherwise is set from app.config.database_configuration (which I believe is read from the YAML file). The connection specification resolver then has to do a lot of work to conditionally parse out the connection spec from the URL.

In any case, this is a Rails issue, I think, and for now @wuputah's suggestion of exporting our own DATABASE_URL when starting the workers might be the work around.

@wuputah

@stevenharman I think you put .connections when you meant .configurations. :smile:

Glad to hear that Rails does parse the extra query string arguments in this case. :+1:

@stevenharman

@wuputah Using the bin/sidekiq script to first export a new DATABASE_URL kinda breaks locally then. Unless you've set your own DATABASE_URL (perhaps via a .env file), which neuters the database.yml and won't allow you to switch between databases for development vs. test.

The more I look into this the more I think that rails/rails@4605b56 was a mistake.

@wuputah

That's exactly what I do! dev/prod parity is good! :smile:

For tests, I use a bin/test script to set up the environment as needed, but I'll admit this is well outside the typical Ruby flow of using rake. With a Rails app, though, rake is just too loaded down with stuff I don't want (particularly since I don't load the entire Rails environment in my tests). But yes, again, that's well outside the typical Rails flow. I'm lucky enough to be able to experiment with these things.

I don't think the problem is with AR using DATABASE_URL by default; the problem is settings loaded via DATABASE_URL and are not easily mutable from your application. It's somewhat questionable whether you should be mutating these settings at runtime anyway, though. Who is to say that a change to the configuration will have the desired effect? I would assume you must make that change before AR reads that configuration to connect to the database. So perhaps the right thing to do is to call establish_connection with your desired parameters instead.

Of course, given the connections in the pool are lazily created, need I ask why it is harmful to have a large pool size apply to all processes (i.e. also your unicorn processes)? It was probably discussed somewhere up-thread but this is getting rather large.

@TheCorp

Steve (I believe) was just mentioning that his Unicorn processes could potentially be consuming all of the connections in his pool and all they really need is the 5 default and that he'd like the larger pool size to only be restricted to his Sidekiq workers.

@stevenharman

@wuputah I also isolate tests so as to only load Rails when necessary (full-stack and ActiveRecord scope tests, primarily), and never use Rake to run tests (b/c who wants to load rails TWICE!?). But I find the bin/test approach a bit awkward - though I have used similar techniques in the past to optimize load times when running tests from Vim... so maybe I should just get over it.

Or just roll with the pool=xx parameter on DATABASE_URL and rely on ActiveRecord being well behaved and lazily loading the connections (and not soaking up all of them).

Though I still don't see the value of (especially when you look at all of the other hacks that have been added to support) automagically reading DATABASE_URL.

Also, WOW, this is a long darn thread!

@stevenharman

Well, after much spelunking through Rails, looking at how it picks a database connection "specification", initializes ActiveRecord, etc. I ended up with this:

Sidekiq.configure_server do |config|
  config.redis = { url: ENV['REDISTOGO_URL'], namespace: 'my-app' }

  database_url = ENV['DATABASE_URL']
  sidekiq_concurrency = ENV['SIDEKIQ_CONCURRENCY']
  if(database_url && sidekiq_concurrency)
    Rails.logger.debug("Setting custom connection pool size of #{sidekiq_concurrency} for Sidekiq Server")

    ENV['DATABASE_URL'] = "#{database_url}?pool=#{sidekiq_concurrency}"
    Rails.logger.debug(%Q(DATABASE_URL => "#{ENV['DATABASE_URL']}"))

    ActiveRecord::Base.establish_connection
  end

  Rails.logger.info("Connection Pool size for Sidekiq Server is now: #{ActiveRecord::Base.connection.pool.instance_variable_get('@size')}")
end

I use a SIDEKIQ_CONCURRENCY environment variable to set concurrency. Within the Sidekiq.configure_server block (which only runs on the Sidekiq sever process) when we detect both a DATABASE_URL and a SIDEKIQ_CONCURRENCY environment variable we tack the SIDEKIQ_CONCURRENCY value onto the DATABASE_URL. And then we force ActiveRecord::Base to re-establish a new connection with the database using the new pool size.

Seems to be working for me and that addresses all of my issues, and might be enough to close this issue... @fenelon, @Ragmaanir, et al... are you good?

@Ragmaanir

@stevenharman yeah, everything seems to work fine, no "too many connections" errors.

@mperham mperham closed this
@marclennox

Is it possible to determine in code the concurrency value supplied through the cli (the -c argument)?

@stevenharman

Off the cuff, maybe Sidekiq.options[:concurrency]?

@marclennox

Yep, that worked. I'm using DATABASE_URL and noticing that the Rails message about "using DATABASE_URL to connect" happens before the Sikekiq configure_server block. I'm trying to override the pool attribute in AR connection before it connects. Is the configure_server block the right place to do this?

@stevenharman

@marclennox: See my comment above for how I've done this. I also added an example to the (Advanced Options :: Concurrency](https://github.com/mperham/sidekiq/wiki/Advanced-Options) wiki page, so you can check there too.

@marclennox

Thanks Steve, I read that earlier. What's throwing me off is that I see AR log that it's connecting with DATABASE_URL before it gets to to my connection pool modification code, so I'm not sure that my change is taking affect.

15:05:17 worker.1        | Connecting to database specified by DATABASE_URL
15:05:17 worker.1        | Setting connection pool size to 17

Note also that I added a bit more robustness to the URI parsing because my DATABASE_URL value already has a default pool query argument, as well as other query parameters that I don't want to clobber.

Sidekiq.configure_server do |config|
  config.redis={url: Settings[:redis].url, namespace: Settings[:sidekiq].namespace}
  if (database_url=ENV['DATABASE_URL']) && (concurrency=Sidekiq.options[:concurrency])
    uri=URI.parse(database_url)
    # Flatten query parameter array
    params=CGI.parse(uri.query).inject({}) { |h, (k, v)| h[k] = v[0]; h }
    params['pool']=(concurrency * 1.1).round(0)
    uri.query="?#{params.to_query}"
    Rails.logger.info("Setting connection pool size to #{params['pool']}")
    ENV['DATABASE_URL']=uri.to_s
    Rails.logger.debug("DATABASE_URL => #{uri.to_s}")
  end
end
@stevenharman

@marclennox You need to be sure to

ActiveRecord::Base.establish_connection

after updating the DATABASE_URL. This will force ActiveRecord to use the new connection settings, thereby setting your pool to the new size.

And thanks for the more robust code - in reality I too have something more intelligent, but wanted to keep it simple so as to not distract folks from the goal.

@marclennox

Got it, thanks Steve!

@pboling

I am getting the error

could not obtain a database connection within 5 seconds (waited 5.014356133 seconds).
The max pool size is currently 5; consider increasing it.

But I have used the examples in this thread to override my pool size, currently to 7, but I've also tried 25 and 20.

I don't understand why it would say the current max is 5, when I am setting it to 7.

I've just refactored and simplified my code, so I am getting the error with the following:

Sidekiq.configure_server do |config|
  config.redis = {url: ENV["REDISCLOUD_URL"], namespace: 'sidekiq'}
  database_url = ENV['DATABASE_URL']
  concurrency = Sidekiq.options[:concurrency].to_i
  if (database_url && concurrency)
    uri = URI.parse(database_url)
    puts "Original DATABASE_URL => #{uri}"
    pool_size = (concurrency * 1.1).round(0)
    params = {}
    params['pool'] = pool_size
    uri.query = "?#{params.to_query}"
    ENV['DATABASE_URL'] = uri.to_s
    puts "Sidekiq Server is reconnecting with new pool size: #{params['pool']} - DATABASE_URL => #{uri.to_s}"
    ActiveRecord::Base.establish_connection
  end

The original database url does not have any pre-existing query parameters, so I am just appending the pool size, and the URL that is printed looks fine to me.

My Heroku plan is supposed to have 20 connections available to it, and the database.yml is set to use 20, though from this thread I am not sure if heroku is even using that setting.

@stevenharman

@pboling

A few things:

1)

if (database_url && concurrency)

currency is always going to be truthy because in Ruby, nil.to_i #=> 0, which is truthy. I would wager that is not what you intend.

2)

database.yml is set to use 20, though from this thread I am not sure if heroku is even using that setting.

If you are running a recent version of Rails, that is correct; Rails completely ignores the database.yml when a DATABASE_URL env var is available.

3) Just to be sure, is that error being reported by your Sidekiq worker, or the Rails app?

@pboling
  • \1. Good call. I'll fix that.

  • \2. I am on 3.2.12, and that was my understanding from this thread. However going back to look at my database.yml I had actually completely removed the staging and prod sections from the file. So there is no setting in database.yml, and that nothing is being ignored by Heroku's DATABASE_URL.

I followed a stack overflow answer to a similar issue to add a pool param to the URL itself:

heroku config -s --remote staging | awk '/^DATABASE_URL=/{print $0 "?pool=20"}' | xargs heroku config:add --remote staging

This seems to have had no effect, other than now my Sidekiq Server hack says the pool was 20 and it is reestablishing a connection with 10, based on concurrency of 6.

The error remained. :(

  • \3. The sidekiq worker is failing its processing with that error. The app is not under load (staging env), so I can't really tell how it is faring.

  • \4. In the end I reduced concurrency to 4, to be within this mysterious 5, and that resolved all these error.
    This will not be a good solution at scale.

@lulalala

To those who said on Heroku the ActiveRecord::Base.configurations is empty, could you try to see if ActiveRecord::Base.connection_pool.instance_variable_get(:@size) is empty too?

If it is set, maybe we can set the pool size programmatically by calling ActiveRecord::Base.connection_pool.instance_variable_set(:@size, 30) here.

@mperham
Owner

I'd urge everyone to contact Heroku support asking for a good way to configure the database pool size. The status quo is ridiculous.

@TheCorp

I've already mentioned this to them, it is pretty heinous

@wuputah

Representing Heroku, please see my posts on this thread. I think people have been bottom posting and not reading the thread, though that is understandable given the length. There are a myriad of solutions depending on how you want to manage the problem.

This is our present recommendation - it allows you to manage fine-grained details of your database connection without modifying or depending on DATABASE_URL:
https://devcenter.heroku.com/articles/concurrency-and-database-connections#connection-pool

@martinstreicher

I see a summary for the connection pool issue on Heroku. What is the bottom line for Rails apps running on a typical Unix server? Is it to programmatically set

ActiveRecord::Base.configurations['production']['pool'] = 25

Why is the pool parameter in database.yml being ignored?

@wuputah
Rails.application.config.after_initialize do
  ActiveRecord::Base.connection_pool.disconnect!

  ActiveSupport.on_load(:active_record) do
    config = Rails.application.config.database_configuration[Rails.env]
    config['reaping_frequency'] = ENV['DB_REAP_FREQ'] || 10 # seconds
    config['pool']              = ENV['DB_POOL'] || 5
    ActiveRecord::Base.establish_connection(config)
  end
end
@martinstreicher

@wuputah Thanks. So this is a general solution, assuming you set DB_POOL in your Heroku config and in the Unix shell for EC2 deploys, say? And if config['pool'] is set, this sets it to 5. Why not...

config['pool'] ||= .....
@wuputah

On Heroku, we don't recommend modifying DATABASE_URL anymore, which means Rails will be using the default setting of 5.

In general, using an environment variable maximizes your flexibility. For instance, this code can be used with sidekiq to customize your database pool for sidekiq processes:

env DB_POOL=25 bundle exec sidekiq

Or even do this:

env DB_POOL=$SIDEKIQ_DB_POOL bundle exec sidekiq

Alternatively you can capture any such logic in a bin/worker or bin/sidekiq script.

@martinstreicher

Thanks @wuputah.

Why does this not work out of the box from Rails and database.yml? I read the thread above. Wrong order of initialization?

@HenleyChiu

The Heroku configuration "issues" are really trivial, compared to the main issue, which I have definitely experienced. I have X Sidekiq workers, and somehow there's 3X number of db connections after awhile, most of them idle, all coming from Sidekiq.

I don't see the disadvantage of doing something like this:
def perform
ActiveRecord::Base.connection_pool.with_connection do
do your work here
end
end

@meltingice

I'm having these same problems, but not on Heroku. No matter what I do, Sidekiq eventually fails because it says the pool count is only 5. It's set to 30 in the database.yml. I've also tried setting it manually in the code for grins, and it didn't do anything:

ActiveRecord::Base.configurations['production']['pool'] = 25

Ultimately I got around this problem by reducing Sidekiq concurrency to 5 and by running 2 separate Sidekiq processes. Doesn't feel ideal, but it works.

@akitaonrails akitaonrails referenced this issue from a commit
Commit has since been removed from the repository and is no longer available.
@akitaonrails akitaonrails referenced this issue from a commit
Commit has since been removed from the repository and is no longer available.
@espen

I'm setting sidekiq concurrency to 2 and database pool to 2. I get occasional errors about the database pool being too small. On Heroku.

@PikachuEXE

I set it like this:

require 'try_to'

# To config Redis server, please use REDIS_PROVIDER or REDIS_URL
# https://github.com/mperham/sidekiq/wiki/Advanced-Options#setting-the-location-of-your-redis-server

sidekiq_concurrency_number = try_to(3) { Integer(ENV.fetch('SIDEKIQ_CONCURRENCY')) }

Sidekiq.configure_server do |config|
  config.redis = { ... }

  # Change DB pool size for ActiveRecord
  # Replace with MongoDB or whatever
  if defined?(ActiveRecord::Base)
    config = Rails.application.config.database_configuration[Rails.env]
    config['reaping_frequency'] = (ENV['DATABASE_REAPING_FREQUENCY'] || 10).to_i # seconds
    config['pool'] = sidekiq_concurrency_number + 2 # Give it some buffer
    ActiveRecord::Base.establish_connection
    Rails.logger.info('Connected to ActiveRecord in Sidekiq')
  end
end

Sidekiq.configure_client do |config|
  config.redis = { ... }
end

Sidekiq.options[:concurrency] = sidekiq_concurrency_number
# Sidekiq got a bug for this default option
Sidekiq.default_worker_options = { queue: :default, retry: false, backtrace: true }.stringify_keys
@espen

But why the need of a buffer? Why is Sidekiq using more db connections than workers?

@PikachuEXE

Just to avoid and dead connection that are not reaped yet

@zaharpecherin

What numbers pool need to Heroku PX dynos?

@seuros
Collaborator

20-30 max (depend on your application)

@zaharpecherin

Why when I use PX dynos Heroku application crashed?
We are use Unicorn and Amazon RDS.
It is work for 1X and 2X dynos but not work PX dynos.

@seuros
Collaborator

Maybe you should contact Heroku support since it PX dynos issue and not sidekiq.

@zaharpecherin

I thought you could help me :) Sorry, thanks!

@vigram

As per my understanding this 'too many connections' issue is due to Rails ActiveRecord, as it's not check-In back the DB connection back to pool properly when you are spawning application level Threads. Seems, it is programmer's responsibility to check-In back the connection explicitly in multi-threaded mode. For more detail, look at my article about my experiment

@zetter

I wanted to share a problem we had recently because we had followed some of the patterns in this thread.

To make sure Sidekiq used a bigger pool size that our default we had code similar to some of the snippets above:

Sidekiq.configure_server do |config|
  ENV['DATABASE_URL'] = "#{ENV['DATABASE_URL']}?pool=#{sidekiq_concurrency}"
  ActiveRecord::Base.establish_connection
end

This used to work fine, but we found it stopped working when we performed a recent Rails upgrade. As of Rails v4.1.0 the establish_connection method was changed to not default to the current DATABASE_URL environment variable when Rails had already been loaded.

To solve this problem we are now explicitly passing in the database URL we want to use into establish_connection:

Sidekiq.configure_server do |config|
  database_url = "#{ENV['DATABASE_URL']}?pool=#{sidekiq_concurrency}"
  ActiveRecord::Base.establish_connection(database_url)
end

This assumes that you're not calling establish_connection anywhere else within your workers, I'm not sure what the behaviour would be if you are.

@stevenharman

@zetter,
As of Rails 4.1 your database.yml file will be merged with the DATABASE_URL Env var. (see rails/rails#13582)

Given then, I'd probably opt for

# database.yml
pool: <%= ENV['DATABASE_POOL_SIZE'] || 5 %>
url: <%= ENV['DATABASE_URL'] %>

See the docs for configuring the database, and the connection preferences.

@henrik

@stevenharman You typoed url: url:.

@stevenharman

@henrik Oops! Fixed. Thanks for catching that.

/me: need more :coffee:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.