PostgreSQL 14

Introduction

In the evolving landscape of database management, optimizing performance is paramount. For enterprises relying on PostgreSQL, a robust monitoring tool can make all the difference. Using the extension pg_profile , can be a game changer in the realm of PostgreSQL performance tuning. Every millisecond counts, and for PostgreSQL users, navigating the complexities of performance tuning can be daunting. Monitoring and fine-tuning of slow query execution can increase the performance. In this blog , we will delve into the installation, configuration and generating the statistical report using pg_profile .

pg_profile

pg_profile is a extension designed specifically for monitoring and analyzing the performance of PostgreSQL databases. It helps database administrators identify and address performance bottlenecks, optimize queries, and fine-tune configurations to enhance the overall efficiency and responsiveness of PostgreSQL database systems. pg_profile is having some key features like query performance analysis, resource monitoring , index usage analysis and configuration parameter recommendations.

Installation

Here, we are installing version 4.6 of pg_profile and unzip into a folder.

#Installing using wget
[root@localhost ~]# wget https://github.com/zubkov-andrei/pg_profile/releases/download/4.6/pg_profile--4.6.tar.gz

#Unzip into a folder
[root@localhost ~]# tar -xvf pg_profile--4.6.tar.gz --directory /usr/pgsql-16/share/extension
pg_profile--4.6.sql
pg_profile.control
pg_profile--4.5--4.6.sql
[root@localhost ~]# cd /usr/pgsql-16/share/extension
[root@localhost extension]# ls -ltr
total 1680
-rw-r--r--. 1 postgres postgres     193 Apr 27 20:10 pg_profile.control
-rw-r--r--. 1 postgres postgres 1073167 Apr 27 20:10 pg_profile--4.6.sql
-rw-r--r--. 1 postgres postgres  635910 Apr 27 20:10 pg_profile--4.5--4.6.sql

Create extension

pg_profile extension depends on extensions  plpgsql and dblink. The only mandatory requirement for server cluster is the ability to connect from pg_profile database using provided server connection string. All other requirements are optional, but they can improve completeness of gathered statistics. To check the existence of this extensions ,

[root@localhost extension]# yum install postgresql16-contrib

#Login in to psql and create extensions
postgres=# create extension dblink;
CREATE EXTENSION
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# create extension pg_profile;
CREATE EXTENSION
postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description                               
--------------------+---------+------------+------------------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_profile         | 4.6     | public     | PostgreSQL load profile repository and report builder
 pg_stat_statements | 1.10    | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language

Configuration

We need to update few parameters in postgresql.conf file .

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 20000
pg_stat_statements.track = top

#Restart the server

To know the objects in pg_profile , use the command \dx+ pg_profile; . It would display all the objects in the extension, including functions, tables and views.

Generating the statistical report

To generate the statistical report , we need to know the list of servers in pg_profile.

#To know the servers list in pg_profile 
postgres=# select * from show_servers() ;
 server_name |          connstr          | enabled | max_sample_age | description 
-------------+---------------------------+---------+----------------+-------------
 local       | dbname=postgres port=5432 | t       |                | 
(1 row)

As a default , there is a local server. In order the generate the report , we need to take samples at regular intervals. We can then generate a report in-between those samples.

# To take samples


postgres=# select * from take_sample();
 server | result |   elapsed   
--------+--------+-------------
 local  | OK     | 00:00:01.38
(1 row)

#To view samples taken
postgres=# select * from show_samples();
 sample |        sample_time        | sizes_collected | dbstats_reset | clustats_reset | archstats_reset 
--------+---------------------------+-----------------+---------------+----------------+-----------------
      1 | 2024-05-17 00:38:59+05:30 | t               |               |                | 
      2 | 2024-05-17 00:40:01+05:30 | t               |               |                | 
      3 | 2024-05-17 00:40:06+05:30 | t               |               |                | 
      4 | 2024-05-17 00:43:51+05:30 | t               |               |                | 
(4 rows)

Here , We have taken four samples . We can generate the report in between these samples. For example , between 1-2,1-3 ,1-4 ,2-3 ,2-4 & 3-4 .

#To generate a report in html

[postgres@localhost ~]$ psql -Aqtc "SELECT get_report('local',1,2)" -o Report.html
[postgres@localhost ~]$ ls -ltr
total 348
drwx------. 4 postgres postgres     51 May 15 07:25 16
-rw-r--r--. 1 postgres postgres 350993 May 17 00:56 Report.html

On viewing the generated html report, we can find SQL query statistics ,schema object statistics ,user function statistics and vacuum related statistics.

Summary

To summarize – in the current landscape of database management , it is important to monitor and optimize the database performance. In this post, we have introduced you to pg_profile, a game-changing tool tailored specifically for PostgreSQL performance monitoring. We’ve delved into its features, installation process, and seamless integration with existing PostgreSQL infrastructures, setting the stage for an immersive exploration of its capabilities.

Thank you and Stay tuned for more…

Introduction

In the realm of database performance tuning, the pursuit of optimal throughput and responsiveness is a never-ending journey. In our previous blog , we discussed about pgbench , setting up and first run of pgbench , tips, tricks and common pitfalls of benchmarking. Building upon that foundation, we now venture into the realm of advanced optimization strategies, where we delve deeper into the intricacies of pgbench with more options in it. Additionally, we will explore ways to interpret pgbench results effectively, transforming raw metrics into actionable insights that drive tangible performance enhancements.

If you haven’t read our previous blog , read here.

Exploring pgbench with more options

In our previous blog , we explored the basic options of pgbench like -c for number of clients,-j for number of threads, -t for number of transactions, -T for time limit . But, there are lot more options that the pgbench utility offers. Let’s explore a few more of these options and see how they can be used.

No vacuum -n

It is used to avoid the vacuuming before running the test . Usually , this option is used in a custom test run using a script which does not include the default tables.

#Running test run without -n
[postgres@localhost bin]$ ./pgbench db
pgbench (16.3)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>

#Running test run with -n
[postgres@localhost bin]$ ./pgbench -n db
pgbench (16.3)
transaction type: <builtin: TPC-B (sort of)>

Analyzing the difference , without option -n it is running vacuum. You can see avoiding vacuum with option -n

Connect -C

This option will establish a new connection for each transaction instead of once per client sessions. It is used to measure the client overhead.

#Using -C
[postgres@localhost bin]$ ./pgbench -C db
pgbench (16.3)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 5.255 ms
average connection time = 2.524 ms
tps = 190.284094 (including reconnection times)

Observing the tps , it is including the time taken for reconnection . If you don’t use this option , you will see tps without initial connection time.

Rate -R & Latency limit -L

These two options are mostly used together. Rate -R used to specify the rate/speed of transactions and Latency limit -L used to count the transaction over the specified limit. These two are calculated in milliseconds.

[postgres@localhost bin]$ ./pgbench -R 10 -L 10 db
pgbench (16.3)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
number of transactions skipped: 0 (0.000%)
number of transactions above the 10.0 ms latency limit: 0/10 (0.000%)
latency average = 3.153 ms
latency stddev = 0.480 ms
rate limit schedule lag: avg 0.311 (max 0.518) ms
initial connection time = 2.973 ms
tps = 6.476235 (without initial connection time)

Here, we can see the number of transactions above the specified latency limit and number of transactions skipped. In this case, there are no transactions that crossed the given inputs.

Report per command -r

This option gives us the stats of each command after the benchmark finishes.

[postgres@localhost bin]$ ./pgbench -r db
pgbench (16.3)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
number of failed transactions: 0 (0.000%)
latency average = 1.020 ms
initial connection time = 2.315 ms
tps = 980.199961 (without initial connection time)
statement latencies in milliseconds and failures:
         0.001           0  \set aid random(1, 100000 * :scale)
         0.000           0  \set bid random(1, 1 * :scale)
         0.000           0  \set tid random(1, 10 * :scale)
         0.000           0  \set delta random(-5000, 5000)
         0.029           0  BEGIN;
         0.169           0  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.053           0  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.056           0  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.043           0  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.054           0  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
	 0.611           0  END;

It gives us the latency and failures of the default script used by pgbench.

Summary

To summarize, effective benchmarking is crucial for the optimal performance and scalability of databases.By leveraging benchmarking tools such as pgbench, database administrators and developers can simulate real-world workloads, measure performance metrics, and identify areas for optimization. However, successful benchmarking requires careful planning, execution, and analysis. In this blog , we have explored few more options in leveraging the pgbench utility. If you haven’t checked our previous blog on pgbench , here is the blog.

Thank you and stay tuned for more…

Introduction

In today’s world of databases, performance is one of the key aspects that need to be taken care-of irrespective of the size of the database. If you’re in charge of managing a PostgreSQL database, ensuring peak performance is undoubtedly a top priority. Enter pgbench, PostgreSQL’s own benchmarking powerhouse, designed to help you simulate workloads and test different database strategies to optimize efficiency. Whether you’re a seasoned DBA or just starting out, understanding how to leverage pgbench can be a game-changer for your database operations. This comprehensive guide will cover everything you need to know to get started with pgbench, interpret its results, and implement its insights for optimal database performance.

pgbench

pgbench is a benchmarking tool included with PostgreSQL that simulates a standard workload. A simple program for running benchmark tests on PostgreSQL.. By default, pgbench tests are based on the TPC-B benchmark, which includes executing 5 SELECT, INSERT, and UPDATE commands per transaction. It is available as a part of the PostgreSQL installation. Using pgbench , we can simulate various workloads on PostgreSQL databases by generating multiple client connections and also by predefined scripts or set of parameters.

Setting up and running my first pgbench test

pgbench should be invoked with the -i (initialize) option to create and populate these tables. While initializing pgbench -i  database_name, it creates four tables pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers dropping any existing tables of these names.

However it will check for the previous tables of these names and create again whenever -i (initialize) is given. In order a scale up , with a scale factor -s of 20, it will insert data into the tables to represent a workload 20 times larger than the default scale. Each tuple inserted represents a row of data in the database tables as shown below.

There are various parameters in pgbench to perform benchmark tests. The most common options are -c for number of clients,-j for number of threads, -t for number of transactions, -T for time limit and -f for specify a custom script file.

Running pgbench

Simulating the workload with 50 client connections and 100 transactions per client to database mydb

Understanding the Output

Let’s analyze the output, there are some defaults that will be considered such as scaling factor =1 , number of threads =1 if you didn’t give them in input.

Number of clients , Number of transactions per client is given as input to pgbench and number of transactions actually processed is the product of those two. There are zero transactions failed.

Latency average is the time taken to respond for a given request. It is measured in milliseconds . here it is 92.234ms

Initial connection time is the time taken for the client to establish connection with the server. Here it took 416.143ms

Most important is tps i.e transaction per seconds , it refers to the number of transactions processed by the system in one second time interval. It varies based on the complexity of transactions(or query) , system resources, concurrency levels, and database configuration. It is better to have high tps.

Tips and Tricks

• Understand the workloads – It is important to understand the workloads on application and simulate accordingly using customized SQL scripts and adjusting scale factor.
• Controlling concurrency level – We can control the concurrency level  in the pgbench test using the options -c and -j accordingly.
• Monitor System Metrics – Use system monitoring tools such as top to monitor CPU usage, memory utilization, disk I/O, and network activity during pgbench tests. 
• Tune PostgreSQL configurations – Adjust postgresql.conf parameters , such as shared_buffers, max_connections, work_mem based on results.
• Run multiple tests – To get the accurate assessment on performance , run the multiple pgbench tests.
• Documentation – Documenting the methodologies & results of your testing activities will help in further analysis.

Common pitfalls to avoid in database benchmarking

• Doing the benchmark testing without understanding the workload.
• Running the test on the insufficient CPU, memory, storage, and network resources to support the expected workload.
• Incorrect benchmarking tools and methodologies can lead to incorrect results.
• Not testing on realistic conditions or failing to replicate the environment conditions leads to improper results.
• Considering the result of single test rather than the average of multiple tests
• No proper documentation

Summary

To summarize, effective benchmarking is crucial for the optimal performance and scalability of databases.By leveraging benchmarking tools such as pgbench, database administrators and developers can simulate real-world workloads, measure performance metrics, and identify areas for optimization. However, successful benchmarking requires careful planning, execution, and analysis.In this blog, we have explored some features and results of pgbench including tips , tricks and also common pitfalls need to be avoided in database benchmarking.

Thank you and stay tuned for more…

Introduction

Hey All

Managing a PostgreSQL replication setup involves various challenges, especially when encountering errors during configuration. This blog post explores common errors encountered while setting up replication using repmgr, a popular replication manager for PostgreSQL. We’ll delve into the causes of these errors and provide solutions to overcome them, ensuring a seamless replication setup. We had discussed setting up High Availability in Postgres using repmgr in this post. If you did not get a chance to go through it, now might be a good time for it.

These are the some of the errors/issues I faced in my initial work on repmgr as well as with ec2 instances. They might look very basic, but they reinforce the fact that minute details are important.

Error#1

This is the first brake of my smooth journey with ec2 instances & repmgr.

[postgres@ip-172-31-14-99 ~]$ /usr/pgsql-14/bin/repmgr -f /var/lib/pgsql/repmgr.conf primary register
WARNING: the following problems were found in the configuration file:
  parameter "cluster" is deprecated and will be ignored
INFO: connecting to primary database...
ERROR: connection to database failed
DETAIL:
connection to server at "52.64.241.210", port 5432 failed: timeout expired

DETAIL: attempted to connect using:
  user=repmgr connect_timeout=2 dbname=repmgr host=52.64.241.210 fallback_application_name=repmgr options=-csearch_path=

Solution

We need to edit the inbound rules of the security group and add each and every PostgreSQL EC2 instances to allow traffic to flow between those instances.
Steps to do : Instances –> Security–>Security group–>Edit Inbound rules

Add the PostgreSQL to the security group as mentioned below

Result

[postgres@ip-172-31-14-99 bin]$ /usr/pgsql-14/bin/repmgr -f /var/lib/pgsql/repmgr.conf primary register
WARNING: the following problems were found in the configuration file:
  parameter "cluster" is deprecated and will be ignored
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered

#Verifying Primary registeration 
[postgres@ip-172-31-14-99 bin]$ /usr/pgsql-14/bin/repmgr -f /var/lib/pgsql/repmgr.conf cluster show
WARNING: the following problems were found in the configuration file:
  parameter "cluster" is deprecated and will be ignored
 ID | Name  | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string
----+-------+---------+-----------+----------+----------+----------+----------+----------------------------------------------------------------
 1  | node1 | primary | * running |          | default  | 100      | 1        | host=52.64.241.210 user=repmgr dbname=repmgr connect_timeout=2

Error#2

This is the basic error , we DBA’s deal with this on daily basis. “No Entries in pg_hba.conf” . I encountered this error while performing the Standby clone dry run operatiion.

[postgres@ip-172-31-7-43 ~]$ /usr/pgsql-14/bin/repmgr -h 52.64.241.210 -U repmgr -d repmgr -f /var/lib/pgsql/repmgr.conf standby clone --dry-run

DETAIL:
connection to server at "52.64.241.210", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "13.210.66.27", user "repmgr", no encryption

ERROR: connection to database failed
DETAIL:
connection to server at "52.64.241.210", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "13.210.66.27", user "repmgr", no encryption

Solution

It clearly indicates that we need to make an entry in pg_hba.conf of Primary server.

cd $PGDATA
vi pg_hba.conf
host    replication     repmgr          {Standby1 IP}           md5
host    repmgr          repmgr          {Standby1 IP}           md5

# We have to add details of all of the Standby nodes and the Witness node too, if applicable.

Note : As we will clone the data from the Primary to both of the Standby nodes , there is no need to make these changes in the Standby nodes. But on the Witness mode , we need to enter the IP’s in pg_hba.conf file and change the parameters in postgresql.conf file . (For more details, refer to this post)

Result

[postgres@ip-172-31-7-43 ~]$ /usr/pgsql-14/bin/repmgr -h 52.64.241.210 -U repmgr -d repmgr -f /var/lib/pgsql/repmgr.conf standby clone --dry-run

NOTICE: standby will attach to upstream node 1
HINT: consider using the -c/--fast-checkpoint option
INFO: would execute:
  pg_basebackup -l "repmgr base backup"  -D /var/lib/pgsql/14/data -h 52.64.241.210 -p 5432 -U repmgr -X stream
INFO: all prerequisites for "standby clone" are met

Error#3

I had encountered this error when I was setting up the Witness node. It is possible that even after you have done an entry in pg_hba.conf file, you get the following error:

[postgres@ip-172-31-8-219 ~]$ /usr/pgsql-14/bin/repmgr -f /var/lib/pgsql/repmgr.conf witness register -h 52.64.241.210
WARNING: the following problems were found in the configuration file:
  parameter "cluster" is deprecated and will be ignored
INFO: connecting to witness node "node4" (ID: 4)
ERROR: unable to connect to witness node "node4" (ID: 4)
DETAIL:
connection to server at "ec2-13-211-191-92.ap-southeast-2.compute.amazonaws.com" (172.31.8.219), port 5432 failed: FATAL:  role "repmgr" is not permitted to log in

HINT: the witness node must be running before it can be registered

Solution

Login to psql and check the attributes of the user

[postgres@ip-172-31-8-219 ~]$ psql
psql (14.11)
Type "help" for help.
postgres=# \du+

                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 repmgr    | Superuser, Cannot login                                    | {}        |

# It shows Role "repmgr" cannot login
# Simply alter the role 
postgres=# ALTER ROLE repmgr LOGIN;
ALTER ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 repmgr    | Superuser                                                  | {}        |
 

Result

After altering the role , retry registering the witness node.

[postgres@ip-172-31-8-219 ~]$ /usr/pgsql-14/bin/repmgr -f /var/lib/pgsql/repmgr.conf witness register -h 52.64.241.210
WARNING: the following problems were found in the configuration file:
  parameter "cluster" is deprecated and will be ignored
INFO: connecting to witness node "node4" (ID: 4)
INFO: connecting to primary node
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
INFO: witness registration complete
NOTICE: witness node "node4" (ID: 4) successfully registered

Conclusion

In PostgreSQL replication setups, encountering errors is not uncommon, but with the right knowledge and troubleshooting steps, they can be resolved effectively. In this post, we have discussed some of the common errors encountered during the configuration of replication using repmgr. By understanding the root cause of these errors and implementing the provided solutions, administrators can ensure a smooth and reliable PostgreSQL replication setup, facilitating data redundancy and high availability for their applications. These might already be known to many of the experienced DBA’s, but can serve as a useful guide to novice PostgreSQL enthusiasts, and DBAs with less experience, and save precious time. If you have encountered any other with issues while building an PostgreSQL HA systems using repmgr, please feel free to comment below, and reach out to us. As always, we’re happy to help.

Thank you and Stay tuned…