DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on • Updated on

How to measure performance of PostgreSQL Database Server(s)?

#postgres #database #performance #devops

In this blog I will demonstrate how to measure performance of PostgreSQL Database Server(s). I will guide you how to run a benchmark test.

The benchmark test is designed to evaluate and compare the performance of different configurations, topologies, systems and components. For this purpose I will use the pgbench utility.

You may ask, why use a separate utility to measure the performance of a PostgreSQL database? After all, we usually have a specific application or system that accesses the database and manipulates data. By running an existing application in different modes and simulating different load scenarios, we can check the performance of the database.

However, this method does not always work because the application itself may have architectural or system limitations. As a result, it can be very difficult and challenging to generate enough load and to objectively evaluate the performance of the database. When we're talking about measuring performance of the database the most challenging thing is to generate enough load.

The pgbench is a benchmarking tool for PostgreSQL databases. It allows simulating a workload of multiple clients executing transactions on a PostgreSQL database. The pgbench utility measures the database's performance under different scenarios.

Assume we have installed PostgreSQL database server:

dmi@dmi-VirtualBox:~$ psql -h 127.0.0.1 -p 5444 -U postgres -d postgres
Password for user postgres:
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#
Enter fullscreen mode Exit fullscreen mode

To run a benchmark test I will create a new database.

create database my_benchmark_test_db;
Enter fullscreen mode Exit fullscreen mode

By running \list command I can validate the DB has created successfully:

postgres=# \l
                                                  List of databases
         Name         |  Owner   | Encoding | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges
----------------------+----------+----------+---------+-------+------------+-----------------+-----------------------
 my_benchmark_test_db | postgres | UTF8     | en_IL   | en_IL |            | libc            |
 postgres             | postgres | UTF8     | en_IL   | en_IL |            | libc            |
 template0            | postgres | UTF8     | en_IL   | en_IL |            | libc            | =c/postgres          +
                      |          |          |         |       |            |                 | postgres=CTc/postgres
 template1            | postgres | UTF8     | en_IL   | en_IL |            | libc            | =c/postgres          +
                      |          |          |         |       |            |                 | postgres=CTc/postgres
(4 rows)

postgres=#
Enter fullscreen mode Exit fullscreen mode

To set up pgbench to work with the newly created database my_benchmark_test_db run the command:

pgbench -i -s 50 my_benchmark_test_db -h <db_hostname> -p <db_port> -U <db_user>
Enter fullscreen mode Exit fullscreen mode

For example:

dmi@dmi-VirtualBox:~$ pgbench -i -s 50 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
5000000 of 5000000 tuples (100%) done (elapsed 10.19 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 30.29 s (drop tables 0.05 s, create tables 0.04 s, client-side generate 10.64 s, vacuum 4.75 s, primary keys 14.81 s).
dmi@dmi-VirtualBox:~$
Enter fullscreen mode Exit fullscreen mode

The -i (initialize) option tells pgbench to initialize the database specified.

Connecting to the PostgreSQL database my_benchmark_test_db we can see the pgbench created four tables: pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers

dmi@dmi-VirtualBox:~$ psql -h 127.0.0.1 -p 5444 -U postgres -d my_benchmark_test_db
Password for user postgres:
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

my_benchmark_test_db=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

my_benchmark_test_db=#
Enter fullscreen mode Exit fullscreen mode

Let's examine the number of rows in each table:

my_benchmark_test_db=# select count(1) from pgbench_accounts;
  count
---------
 5000000
(1 row)

my_benchmark_test_db=# select count(1) from pgbench_branches;
 count
-------
    50
(1 row)

my_benchmark_test_db=# select count(1) from pgbench_history;
 count
-------
     0
(1 row)

my_benchmark_test_db=# select count(1) from pgbench_tellers;
 count
-------
   500
(1 row)

my_benchmark_test_db=#
Enter fullscreen mode Exit fullscreen mode

The database my_benchmark_test_db is now populated and ready to measure the performance of our PostgreSQL database server.

When we want to determine the performance of a system, we usually compare its metrics against a certain baseline. Let's run the pgbench utility and set a baseline.

For this purpose, we run pgbench with the following parameters:

pgbench -c <the_number_of_clients_to_connect_with> -j <the_number_of_workers_processes> -t <the_number_of_transactions_to_execute> <sample_db_name>
Enter fullscreen mode Exit fullscreen mode

For example:

pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres

dmi@dmi-VirtualBox:~$ pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 75.438 ms
initial connection time = 160.700 ms
tps = 132.559344 (without initial connection time)
dmi@dmi-VirtualBox:~$
Enter fullscreen mode Exit fullscreen mode

The output has a lot of information.

The most interesting one is:

latency average = 75.438 ms
initial connection time = 160.700 ms
tps = 132.559344 (without initial connection time)
Enter fullscreen mode Exit fullscreen mode

Now let's increase the amount of memory the PostgreSQL DB Server can utilize for caching.
The idea is to store the contents of tables and indexes in memory.
For this purpose we will change the PostgreSQL parameter shared_buffers.

Currently it's set to:

my_benchmark_test_db=# show shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

my_benchmark_test_db=#
Enter fullscreen mode Exit fullscreen mode

Let's change it to 2GB:

sudo vi /etc/postgresql/15/main/postgresql.conf

...
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 1GB                    # min 128kB
                                        # (change requires restart)
...
Enter fullscreen mode Exit fullscreen mode

The change requires to restart the PostgreSQL service:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Examine the changed PostgreSQL parameter shared_buffers:

dmi@dmi-VirtualBox:~$ psql -h 127.0.0.1 -p 5444 -U postgres -d my_benchmark_test_db
Password for user postgres:
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

my_benchmark_test_db=# show shared_buffers;
 shared_buffers
----------------
 1GB
(1 row)

my_benchmark_test_db=#
Enter fullscreen mode Exit fullscreen mode

Rerun the pgbench utility after the shared_buffers PostgreSQL parameter was increased:

pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres

dmi@dmi-VirtualBox:~$ pgbench -c 10 -j 2 -t 1000 my_benchmark_test_db -h 127.0.0.1 -p 5444 -U postgres
Password:
pgbench (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 47.632 ms
initial connection time = 148.379 ms
tps = 209.944478 (without initial connection time)
dmi@dmi-VirtualBox:~$
Enter fullscreen mode Exit fullscreen mode

latency average = 47.632 ms
initial connection time = 148.379 ms
tps = 209.944478 (without initial connection time)

In the baseline test, we reached a rate of 132 transactions per second. in this run, after increasing the shared_buffers parameter, we reached 209 transactions per second, an increase of ~58%.

The best practice recommends setting the shared_buffers value to 1/4 (one-fourth) of the system memory (free -m).

By measuring the performance of a database using pgbench utility, we can continue. For example, we can compare the performance of different versions of the PostgreSQL engines. This way, we can determine whether one version of PostgreSQL engine performs better or worse than another.

This methodology can become part of a performance test for your company.

Top comments (0)

Read next

yayabobi profile image

The Developer's Guide to OWASP API Security

yayabobi -

tungbq profile image

The DevOps Basics 🚀

Tung Leo -

env0team profile image

A Complete Guide to Terraform Cloud Pricing

env0 Team -

r0mymendez profile image

Simplify Database Migrations using Python with Alembic

Romina Mendez -