Select Page
One of the first questions any potential customer asks us is “how much can caching improve my application’s performance.”  This is a very difficult question to answer beyond “it depends”.  What is easier is to describe a methodology which will provide a good estimate on the impact the database has on the application.  With this, it is much easier to estimate the overall impact of optimization.

Measuring Application and SQL Wait Time

To estimate the SQL response times, it’s essential to measure the overall application wait time. For web applications, this can be done by analyzing the web access logs. By summing up the total access time for a specific period, such as ten minutes, we can capture the comprehensive picture. Another approach is to create a local benchmark that mimics user activity, recording the response time of each query across a single connection.

Analyzing SQL Traffic and Comparing Data Access

To gain deeper insights, you analyze the wait time related to SQL traffic. By enabling the SQL logging function on the Hemdall Proxy and aggregating the execution times of all queries during the designated test period, we can determine the total SQL wait time. This analysis allows us to assess the impact of SQL on application performance accurately.

By comparing the application wait time and SQL wait time data collected for the same period, we can isolate the percentage of wait time attributable to data access. For example, if the total application time is one hour, and the database access time is thirty minutes, the data access impact on the application would be 50%. With this information, implementing caching at a 50% cache hit rate can potentially result in an overall performance improvement of 25%.

Additionally, the advantage of this methodology is that it quickly gets to the point of what is possible – instead of spending time tuning the cache and trying to achieve an optimal cache hit rate, it bounds expectations to determine if this effort will be worth it before it continues optimizing the database performance.

Next in this series, we will apply this methodology to analyze an application from start to end, to show the impact of query caching on the application, with different amounts of latency between the application and the database. This will include the scripts and configuration steps to configure the application, sample data, and overall results.