Rebuild Database Cache - The request timed out

Questions
1

Hi all,

I have a site with around 30000 nodes. I am trying to rebuild the database cache and am getting this error

The request timed out.

image
image623×347 19.5 KB

I have the Connection Timeout set to 1200 in the connection string and that still doesn’t help.

Do you have any ideas on how to fix this please?

Kind regards

Paul

3

I think it’s more like a request timeout instead of an SQL timeout. It depends on your hosting platform how to increase the timeout. What is the hosting platform?

4

Hi Luke

Thanks for answering, it’s on Azure App Service.

Kind regards

Paul

5

I would image that the process is still running in the background but the request from clicking the button in the back office has timed out. The backoffice code then shows the error, but I would try monitoring DB activity to see if this is still running or not.

6

I would confirm a DB issue - increase the number of DTUs (x4 or x8) - rebuild the cache. If it responds quickly you know where your issue is for sure.

Azure SQL seems woefully underpowered (deliberately) to me (for what you pay). £$£$

7

Thanks Steve

It wasn’t working on 50 DTUs, the database was maxing out at 100%
I’ve tried doubling it to 100 DTUs and I’ve increased the App Service plan to P2 v3 as well.

I will report back and let you know how I get on.

It all works locally by the way.

Kind regards

Paul

8

Does the database have any recommendations? I once has a recommendation to add a certain index and that brought the DTU usage down from 80% to about 4%.

9

That’s interesting - can you remember what the index was?

10

Yeah, but I don’t think it will be an issue in most cases. I built a package for cookie and consent management and I used the Umbraco ContentService to register what consent the user had given. We also build the CSP header on a per user/request base, based on the consent the user had given. For instance, if the user didn’t consent to youtube, the youtube urls would not even be in the CSP header, effectively blocking everything from youtube.

However, the ConsentService turned out to be just a simple service that works directly on the database. So for every request we would get the consent for the current user, which causes massive amounts of database queries. Added an SQL index made this really fast and use very little resources (at the expense of some diskspace for the index).

1 Like
11

Hi all, just an update for you.

I have incremented the app service plan and database DTUs.

With the database, I got the rebuild to work on 100 DTUs. Anything lower was maxing out and failing.

With the App Service Plan, I went up to P3v3, because when it was on lower than that it was giving Out of Memory exceptions when attempting the Rebuild of the Database Cache.

It currently takes 15 minutes to Rebuild of the Database Cache, the UI still times out, but I can confirm it is done by checking the database with this query:

SELECT TOP (1000) [key]
      ,[value]
      ,[updated]
  FROM [dbo].[umbracoKeyValue]
  WHERE [key] = 'Umbraco.Web.PublishedCache.NuCache.Serializer'

That query gives me the date and time of when the last successful NuCache rebuild happened.

The Rebuild of the memory cache now works once the NuCache rebuild has finished.

I will try lowering the app service down to P2v3 next to see if it still works or it has the out of memory issues again.

Kind regards

Paul

12

Does it help to set this setting (usePagedSql) to false?

13

Try cloning the DB - I once had “some weirdness” with this sort of thing and the clone seemed to clean up whatever data issue the DB was having.

Worth a shot?

14

100DTUs for 30,000 nodes sounds fair.

On a rebuild of the database cache Umbraco needs to read and re-write every single property value of every single published node from a highly normalised database. This is a hugely intensive operation for the database.

Because under normal operation Umbraco doesn’t need to read/write to the database in bulk we tend to get away with running Umbraco on underpowered database instances and slowly add more and more nodes that the database can support most of the time, but not all of the time. What you now know is that this site needs a 100DTU database to support certain operations on that much content. Running an upgrade will likely require the same.

One way round this, if you want to avoid running at 100 DTUs all the time is scaling (manually or automatically) the database instance before carrying out these kinds of operations (cache rebuild, upgrades, anything else similar).

Thinking out loud…

It would be nice if an Umbraco instance knew at which point it was going to struggle under the weight of its own content. I wonder if there’s a metric that could be used to estimate this.