OmniDB: Average execution time of PostgreSQL webmethods

1. Introduction

Being a web application, OmniDB is composed of many webmethods. A webmethod is a special kind of function hosted on the server side (the web server), called from the client side (the web browser), when the user performs some action on OmniDB web interface. For instance, OmniDB has several webmethods that know how to communicate with all supported versions of PostgreSQL. This set of webmethods is called PostgreSQL webmethods of OmniDB.

We automated the execution of all PostgreSQL webmethods through the Django unit tests feature. For each supported version of PostgreSQL (from 9.2 to 9.6 and also 10), there are 56 unit tests that simulate common user behavior. The database used for the tests is a well known PostgreSQL sample called dellstore2.

Using vagrant, we also automated the creation of virtual machines, installation of PostgreSQL inside the VM, and the creation of dellstore2 database.

2. Simulating tests in your machine

If you want, you can simulate these tests following the steps below, or you can just skip to section 3 to see average execution time of the tests.

2.1. Pull OmniDB repo

The first thing you need to do is to download OmniDB repo from GitHub and make sure you are in the development branch. Run the following:

git clone http://github.com/OmniDB/OmniDB
cd OmniDB
git checkout dev

2.2. Create a virtual machine with PostgreSQL and dellstore2 sample database

On your host machine, you need to have installed:

• VirtualBox
• Vagrant
• Vagrant plugin vbguest

Please refer to VirtualBox and Vagrant websites for more information.

Now on your terminal (assuming you are on the root directory of OmniDB repo) to create a virtual machine with PostgreSQL 10, for example, you need to do:

cd OmniDB/OmniDB_app/tests/vagrant/postgresql-10
vagrant up

PostgreSQL will be listening on port 5432 of the virtual machine. But the above script also maps this port into port 5410 of your host machine. Version 9.6 is mapped into port 5496 of the host machine, version 9.5 is mapped into port 5495, and so on.

The above script also creates user omnidb with password omnidb, and an empty database called omnidb_tests. If you want to tweak this and other settings, please refer to file OmniDB/OmniDB_app/tests/vagrant/postgresql-10/bootstrap.sh.

After the machine is created and up, and PostgreSQL is running, you can restore dellstore2* inside of omnidb_testsdatabase:

./restore.sh

Now you can shutdown your machine anytime with:

vagrant halt

And to start it up again, execute:

vagrant up

Please note that you don’t need to run ./restore.sh again, because the database omnidb_tests is already filled.

2.3. Run unit tests for a specific version of PostgreSQL

On your terminal, assuming you are on the root of the OmniDB repo and the above machine is up, do the following:

cd OmniDB
python manage.py test OmniDB_app.tests.test_postgresql10

The output will be like this:

Creating test database for alias 'default'...
System check identified no issues (0 silenced).
........................................................
----------------------------------------------------------------------
Ran 56 tests in 2.215s

OK
Destroying test database for alias 'default'...

Meaning that all 56 unit tests for PostgreSQL 10 have passed, and the total execution time was 2.215 seconds.

You can run similar tests for other versions of PostgreSQL by just repeating steps 2 and 3, changing the directory like this:

3. Results

3.1. PostgreSQL running inside a virtual machine

The hardware used in this experiment was a notebook with Intel(R) Core(TM) i7-4500U CPU @ 1.80GHz and 8GB RAM. However, each Vagrant VM (the ones running PostgreSQL) had only 512MB RAM. We ran only one Vagrant VM at the same time, making sure one was shutdown before starting another, to not impact on the performance results.

Following the steps presented in section 2 and 3 of this blog post, we ran each test 3 times. All tests always passed and we wrote down the execution times like in the table below.

Bear in mind that every time we run a test for a specific version, there are 56 unit tests being executed. Each unit test executes an OmniDB webmethod. As we are running 6 versions of PostgreSQL (from 9.2 to 10), we are in fact running 336 unit tests total.

So, the average runtime of OmniDB webmethods that handles PostgreSQL elements (when PostgreSQL is hosted inside a virtual machine) is 0.038 seconds.

3.2. PostgreSQL running in the same host as OmniDB

We can compare these results against running both OmniDB and PostgreSQL in the same host. The same machine used for the tests above has a PostgreSQL 9.6 installed, so we performed the same test 3 times:

In this case, the average execution time when PostgreSQL is in the same host is 0.019 seconds. When compared to the average execution time for when PostgreSQL 9.6 is installed in a VM (0.038 seconds), we can say that it is 3 times faster, as expected.