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