Running out of connections

[matteeyah]

Summary

I've been getting a couple of connection errors in production. I have about 110-120 free connection on the database, but SolidQueue is reporting it couldn't get a connection.

Here's the error in question:

2024-01-29 14:28:07.568 [optonal_prod] worker.1 [SolidQueue] could not obtain a connection from the pool within 5.000 seconds (waited 5.081 seconds); all pooled connections were in use

Here's my config/database.yml

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: optonal_development
  username: <%= 'postgres' if File.exist?('/.dockerenv') %>
  password:
  host: <%= 'db' if File.exist?('/.dockerenv') %>

test:
  <<: *default
  database: optonal_test
  username: <%= ENV.fetch('POSTGRES_USER', '') %>
  password: <%= ENV.fetch('POSTGRES_PASSWORD', '') %>
  host: <%= ENV.fetch('CI', '').empty? ? '' : 'postgres' %>

production:
  <<: *default
  database: optonal_production

Here's my config/solidqueue.yml

default: &default
   dispatchers:
     - polling_interval: 1
       batch_size: 500
   workers:
     - queues: [ real_time, background ]
       threads: 5
       processes: 1
       polling_interval: 0.1

development:
 <<: *default

test:
 <<: *default

production:
 <<: *default

Proposal

Document which connection pool SolidQueue uses to connect to the database. Document how many connections does SolidQueue need (e.g. threads * processes).

[matteeyah]

What's throwing me off here - I've set threads to 5, but I regularly see more than 5 entries in the solid_queue_claimed_executions table - does that mean it's trying to run more than 5 jobs at the same time? Because that would explain running out of connections since config/database.yml limits the number of connections in the pool to 5.

[lrjbrual]

Hi @matteeyah, I am new using solid queue.

When I push our codes with solid queue in the server, it seems I stock always with this:

[2024-02-01 22:07:45] [SolidQueue] Starting Worker(pid=7, hostname=salesforce-connect-58dc4c96bc-qrj8w, metadata={:polling_interval=>2, :queues=>"*", :thread_pool_size=>2})
[2024-02-01 22:07:45] [SolidQueue] Starting Worker(pid=6, hostname=salesforce-connect-58dc4c96bc-qrj8w, metadata={:polling_interval=>2, :queues=>"*", :thread_pool_size=>2})
[2024-02-01 22:07:45] [SolidQueue] Starting Worker(pid=8, hostname=salesforce-connect-58dc4c96bc-qrj8w, metadata={:polling_interval=>2, :queues=>"*", :thread_pool_size=>2})
[2024-02-01 22:07:45] [SolidQueue] Starting Worker(pid=9, hostname=salesforce-connect-58dc4c96bc-qrj8w, metadata={:polling_interval=>2, :queues=>"*", :thread_pool_size=>2})
[2024-02-01 22:07:48] [SolidQueue] Starting Dispatcher(pid=5, hostname=salesforce-connect-58dc4c96bc-qrj8w, metadata={:polling_interval=>10, :batch_size=>500})
[]

our .yml

default: &default
  dispatchers:
    - polling_interval: 10
      batch_size: 500
  workers:
    - queues: "*"
      threads: 2
      processes: 4
      polling_interval: 2

Seems like we are missing something. I already upgraded the server to 1gig with 4core. but this doesn't work, solid_queue is heavy process in our server. is that normal, or can you give us a point to look on it? thank you

[matteeyah]

@lrjbrual I don't think these two are related.

I'm not very familiar with the internals of SolidQueue, but the log lines that you sent don't seem to match with your config. The config has only one worker configured, but the logs seem to indicate there's multiple workers booting up. Maybe @rosa can help shed some light on your situation.

[rosa]

Hey @matteeyah, the pool used is the app's pool by default, which you'd have configured in your database.yml file as

pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

If you're using 5 threads, you should have at least 7 connections available in the pool, as you'd need one per thread + the ones used by the worker (check this, that I plan to wire up to emit a warning when you start the supervisor).

@lrjbrual, what's your issue in particular? The logs you show seem ok, 4 workers with 2 threads each + 1 dispatcher. Could you provide more details about what's not working?

[jasoncharnes]

If you're using 5 threads, you should have at least 7 connections available in the pool, as you'd need one per thread + the ones used by the worker (check this, that I plan to wire up to emit a warning when you start the supervisor).

@rosa thank you for pointing to this!

I was experiencing the same issue. After seeing this I'm fairly certain it's because I was using the same value for max thread and pool. I've bumped the pool up by two and will keep an eye out. Thanks, again!

[matteeyah]

I've bumped the pool up by two and will keep an eye out.

I actually suggest doing the inverse - reduce the amount of threads a worker uses by two. Bumping the thread pool up by two affects the Rails app as well, and if you tweaked the thread pool for optimum performance, you should keep it constant.

[matteeyah]

Thanks for the clarification @rosa! This setup is a little confusing because:

1. The default config for Solid Queue is 1 worker with 5 threads
   1. Which use up to 7 connections
2. The default setting for the database connection pool is 5
   1. ENV.fetch("RAILS_MAX_THREADS") { 5 }
3. In config/puma.rb the default number of threads used is 5
   1. max_threads_count = ENV.fetch("RAILS_MAX_THREADS", 5)

This means that by default:

1. There's 5 threads per puma process
2. The database connection pool is 5 connections
3. But SolidQueue uses 7 connections per process by default

This means that by default, Solid Queue is using 2 more connections than the pool. I think this is confusing, but it should be easily fixable by:

1. Documenting the database connection pool usage
   1. I opened a PR for this Document database connection usage #149
2. Updating the default configuration for Solid Queue to use 3 threads per worker
   1. I'll hold off on creating a PR for this, because I'd love to hear your opinion about this first

[rosa]

Hey @matteeyah, yeah, I think we could go with:

Updating the default configuration for Solid Queue to use 3 threads per worker

That makes sense, the value of 5 was something set in the very beginning of the project and for completely unrelated reasons that don't apply now.

[rosa]

I did the change to the default number of threads, it's 3 now, so I'm going to close this one.