After deploying my Umbraco project (U7.4.3) to the live server the back office is now running very slowly, especially the media section. There are a reasonable number of images, but I wouldn't have thought it is enough to cause this kind of performance hit.
My guess is that I have lost some important database table indexes during the move - due to restrictions placed on me by my hosting company I had to deploy the database by exporting to scripts and rebuilding, and while this has got the data I don't think it rebuilt the indexes as it should. Added to this I had to do a similar thing to my development database as I was fumbling my way through the deployment, so I don't have a clear record of what indexes should exist.
Is there a definitive list of what indexes (and any other performance related table properties) should exist so I can rebuild them manually? Or is there an update routine I can run to get Umbraco to do this itself?
Or, if this doesn't sound like the right diagnosis, any other suggestions appreciated.
... and a simple click on the Media tab in the left menu takes ages to respond, does eventually populate the central pane with folders and images, but doesn't populate the left hand pane with the Media folder structure, and pops up the following error message:
An error occured
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
EXCEPTION DETAILS
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
STACKTRACE
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TrySetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.<>c__DisplayClass4.<ExecuteReaderWithRetry>b__3()
at Umbraco.Core.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy cmdRetryPolicy, RetryPolicy conRetryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy retryPolicy)
at Umbraco.Core.Persistence.PetaPocoCommandExtensions.ExecuteReaderWithRetry(IDbCommand command)
at Umbraco.Core.Persistence.Database.<Query>d__1c`1.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Umbraco.Core.Persistence.Database.Fetch[T1,T2,TRet](Func`3 cb, Sql sql)
at Umbraco.Core.Persistence.Repositories.EntityRepository.GetByQuery(IQuery`1 query, Guid objectTypeId)
at Umbraco.Core.Services.EntityService.GetChildren(Int32 parentId, UmbracoObjectTypes umbracoObjectType)
at Umbraco.Web.Trees.ContentTreeControllerBase.GetChildEntities(String id)
at Umbraco.Web.Trees.ContentTreeControllerBase.PerformGetTreeNodes(String id, FormDataCollection queryStrings)
at Umbraco.Web.Trees.ContentTreeControllerBase.GetTreeNodesInternal(String id, FormDataCollection queryStrings)
at Umbraco.Web.Trees.ContentTreeControllerBase.GetTreeNodes(String id, FormDataCollection queryStrings)
at Umbraco.Web.Trees.TreeControllerBase.GetNodes(String id, FormDataCollection queryStrings)
at Umbraco.Web.Trees.ApplicationTreeExtensions.TryLoadFromControllerTree(ApplicationTree appTree, String id, FormDataCollection formCollection, HttpControllerContext controllerContext)
at Umbraco.Web.Trees.ApplicationTreeController.<GetRootForSingleAppTree>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Umbraco.Web.Trees.ApplicationTreeController.<GetApplicationTrees>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__3`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__2.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()
INNER EXCEPTION
System.ComponentModel.Win32Exception: The wait operation timed out
The write access is a good point, but I've already done that - had lots of fun getting Umbraco to work at all due to write access problems. But I believe I have that under control now.
Just in case this is helpful for anyone, I've finally fixed this problem - the solution in my case was to set the SQL Connection Timeout to a large number in the database connection string in the web.config file:
which loads the tree view on the left hand side without giving timeout errors, and then deleting the large number of deleted Media items from the Recycle Bin, which was part of what was causing the problem.
The other problem I identified was very poor SQL performance from my hosting company (around 20 seconds do a simple SELECT * from a single table returning 40,000 rows - the same request takes a fraction of a second on my development server) - which is obviously nothing to do with Umbraco.
I hadn't tried setting the SQL Connection Timeout previously as I know (or thought I knew) from other projects that the Connection Timeout and Command Timeout are different things, so I wasn't expecting the Connection Timeout to fix the SQL error - but it did!
Slow media access - rebuild database indexes?
Hi,
After deploying my Umbraco project (U7.4.3) to the live server the back office is now running very slowly, especially the media section. There are a reasonable number of images, but I wouldn't have thought it is enough to cause this kind of performance hit.
My guess is that I have lost some important database table indexes during the move - due to restrictions placed on me by my hosting company I had to deploy the database by exporting to scripts and rebuilding, and while this has got the data I don't think it rebuilt the indexes as it should. Added to this I had to do a similar thing to my development database as I was fumbling my way through the deployment, so I don't have a clear record of what indexes should exist.
Is there a definitive list of what indexes (and any other performance related table properties) should exist so I can rebuild them manually? Or is there an update routine I can run to get Umbraco to do this itself?
Or, if this doesn't sound like the right diagnosis, any other suggestions appreciated.
Cheers,
Crac
... and a simple click on the Media tab in the left menu takes ages to respond, does eventually populate the central pane with folders and images, but doesn't populate the left hand pane with the Media folder structure, and pops up the following error message:
Any ideas?
Cheers,
Crac
Hi Caractacus,
your guess about DB indexes concerning to the DB upload method you described may be true...
And just another guess, after your words about hosting company restrictions:
gives the hoster an write access for the windows account of application pool owner to the several paths of the application's folder structure?
First of all - I speak about 'App_Data' folder, which contains among the others the folders of Lucene indexes...
The write access is a good point, but I've already done that - had lots of fun getting Umbraco to work at all due to write access problems. But I believe I have that under control now.
Thanks for the response.
Cheers,
Crac
Just in case this is helpful for anyone, I've finally fixed this problem - the solution in my case was to set the SQL Connection Timeout to a large number in the database connection string in the web.config file:
which loads the tree view on the left hand side without giving timeout errors, and then deleting the large number of deleted Media items from the Recycle Bin, which was part of what was causing the problem.
The other problem I identified was very poor SQL performance from my hosting company (around 20 seconds do a simple SELECT * from a single table returning 40,000 rows - the same request takes a fraction of a second on my development server) - which is obviously nothing to do with Umbraco.
I hadn't tried setting the SQL Connection Timeout previously as I know (or thought I knew) from other projects that the Connection Timeout and Command Timeout are different things, so I wasn't expecting the Connection Timeout to fix the SQL error - but it did!
I hope that may be useful to someone else.
Cheers,
Crac
is working on a reply...