Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Pavan Kumar 8 posts 78 karma points notactivated
    Sep 23, 2019 @ 15:38
    Pavan Kumar
    0

    Umbraco 8 upgrade taking long time

    Hello,

    While upgrading Umbraco from 7.15.2 to Umbraco 8.1.4, we are facing issues with long migration time and timeouts.

    Our situation is

    • cmsContentVersion table has ~940K rows
    • cmsPropertyData table has ~10 million rows.

    When we were following the recommended steps for migration to Umbraco8, we were getting timeout errors. We then updated the timeout settings and after more than 12 hours its still running. This is resulting in hug DB size increase too.

    When we looked into the migration code, we found out where its taking time. Please take a look at below screenshot:

    enter image description here

    Looks like its loading all the >940K records from "cmsContentVersion" in memory and updating records into "cmsPropertyData". We think because it runs in a transaction, it is also resulting in big increse in DB size.

    Is there any better way to accomplish this step? (may be doing this in stored procedure?)

    Thoughts?

    Thanks

    Pavan

  • Shaishav Karnani from digitallymedia.com 330 posts 1536 karma points
    Sep 23, 2019 @ 15:47
    Shaishav Karnani from digitallymedia.com
    0

    Hi Pavan,

    You can delete Version History and this should clear lot of your unused records.

    This link will help you to reduce the records. https://our.umbraco.com/forum/core/general/74365-remove-audit-trail-and-version-history

    Hope this helps to solve your issue.

    Cheers,

    Shaishav

  • Pavan Kumar 8 posts 78 karma points notactivated
    Sep 27, 2019 @ 10:33
    Pavan Kumar
    0

    Hi Shaishav,

    There was not much difference after running the script in the given link to clean the audit trail and version history. It has only come down by few thousands.

  • Steve Megson 118 posts 689 karma points c-trib
    Sep 25, 2019 @ 06:49
    Steve Megson
    0

    I'm working on some changes to improve the speed of upgrades. If you'd be interested in testing them, there's a modified version of Umbraco.Core.dll from 8.1.4 here.

    If it works for you, I'd appreciate a copy of the log file from the upgrade so that I can see where there's still room for improvement.

  • Pavan Kumar 8 posts 78 karma points notactivated
    Sep 27, 2019 @ 10:52
    Pavan Kumar
    0

    Hi Steve Megson,

    I have tried running the modified Umbraco.Core.dll for migration. The log file is updated only for the first 5 minutes and last log statement is 'ALTER TABLE [cmsPropertyData] ADD [versionId2] INTEGER NULL'. It keeps running and had to stop it after more than 12 hrs.

    Is it by any chance going into the foreach loop? enter image description here

    Is it a configuration setting to say that it is not a compact server?

  • Steve Megson 118 posts 689 karma points c-trib
    Sep 27, 2019 @ 11:28
    Steve Megson
    0

    How odd. The IsSqlCe test is based on the connection string's providerName, so that shouldn't be an issue. Can you check what it's actually running with something like

    SELECT sqltext.TEXT, req.session_id, req.status, req.start_time, req.command, req.cpu_time, req.total_elapsed_time
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
    

    The query it should be running for SQL Server is still fairly unpleasant and slow, but it shouldn't be taking hours.

  • Pavan Kumar 8 posts 78 karma points notactivated
    Sep 30, 2019 @ 10:02
    Pavan Kumar
    0
    UPDATE cmsPropertyData SET versionId2=cmsContentVersion.id FROM cmsContentVersion INNER JOIN cmsPropertyData ON cmsContentVersion.versionId = cmsPropertyData.versionId
    

    It shows that the above query is running

  • Steve Megson 118 posts 689 karma points c-trib
    Sep 30, 2019 @ 13:15
    Steve Megson
    0

    Reassuring that it's not using the SQL CE version, though strange that it's taking so long. My test database has 8.5 million rows in cmsPropertyData and only takes a couple of minutes to run that query.

    You could do the change of the versionId column manually before starting the migration, and the migration should then quietly skip that bit. That might let you experiment with indexes to speed it up.

    This SQL should be what the migration runs, though you might need to drop an index or two first:

    ALTER TABLE cmsPropertyData ADD [versionId2] [int] NULL
    
    UPDATE cmsPropertyData SET versionId2=cmsContentVersion.id FROM cmsContentVersion INNER JOIN cmsPropertyData ON cmsContentVersion.versionId = cmsPropertyData.versionId
    
    ALTER TABLE cmsPropertyData DROP COLUMN [versionId]
    
    sp_rename 'cmsPropertyData.versionId2', 'versionId', 'COLUMN'
    
  • Pavan Kumar 8 posts 78 karma points notactivated
    Oct 03, 2019 @ 13:54
    Pavan Kumar
    0

    It finishes the given query in SSMS very fast, like in 3 mins. But this keeps running for hours from the application. As a workaround, i'm running the alter, update and rename query from SSMS before migration and then start Umbraco migration, now it is not getting stuck at the update query. I will let you know once the whole migration process is done. Thanks.

  • Pavan Kumar 8 posts 78 karma points notactivated
    Oct 04, 2019 @ 12:42
    Pavan Kumar
    0

    Hi Steve Megson,

    We are now getting an error in PostMigrations.

    {"@t":"2019-10-03T14:21:55.0051163Z","@mt":"Database configuration failed","@l":"Error","@x":"System.NotSupportedException: Surrogate pairs are not supported.\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanCodeString(String text, CleanStringType caseType, Char separator, String culture, Config config)\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanString(String text, CleanStringType stringType, String culture, Nullable`1 separator)\r\n   at Umbraco.Core.Strings.DefaultShortStringHelper.CleanStringForUrlSegment(String text, String culture)\r\n   at Umbraco.Core.StringExtensions.ToUrlSegment(String text, String culture)\r\n   at Umbraco.Core.Strings.DefaultUrlSegmentProvider.GetUrlSegment(IContentBase content, String culture)\r\n   at Umbraco.Core.Strings.ContentBaseExtensions.<>c__DisplayClass0_0.<GetUrlSegment>b__0(IUrlSegmentProvider p)\r\n   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()\r\n   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)\r\n   at Umbraco.Core.Strings.ContentBaseExtensions.GetUrlSegment(IContentBase content, IEnumerable`1 urlSegmentProviders, String culture)\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.GetDto(IContentBase content, Boolean published) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1356\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.<RebuildMemberDbCacheLocked>b__82_1(IMember m) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1581\r\n   at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()\r\n   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)\r\n   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.RebuildMemberDbCacheLocked(IScope scope, Int32 groupSize, IEnumerable`1 contentTypeIds) in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1542\r\n   at Umbraco.Web.PublishedCache.NuCache.PublishedSnapshotService.Rebuild() in d:\\a\\1\\s\\src\\Umbraco.Web\\PublishedCache\\NuCache\\PublishedSnapshotService.cs:line 1393\r\n   at Umbraco.Web.Migrations.PostMigrations.PublishedSnapshotRebuilder.Rebuild() in d:\\a\\1\\s\\src\\Umbraco.Web\\Migrations\\PostMigrations\\PublishedSnapshotRebuilder.cs:line 27\r\n   at Umbraco.Core.Migrations.PostMigrations.RebuildPublishedSnapshot.Migrate()\r\n   at Umbraco.Core.Migrations.MigrationPlan.Execute(IScope scope, String fromState, IMigrationBuilder migrationBuilder, ILogger logger)\r\n   at Umbraco.Core.Migrations.Upgrade.Upgrader.Execute(IScopeProvider scopeProvider, IMigrationBuilder migrationBuilder, IKeyValueService keyValueService, ILogger logger)\r\n   at Umbraco.Core.Migrations.Install.DatabaseBuilder.UpgradeSchemaAndData(MigrationPlan plan)","SourceContext":"Umbraco.Core.Migrations.Install.DatabaseBuilder","ProcessId":14792,"ProcessName":"iisexpress","ThreadId":7,"AppDomainId":2,"AppDomainAppId":"LMW3SVC2ROOT","MachineName":"SLBLR-LT-209","Log4NetLevel":"ERROR","HttpRequestNumber":7,"HttpRequestId":"c6965a3d-c7ee-4be3-83ae-84250fb34d59"}
    

    Could you please share your source code repository or the branch name, so that we could debug and find out the error ?

  • Steve Megson 118 posts 689 karma points c-trib
    Oct 04, 2019 @ 13:39
    Steve Megson
    1

    Here's my repository: https://github.com/stevemegson/Umbraco-CMS/tree/v8/migration-performance

    It looks like you're back into unmodified code with that error, though. The cache is trying to generate clean versions of member names in the same way that document names get cleaned to generate URLs (I don't think we ever use a URL for a member anywhere, but the cache doesn't know that). I'd guess that the most likely reason for surrogate pairs in a member name is someone including emoji.

  • Roger Jarl 14 posts 107 karma points
    1 week ago
    Roger Jarl
    0

    Thanks Steve for the hint about emojis. I also got the "Surrogate pairs are not supported" error. I removed the emojis from content, and then I successfully upgraded from Umbraco 7 to 8.

  • Pavan Kumar 8 posts 78 karma points notactivated
    1 week ago
    Pavan Kumar
    0

    Thanks Steve. We were able to locate the emoji in UmbracoNode table and finished with the migration. Will your performance changes be pushed to the main Umbraco repository and be part of any upcoming release ?

  • Cimplex 97 posts 528 karma points
    1 week ago
    Cimplex
    0

    Hi Steve, Were you able to upgrade your Umbraco installation to v8?

    I was stuck at the version migrations aswell and I ran your SQL Script to edit the version and it looks that worked but now it get stuck at

    {"@t":"2019-11-05T15:08:14.3468145Z","@mt":"SQL [{ContextIndex}]: {Sql}","ContextIndex":6954,"Sql":"ALTER TABLE [cmsContentVersion] DROP COLUMN [ContentId];","SourceContext":"Umbraco.Core.Migrations.Expressions.Delete.Expressions.DeleteColumnExpression","ProcessId":15604,"ProcessName":"w3wp","ThreadId":26,"AppDomainId":3,"AppDomainAppId":"LMW3SVC42ROOT","MachineName":"ZEUS","Log4NetLevel":"INFO ","HttpRequestNumber":7,"HttpRequestId":"d01d1728-323f-4174-ae95-d106fec35c4a"}
    {"@t":"2019-11-05T15:14:49.5133218Z","@mt":"SQL [{ContextIndex}]: {Sql}","ContextIndex":6955,"Sql":"CREATE TABLE [umbracoDocumentVersion] ([id] INTEGER NOT NULL,[templateId] INTEGER NULL,[published] BIT NOT NULL)","SourceContext":"Umbraco.Core.Migrations.Expressions.Execute.Expressions.ExecuteSqlStatementExpression","ProcessId":15604,"ProcessName":"w3wp","ThreadId":37,"AppDomainId":3,"AppDomainAppId":"LMW3SVC42ROOT","MachineName":"ZEUS","Log4NetLevel":"INFO ","HttpRequestNumber":6,"HttpRequestId":"96ebe6a2-a2de-4c79-af41-7a4575c44697"}
    

    Any ideas?

    // Herman

Please Sign in or register to post replies

Write your reply to:

Draft