Using JMeter to test PostGIS

The following is a guide on how to use Apache JMeter to test PostGIS performance

1. Download Apache JMeter

Firstly start by downloading the binary zip from here

Once you have downloaded unzip the file and I like to place it in the C:\ root folder

2. Download the PostgreSQL JDBC driver

In order to get JMeter connected to PostGIS we need to download the PostgreSQL JDBC driver from here. It is worth reading some of details there as it tells you which version you should download.

Download the correct version and copy the *.jar file into the C:\apache-jmeter-2.13\lib folder

3. Launch JMeter

To launch JMeter go to C:\apache-jmeter-2.13\bin and run the jmeter.bat file

This should launch JMeter and you should get the following landing page

4. Creating a Test Plan

Start by giving the Test Plan a name and a comment:

PostGIS Test Plan
A test plan for testing PostGIS performance

Then save the test plan.

Now we need to add a Thread Group to our plan, in JMeter a thread is the equivalent to a user.

In the left-hand side menu, right-click the 'PostGIS Test Plan' and choose 'Add | Threads (Users) | Thread Group option. This will add a Thread Group to our plan which we can now configure.

Change the name, number of Threads and the number of Loops

Next we need to add the JDBC Connection. So right-click the Data Requests node and choose 'Add | Config Element | JDBC Connection Configuration'

We need to give it a name, a variable name and setup the database connection details

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

Now that we have setup the database conncection we now need to add a JDBC request.

Right-click the Data Requests node and 'Add | Sampler | JDBC Request'

We need to give it a name, set the Variable Name to the same name as the 'PostGIS Connection' variable name that we set earlier.

We can also add an SQL query just to test the connection works.

Finally we need to add a listener to the Plan so that we can see the results of the queries. There are lots of different types from Graphs to Tress, at this stage we will just add a Summary Report.

Right-click the Data Requests node and choose 'Add | Listener | Summary Report'

Now to run this plan we can press the green run button on the toolbar.

You can see that 100 Samples were made and the time taken.

This isn't a very good example of a query that will be made against our database so lets change that now.

5. What queries to run

We normally use a PostGIS database with GeoServer, QGIS or MapBox, so we need to figure out what those queries look like and use them as our SQL query.

To do this open pgAdminIII and run a new SQL query

SELECT query FROM pg_stat_activity WHERE state = 'active'

This will show you the current running SQL queries.

So to find the queries that your software uses start using that software and when it is pulling data from PostGIS run that query and you should see a query like this:

SELECT "ogc_fid",encode(ST_AsBinary(ST_Simplify(ST_Force_2D("wkb_geometry"), 30.54058436769992)),'base64') as "wkb_geometry" FROM "vml"."roadcline" WHERE  "wkb_geometry" && ST_GeomFromText('POLYGON ((305357.76531244203 108891.21934787626, 305357.7653124420 (...)"

This is an example request from GeoServer.

But there is a problem with using the same query that uses the same polygon over and over again. So how can we use a number of different polygons to use as part of the plan.

Well every year at FOSS4G there is normally a WMS challenge where the developers from each of the main geographic server (Mapserver, GeoServer, QGIS Server etc) compete against one another. To make it a fair fight they use the same data and they use a JMeter test plan. To create the requests Frank Warmerdam wrote a python script that will create a CSV file that contains the different width/height and bounds.

This script can be found here

6. Use wms_request.py

Save the wms_request.py script locally and we can get it to create a CSV that contains different bounding boxes we can use in our queries.

Open a command prompt window in the same folder as the script and run the following command

python wms_request.py -count 1000 -region 300000 100000 600000 900000 -minsize 400 400 -maxsize 800 800 -minres 1 -maxres 15 -srs 27700

This will output a 27700.csv file.

Open this file and replace all ';' (semi-colons) with ',' (commas)

JMeter does not like to have a line of data with a mixture of delimiters.

We can now use this file in JMeter to make a much more useful text plan.

7. Use CSV in test plan

In your JMeter test plan we need to add a CSV data config so right-click the 'Data Requests' node and choose 'Add | Config Element | CSV Data Set Config'

We need to give it a name and then specify the location and contents of the CSV file.

Now we need to edit our JDBC request to use the values in the CSV file.

So we have added four parameter values (${left},${bottom},${right},${top}) and then specified that each is a DECIMAL as a parameter type.

We have then modified our SQL query type to Prepared Select Statement and then changed the SQL query to something similar to what a piece of software would request.

If we run the plan and look at the Summary Report we can see the results.

Now just to show you why using JMeter is so useful we can now test what the performance would be like if we didn't have a spatial index on that PostGIS table.

If you compare the different:

With spatial index: 75 milliseconds
Without spatial index: 85036 milliseconds

That shows just how important having a spatial index is.