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

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.

A Cloudy Day in Performance Load Testing

Deploying websites on Cloud infrastructure may be a panacea for many but problems may still exists.

Nowadays it is rare for a website or web application to go live without passing pre-determined performance criteria that has been previously defined as necessary by the business.  This normally comes down to number of webpages or transactions per second all loaded within a specific download speed. Testing to prove the website against this type of performance criteria is necessary to ensure the end-user experience is as intended.

However as more websites move onto cloud infrastructures the benefits of cloud, such as the dynamic addition of infrastructure through auto-scaling and the setting of predictable performance levels with database transaction units (DTUs – in Microsoft’s Azure ) must also be proven in load testing. This is not only to ensure that these features work as expected but also to ensure that costs are not unnecessarily incurred or business not transacted through their mis-configuration or even under-assignment. 

Auto-scaling and Performance Load Testing

As it is a major benefit of cloud environments it is important to get auto-scaling to work correctly as the load on your website fluctuates otherwise you may end up paying for services you are not using.  Fortunately, cloud vendors have made the configuration of auto-scaling simple so that the service is quick and easy to use, but when dealing with dynamic loads on a system you need to ensure that the rules will work when you need them most. 

In a recent load test the client began a user journey (UJ) test that would continually increase load up to 500 concurrent users over a 30 minute period. They were confident that their application would perform well but what we found was the way that auto-scaling had been configured, end-user experience would have been extremely poor. In our results chart we can see that after 2.5 minutes of the test, the download speed of the UJ started to increase at an alarming rate. 

Performance Load Test Results with Auto-scaling

From our previous benchmark test, the UJ should complete in approximately 47 seconds but in the auto-scaling test as the load went over 50 users the download speed started to climb very quickly.  We observed that the CPU was also moving towards 100% at this point, but the planned auto-scaling had not kicked in.      

Auto-scaling had been configured to bring more resources to bear from 70% CPU utilisation, but only if CPU was at 70% or greater for 10 minutes, so we had found the reason why more servers had not spun up.   This proved to be the case as about 10 minutes after auto-scaling had occurred the download speed started to improve due to the extra resources within the environment .

The client quickly configured a new rule that stated that if CPU is 80% or greater, then just spin up the extra resources.  This dynamic change to configuration proved worthy as CPU started to climb again at about 18 minutes. This was sufficient to contain the increasing workload on the CPU resources and start to reduce download speed.

In the graph we can see than the UJ never recovers back to target time of 47 seconds. Further investigation showed that this was due to a secondary problem that had been identified through performance load testing.

Database Transaction Units (DTUs)

Because we could now see the CPU resources had settled to an acceptable level this secondary problem was something new.  

The client’s databases are hosted in Microsoft’s Azure where it is possible to procure a specific performance level for your database. This is where Microsoft is committing a certain level of resources for your specific database with the intention of delivering a predictable level of performance.  The Azure platform implements this approach through the concept of Database Transaction Units (DTUs) and this enables Microsoft to charge a price that will be dependent upon how performant you want your databases to be, so it is important to get these settings right.

Further analysis showed that the number of DTUs assigned to one of the three databases used in this UJ had reached its maximum.  Consequently, the database was unable to work any faster. 

Of course, the simple thing to do at this point would be to add ore DTUs, which over time could prove expensive as this could be a never ending cost to the operating budget.  However, the client decided it was worth a review of how this specific database was being used in this UJ. Using the knowledge gained of how the application works under load the client was able to make some minor enhancements that resolved this without incurring further infrastructure costs.

It’s Not a Cloudy Day Though

As a mainstream technology Cloud delivers many benefits and advanced infrastructure features that enable consistent performance and the dynamic scaling of services.  However, regular and effective performance load testing is essential to maximising cost benefits while ensuring that your website or application will perform for your end-users as expected when most needed.