2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / OmniDB3 / OmniDB: Average execution time of PostgreSQL webmethods
William Ivanski

OmniDB: Average execution time of PostgreSQL webmethods

August 29, 2017/0 Comments/in OmniDB, William's PlanetPostgreSQL /by William Ivanski

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:

Version Path
9.2 OmniDB/OmniDB_app/tests/vagrant/postgresql-92
9.3 OmniDB/OmniDB_app/tests/vagrant/postgresql-93
9.4 OmniDB/OmniDB_app/tests/vagrant/postgresql-94
9.5 OmniDB/OmniDB_app/tests/vagrant/postgresql-95
9.6 OmniDB/OmniDB_app/tests/vagrant/postgresql-96
10 OmniDB/OmniDB_app/tests/vagrant/postgresql-10

 

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.

Version Number of webmethods 1st run 2nd run 3rd run Average for all webmethods Average for a single webmethod
9.2 56 2.083s 2.054s 2.040s 2.059s 0.037s
9.3 56 2.099s 2.123s 2.071s 2.098s 0.037s
9.4 56 2.172s 2.132s 2.105s 2.136s 0.038s
9.5 56 2.218s 2.087s 2.106s 2.137s 0.038s
9.6 56 2.222s 2.060s 2.065s 2.116s 0.038s
10 56 2.215s 2.118s 2.080s 2.138s 0.038s
Total 336 13.009s 12.574s 12.465s 12.683s 0.038s

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:

Version Number of webmethods 1st run 2nd run 3rd run Average for all webmethods Average for a single webmethod
9.6 local 56 1.079s 1.094s 1.082s 1.085 0.019

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.

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
JOOQ Object Oriented Querying (jOOQ) The Barman is ready for PostgreSQL 10
Scroll to top
×