Common Database Performance Problems in Web Application

Most online applications of scale are backed by some form of relational or non-relational database and it can be easily said that backbone of any modern web application is its data.  As a result, the database is critical to the functionality and performance of the application and should not come as a surprise that it is the biggest source of performance issues for web applications.  The problems can occur at many places, the application code might be accessing the database inefficiently, database connection issues or database itself might need tuning.  In this blog, I will be focusing mainly on database performance issues that can occur on application server (business logic) side.  The web application can be JAVA based, PHP based or ASP.NET based but the core of the issue would be same.

Application code making excessive database queries
This performance anti pattern ultimately results in slow response time as application load increases.  The problem is hard to detect under low user load because database might be having plenty of processing power. 

If the application load and database load increase at the same rate then probably database utilization is good, but if an increase in load on the application server results in a disproportionate increase of load on database server then it is a good chance that this problem is due to mentioned anti-pattern i.e. “Application code is making excessive database queries”.

The symptoms for this anti-pattern can be observed and analyzed by trending following information (Number of database calls, Number of executed business transactions and hardest part i.e. correlation between a business transaction and number of database calls it is making).  APM tool like AppDynamics helps in correlating between a business transaction and number of database calls it is making.

Further Reading:
Linq2Sql: Prevent performance issues when operating on multiple rows with Stored Procedures

No cache or improper configuration of Caching
No one can deny that database calls can be very expensive from a performance standpoint.  This is why; many organizations have turned to caching to optimize the performance of their application.  It is much faster to read data from in-memory cache than to make a database call across a network.  Usually cache behaves as follows: when a request for object is made, first it is checked in cache, if it is and not expired then served from cache otherwise call to database is made to retrieve the object, save it in cache and then return back to caller. 

It is not using a cache properly if there is an increased database load and slow response times as a result of that load.  The relative database load increases in direct proportion to application load.  As load increases, CPU overhead and disk I/O rate increases proportionally, which degrades the overall performance of business transactions that interact with the database.  If caching is being used properly, database load should not increase in proportion to application load, because majority of requests will be served from cache.  

The symptom of this anti-pattern can be observed and analyzed by trending following information (Database server resource utilization especially the CPU & Disk I/O rate, amount of load being sent to database, cache hit ratio and cache miss ratio).  Free tool like “perfmon” helps in monitoring and trending this information.

Further Reading:
Improving entity performance Part 2 (EJB 3)

An improperly configured database connection pool
The number of connections to database controls how many concurrent queries can be executed against it.  If there are too few connections in the pool, application can observe bottleneck.  Database connections are pooled for several reasons, first database connections are relatively expensive to create, second database is a shared resource so it makes sense to create a pool of connections and share them across all business transactions and lastly database connection pool limits the traffic to database. 

If the database connection pool is sized improperly then you can see high response time across all business transactions.  If it is sized very small then you can see low resource utilization on database servers whereas if it is sized very big then you can see high resource utilization on database server.

The symptoms of this anti-pattern can be observed and analyzed using following information.  Database connection pool is improperly configured if –
a)       Application code is waiting on calls like Datasource.getConnections() and database is underutilized
b)       Application code is waiting on database query executions and the database is over-utilized
APM tool like AppDynamics helps in expediting this analysis.

Further Reading:
Ensure Proper SQL Server Connection Pooling


Comments

Post a Comment

Popular posts from this blog

Test Case Template for Project Using Agile Methodology

User Story One template that can be used while writing user stories could be – “As a (role) I want (functionality) so that (benefit).” For example – As a ‘Bank Customer’, wants to ‘Transfer Funds’ to one of their friends / acquaintances online, which is fast and auditable. Or, the user story could be described as a single line item (2-5 words) as well. For example – Fund Transfer Test Cases Few of the test cases (summary) for above user story could be as follows.  These test cases should be developed with the help of Business analyst. Transfer fund when fund is in range of acceptable limit and existing balance is sufficient. Or, Fund transfer amount less than existing balance Transfer fund when fund is in range of acceptable limit but existing balance is not sufficient. Or, Fund transfer amount more than existing balance Transfer fund when fund amount is beyond acceptable limit. Or, Fund transfer amount more t...
Read more

Measurement & Metrics

"You cannot improve what you cannot measure" A lot had been talked and spoken already about ‘Measurement & Metrics” in Software Testing.  In this article I am not saying that something new will be written or discussed, but my idea for this article is to summarize on this topic from various different sources. Measuring, Monitoring and Acting on it are key roles of any manager in any industry and Software Testing is no different.  A lot of times it has been seen that KPIs are being measured and monitored but not being acted.  In that case, this whole exercise becomes worthless.  Sometimes, the measurements themselves are not collected and reported correctly, which is more dangerous than not doing this activity at all. Other times, it had been observed that there are numerous KPIs are being monitored without having any goals in mind.  As per me, the number of KPIs that should be measured and monitored should be minimal and any addition to new KPIs sho...
Read more

Performance Test Run Report Template

You have gathered requirements from clients, identified key critical business scenarios, analyzed the workload distributions, found out performance goals and requirements, identified  tool for generating load, setup the test environment, designed the scripts, executed the performance run, collected the raw data, analyzed it, what next?  Send information to stakeholders? No!!  Wait!! It needs to formatted, customized for different stakeholder(s) and reported accordingly. This article suggests one of the ways to report performance run results to client especially if the performance testing activity has been outsourced to yours company.  It discusses the performance report template and importantly the reasons for including the items in the report. The performance report template contains information by data collected from client side only and doesn’t discuss / talk about data from server side like processor utilization etc. Broadly the performance report has been divi...
Read more