Sitecore Event Queue – How to clean it – and why

Yesterday the dreaded Sitecore Event Queue almost killed me again – well it certainly almost killed my CM server. The server went from being busy but stable to being unresponsive. CPU and memory load skyrocketed:

A fully loaded CM server

Obviously it happened after a system update, so after a panic debug and rollback the system owner pointed out: “Hey, the event queue table is quite big?“.
Of course, the system updated flooded the event with 1.5 million events, and the problem did not go away because I keep 1 day of events in the queue.

SO WHAT TO DO ABOUT IT?

First we need to stabilize the system, then we need to update the configuration.

STEP 1: CLEAN OUT EVENT QUEUE, HISTORY TABLE, PUBLISH QUEUE

The following SQL statement will clean out the history table, publish queue and event queue, leaving only 12 hours of history and publish data and 4 hours of events. Replace YOURDATABASE with the name of your database:

/****** History ******/
delete FROM [YOURDATABASE_Core].[dbo].[History] where Created < DATEADD(HOUR, -12, GETDATE())
delete FROM [YOURDATABASE_Master].[dbo].[History] where Created < DATEADD(HOUR, -12, GETDATE())
delete FROM [YOURDATABASE_Web].[dbo].[History] where Created < DATEADD(HOUR, -12, GETDATE())
 
/****** Publishqueue ******/
delete FROM [YOURDATABASE_Core].[dbo].[PublishQueue] where Date < DATEADD(HOUR, -12, GETDATE());    
delete FROM [YOURDATABASE_Master].[dbo].[PublishQueue] where Date < DATEADD(HOUR, -12, GETDATE());
delete FROM [YOURDATABASE_Web].[dbo].[PublishQueue] where Date < DATEADD(HOUR, -12, GETDATE());
    
/****** EventQueue ******/
delete FROM [YOURDATABASE_Master].[dbo].[EventQueue] where [Created] < DATEADD(HOUR, -4, GETDATE())
delete FROM [YOURDATABASE_Core].[dbo].[EventQueue] where [Created] < DATEADD(HOUR, -4, GETDATE())
delete FROM [YOURDATABASE_Web].[dbo].[EventQueue] where [Created] < DATEADD(HOUR, -4, GETDATE())

STEP 2: CONFIGURE THE SYSTEM TO CLEAN THE TABLES MORE OFTEN

With the system stabilized, we need to take more care of the table sizes.

HISTORY TABLE:

Sitecore is already configured to clean the tables so they only contain 12 hours of data. 12 hours of data is usually what any SQL server will handle, and you will have up to 10.000 rows in the table.

<Engines.HistoryEngine.Storage>
  <obj type="Sitecore.Data.$(database).$(database)HistoryStorage, Sitecore.Kernel">
    <param connectionStringName="$(id)" />
    <EntryLifeTime>00.12:00:00</EntryLifeTime>
  </obj>
</Engines.HistoryEngine.Storage>

PUBLISH QUEUE:

Sitecore keeps 30 days of publish queue. If you insert and update items often, you should lower this number. For each item change (including any change that the system does) is stored here.

<agent type="Sitecore.Tasks.CleanupPublishQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
  <DaysToKeep>2</DaysToKeep>
</agent>

EVENT QUEUE:

The event queue is the most important table to keep small. In a distributed environment, each server will read the contents of the table every 5 seconds, using a time stamp stored in the Properties table as key. Any row before the time stamp will not be read.

You therefore need enough history to cater that a server will be offline for a while, but at the same time so little contents that any read and write will be amazingly fast.

If you can keep the number of rows below 7.000, most SQL server should be able to handle that amount of data. Even smaller numbers are preferred as well.

Before Sitecore 8.1, Sitecore would only allow you to clean events older that 1 day. This is way too much, especially if you publish often. The new IntervalToKeep will allow you to determine the hours to keep as well:

<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
  <IntervalToKeep>04:00:00</IntervalToKeep>
  <DaysToKeep>1</DaysToKeep>
</agent>

 

THE EFFECT ON THE CLEANUP

After these changes, my server is back to normal, busy but responsive:

Normal Load

MORE TO READ:

• Reducing pressure on Sitecore Event Queue table by The Sitecore Zone
• Sitecore EventQueue deadlocks – how to solve them and how to avoid them by briancaos
• 27 Tips: Configuring Sitecore for performance, scalability and security by Nonlinear Digital