PgBouncer installation an configuration on k8s cluster to enhance PostgreSQL performance
PgBouncer is the most widely used connection pooler for PostgreSQL. In this article, I will show you how to configure PgBouncer on k8s cluster using helm to enhance your PostgreSQL database and better connection handling
Why you should use PgBouncer ?
It reduces PostgreSQL resource consumption (memory, backends, fork)
PostgreSQL forks a separate process for each client connection. At the same time applications do not always use connection to the database permanently, connections are often idle. Thus PgBouncer creates a pool of database connections and provides these connections to clients when the connection is required. Thus, if you have a lot of client connections to the database, PgBouncer can reduce the number of PostgreSQL backends processes. The response time between database and a client also decreases, because no need to fork a new backend process
It supports online restart/upgrade without dropping client connections.
PgBouncer support online restart/upgrade. This function allows you to restart PgBouncer without dropping client connections
It allows allows PostgreSQL restart/upgrade without dropping client connections.
PgBouncer was originally created for Skype, which requires almost zero downtime for your database. PgBouncer works as a proxy for your database, so you can pause connection to your database during restart. The only drawback is that the database clients will get slow response. Thus, maintenance can be performed transparently for applications and clients
Installation
Prerequisites
- Admin access on Kubernetes cluster ≥1.17
- helm version 3
- helmfile
Install PostgresSQL database helm chart
This script should do basic installation for Bitnami PostgreSQL helm chart. Then you need to export DB password as environment variable. It will be used later to connect to your database.
Connect to PostgreSQL pod
Create PgBouncer user on PostgreSQL database
After you run previous command, you will be prompted to database shell.
Now, you need to create a user for PgBouncer to able to connect to database and get md5 password hashes for all database users. This is required as the client applications needs to be authenticated to PgBouncer first.
I set the password here to change me, but you should it to something more secure
Install PgBouncer helm chart
Before you install Pgboucner helm chart, make sure that you change the password from previous command
Verify that you can connect to PostgreSQL via PgPouncer pod
After you run above command you should be connected to PostgreSQL via PgBouncer.
Benchmarking:
This is a result of a simple test, The latency for PgBouncer is higher, as it introduces additional network hop, but it achieves higher tbs
postgresql-client:/$ pgbench -U postgres -h postgresql -p 5432 -c 100 -j 2 -t 100 postgresstarting vacuum…end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 100
number of threads: 2
number of transactions per client: 100
number of transactions actually processed: 10000/10000
latency average = 442.811 ms
tps = 225.830158 (including connections establishing)
tps = 225.865945 (excluding connections establishing)postgresql-client:/$ pgbench -U postgres -h pgbouncer-pgbouncer -p 5432 -c 100 -j 2 -t 100 postgres
starting vacuum…end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 50
query mode: simple
number of clients: 100
number of threads: 2
number of transactions per client: 100
number of transactions actually processed: 10000/10000
latency average = 593.158 ms
tps = 254.350416 (including connections establishing)
tps = 254.369747 (excluding connections establishing)
References:
