Event Queues Part Duo

IMPROVING EVENT QUEUE PROCESSING SPEED

The number one problem is high number of records in Event Queue table. As we already know once the event is processed by all the Sitecore instances, it becomes useless and eligible to be removed.

If the number of records gets too high, cleanup procedure can fail with timeout exception, causing MSSQL to bend the knee.

Determine events by type statistics

Given SQL query shows 'hot' event types, and highlights priority optimizations:

SELECT MIN([Created]) AS Earliest,
MAX([Created]) AS latest,
DATEDIFF(day, MIN([Created]),MAX([Created])) as [Days between min and max],
COUNT(1) as TOTAL,
[EventType]
  FROM [EventQueue]
 GROUP BY [EventType]

The 'PropertyChangedRemoteEvent' usually is the winner for core database.
Lets see how can we reduce the received numbers.

Reducing number of events

Switching indexes property store to file system

Sitecore Content Search engine stores indexing metadata inside database property store.

Content Search needs a reliable storage to store metadata ( f.e. last indexed item ) to avoid re-indexing of already indexed data.

There is no need to share/sync the data between servers, thus no need to sync changes.

Using database properties could be costly in terms of performance in this case.
Sitecore Content Search may cause performance issues due of excessive updates of the EventQueue table gives a solution how to change property store.

The #420602 performance optimization  not to raise events in case code executed under EventDisabler) is addressed in 7.2 Update-5.

Logging to Sitecore interfaces with 'Remember Me' flag

In case 'Remember Me' flag is not selected during login to Sitecore interfaces, sliding expiration policy would be applied, and user ticket would be prolonged on every request.

Since Sitecore Client security mechanism should keep track/share information about simultaneously logged in users between servers, it has to use properties as well =\

Every ticket prolongation would update value inside properties table, thus provoke 'property changed' event.

A #443748 performance optimization has been introduced in CMS 7.2 Update 6, that noticeably reduces number of database property calls.

To sum up, always select 'Remember Me' flag.

Reducing number of publish operations

Every publish operation would produce 'publish:begin', 'publish:end' events, as well as publish 'languages' items.

Even though there are no actual content items to be published, a set of events would be produced, and system language items would be published.

If you are not going to add more languages to your solution, can comment out 'AddLanguagesToQueue' processor inside publish pipeline.

You can also consider to reducing the frequency of PublishingAgent executions.

Write less data into EventQueue

MSSQL would perform better if rows would have less data.

The #422510 optimization that allows to specify which changes are to be added into event data is available from CMS 7.2 Update 3.

In short - whenever item is saved, a list of modified fields with values is added into EventQueue.

If you update a field with 500 KB HTML text, it would be serialized and forwarded to database.

Needless to say that database server would appreciate if we could put less data.

I will create an article to describe in more details HowTo configure the configuration.

Aggressive cleanup policy

Stock 'CleanupEventQueue' agent was improved to perform cleanup in more aggressive way starting from 7.2 Update 4 (#392673).

For prior CMS versions once can use reworked stock cleanup task

How to pick optimal interval

The interval to keep should be more than longest running operation that uses EventQueue ( f.e. onPublishEnd indexing strategy )

F.e. Content indexes would be populated with freshly published data when publish:end is raised.

One should keep EventQueue rows produced by the publishing until indexing on all servers is over.