Oban - Caching Ecto Statements ETS Table memory leak -> OOM

nono.31 · November 20, 2024, 2:51am

Hello

We’re using Oban.Pro 1.14
we have 23 queues
we use partitioning
we use uniqueness
we use local limits
we use global limits
we use rate limits
we use DynamicCron heavily (1800+ cron entries)
we have multiple nodes
we process millions of jobs a day

We are having an issue where our Ecto.Repo ETS table is slowly growing until we get an OOM (currently reaching our limit of 8GB).

Our Repo is mainly used for Oban.

I am trying to dig deeper into this to try to isolate where this can come from, but it seems related to internals of Oban

Here we are using 105MB after a few hours:

Some of the ETS table details at this point:

has 16178 references to the oban_jobs table
has 12 reference to name: "ecto_*

You can find a small portion of it at https://gist.githubusercontent.com/mathieurousseau/6e6529da36bc09927fb9227ed4acbebf/raw/72f46766b6acf9aede031d820e70df59577afd10/gistfile1.txt

Something I have isolated:

The leader ecto ets table does not grow if it does not process any jobs (probably only taking care of main Oban business and the Cron)

What can I provide to help with this investigation?
Is there some obvious configuration that you know could mitigate this? (using prepare: :unamed on ecto configuration did not change anything)

Thanks.

Mathieu

benwilson512 · November 20, 2024, 4:15am

What version of Ecto are you using? I seem to recall some isssue in Ecto related to this that was fixed at one point.

dimitarvp · November 20, 2024, 4:34am

Probably talking about this?

sorentwo · November 20, 2024, 12:21pm

As indicated, this is fundamentally an Ecto issue. There are improvements in more recent versions of Pro to compensate for this issue by disabling prepared statements for highly dynamic queries.

Aside from upgrading Ecto and Pro, you could disable prepared statements for all Repo queries with prepare: :unnamed (double check the options in the Ecto docs).

nono.31 · November 20, 2024, 2:57pm

About the Ecto fix post:
You can find me saying on that post that last version of Ecto did not solve the issue for us

We tested 1.5 RC but issue remained. We are on latest Ecto and prepare: :unnamed did not work

I will try to create a dummy project to reproduce this behaviour.

dimitarvp · November 20, 2024, 3:09pm

Whoops, sorry. No clue then.

sorentwo · November 20, 2024, 3:10pm

That should disable prepared statement caching entirely. Are you certain you configured that for the Repo that Oban is using?

Please do. If you have a reproduction I’ll gladly investigate.

nono.31 · November 25, 2024, 3:52pm

yeah I am sure I have configured prepare: :unnamed on the Repo

The catch though, I have doubled check on our deploy and:

prepare: :unnamed done in runtime.exs does not work

I will put it in the config.exs and test again.

nono.31 · November 25, 2024, 5:37pm

Well, that did not make any difference. I doubled check the conf was loaded and ETS table still grows

sorentwo · November 26, 2024, 8:45am

That sounds like an issue with ecto or dbconnection itself then. Oban makes judicious use of prepare: :unnamed for frequent, variable queries, and it would be an problem if the option isn’t being respected.

nono.31 · November 27, 2024, 11:06pm

yes, that’s probably it.

I have a dummy project that I need to clean up that I will share shortly.

But here are the queries that are still being cached:

all, delete_all, update_all

Example of the largest one (141k):

nono.31 · November 28, 2024, 5:17pm

Hi

here is the POC:

https://github.com/mathieurousseau/simple_oban

You will be able to have the ETS table growing.

The homepage will give you a list of the entries of the SimpleOban.Repo ETS tablle.

sorentwo · November 29, 2024, 11:03am

The repo appears to be private. Will you make it public, or invite us (@sorenone and @sorentwo)?

nono.31 · November 29, 2024, 4:46pm

That was private

Made it public.

Also on our real app, after 11h we had: 2695 entries of :update_all for a total of 2714 entries.

nono.31 · December 18, 2024, 5:54am

Hello.

Do you need some assistance with the sample project?

I know it is holiday season

Best,

sorentwo · December 19, 2024, 10:38am

No assistance is needed. The Ecto cache (what is stored in ETS) is separate from prepared statements, and there’s no way to disable it as far as I can tell. The high cardinality of partitioned queries appears to cause the table growth, and that’s not a trivial fix on the Oban/Pro side.

We plan on overhauling those queries for reduced cardinality, speed, and flexibility in Pro v1.6. A cardinality fix may be possible as a v1.5 patch.

nono.31 · January 3, 2025, 3:24pm

Thank!!!

ataideal · September 18, 2025, 4:25pm

Hi, I just wanted to raise that we faced this same issue on one of our services, where we are getting OOM and container restarts every day, even using Oban Pro v1.5+.Tracking down this issue it started a few months ago when we enabled rate limit on one of our queues, the ratelimit uses 2 arguments where one of them is a fixed map size and the second on is a list of string (which might be the issue here).We notice that oban fetch_jobs query started to generate thousand of different queries because of this ratelimit and since each of these queries are going to Ecto ETS table (prepared statements), the memory eventually blows up.here is one of these many queries generated:

WITH "subset" AS (SELECT ss0."id" AS "id" FROM (SELECT sso0."id" AS "id", sso0."priority" AS "priority", sso0."scheduled_at" AS "scheduled_at", sso0."worker" AS "worker", sso0."args" AS "args", dense_rank() OVER "partition" AS "rank" FROM "public"."oban_jobs" AS sso0 INNER JOIN (SELECT ssso0."id" AS "id" FROM "public"."oban_jobs" AS ssso0 WHERE ((ssso0."state" = 'available') AND (ssso0."queue" = $1)) ORDER BY ssso0."priority", ssso0."scheduled_at", ssso0."id" LIMIT 5000) AS sss1 ON sso0."id" = sss1."id" WINDOW "partition" AS (PARTITION BY sso0."args"->>$2, sso0."args"->>$3 ORDER BY sso0."priority", sso0."scheduled_at", sso0."id")) AS ss0 WHERE (((ss0."rank" <= $4) AND ss0."args" @> $5) OR (((ss0."rank" <= $6) AND ss0."args" @> $7) OR (((ss0."rank" <= $8) AND ss0."args" @> $9) OR (((ss0."rank" <= $10) AND ss0."args" @> $11) OR (((ss0."rank" <= $12) AND ss0."args" @> $13) OR (NOT (ss0."args" @> $14) AND (NOT (ss0."args" @> $15) AND (NOT (ss0."args" @> $16) AND (NOT (ss0."args" @> $17) AND (NOT (ss0."args" @> $18) AND (NOT (ss0."args" @> $19) AND (NOT (ss0."args" @> $20) AND (NOT (ss0."args" @> $21) AND (NOT (ss0."args" @> $22) AND (NOT (ss0."args" @> $23) AND (NOT (ss0."args" @> $24) AND (NOT (ss0."args" @> $25) AND (NOT (ss0."args" @> $26) AND (NOT (ss0."args" @> $27) AND (NOT (ss0."args" @> $28) AND (NOT (ss0."args" @> $29) AND (NOT (ss0."args" @> $30) AND (NOT (ss0."args" @> $31) AND (NOT (ss0."args" @> $32) AND (NOT (ss0."args" @> $33) AND (NOT (ss0."args" @> $34) AND (NOT (ss0."args" @> $35) AND (NOT (ss0."args" @> $36) AND (NOT (ss0."args" @> $37) AND (NOT (ss0."args" @> $38) AND (NOT (ss0."args" @> $39) AND (NOT (ss0."args" @> $40) AND (NOT (ss0."args" @> $41) AND (NOT (ss0."args" @> $42) AND (NOT (ss0."args" @> $43) AND (NOT (ss0."args" @> $44) AND (NOT (ss0."args" @> $45) AND (NOT (ss0."args" @> $46) AND (NOT (ss0."args" @> $47) AND (NOT (ss0."args" @> $48) AND (NOT (ss0."args" @> $49) AND (NOT (ss0."args" @> $50) AND (NOT (ss0."args" @> $51) AND (NOT (ss0."args" @> $52) AND (NOT (ss0."args" @> $53) AND (NOT (ss0."args" @> $54) AND (NOT (ss0."args" @> $55) AND (NOT (ss0."args" @> $56) AND (NOT (ss0."args" @> $57) AND (NOT (ss0."args" @> $58) AND (NOT (ss0."args" @> $59) AND (NOT (ss0."args" @> $60) AND (NOT (ss0."args" @> $61) AND (NOT (ss0."args" @> $62) AND (NOT (ss0."args" @> $63) AND (NOT (ss0."args" @> $64) AND (NOT (ss0."args" @> $65) AND (NOT (ss0."args" @> $66) AND (NOT (ss0."args" @> $67) AND (NOT (ss0."args" @> $68) AND (NOT (ss0."args" @> $69) AND (NOT (ss0."args" @> $70) AND (NOT (ss0."args" @> $71) AND (NOT (ss0."args" @> $72) AND (NOT (ss0."args" @> $73) AND (NOT (ss0."args" @> $74) AND (NOT (ss0."args" @> $75) AND (NOT (ss0."args" @> $76) AND (NOT (ss0."args" @> $77) AND (NOT (ss0."args" @> $78) AND (NOT (ss0."args" @> $79) AND (NOT (ss0."args" @> $80) AND (NOT (ss0."args" @> $81) AND (NOT (ss0."args" @> $82) AND (NOT (ss0."args" @> $83) AND (NOT (ss0."args" @> $84) AND (NOT (ss0."args" @> $85) AND (NOT (ss0."args" @> $86) AND (NOT (ss0."args" @> $87) AND (NOT (ss0."args" @> $88) AND (NOT (ss0."args" @> $89) AND (NOT (ss0."args" @> $90) AND (NOT (ss0."args" @> $91) AND (NOT (ss0."args" @> $92) AND (NOT (ss0."args" @> $93) AND (NOT (ss0."args" @> $94) AND (NOT (ss0."args" @> $95) AND (NOT (ss0."args" @> $96) AND (NOT (ss0."args" @> $97) AND (NOT (ss0."args" @> $98) AND (NOT (ss0."args" @> $99) AND (NOT (ss0."args" @> $100) AND (NOT (ss0."args" @> $101) AND (NOT (ss0."args" @> $102) AND (NOT (ss0."args" @> $103) AND (NOT (ss0."args" @> $104) AND (NOT (ss0."args" @> $105) AND (NOT (ss0."args" @> $106) AND (NOT (ss0."args" @> $107) AND (NOT (ss0."args" @> $108) AND (NOT (ss0."args" @> $109) AND (NOT (ss0."args" @> $110) AND (NOT (ss0."args" @> $111) AND (NOT (ss0."args" @> $112) AND (NOT (ss0."args" @> $113) AND (NOT (ss0."args" @> $114) AND (NOT (ss0."args" @> $115) AND (NOT (ss0."args" @> $116) AND (NOT (ss0."args" @> $117) AND (NOT (ss0."args" @> $118) AND (NOT (ss0."args" @> $119) AND (NOT (ss0."args" @> $120) AND (NOT (ss0."args" @> $121) AND (NOT (ss0."args" @> $122) AND (NOT (ss0."args" @> $123) AND (NOT (ss0."args" @> $124) AND (ss0."rank" <= $125))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) ORDER BY ss0."priority", ss0."scheduled_at", ss0."id" LIMIT $126) UPDATE "public"."oban_jobs" AS o0 SET "state" = $127, "attempted_at" = $128, "attempted_by" = $129, "attempt" = o0."attempt" + $130 FROM "subset" AS f1 WHERE (((o0."id" = f1."id") AND (o0."state" = 'available')) AND (o0."attempt" < o0."max_attempts")) RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"

So I am wondering if the ratelimit feature was not designed to work with list type arguments or if there is a better way to ratelimit on this case?

sorentwo · September 18, 2025, 4:43pm

There were cardinality limitations for partitions prior to Pro v1.6. One of the primary improvements in v1.6 is how it handles these types of partitioned queries.

Schultzer · September 18, 2025, 4:53pm

A simple solution to avoid this in the future would be to use a library that does not cache queries in ETS at all, e.g GitHub - elixir-dbvisor/sql: SQL provides state-of-the-art, high-performance SQL integration for Elixir, built to handle extreme concurrency with unmatched expressiveness and ergonomic query composition. Write safe, composable, parameterized queries directly, without translating to Ecto or any ORM. · GitHub