Poor performance on Job table (high cpu)

question
1

  • Schema Version: 9, hangfire 1.8.5

  • I have a very poor management of the hangfire failed jobs for years that was never deleted. It has more than 1 million rows, and nome of the arguments column is very very big;

  • My hangfire database has more than 800GB, and most of its data its garbage, and need cleaning

  • Recently implemented the lifetime of expired jobs, to delete new failed ones;

  • I am having high cpu usage when hangfire tried to delete automatically failed/succeded expired jobs

  • Triied some forum scripts, like this one:

TRUNCATE TABLE [HangFire].[AggregatedCounter]
TRUNCATE TABLE [HangFire].[Counter]
TRUNCATE TABLE [HangFire].[JobParameter]
TRUNCATE TABLE [HangFire].[JobQueue]
TRUNCATE TABLE [HangFire].[List]
TRUNCATE TABLE [HangFire].[State]
DELETE FROM [HangFire].[Job]
DBCC CHECKIDENT ('[HangFire].[Job]', reseed, 0)
UPDATE [HangFire].[Hash] SET Value = 1 WHERE Field = 'LastJobId'
  • The deleted statement blocked incoming inserts on table due to table locks, so I tried deleted in batches instead but it was very slow, less than 50 rows delete per minute.

Questions:

  • What would be the impact of TRUNCATING the [HangFire].[Job] table instead of deleting it?
 var options = new SqlServerStorageOptions
            {
                CommandBatchMaxTimeout = TimeSpan.FromMinutes(21),
                SlidingInvisibilityTimeout = TimeSpan.FromMinutes(5),
                QueuePollInterval = TimeSpan.Zero,
                UseRecommendedIsolationLevel = true,
                DisableGlobalLocks = true,
                SqlClientFactory = SqlClientFactory.Instance,
                PrepareSchemaIfNecessary = true,
                EnableHeavyMigrations = false,
                DeleteExpiredBatchSize = 1000,
                TryAutoDetectSchemaDependentOptions = true,
                JobExpirationCheckInterval = TimeSpan.FromDays(1)
            };
            var storage = configuration.UseSqlServerStorage(_hangfireConnectionString, options).WithJobExpirationTimeout(TimeSpan.FromDays(2));
  • Is there any way to specify the cron date instead of a timespan? sometimes it tried to delete jobs during working hours, does the JobExpirationCheckInterval consider, 1 day span after app startup? i want to control specific time for this operation to run.

Tks