Database queries are one of the most expensive parts of an application in terms of cost and performance — so it's important to test their performance.

Why Load Test a Database?

Database queries are expensive in terms of cost and performance, which makes them a natural target for performance tests.

Most database queries occur through HTTP, FTP, or other protocols. But there are some cases where it makes sense to directly test a database without the middleman. For example, you may want to check the performance of a specific high-value query without triggering every query involved, or see how a new database query will perform under load.

You can also use database load tests to optimize your time and resources. Fine-tuning SQL statements and other database operations is time-consuming. With performance testing, you can determine where the most significant bottlenecks exist and focus your efforts on those database queries, rather than selecting arbitrary queries or avoiding optimization altogether.

How to Load Test Databases

There are many ways to load test databases. In addition to selecting the right testing platform, you need an appropriate test plan.

Most approaches use the Java Database Connectivity, or JDBC, which provides a call-level API for SQL-based database access. While you could simply write tests with JDBC, there are several database testing platforms that support JDBC tests out of the box, making it easier to build and run the load tests.

JDBC Request on JMeter – Source: JMeter

For instance, Apache JMeter's built-in JDBC Request module lets you connect to any database and run concurrent SQL queries. You simply input the database, pool details, and then define the SQL query that you'd like to test. The results from the test can be run as part of a CI process and published in the same way as any other JMeter performance test.

SoapUI and ReadyAPI provide similar functionality that’s especially well-suited for APIs. Unlike JMeter, SoapUI lets you easily build SQL queries using a query builder, which can be extremely helpful to avoid easy-to-miss errors or handle complex queries.

Deciding on What to Test

The process of creating database load tests is fairly straightforward, but it may not be clear what queries you should be testing. Or how you should test them.

You should decide what database queries to include, then ensure that the tests accurately represent the production environment. For example, you can't run an SQL query that increments a value on a user table without randomizing users, or test the performance of a delete query without many, many records.

Some ways to help these problems include:

• Use backups of production databases as a source for data, while being sure to anonymize sensitive information.
• Analyze production database queries and figure out how to randomize them in a way that mimics reality.
• Account for differences between the production database backup and queries relying on new tables or columns.

Monitoring Database Queries

Database load testing may help identify problems during development, but it's impossible to cover every query in production. That's why database monitoring is important – to find out, in real time, which queries are causing problems so you can address them quickly. Fortunately, there are many great tools out there to help.

New Relic's Database Monitoring – Source: New Relic

For example, New Relic provides instance-level details where you can see the database server and query that's causing a problem. You can also set up alerts to notify you of potential performance issues before they impact customers. These alerts can be sent straight to DevOps or developers for follow-up.

The Advantage of Browser-based Tests

Database load testing is helpful for finding expensive queries that impact performance, but it doesn't ensure your entire application is performing well. While protocol-based load testing frameworks, like JMeter, can rapidly test endpoints, they don't effectively test client-side performance (e.g. JavaScript execution and other factors).

LoadNinja offers an easy-to-use browser-based load testing platform that provides end-to-end coverage. While you may want to use database-specific load tests in some cases, the majority can be handled through end-to-end load testing platforms. They track performance at every part of the process, rather than focusing on each individual piece.

LoadNinja Record and Replay – Source: LoadNinja

There are several reasons to choose LoadNinja:

• Record and Replay – Browser-based record and replay capabilities make it easy for anyone to create load tests without programming knowledge.
   
• Rapid Scalability – Generate a realistic load using tens of thousands of real browsers in the cloud for data that accurately represents end users.
   
• Extensive Diagnostics – Analyze navigation timings and other data sourced straight from real browsers in load tests, as well as access virtual user DOMs.

LoadNinja makes it easy to incorporate these tests into your CI/CD environment with a Jenkins plugin and easy-to-use API for other services.

Worth Your While

Database performance is a common bottleneck for web applications. While most problems can be identified when using end-to-end testing platforms, in some cases you may want to test individual queries using JDBC-driven tools. These tests can provide your team with greater confidence and ensure that you're not paying too much or underperforming.

Either way, load tests don’t nearly involve the effort they used to.