Database Pool Management with Sidekiq and load_async

Recently I came across a very common issue after integrating a few Sidekiq jobs to a project.

During this activity, I set the pool size in database.yml of the worker process equal to the concurrency I set for Sidekiq. Its recommended to have pool size equal to or greater than the concurrency setting otherwise, you can run into issues or even if there are no apparent errors, this will likely cause additional latency in the queues.

But strangely, I still ran into this error:

ActiveRecord::ConnectionTimeoutError <background job name> could not obtain a connection from the pool within 5.000 seconds (waited 5.062 seconds); all pooled connections were in use

As the error suggests, the worker thread executing the job attempted to obtain a database connection but was not able to and after waiting for 5 seconds raised this exception.

Possible Reason??

In my case, the issue was not as straight forward as the pool size being less than the total concurrency set for Sidekiq.

load_async

I searched for the codebase for Thread.new initially to see if there are any queries being executed in separate threads in any of the jobs, but the only occurrences I came across were not relevant.
Secondly I looked for load_async (which basically queries database asynchronously details) and actually found it being used by one of the classes that was being instantiated and used in one of the background jobs.

And that was it, despite having a pool value equal to the concurrency that I have set in Sidekiq, having load_async in one of the jobs meant, this was an additional (unaccounted) thread that can consume a connection from the pool causing another thread to wait.

With the root cause of the issue established, I adjusted my pool size for the worker process (in database.yml) as recommended here

pool = total_concurrency + global_executor_concurrency + 1

Where global_executor_concurrency refers to the number of asynchronous queries that can be executed concurrently and its default value is 4.

Which meant for me, the eventual value of the pool size I set was

pool = total_concurrency + 4 + 1

For me this was an interesting finding and hope it helps and save some time for you if you come across similar scenario.

Happy Coding!