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

2

the same, I want specific solutions for development?

Hight CPU 100% when view dashboad and click menu

image
image332×364 8.06 KB

                Hangfire.GlobalConfiguration.Configuration.SetDataCompatibilityLevel(CompatibilityLevel.Version_180)
               .UseSimpleAssemblyNameTypeSerializer()
               .UseSqlServerStorage(ConnectionString, new SqlServerStorageOptions
               {
                   CommandBatchMaxTimeout = TimeSpan.FromMinutes(5), 
                   SlidingInvisibilityTimeout = TimeSpan.FromMinutes(10),
                   QueuePollInterval = TimeSpan.FromSeconds(15),
                   UseRecommendedIsolationLevel = false,được khuyến nghị
                   DisableGlobalLocks = true,
                   JobExpirationCheckInterval = TimeSpan.FromMinutes(10),
                   //UsePageLocksOnDequeue = true, 
               }).WithJobExpirationTimeout(TimeSpan.FromMinutes(30))
                 .UseDashboardMetric(DashboardMetrics.FailedCount);

the ultimate bad solution to make it work
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’