PostgreSQL load testing using JMeter, Yandex.Tank and Overload

A few words to start

So, this manual is suitable for those who are looking for universal tools for testing a large pool of systems and solving most tasks of load testing. The article is intended for beginners in this matter, so I will try to maximize the details and simplify the process. We will briefly discuss each of the elements in our bundle and move on to their initial installation and configuration:

1. Apache JMeter is a load testing tool that can run tests for JDBC connections, FTP, LDAP, SOAP, JMS, POP3, IMAP, HTTP and TCP out of the box and many other protocols and solutions using various plugins.
2. Yandex.Tank is a cloud-based tool for load testing. It uses various load generators, including JMeter.
3. Yandex.OverLoad is a service for convenient monitoring and analysis of servers under load.

Installation and setup

The key to success in installing JMeter correctly is installing java correctly. At this stage, it is worth saying that all manipulations will continue to occur under Linux. On the official website there are good manuals with all the nuances:

1. Install a 32-bit version of Java.
2. Install a 64-bit version of Java.

With Java sorted out for verification, make java -version in the console, the answer should be something like this:

Now let's move on to Apache JMeter. You can install any version of JMeter, but if you encounter problems such as “Error in NonGUIDriver”, then most likely you need a tank of a fresh version or you can easily switch to working 2.13. Let's go back to the installation - it is similar for all versions:
Download the client :

wget https://archive.apache.org/dist/jmeter/binaries/apache-jmeter-2.13.tgz

Unpack:

tar -zxvf apache-jmeter-2.13.tgz

JMeter is installed and will already work. For initial setup, it will be enough to make only one change. We follow the path apache-jmeter-2.13 / bin, open the jmeter file without the extension for editing:

cd apache-jmeter-2.13/bin nano jmeter

We find the line, as in the screenshot below. We set the heap size values ​​in accordance with the characteristics of the server used. The first value of HEAP Xms is the amount of RAM allocated to the process when it starts, and the second Xmx is the maximum value of RAM that will be available to the process.



If the server is without a GUI and access to it is remote, as in most cases, it is better to install JMeter locally for debugging and writing scripts. Ideally, the versions should be the same, but in most cases, JMeter understands the scripts of neighboring or lower versions. Of course, Java and JMeter itself must also be installed on the local machine .
We proceed to the installation and configuration of Yandex.Tank, for this to be sufficient, these steps should be completed:

sudo apt-get install python-pip build-essential python-dev libffi-dev gfortran libssl-dev
sudo -H pip install --upgrade pip
sudo -H pip install --upgrade setuptools
sudo -H pip install https://api.github.com/repos/yandex/yandex-tank/tarball/master

The next step is to tell our gun where to shoot and how. To do this, create a working directory on the machine with the tank with the configuration file load.ini. The

mkdir test cd test nano load.ini

contents of the configuration file is a guide to the actions of the tank, it needs to reflect all the key points of the test. Here is an example load.ini for a test using JMeter:



I think everything is clear in the [tank] block, if questions arise, there is a description of each field and block from the tankers themselves. All the most interesting in the [jmeter] block.
The jmx parameter contains the path directly to the script, jmeter_path is the path to the JMeter executable file, well, in order to prevent the tank from making unnecessary movements, you need to specify the version of JMeter in the jmeter_ver parameter.

Script development

It's time to write the first test. To do this, open the JMeter client - most Windows systems require running as administrator. About how to start mastering JMeter, and at the same time to understand its GUI is well written in this article . We have an example with a database, which is slightly different from regular http requests. The first difference is that PostgreSQL is not supported in JMeter out of the box, so you need to download the driver of the correct version. Then you need to put the downloaded .jar in the / lib directory in the folder with JMeter. The same .jar must be placed along a similar path on a car with a tank. With the driver sorted out, go to the script.

The first thing you need to do is configure the database connection, for this, right-click on Test Plan -> Add -> Config Element -> JDBC Connection Configuration. The test tree will be replenished with the configuration element, as in the screenshot:



Going to it, we will see many necessary fields. Here you need to pay attention first to the top:



Here it is important to fill in the Variable Name field. This name will be used in the JDBC Request (Sampler) to access the session pool. It is also worth paying attention to Max Number Of Connections, this parameter limits the number of simultaneous connections to the database. The rest in this block can be left as it is, if there are no special requirements for timeouts and the life cycle of the connection.



We pass to the block in the screenshot above, here we will be required to enter data to connect to the database. The template is this:

• Database URL: jdbc: postgresql: // IPAddress: PortNo / DatabaseName? AutoReconnect = true;
• JDBC Driver class: org.postgresql.Driver;
• Username: username of database;
• Password: password of database.

We proceed to further filling the dough tree. Click Test Plan -> Add -> Threads (Users) -> Thread Group again.



Before us is the firing control panel, it is here that we enter how many users - Number of Threads, in what time it will be reached - Ramp-Up Period and with what intensity each new user will repeat the actions along with the previous one. If we need to send requests endlessly, check “forever” opposite the Loop Count. For the first test, you can set the value to a couple of users, with a small number of repetitions. Now we pass directly to the queries in the database.



To add a JDBC request sampler plan to the test, right-click on the Thread Group -> Add -> Sampler -> JDBC Request we need. We are initially interested in the Variable Name field, it must match the same parameter from the JBDC Connection Configuration. After that, we look at the Query Type drop-down list, depending on your query, there should be an appropriate value, for example, for a SELECT query in the database, you need to select Select Statement, and for INSERT, COPY and UPDATE - Update Statement, as in our example. At the end of working with this sampler, we need to specify the request body, which should correspond to a valid SQL query.

In general, the script is ready, but we need to debug it, it is done like this:

• after the JDBC Request, add Debug Sampler to the desired Thread Group. It will display the response received after a query to the database. It is added similarly to the previous elements. Thread Group -> Add -> Sampler -> Debug Sampler;
• in order to see the results of one or another sampler, at the end of the test plan we need to monitor the results - it is added like this: Test Plan -> Add -> Listener -> View Results Tree. When you run the script there will be a drilldown on all executed requests.

The time has come for a test run from JMeter itself - just click and click on the View Results Tree. After working out the script, I got the following results:

Script debugging

We see that the first one worked successfully and we look at the Response message field, it says that the field must be unique. So we move on to parameterization. There are many ways to do this, the most unique of which is parameterization using BeanShell PreProcessor. To do this, we need to insert the above preprocessor before our JDBC Request - Thread Group -> Add -> Pre Processors -> BeanShell PreProcessor. You can read about preprocessors here or on the JMeter website . BeanShell PreProcessor is not recommended for heavy loads, as a stable and fast option use JSR223 PreProcessor + Groovy.

There are no frills here - we write in java, import the necessary class, declare a variable, assign a random variable of the required format to it. To put the resulting variable in a JMeter variable, use vars.put (). Now go to our JDBC Request and add a variable in the format $ {needUUID} instead of the value in a unique field, an example in the screenshot below:



The final structure of our test plan is as follows:



We make another run from JMeter, immediately look at View Results Tree, select Debug Sampler and go to the Response Data tab:



Here we can make sure that our variable was successfully generated and that it is different for all requests. When the test is debugged and ready to run, we need to remove the debugging elements from it, namely Debug Sampler and View Results Tree. This is done by simply pressing the right button on the element and selecting Remove. Just in case, I apply the type of test after this:



Now we save the test and transfer it to the car with the tank. We add it to the “jmx” parameter in the file load.ini and save it. Absolute addressing is used. It looks like this to me:



The time has come for the first launch to understand that everything works at this stage. Before that, we need to control the addition of rows to the database. You can use the pgAdmin tool to do this.or just go to the server with the database and do count lines before and after running the script. Connecting to the database is performed by a similar command:

psql -h IpAddress -d dbName -U UserName

do count itself like this:

select count(*) from alert;

Go back to the car with the tank, go to the directory where load.ini is located and enter the command to start the tank:

yandex-tank

A few seconds after the launch, a window with the current test parameters appears. Keep in mind that during short tests the tank may not have time to get data on the current parameters.

Monitoring

After checking the execution and testing of the test, we proceed to the monitoring settings. Fortunately, overload monitoring will require minimal effort. The following actions are sufficient:

• registration on Overload ;
• we get the token by clicking on the profile icon in Overload and selecting “my api token”;
• create token.txt in the same folder as load.ini on the machine with the tank;
• we enter the received token in token.txt and save it;
• add the [overload] block to load.ini - description from the tankers;

We get roughly the following contents of load.ini:

Now, after starting the test, our Overload profile will display the current test and all the past ones with numerous useful graphs:



C Overload is done, now a few words about other metrics and their monitoring. There are many guides for directly monitoring the database, so I will not describe this process in detail. I advise you to use telegraf to remove server metrics and influxdb for the database. The output of all metrics can be organized in Grafana. To do this, you can use the installation process in this guide .

In the end, it would be nice to say that recently, namely, starting from version 3.2, JMeter has a built-in influx-based monitoring solution, but there, unlike Overload, you have to configure everything yourself.

That's all. Good shooting everyone!