Postgresql - Pgbouncer Connection and Query Performance Problem

From: Abdullah Ergin <abdullaherginwork(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Postgresql - Pgbouncer Connection and Query Performance Problem
Date: 2024-04-02 12:39:58
Message-ID: CAJZxWENRnzd4z_5HfTmU=PUnm-vRREmTA35ykj97KrJ5bRunzA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

We are using prepared transactions in PostgreSQL. We use pgbouncer as our
pooling software. From time to time, I encountered connection issues in my
database sessions, and to resolve them, I had to restart pgbouncer. While
the *pool_mode* parameter in pgbouncer was set to *session*, I changed it
to *transaction*, and the connection issue was resolved. However, I am now
experiencing significant slowdowns in my queries. I attribute this to two
parameters in my PostgreSQL clusters and three parameters in pgbouncer.
Firstly, on my server, I have 15 PostgreSQL clusters, and the values of
these two parameters in the postgresql.conf file of each cluster seem very
high;

*max_connection = 4096
max_prepared_transaction = 4096*

These two parameters are set to 4096 in all 15 clusters.

Do these values seem too high, and could they be causing my queries to
run slowly?

On the other hand, the values I find high in pgbouncer are;

*max_db_connections = 8192
default_pool_size = 4096
max_client_conn = 2048*

Could these high values be causing excessive load on pgbouncer?

I have actively 15,000 customers on my server, and there is an average data
flow of 5 million daily (including updates). What should be the value of
these parameters? Especially, what should I pay attention to when setting
pgbouncer parameters?

I changed the specified three parameters in pgbouncer to the following
values;

*max_db_connections = 500
default_pool_size = 300
max_client_conn = 500*

Currently, it seems like my query performance has improved, but what should
I do to permanently solve this? What other parameters should I pay
attention to besides these?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2024-04-02 13:26:32 Re: Postgresql - Pgbouncer Connection and Query Performance Problem
Previous Message Dave Cramer 2024-03-31 10:05:48 Re: cached plan must not change result type