Database connection errors running Oban on Heroku

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
  1. 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?

  2. 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?

  3. Should I be worried about this?

Thanks for any insight!

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.

2 Likes

Thanks for the reply @benwilson512 :+1:

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?

1 Like

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.

3 Likes

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
4 Likes

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 :slight_smile:

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.

1 Like

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 :+1:

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.

1 Like

Definitely. Please open a PR :+1:

2 Likes

Will do :+1:

2 Likes

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.

3 Likes

Yes, you’re right. 15 sounds like a sensible number. I’ll go with that :+1:

1 Like