ahydrax / Hangfire.PostgreSql

Alternative PostgreSql Storage Provider for Hangfire
https://www.nuget.org/packages/Hangfire.PostgreSql.ahydrax/
Other
19 stars 7 forks source link

Hangfire Clean up #27

Open adnan-kamili opened 2 years ago

adnan-kamili commented 2 years ago

Please refer to the following at the bottom:

https://github.com/HangfireIO/Hangfire/issues/1705

ahydrax commented 2 years ago

Hi there, Could you launch select count(*) from hangifre.[table_name] where expireat is null for the following tables: Counter, Job, Jobparameter, State?

adnan-kamili commented 2 years ago

Thanks for looking into this. Here are the results:

select count(*) from hangfire.counter where expireat is null : 202,560,245

select count(*) from hangfire.job where expireat is null: 1

I manually ran this to clean this up:

delete from hangfire.job where expireat is not null and expireat < now()otherwise it was 163 million.

select count(*) from hangfire.jobparameter // no expireat column: 163 ( I ran the delete query, previously it was 126 million)

select count(*) from hangfire.state // no expireat column: 537 ( I ran the delete query, previously it was189 million)

adnan-kamili commented 2 years ago

select key, sum(value) from counter where key in (?, ?) group by key

The above query pushes the AWS Aurora Postgres to limits. Any query you suggest to cleanup this table?

adnan-kamili commented 2 years ago

I ran this query to cleanup:

delete from hangfire.counter where key = 'stats:succeeded'

It took 15 mins.

And then:

delete from hangfire.counter where expireat is not null

Now it has only three rows

ahydrax commented 2 years ago

Thanks for the info you provided!

What version of package do you use now?

adnan-kamili commented 2 years ago
 <PackageReference Include="Hangfire.AspNetCore" Version="1.7.27" />
 <PackageReference Include="Hangfire.PostgreSql.ahydrax" Version="1.7.4" />
adnan-kamili commented 2 years ago

Now, I see a new weird behaviour:

autovacuum: VACUUM hangfire.counter

is continuously timing out.

ahydrax commented 2 years ago

It looks like autovacuum is enabled in AWS Aurora Postgres by default. You can try to launch it manually in query console. My package doesn't call vacuum explicitly.