Table of contents
The Issue: Severe Umbraco Performance Degradation
After updating to Umbraco 13.4.1, we observed significant performance issues, especially when publishing and unpublishing documents.
Our Umbraco system, managing around 325,000 documents, encountered ultra-slow operations and frequent database lock errors.
These issues made the Umbraco CMS unusable for our editors.
Symptoms and Initial Observations in Umbraco
- Sluggish Operations: Publishing and unpublishing processes within Umbraco were extremely slow.
- Database Lock Errors: These errors frequently occurred, causing significant disruptions in the Umbraco workflow.
- Upgrade Challenges: The installation process was unusually lengthy, and the Umbraco site required a manual restart to function normally.
Investigation and Troubleshooting in Umbraco
Identifying the root cause within Umbraco was not straightforward.
We tried multiple strategies to pinpoint the problem:
- Disabling Nucache Local DB: This was an attempt to mitigate caching-related issues in Umbraco.
- Rebuilding Caches and Indexes: We forced a rebuild of both the database/memory cache and database indexes in Umbraco.
- Disabling Custom Handlers: We temporarily disabled all custom handlers, such as ContentPublishingNotificationHandler in Umbraco.
- Reducing Background Processes: Features like DeliveryApi, Webhooks, and the Content Cleanup Hosted Service were disabled to minimize background load in Umbraco.
You can check more details in initial issue #16803 reported to Umbraco HQ.
🛠️ Hey @umbraco ! Could you please review and merge this critical performance fix into the Umbraco CORE?
— Piotr Bach👨💻 (@piotrbachtech) July 30, 2024
PR: https://t.co/7EB3f8c4Rm
More details here: https://t.co/Ckj0zcV8Sg
It's super important for #umbraco13 users! 🙏
Thank you in advance! ❤️#performance
Despite these measures, the problem persisted, indicating a deeper issue within the core Umbraco code.
Root Cause and Resolution for Umbraco
After a thorough investigation, we discovered a regression bug introduced in a previous Umbraco update.
The critical flaw was the absence of a WHERE clause in specific SQL count queries, which led to operations miscounting records significantly.
This issue was particularly problematic in Umbraco systems, which have a large content volume.
We created a pull request #16837 to address this issue.
The fix added the missing WHERE clause, significantly improving the performance of publish/unpublish operations in Umbraco.
The solution was tested on databases with over 320,000 nodes, ensuring it addressed the issue comprehensively.
Here is an example of the T-SQL query that was problematic:
DECLARE @var0 BIT = 1;
DECLARE @var1 BIT = 1;
DECLARE @var2 BIT = 0;
DECLARE @var3 BIT = 1;
DECLARE @var4 UNIQUEIDENTIFIER = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972';
DECLARE @var5 BIT = 0;
DECLARE @var6 INT = 398650;
SELECT [umbracoNode].[id] AS [Id], [umbracoNode].[uniqueId] AS [Key], [umbracoNode].[level] AS [Level], [umbracoNode].[path] AS [Path], [umbracoNode].[sortOrder] AS [SortOrder], [umbracoNode].[parentId] AS [ParentId], [umbracoNode].[createDate] AS [CreateDate], [umbracoNode].[nodeUser] AS [CreatorId]
, [umbracoContent].[contentTypeId] AS [ContentTypeId]
, [umbracoDocument].[published] AS [Published], [umbracoDocument].[edited] AS [Edited]
, [umbracoContentVersion].[id] AS [VersionId], [umbracoContentVersion].[text] AS [EditName], [umbracoContentVersion].[versionDate] AS [EditVersionDate], [umbracoContentVersion].[userId] AS [EditWriterId]
, [umbracoDocumentVersion].[templateId] AS [EditTemplateId]
, [pcver].[id] AS [PublishedVersionId], [pcver].[text] AS [PubName], [pcver].[versionDate] AS [PubVersionDate], [pcver].[userId] AS [PubWriterId]
, [pdver].[templateId] AS [PubTemplateId]
, [nuEdit].[data] AS [EditData]
, [nuPub].[data] AS [PubData]
, [nuEdit].[dataRaw] AS [EditDataRaw]
, [nuPub].[dataRaw] AS [PubDataRaw]
FROM [umbracoNode]
INNER JOIN [umbracoNode] [x]
ON (([umbracoNode].[id] = [x].[id]) OR ([umbracoNode].[path] LIKE concat([x].[path],',%')))
INNER JOIN [umbracoContent]
ON ([umbracoNode].[id] = [umbracoContent].[nodeId])
INNER JOIN [umbracoDocument]
ON ([umbracoNode].[id] = [umbracoDocument].[nodeId])
INNER JOIN [umbracoContentVersion]
ON (([umbracoNode].[id] = [umbracoContentVersion].[nodeId]) AND [umbracoContentVersion].[current] = @var0)
INNER JOIN [umbracoDocumentVersion]
ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id])
LEFT JOIN [umbracoContentVersion] [pcver]
INNER JOIN [umbracoDocumentVersion] [pdver]
ON (([pcver].[id] = [pdver].[id]) AND ([pdver].[published] = @var1))
ON ([umbracoNode].[id] = [pcver].[nodeId])
LEFT JOIN [cmsContentNu] [nuEdit]
ON (([umbracoNode].[id] = [nuEdit].[nodeId]) AND [nuEdit].[published] = @var2)
LEFT JOIN [cmsContentNu] [nuPub]
ON (([umbracoNode].[id] = [nuPub].[nodeId]) AND ([nuPub].[published] = @var3))
WHERE ((([umbracoNode].[nodeObjectType] = @var4) AND ([umbracoNode].[trashed] = @var5)))
AND (([x].[id] = @var6))
ORDER BY [umbracoNode].[level], [umbracoNode].[parentId], [umbracoNode].[sortOrder]
-- This query returns 324324 records instead of 2
SELECT COUNT(*) FROM (
SELECT [umbracoNode].[id] AS [Id]
FROM [umbracoNode]
INNER JOIN [umbracoContent]
ON ([umbracoNode].[id] = [umbracoContent].[nodeId])
INNER JOIN [umbracoDocument]
ON ([umbracoNode].[id] = [umbracoDocument].[nodeId])
INNER JOIN [umbracoContentVersion]
ON (([umbracoNode].[id] = [umbracoContentVersion].[nodeId]) AND [umbracoContentVersion].[current] = 1)
INNER JOIN [umbracoDocumentVersion]
ON ([umbracoContentVersion].[id] = [umbracoDocumentVersion].[id])
LEFT JOIN [umbracoContentVersion] [pcver]
INNER JOIN [umbracoDocumentVersion] [pdver]
ON (([pcver].[id] = [pdver].[id]) AND [pdver].[published] = 1)
ON ([umbracoNode].[id] = [pcver].[nodeId])
WHERE ((([umbracoNode].[nodeObjectType] = 'c66ba18e-eaf3-4cff-8a22-41b16d66a972') AND ([umbracoNode].[trashed] = 0)))
) npoco_tbl
The lack of proper filtering in this query caused severe performance issues in our large-scale Umbraco setup.
This SQL query example illustrates a performance issue in Umbraco CMS where a query returns 324,324 records instead of 2, highlighting the need for proper filtering and optimization
As a result, the QueryPaged method (located in NPocoDatabaseExtensions.cs) had to iterate over 324,324 items instead of two, due to the incorrect sqlCount query.
This method executes during application cache refresh operations, such as after publish or unpublish actions:
The QueryPaged method in Umbraco's NPocoDatabaseExtensions.cs file, which iterates over paged query results. Due to a faulty SQL count query, this method had to process 324,324 items instead of the expected two, affecting performance during cache refresh operations like publish/unpublish.
Conclusion: Update Your Umbraco to a Later Version
If you're currently using Umbraco 13.4.1 and experiencing similar issues, it's crucial to update to a version that includes this fix.
Regular updates not only provide new features but also fix critical bugs that can impact your system's usability and performance.
Analyzing and resolving performance issues in Umbraco can be complex, requiring a diverse set of skills, from database management to front-end and back-end development.
For those looking to deepen their expertise, check out this guide on key Umbraco developer skills.
It's an excellent resource for honing the necessary skills to tackle these kinds of challenges effectively.
Usually, improving the performance of your Umbraco installation can involve various tweaks and optimizations.
For practical tips, you might find this article on speeding up Umbraco CMS particularly useful.
Be aware the problem affects Umbraco v10, v13 and v14, so it's recommended to upgrade to the latest version containing the fix.
What's Next?
Looking for Umbraco experts to help optimize your CMS or resolve issues?
Contact us today!
Our experienced team is ready to assist you with all your Umbraco needs.
Let's ensure your website performs at its best.