LibrarySites.Banner

Publish Queue, History and Event Queue too big

Hi!
On our current production with several sites and may editors editing content, we are currently experience several problems related to publishing, delayed publishing, index updating. We checked the queues and we have something like:

Master DB:
Publish Queue - 32M items
History - 165k items
Event Queue - 7.4k items


Web DB:
Publish Queue - 0 M items
History - 6.9M items
Event Queue - 1M items


These are huge numbers as you can see. Somehow it seems these queues are not being cleared properly. Would you advise to change the the daystokeep or other configuration related to these queues?

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

<!-- Agent to clean up the event queue -->
<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
	<DaysToKeep>1</DaysToKeep>
</agent>


Thanks.

									
		


				
				
									

					
				

					
					

	
					
					
	

					
				Bruno Vieira
			
			







	
		
			
		
	
	
	


		
			

	


							
17 Replies

	
	
    
		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Vincent van Middendorp
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
						Hi,
    

    
We had similar problems and lowered the "CleanupPublishQueue" to about 7 days which seemed to work fine. 
    
You can clean all the tables up quite easily for now by using this SQL query that Sitecore mentions in the SC 6 Tuning Guide:
    

    
USE /* database name */
    
/* TRUNCATE History TABLE */
    
IF OBJECT_ID('History', 'U') IS NOT NULL
    
IF((SELECT COUNT(*) FROM [History]) > 1000)
    
BEGIN
    
TRUNCATE TABLE [History];
    
PRINT 'Truncated the History Table';
    
END
    
/* TRUNCATE EventQueue TABLE */
    
IF OBJECT_ID('EventQueue', 'U') IS NOT NULL
    
if((SELECT COUNT(*) FROM [EventQueue]) > 1000)
    
BEGIN
    
TRUNCATE TABLE [EventQueue];
    
PRINT 'Truncated the EventQueue Table';
    
END
    
/* TRUNCATE PublishQueue TABLE */
    
IF OBJECT_ID('PublishQueue', 'U') IS NOT NULL
    
IF((SELECT COUNT(*) FROM [PublishQueue]) > 1000)
    
BEGIN
    
TRUNCATE TABLE [PublishQueue];
    
PRINT 'Truncated the PublishQueue Table';
    
END
    

    
sdn.sitecore.net/.../cms_tuning_guide_sc60-65-a4.pdf. 
    

    
This is also mentioned:
    

    
"Sitecore recommends that the number of rows (entries) in the History, PublishQueue, and
    
EventQueue tables be less than 1000. This prevents timeouts from occurring while the cleanup
    
agents run"
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Bruno Vieira
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Vincent van Middendorp:
    
															Hi Vincent.
    

    
Thanks you for your reply. We have already done those steps several times, the problem is those queues grow like rabbits :)
    
Do you think this is connected with the fact that some editors are misusing the publishing rights and by publishing many, many times a day (not at the root level but on "page" level), causes these queues to grow so fast?
    

    
We do have a way to clear the caches on the CDs which seems to fix the problem temporarily until the next time it is needed. Of course, this doesn't solve the indexing issue, meaning, indexes are running behind and don't have the latest content. But let's ignore the indexes for now.
    

    
Thanks a lot.
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Chaturanga Ranatunga
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Bruno Vieira:
    
															Hi Bruno,
    
just to get the full idea, what do you mean by "..clear the caches on the CDs which seems to fix the problem temperarily.." ?
    
after truncating the eventQueue, publishQueue, History tables, how much time it takes to grow them to around 10k items ?
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Kiran Patil
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Bruno Vieira:
    
															
    Hey Bruno,
    

    It looks like Something is not right with Event Queue -- Might be miss configuration or EQ Table got corrupted. Would suggest you to read this:
    

    

    

    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Kiran Patil
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Kiran Patil:
    
															Also, as mentioned in last blog. Can you please try to run following scripts on each DB?
    

    
    DELETE FROM EventQueue;  
    
    DELETE FROM [Properties] WHERE  [Key] LIKE 'EQStamp%';
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Menno Visser
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
						Can you re check your configuration with the sitecore documentation, it seems that your keep alive is not runnig properly or that your app pool is restarting much.
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Bruno Vieira
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Chaturanga Ranatunga:
    
															Hi Chaturanga.
    
When we publish something (or the scheduled agent does it), the items end up in the Web DB. But, they don't appear on the published site. This is because the CDs didn't have enough time yet to get to the event that triggers the cache clearing (due to such big tables to process).
    
So, when if I clear the cache manually, the CDs then have to get the items again from the Database thus showing the items on the live site.
    

    
So, this action fixes the problem temporary because the problem itself is still there for the next publishes.
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Rene Naplava
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
						Hi Bruno.
    

    
Which version of Sitecore are you using? Are you using Incremental publish? Maybe you can find interesting our experiences that are summarized in this post:  renasitecore.blogspot.cz/.../huge-number-of-rows-in-publishqueue.html
    

    
There was a bug that incremental publish was adding a huge amount of new entries into the PublishQueue table. We had similar troubles like you - we  were not able even to clear the table by the agent (until we have customized the delete procedure) with so huge number of rows.
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Bruno Vieira
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Rene Naplava:
    
															Hi Rene.
    

    
Than you for your comment.
    
The issue was mapped to problems in the Sitecore 7.2 update 2 we were using.
    
We have now upgraded to 7.2 update 5 which will hopefully fix the problems we had.
    
Sitecore advised us to upgrade to update 3 which would fix most of them but we decided to update to update 5 since it came out just when we were starting the upgrade to update 4.
    
There are many issues fixed in these updates, mainly reducing the number of records it keeps in these tables due to some bugs in update 2.
    

    
Not sure how to close this topic now but I would say the problem is about to get fixed.
    
Thanks everyone for your insights.
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Varghese Jinto
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
						
    Hi Bruno - glad to hear that the issues would be addressed with update 5. Nevertheless, this post talks about some strategies you can adopt for optimizing Event Queue performance - maybe helpful!
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Jindrich Piroutek
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
						
    Hi there!
    

    We actually had very a simililar problem in our production system and the result of this was that content editors were not able to see their content changes on Content Delivery servers. It happened very randomly and also on random nodes.
    

    After some investigation we found out, that the problem actually was a very big EventQueue table and MSSQL was not able to serve all connected CDs. Check out the details here: 
    

    Cheers
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Markus Klimmasch
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
						Hi Bruno,
    

    

    
note that you can also use the setting "IntervalToKeep" instead. In bigger Sitecore installations, DaysToKeep is not granular enough to handle the event queue efficently.
    

    

    
Greetz,
    
Markus
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Paul Phillips
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Bruno Vieira:
    
															Hi, 
    

    
I appreciate this topic is a little "old" now, but can anybody offer any other ideas for tracking down reasons for slow publishing? 
    

    
I've tried the truncate table suggestions in the tuning guide, but initially at least it seemed to make matters much worse!  An "Update Statistics" run may have rescued me there, but time will tell. On our production box, a Full Smart publish takes about 20 minutes via Sitecore Rocks. The job viewer shows approx 38,000 items processed, but I don't know if thats actually published items or just "checked" items. I have tried deploying smaller update packages (via TDS) but again that didn;t seem to make much difference. 
    

    
Our History, EventQueue and PublishQueue tables on the master database had 20,000 rows give or take, so much less than some people but obviously more than the 1,000 recommendation. 
    

    
Currently running Sitecore.NET 7.2 (rev. 140526) 
    

    
If anyone can help, would much appreciate...
    

    
Thanks,    Paul
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Hishaam Namooya
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Paul Phillips:
    
															When you hover on the job from the job aspx page, it will show you how many items have been created, skipped and so on. What type of publishing are you performing?
    
									
		
    

				
				
		
					
    
	
		
			
		
	
		
	
	

    
		
			
    

		







	
  • 
	
    

					
    
	
					
					
	
    
					
				Paul Phillips
			
			
    

    



    
	
    
		
	
    

    
		
		
		
				
		
    
																		
    In reply to Hishaam Namooya:
    
															"Full Smart publish". I'm not aware of a job.aspx page, where can I find this?
    

    
Thanks