Hello. I have an Umbraco site running 24 worker processes (multiple servers each with multiple WP's). When content is published, the front end processes all try to hit the database to reload their cache. This call to LoadContentFromDatabase(), causes the public facing web site stall at this point for extended periods and it doesn't respond to requests. According to our db monitoring tools, this is because LoadContentFromDatabase holds a read lock while it processes the data inside the application, and all the other WP's get queued for long periods.
Running the problem query inside SSMS shows it takes 7 seconds to read all the content. So, 7 * 24 WP's = 168 seconds which is well over the 90 second HTTP default timeout so many of these updates aren't working.
We are running Umbraco v6.1.6 but I've looked at the latest v7 code and the affected area doesn't appear to have changed. There are 21k documents in the database.
We have always run with the XML cache turned off, which is a decision that might need revisiting, but even so I can't see how it would help in this case because LoadContentFromDatabase() would be called to reload the XML cache anyway?
Unless anyone has any other advice my plan to get around this (fairly severe) problem is to change the SQL locking strategy to READ COMMITTED SNAPSHOT so that the reads don't block each other. I've tested that this works by simulating a lock on the table and it appears to work fine.
Have we ever considered adding a NOLOCK hint to the query?
LoadContentFromDatabase taking site down
Hello. I have an Umbraco site running 24 worker processes (multiple servers each with multiple WP's). When content is published, the front end processes all try to hit the database to reload their cache. This call to LoadContentFromDatabase(), causes the public facing web site stall at this point for extended periods and it doesn't respond to requests. According to our db monitoring tools, this is because LoadContentFromDatabase holds a read lock while it processes the data inside the application, and all the other WP's get queued for long periods.
Running the problem query inside SSMS shows it takes 7 seconds to read all the content. So, 7 * 24 WP's = 168 seconds which is well over the 90 second HTTP default timeout so many of these updates aren't working.
We are running Umbraco v6.1.6 but I've looked at the latest v7 code and the affected area doesn't appear to have changed. There are 21k documents in the database.
We have always run with the XML cache turned off, which is a decision that might need revisiting, but even so I can't see how it would help in this case because LoadContentFromDatabase() would be called to reload the XML cache anyway?
Unless anyone has any other advice my plan to get around this (fairly severe) problem is to change the SQL locking strategy to READ COMMITTED SNAPSHOT so that the reads don't block each other. I've tested that this works by simulating a lock on the table and it appears to work fine.
Have we ever considered adding a NOLOCK hint to the query?
is working on a reply...