Effective way to resolve the EventQueue, CPU and memory load related issue

Question

I have hundreds of thousands items in Sitecore, sometimes it needs to publish all of them and as the result CPU and memory are overloaded. The reason of the perfomance issue is a huge amount of items in EventQueue table.

I know there is configurable agent in Sitecore responsible for cleaning EventQueue table in database. There are also some SQL scripts to clean out the table manually. But I'm trying to find out more handy solution as it's not convenient on the production environment to run custom script or wait till the agent to do the job.

Solutions I can think of are:

• Implementing a custom handler on publish:end event
• Creating a scheduled SQL job to run the clean up script
• Limiting the size of the EventQueue table in SQL Server
• Runing cleanup agent more often (e.g. per 1/2 hour)

Update: As I've found that Sitecore introduced <IntervalToKeep>hh:mm:ss</IntervalToKeep> setting in Sitecore, so the best solution that I see is to:

• Set <IntervalToKeep>01:00:00</IntervalToKeep>
• Creating a scheduled SQL job to run the clean up script (http://sdn.sitecore.net/upload/sitecore7/70/cms_tuning_guide_sc70-usletter.pdf)

Answer 1

You can usually improve this by tuning the settings for our queues. A good post in this can be found here: https://briancaos.wordpress.com/2016/08/12/sitecore-event-queue-how-to-clean-it-and-why/

What I normally do:

• On the CM: clean the eventqueue very aggressively
• On the CD:
  • poll the eventqueue less aggressively
  • disable the eventqueue cleaning

Example:

On CM: change <DaysToKeep>1</DaysToKeep> to <IntervalToKeep>01:00:00</IntervalToKeep> for the CleanupEventQueue

On CD: set the <processingInterval>00:00:02</processingInterval> to <processingInterval>00:02:00</processingInterval> for the <eventQueue> and disable the CleanupEventQueue by setting its interval to 00:00:00

By tuning these parameters we were able to get a site with millions of items published.

That said, publishing all of your items regularly is never a good idea. There are always items/trees that don't need publishing. Your ultimate solution is to think about that and come up with a way to avoid the full publish.

Edit: (based on comment)

The CleanupEventQueue is disabled on the CD because the CM does the job. No need for several instances to start this job. We did see better performance and smaller queues with these settings. Of course, you can tune them as it fits best in your solution.

The larger processing interval does indeed mean that CD's might/will detect content changes later. And yes, the bigger this interval, the bigger the risk of inconsistancy. So this is surely something to consider and see what is possible.

Answer 2

You can optimize memory usage by enabling Sitecore Interning. To do this enable this two settings:

<setting name="Interning.Enabled" value="false"/>
<setting name="Interning.InternKnownFieldValues" value="false"/>

and add your custom fields, that you know can have a lot of duplicated values, to this list:

<fieldIdsToIntern />

Another thing you can do to optimize memory usage is to configure MemoryMonitorHook to clear sitecore cache when it exceeds defined threashold:

<hook type="Sitecore.Diagnostics.MemoryMonitorHook, Sitecore.Kernel">
    <param desc="Threshold">10GB</param>
    <param desc="Check interval">00:01:00</param>
    <param desc="Minimum time between log entries">00:00:20</param>
    <ClearCaches>true</ClearCaches>
    <GarbageCollect>true</GarbageCollect>
    <AdjustLoadFactor>false</AdjustLoadFactor>
</hook>

I recently faced similar problem and wrote a blog post about this. You can read it here.

Answer 3

That's true. you can use this option to clean event queue.

If you are facing CPU or memory load issue or may be performance issue so its better to clean few more table as well apart from EventQueue:

• History table using option
• Publishing queue using option

For more details see this blog.

https://briancaos.wordpress.com/2016/08/12/sitecore-event-queue-how-to-clean-it-and-why/