PostgreSQL DB
Application description
PostgreSQL is one of the most popular open source relational DataBase applications in use today. There is significant community support for the database with tools and scripts that have been developed to assist with management and access to the DB.
Infrastructure Environment tested
The PostgreSQL DB application has been tested on the following:
| Resource | Value |
|---|---|
| Host OS | Ubuntu 18.04 |
| Kernel Version | 4.15.0-51-generic |
| Package manager | apt |
| Application version | server 9.6.14 |
| Environments tested | AWS r5.2xlarge, AWS i3.2xlarge, Sunlight |
Configuration and Setup description
Benchmark test tool is the pgbench application that is shipped with the PostgreSQL application package.
#Create DB size of 16MB x 2560 => 40GB
pgbench -i -s 2560
#Run the test by simulating 8 concurrent users using 4 threads to access the pre-created database for 60 seconds
pgbench -c 8 -j 4 -T 60 -P 2
Download the full installation script for Ubuntu 18.04 here.
Data results table
For the PostgreSQL database the TPS as a key performance indicator could be affected by a number of factors, including the size of database, number of concurrent users and various database configurations. In this test, as the purpose is to compare the performance between two different platforms, the default database configurations are applied, where the configuration of shared_buffers is 128MB, so that the amount of RAM used for the application is also identical. The tests across different platforms are based on the database with the same size of 40G, while the impact from the number of concurrent users are investigated by simulating different number of concurrent users in the tests to capture the change of performance in a broad range.
This table provides the measured results of TPS with two selected number of concurrent users (i.e. 32 and 128) to show the comparison. The full results are available in the figures in the next section.
| Flavour | Concurrent users | TPS |
|---|---|---|
| AWS i3.2xlarge + guaranteed IOPs | 32 | 2905 |
| AWS r5.2xlarge + guaranteed IOPs | 32 | 4599 |
| Sunlight | 32 | 9294 |
| AWS i3.2xlarge + guaranteed IOPs | 128 | 2865 |
| AWS r5.2xlarge + guaranteed IOPs | 128 | 5548 |
| Sunlight | 128 | 8004 |
Equivalent test on Sunlight executes around 2-3 times faster than on AWS. When the number of concurrent users are more than 32, the TPS on Sunlight is limited by the CPU availability, as the CPU resources are used to handle the user requests and there isn't enough CPU resource available to process the transactions, which is illustrated in the figure "CPU usage of PostgreSQL".
Since the AWS r5.2xlarge instance has better CPU spec than Sunlight (3.1 GHz vs 2.2 GHz frequency), the following tests are conducted to compare the performance of PostgreSQL using VM vDisk and RAM disk as database storage under the same VM (i.e. the same environment including the same CPU spec).
| Flavour | Database Storage | Concurrent users | TPS | Equivalent performance of RAMdisk |
|---|---|---|---|---|
| AWS i3.2xlarge + guaranteed IOPs | RAMdisk | 32 | 9029 | - |
| AWS i3.2xlarge + guaranteed IOPs | vDisk | 32 | 2905 | 32% |
| AWS r5.2xlarge + guaranteed IOPs | RAMdisk | 32 | 13868 | - |
| AWS r5.2xlarge + guaranteed IOPs | vDisk | 32 | 4599 | 33% |
| Sunlight | RAMdisk | 32 | 11127 | - |
| Sunlight | vDisk | 32 | 9294 | 84% |
Performance graphs
The corresponding CPU usage of the above tests are illustrated as below, which shows that the TPS with AWS vDisk is not limited by the CPU resources at the full range of the tests, so the TPS is limited by the storage performance of AWS vDisk. For the other cases when Sunlight vDisk and ramdisk on both platforms are used, the TPS is bottlenecked by the CPU resources, which reveals that the performance of database is not bottlenecked by the storage of Sunlight platform.
Since the above tests are based on instances with CPUs of different frequencies, another AWS instance type of i3.2xlarge which has CPU frequency of 2.3 GHz is tested against Sunlight platform.
The above results demonstrates that Sunlight could achieve the same performance as the ramdisk on an AWS instance with similar CPU specs.