Load Testing a Database Server

Microservices architectures are increasingly being used, which involves more and more databases being used as well.

Usually, databases are accessed through an API, and therefore, for a load test to be as realistic as possible, it is necessary to test the API, and not the database directly:

Figure 9.1: Microservices architecture versus monolithic architecture

However, it may be useful to test the database directly when:

• The API is not yet ready
• To compare JDBC drivers easily
• To tune database parameters
• To compare different databases
• To compare AWS RDS engine implementations (such as Aurora, Oracle, and SQL Server)
• To check the database configuration
• To check whether the cluster (as in, the VIP and load balancing strategy) is behaving well
• To test the database production server before going live without having an API server production-ready
• To do load testing on a database when a batch is...

A Bit of Theory

The architecture of a basic engine has become very complex, as shown in the following diagram (note that this is only a simplified overview of the Oracle architecture).

The complexity is such that user expertise in one version of Oracle is partly outdated as soon as the next version is released:

Figure 9.2: A simplified description of Oracle architecture

Each part of this architecture can be tuned based on the expected usage of the database.

Add to this the fact that misconfigured databases or unoptimized SQL queries are the cause of most common performance problems, and you can understand the importance of performing load tests on the database server.

For those who are not yet convinced of the importance of tuning the database engine architecture, the following graph shows an example of the response time of a SQL query on Oracle without tuning (where SGA, one of the Oracle database caches, is not correctly configured) versus an optimized Oracle (where the size...

Methodology

Before moving on to concrete examples, it is important to follow a methodology by which to perform the relevant tests. A critical thing when testing a database is to make sure that it is equivalent to production. Otherwise, make sure that:

• The difference between environments is acceptable
• The difference between environments is clearly identified
• The test remains meaningful and usable

By "equivalent to production," we mean two things:

• The configuration of the database engine must be identical to the production engine (if it exists).
• The volume of data in the database must also be as close as possible to that for the production database (the simplest way to do this is to have a backup of what is in production, or, if you start from scratch, to have an idea of the future volume).

For skeptics or those who are curious, we can see in the following graph the response time of the same SQL query executed on different volumes of data:

...

Setup with JMeter

Note

Since JMeter is a Java program, access to a database is done using the JDBC protocol.

The first thing to do is put the JDBC driver in the JMeter classpath if that has not already been done.

The two simplest solutions to this are:

• Dropping the .jar driver file in JMETER_HOME/lib/
• Using the user.classpath property to reference the driver JAR

Once this is done, you must configure the connection to the database with the JDBC Connection Configuration element.

This will allow us to configure the connection string for our database (including the URL, port, connection ID, password, and so on):

v

Figure 9.5: JDBC connection configuration

This element is composed of four parts, named as follows:

• Variable Name Bound to Pool: This name will further be used in JDBC Requests.
• Connection Pool Configuration.
• Connection Validation by Pool.
• Database Connection Configuration.

The property names are very self-explanatory, so we will not dwell any...

Putting Theory into Practice with JMeter

Let's move on to practice.

Example 1: Load Test of a Database

Let's start with a simple example (which we will enrich as we go along) that consists of testing a database under MySQL using a SQL query.

In this example, we will use the Employees database available at https://github.com/datacharmer/test_db.

Let's first deal with SQL queries of the SELECT type:

1. Let's start by configuring our connection to MySQL using the JDBC Connection Configuration item.

   For MySQL, the database URL must be in the form of jdbc:mysql://host:port/dbname and the JDBC driver class must be com.mysql.jdbc.Driver.

   In our case, the MySQL database is installed locally on the same machine as JMeter (avoid this absolutely during real tests). Its URL will be jdbc:mysql://localhost:3306/employees:

   Note

   If not already the case, set the maximum number of connections (Max Number of Connections) to zero so that each thread has its own connection.

   Figure 9.7: JDBC connection...

Conclusion

As we have seen, performing a load test on a database using JMeter is possible without any great difficulty. JMeter's capabilities also make it possible to design a realistic load test with which you can do just about anything that comes to mind, but don't forget to test the API/application that accesses the database. If you do forget, your tests may not be realistic.