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-capsfalse# 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

CREATESCHEMAtest;

DROPTABLEIFEXISTS test.patient;

CREATETABLE test.country (
    idINT PRIMARY KEY,
    nameVARCHAR(225)
);

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

CREATETABLE test.patient (
    idINTGENERATEDBYDEFAULTASIDENTITY PRIMARY KEY,
    nameVARCHAR(225),
    lastname VARCHAR(225),
    photo BYTEA,
    phone VARCHAR(14),
    address VARCHAR(225),
    city INT,
    CONSTRAINT fk_city
        FOREIGN KEY(city)
            REFERENCES test.city(id)
);

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

INSERTINTO 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:

INSERTINTO 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:

INSERTINTO 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:

SELECTcount(p.Name), c.Name FROM test.patient p leftjoin test.city c on p.City = c.Id GROUPBY 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'WHERENamein (SELECTNameFROM test.patient whereNamelike'%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 WHERENamein (SELECTNameFROM test.patient whereNamelike'%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.