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.
