Performance tests IRIS - PostgreSQL - MySQL

As a former JAVA developer it has always been a challenge to decide which database was the most suitable for the project we were going to develop, one of the main criteria I used was their performance, as well as their HA configuration capabilities ( high availability). Well, now is the time to put IRIS to the test with respect to some of the most commonly used databases, so I've decided to create a small Java project based on SpringBoot that connects via JDBC with a MySQL database, another of PostgreSQL and finally with IRIS.

We are going to take advantage of the fact that we have Docker images of these databases to use them in our project and allow you to try it yourself without having to carry out any installation. We can check the docker configuration in our docker-compose.yml file

version: "2.2"
services:
  # mysql
  mysql:
    build:
      context: mysql
    container_name: mysql
    restart: always
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: SYS
      MYSQL_USER: testuser
      MYSQL_PASSWORD: testpassword
      MYSQL_DATABASE: test
    volumes:
    - ./mysql/sql/dump.sql:/docker-entrypoint-initdb.d/dump.sql
    ports:
      - 3306:3306
  # postgres
  postgres:
    build:
      context: postgres
    container_name: postgres
    restart: always
    environment:
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpassword
    volumes:
    - ./postgres/sql/dump.sql:/docker-entrypoint-initdb.d/dump.sql
    ports:
      - 5432:5432
  adminer:
    container_name: adminer
    image: adminer
    restart: always
    depends_on:
      - mysql
      - postgres
    ports:
      - 8081:8080
  # iris
  iris:
    init: true
    container_name: iris
    build:
      context: .
      dockerfile: iris/Dockerfile
    ports:
      - 52773:52773
      - 1972:1972
    command: --check-caps false
  # tomcat
  tomcat:
    init: true
    container_name: tomcat
    build:
      context: .
      dockerfile: tomcat/Dockerfile
    volumes:
      - ./tomcat/performance.war:/usr/local/tomcat/webapps/performance.war
    ports:
      - 8080:8080

With a quick glance we will see that we are using the following images:

• IRIS: IRIS Community instance to which we will connect by JDBC.
• Postgres: PostgreSQL database image listening on port 5432.
• MySQL: MySQL database image listening on port 3306.
• Tomcat: Docker image configured with an Apache Tomcat application server on which we will deploy the WAR file of our application.
• Adminer: database administrator that will allow us to consult the Postgres and MySQL databases.

As you can see, we have configured the listening ports so that they are also mapped on our computer, not only within Docker. In the case of databases, it would not be necessary, since the connection will be made within the Docker containers, so if you have any problems with the ports, you can delete the ports line from the docker-compose.yml file.

Each database image is running a pre-script that will create the tables needed for performance tests, let's look at one of the dump.sql files

CREATE SCHEMA test;

DROP TABLE IF EXISTS test.patient;

CREATE TABLE test.country (
    id INT PRIMARY KEY,
    name VARCHAR(225)
);

CREATE TABLE test.city (
    id INT PRIMARY KEY,
    name VARCHAR(225),
    lastname VARCHAR(225),
    photo BYTEA,
    phone VARCHAR(14),
    address VARCHAR(225),
    country INT,
    CONSTRAINT fk_country
        FOREIGN KEY(country)
            REFERENCES test.country(id)
);

CREATE TABLE test.patient (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(225),
    lastname VARCHAR(225),
    photo BYTEA,
    phone VARCHAR(14),
    address VARCHAR(225),
    city INT,
    CONSTRAINT fk_city
        FOREIGN KEY(city)
            REFERENCES test.city(id)
);

INSERT INTO test.country VALUES (1,'Spain'), (2,'France'), (3,'Portugal'), (4,'Germany');

INSERT INTO test.city VALUES (1,'Madrid',1), (2,'Valencia',1), (3,'Paris',2), (4,'Bordeaux',2), (5,'Lisbon',3), (6,'Porto',3), (7,'Berlin',4), (8,'Frankfurt',4);

We are going to create 3 tables for our tests, patient, city and country, these last two are going to have preloaded data of cities and countries.

Perfect, next we are going to see how we will make the connections to the database.

To do this we have created our Java project using a preconfigured Spring Boot project available from Visual Studio Code that provides us with the basic structure.

Don't worry if you don't understand the structure of the project at first glance, the goal is not to learn Java, but still we are going to explain a little more in detail the main documents.

MyDataSourceFactory.java

Java class that opens the connections to the different databases.

PerformancerController.java

Controller in charge of publishing the endpoints that we will call from Postman.

application.properties

Configuration file with the different connections to the databases deployed in our Docker.

As you can see, the connection URLs use the container name since, when deployed in a Tomcat container, the databases will be accessible by our Java application only with the corresponding container name. We can also check how the URL is making a connection via JDBC to our databases. The Java libraries used in the project are defined in the pom.xml file.

If you modify the source code, you only have to execute the command:

mvn package

And this will generate a file performance-0.0.1-SNAPSHOT.war, rename it to performance.war and move it to the /tomcat directory, replacing the existing one.

As the project is on GitHub, we only need to clone it on our computer from Visual Studio and execute the following commands in the terminal:

docker-compose build
docker-compose up -d

Let's check the Docker portal:

Great! Docker containers working. Now let's check from our Adminer and the IRIS management portal that our tables have been created correctly.

Let's first access the MySQL database. If you consult the file docker-compose.yml we will see that the username and password defined for MySQL and PostgreSQL are the same testuser/testpassword

Here we have our three tables inside our Test database, let's look at our PostgreSQL database:

Let's select the testuser database and the test schema:

Here we have our tables perfectly created in PostgreSQL. Let's finally check that everything is configured correctly in IRIS:

All correct, we have our tables created in the USER Namespace under the Test schema.

Alright, once the checks are done, let's rock! For this we will use Postman, in which we will load the file attached to the project performance.postman_collection.json

These are the different tests that we are going to launch, we will start with inserts and continue with queries against the database. I have not included any type of index beyond those that are created automatically with the definition of primary keys in the different databases.

Insert

REST call: GET http://localhost:8080/performance/tests/insert/{database}?total=1000

The variable {database} may have the following values:

• postgres
• mysql
• iris

And the total attribute will be the one that we will modify to indicate the total number of insertions that we want to make.

The method that will be invoked is called insertRecords and you can find it in the java file PerformanceController.java located at /src/main/java/com/performance/controller/, you can see that it is an extremely simple insert:

INSERT INTO test.patient VALUES (null, ?, ?, null, ?, ?, ?)

The first value is null as it is the autogenerated primary key and the second null corresponds to a BLOB/BYTEA/LONGVARBINARY type field where we will save a photo later.

We are going to launch the following batches of pushes: 100, 1000 , 10000, 20000 and we will check the response times that we will receive in Postman. For each measurement we will do 3 tests and we will calculate the average of the 3 values that we obtain.

Let's see it graphically.

 

Insert with a binary file

In the previous example we did simple inserts, let's go to push the accelerator including in our insert a 50 kB picture as a photo for our patients.

REST call: GET http://localhost:8080/performance/tests/insertBlob/{database}?total=1000

The variable {database} may have the following values:

• postgres
• mysql
• iris

And the total attribute will be the one that we will modify to indicate the total number of insertions that we want to make.

The method that will be invoked is called insertBlobRecords and you can find it in the java file PerformanceController.java located at /src/main/java/com/performance/controller/, you can check that it is an insert similar to the previous one with the exception that we are passing the file in the insert:

INSERT INTO test.patient (Name, Lastname, Photo, Phone, Address, City) VALUES (?, ?, ?, ?, ?, ?)

Let's slightly modify the number of inserts above to avoid the test taking forever and I will clean the Docker of the images to start again with a total level playing field.

 

Let's look at the graph:

 

Select

Let's test performance with a simple query that gets us all the records from the Patient table.

REST call: GET http://localhost:8080/performance/tests/select/{database}

 

The variable {database} may have the following values:

• postgres
• mysql
• iris

The method that will be invoked is called selectRecords and you can find it in the java file PerformanceController.java located at /src/main/java/com/performance/controller/, the query is extremely basic:

SELECT * FROM test.patient

We'll test the query with the same set of items we used for the first insert test.

 

And graphically:

 

Select group by

Let's test performance with a query that includes a left join as well as aggregation functions. 

REST call: GET http://localhost:8080/performance/tests/selectGroupBy/{database}

The variable {database} may have the following values:

• postgres
• mysql
• iris

The method that will be invoked is called selectGroupBy and you can find it in the java file PerformanceController.java located at /src/main/java/com/performance/controller/, let's see the query:

SELECT count(p.Name), c.Name FROM test.patient p left join test.city c on p.City = c.Id GROUP BY c.Name

We'll test the query again with the same set of items we used for the first insert test.

 

And graphically:

 

Update

For the update we are going to launch a query with an associated subquery within its conditions.

REST Call: GET http://localhost:8080/performance/tests/update/{database}

The variable {database} may have the following values:

• postgres
• mysql
• iris

The method that will be invoked is called UpdateRecords and you can find it in the java file PerformanceController.java located at /src/main/java/com/performance/controller/, let's see the query:

UPDATE test.patient SET Phone = '+15553535301' WHERE Name in (SELECT Name FROM test.patient where Name like '%12')

Let's launch the query and see the results.

We note that MySQL does not allow this type of subqueries on the same table that we are going to update, therefore we cannot measure their times under equal conditions. In this case, we will omit the graph as it is so simple.

Delete

For the delete we are going to launch a query with an associated subquery within its conditions.

REST Call: GET http://localhost:8080/performance/tests/delete/{database}

The variable {database} may have the following values:

• postgres
• mysql
• iris

The method that will be called is called DeleteRecords and you can find it in the java file PerformanceController.java located at /src/main/java/com/performance/controller/, let's see the query:

DELETE test.patient WHERE Name in (SELECT Name FROM test.patient where Name like '%12')

Let's launch the query and see the results.

We note again that MySQL does not allow this type of subqueries on the same table from which we are going to delete, therefore we cannot measure their times under equal conditions.

Conclusions

We can affirm that all of them are quite fine-tuned when it comes to querying data, as well as updating and deleting records (except for the incident with MySQL). Where we find the biggest difference is in the handling of inserts. IRIS is the best of the 3 by far, being 6 times faster than PostgreSQL and up to 20 times faster than MySQL at data ingestion.

In order to operate with large data sets, IRIS is undoubtedly the best option in the tests carried out.

So... we already have a winner! IRIS WINS!

 

PS: These are some small examples of tests that you can carry out, feel free to modify the code as you wish.