I noticed when posting that I often got an error, but somewhat later the post was made. Sometimes it wouldn’t arrive to everyone. I also noticed that checking the “preview” would load a long time and throw the same error, but after trying a couple of times, it worked and posting also worked. Today I decided to look somewhat deeper into it.
I had already seen that a lot of IO to the disk happened when trying this, so I expected some heavy query running. I checked what queries were running using
-- Currently running queries
-- https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
select now() - query_start, psa.query, psa.* FROM pg_catalog.pg_stat_activity psa
where psa.datname = 'pleroma_ynh' -- my DB name
and psa.application_name not like 'DBeaver%' -- ignore queries from the client I use
and psa.query not like 'LISTEN%'
order by query_start
;
One query I saw was on oban_jobs.
SELECT 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"
FROM "oban_jobs" AS o0
WHERE (o0."state" = 'scheduled')
AND (o0."queue" = 'activity_expiration')
AND (o0."args"->>'activity_id' = '$1')
;
According to the statistics there’s about 7.000.000 records in there!
SELECT reltuples::bigint AS estimate FROM pg_class where relname='oban_jobs';
-- 7856348|
Most are completed or discarded.
select state, count(*) FROM oban_jobs
group by state;
/*
state |count |
---------+-------+
available| 137|
scheduled| 13623|
executing| 457|
retryable| 410436|
completed|5633742|
discarded|1798991|
cancelled| 1|
*/
So I wondered if these can’t be removed and if we shouldn’t do this by default. I quickly found Oban.Plugins.Pruner — Oban v2.15.2 (I’m on Akkoma 3.12.0 and in mix.exs I see that Oban is version 2.15.2).
There I see there’s a setting to activate Oban.Plugins.Pruner
, but checking config.ex, it’s enabled akkoma/config/config.exs at develop - AkkomaGang/akkoma - Akkoma Development. And I’m not overriding it in neither prod.exs nor prod.secret.exs. I also don’t see a key in the database configuration with oban (select * from config
).
So afaict, it should prune ‘completed’, ‘cancelled’ and ‘discarded’ jobs, but it seems it hasn’t for my instance since 2023-04-15, a bit over a year ago.
select min(completed_at) FROM oban_jobs
where state in ('completed','cancelled','discarded');
/*
min |
-----------------------+
2023-04-15 15:17:52.591|
*/
For now, I’ll try deleting them manually and see if this helps me. But the question does remain why it doesn’t prune, and also if other people maybe also have this problem?
select state, count(*) FROM oban_jobs group by state;
should give no or barely any results for ‘completed’, ‘cancelled’ or ‘discarded’.