This is probably my misunderstanding rather than an Ecto or Oban issue, but I’ve only seen this error since I deployed a toy app using Oban to Heroku.
I set the POOL_SIZE to 18 to allow for mix tasks and pgweb (my database client) to run.
I have one worker which hits a REST API and inserts about 1000 rows into a table within a transaction.
I haven’t seen the error when running on a schedule (I have a crontab entry setup in Oban) but when I insert a job via mix ( heroku run "POOL_SIZE=1 iex -S mix") I get this error.
15:12:47.956 [error] GenServer Oban.Queue.Default.Producer terminating
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2655ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:
1. By tracking down slow queries and making sure they are running fast enough
2. Increasing the pool_size (albeit it increases resource consumption)
3. Allow requests to wait longer by increasing :queue_target and :queue_interval
See DBConnection.start_link/2 for more information
(db_connection) lib/db_connection.ex:745: DBConnection.run/3
(stdlib) gen_server.erl:637: :gen_server.try_dispatch/4
(stdlib) gen_server.erl:711: :gen_server.handle_msg/6
(stdlib) proc_lib.erl:249: :proc_lib.init_p_do_apply/3
I’m pretty sure that Oban uses the same Repo and database connection pool as you tell Oban what your Repo is called in the config. Is that correct?
I believe that each Oban worker is run in its own isolated process but I’m guessing it still shares database connections from the pool?
Only sort of. Oban also needs to start some dedicated connections outside the Repo pool in order to support postgres notifications. It uses the config found on the Repo, but it is an additional connection.
Can you elaborate on why you’re trying to set a pool size of 1? This allows only a single database operation at a time across the entire application, which is going to be an issue.
Only sort of. Oban also needs to start some dedicated connections outside the Repo pool in order to support postgres notifications. It uses the config found on the Repo, but it is an additional connection.
Ok, that makes sense. I’ve just set the POOL_SIZE to 17, and when I check my Postgres database on Heroku is says 18/20 connections are being used. So I assume that extra one is Oban?
Can you elaborate on why you’re trying to set a pool size of 1? This allows only a single database operation at a time across the entire application, which is going to be an issue.
That was just for a one-off iex session, but I’m now realising that would be an issue as you point out. I was just trying to get a remote console on Heroku where I can start a job manually, but I see now that running iex -S mix is going to start the app on another dyno, which would also start a new Oban process.
If I have 20 connections, what is the best practice if I want to run the application and also an additional iex session now and again?
I’m not 100% sure about how it works with heroku, but if they let you run commands inside a running dyno, then if you use releases you can just attach a console to that release allowing you to use the pool that it is already running instead of spawning an additional instance of the app.
If you can’t do a release + remote_console on heroku, then is there an issue just opening up another 20 connections? Will it reach a database limit? You probably want to make sure that you have a flag to disable Oban on the iex -S mix session by the way, since you probably don’t want to run jobs inside that session.
Your initial issue was likely due to the single connection being held by your job while oban tries to fetch more jobs or record heartbeats.
That’s correct. Oban uses one additional connection for pubsub notifications.
I typically use something like this in application.ex to prevent jobs running in an iex session:
defp oban_config do
opts = Application.get_env(:my_app, Oban)
if Code.ensure_loaded?(IEx) and IEx.started?() do
opts
|> Keyword.put(:crontab, false)
|> Keyword.put(:queues, false)
else
opts
end
end
I’m not 100% sure about how it works with heroku, but if they let you run commands inside a running dyno, then if you use releases you can just attach a console to that release allowing you to use the pool that it is already running instead of spawning an additional instance of the app.
That’s something I’ll have to look into. For now, @sorentwo’s solution works well
You probably want to make sure that you have a flag to disable Oban on the iex -S mix session by the way, since you probably don’t want to run jobs inside that session.
Very good point. This is something I’ve thought about before.
Your initial issue was likely due to the single connection being held by your job while oban tries to fetch more jobs or record heartbeats.
Ah, thanks for clarifying.
That’s correct. Oban uses one additional connection for pubsub notifications.
Good to know
Is it worth adding a note about how Oban uses database connections somewhere in the docs? This probably doesn’t matter as much outside of Heroku, but would be nice to have something searchable. I’m happy to add it if you think it’s worth it.
I typically use something like this in application.ex to prevent jobs running in an iex session:
That’s excellent, thanks. I’ve just tried it out and it works great.
I also run on Heroku and I’ve noticed that sometimes ecto goes over the number of connections allotted to it so I leave more of a buffer than you are leaving here. I’d probably set the POOL_SIZE to 15.
