Implement Infinite Scroll Pagination in AngularJS
1 April 2014
Tsunami UDP Protocol – Installation, Setup and Limitations
13 October 2015
Tags
Published by
BluePi
Data-Driven Business Transformation
ASPState database - A performance bottleneck
The Project
We were recently tasked with resolving a performance bottleneck on a very popular online food ordering system in India. The problem statement was rather simple: The website was running very slow during peak hours.
The application was opaque to us – being an off the shelf application whose IP and therefore source the application vendor retained. So our investigations and analysis would need to be limited to the OS, Hardware and application level configuration.
We looked at the metrics provided by New Relic and could determine that the database was performing well. All the stored procedures were completing their operations in under 100 ms. Network latency & bandwidth between the application and the database was also found to be of no concern.
This was a three-tier application and the web tier was indeed taking up to 15 secs to process requests. We decided to work down the tiers to identify and isolate the bottlenecks.
The load balancer was configured to use sticky sessions however the session was being stored in the ASPState database. Given that the LB configuration was sticky we decided to change the session configuration to InProc. And Voila the problems were all gone.
We had a really happy customer – who saw an increase in revenue, reduction in provisioned hardware (therefore costs) and a 10 fold decrease in response time. Simple, right?
The above does point to the fact the ASPState database was a bottleneck and data also demonstrated that it was bottlenecking on Disk I/O. Why would that be?
Anytime a web request was coming in there was a write occurring to the ASPState database. Though the project/client did not need any of the below, but here are a few pointers on how to tune the ASPState database in case your architecture requires its use. Please note that there is a third option called in SessionServer where Session State can be stored on a separate server in memory. Both SessionState and ASPState lead to a single point of failure and bottleneck.
In another .Net Application that we are building ground up we have decided to go stateless as this leads to way more scalable architectures.
Recommendations on tuning ASPState database
Choose Simple Recovery Model – This will dramatically reduce the impact to the log that can be caused by the high volume of (transient and largely disposable) writes. Read more
ALTER DATABASE ASPState SET RECOVERY SIMPLE;
- Minimize or Isolate I/O - Choose the sstype -c and -d options to our the ASPState database on a different disk.
- Though possible never use the same ASPState database for two different web applications or sites.
- The stored procedure tempresettimeout is called on every request and this can be madness on a high volume/many pageviews per visit site. I am not sure entirely sure how to address this but probably we can run this on some requests and not all?
- There is a deleteexpiredtokens stored procedure that kills the performance under load. Run it sporadically and if possible not under peak load.
Bottomline
Coming from the world of Java/J2EE and Play Framework, I dislike single points of failure and session state. In the .Net world an apparently harmless configuration can lead to ominous bottlenecks unless identified early.
