Exposing the vagaries of ORM in load testing

Taking a look at database issues and why an ORM-accessed RDBMS may cause web performance issues

Conduct Load Test

After a long journey in design, development and functional testing, rarely is a new website or application completed without a performance load test as one of its final milestones. Project, and sometimes regulatory, sign-off is dependent on certain criteria being met so there is often much riding on the successful outcome of the load test. Because of this there can be considerable trepidation in the team as they start the load testing process as load testing is designed to exercise both the software and hardware configurations to their limits.

Although the clear objective is to obtain the load test milestone, enabling sign-off for the project, real benefits can be achieved from a well-managed load testing programme that exercises different performance aspects, such as normal and peak operations together with stress, spikey and endurance scenarios. For new applications the load test can often be the first time more than a handful of end-to-end interactions occur concurrently and it is also the time when API’s and endpoints, potentially located in different environments, are tested for stability and scalability.

Its Not Just the Software

In our previous article A Cloudy Day in Performance Load Testing we covered some aspects of how cloud-based environments can influence an application’s scale-ability, but other paradigm’s, such as databases, can be just as influential.

Our chart above shows how influential poor-performing databases can be. These results, from a 4 hour endurance load test, show that after an hour of running at 500 concurrent users, degradation of successful test completions occurs. At this time server response slows, causing page load failures, identified by the black lines, for a significant number of tests.

During the test the client was able to determine that this was caused by a specific database query that was scanning a table for a result rather than using an index. Although the table scan gave the correct result, increasing load was developing on the database server that it could not serve in the time expected of it as the load test continued. The client was able to dynamically resolve the issue creating an index and enabling very fast index searches to occur and which alleviated the bottleneck for the remainder of the test.

ORM and RDBMS

Resolving database scan issues is not uncommon in load testing and when working directly with native databases, schema design, indexes and foreign key issues are easy to identify and resolve. However, things become more complicated when the database is abstracted through object relational mapping (ORM), technology.

ORM is a technology that sits between the database (RDBMS) and application code. As it cr eates a level of abstraction between the application code and the database it reduces the amount and complexity of code necessary to interact with a database. It is not a new technology and there are many commercial frameworks that support ORM and because of the abstraction and programming benefits, take up in application development is increasing.

However, ORM comes with a cost especially in its effect on databases and their performance. A quick search on the internet will unearth many good resources that cover ORM performance but sometimes performance enhancements in ORM are insufficient.

In a recent load test the client found that a particular ORM-accessed database resource was serialising and long queues were developing. This in turn was slowing response as the resource use was pervasive across the application. Having run out of ORM performance tuning options, a quick refactoring taking the resource out of ORM control and using a traditional direct SQL-based transactions resolved the issue. Database resources reduced from 70% to 7% utilisation at peak load and further load testing proved the environment could now run on 50% less configured database resources, saving further costs in their budget for cloud resources.

Closing Observation

Correctly structured, load testing is able to deliver a wealth of performance benefits to any web application project and as this article shows sometimes only when you really exercise the application do you find some real opportunities for performance enhancement and real cost saving.