From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Abdullah Ergin <abdullaherginwork@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgresql - Pgbouncer Connection and Query Performance Problem
Date: Tue, 02 Apr 2024 15:26:32 +0200
Message-ID: <0adf6b95e08946113f12415e6f95fc74d2bc1a06.camel@cybertec.at> (raw)
In-Reply-To: <CAJZxWENRnzd4z_5HfTmU=PUnm-vRREmTA35ykj97KrJ5bRunzA@mail.gmail.com>
References: <CAJZxWENRnzd4z_5HfTmU=PUnm-vRREmTA35ykj97KrJ5bRunzA@mail.gmail.com>
On Tue, 2024-04-02 at 15:39 +0300, Abdullah Ergin wrote:
> We are using prepared transactions in PostgreSQL. We use pgbouncer as our pooling software.
> [...] 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
Yes, they are very high, but they are just limits. As long as you don't actually
establish hundreds of connections, that is not a problem.
> 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?
> 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?
"max_client_conn" is not the problem. The problem is the excessively high pool
size of 300. The size should be so that when all those connections are busy running
statements, your database should not be overloaded.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, abdullaherginwork@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Postgresql - Pgbouncer Connection and Query Performance Problem
In-Reply-To: <0adf6b95e08946113f12415e6f95fc74d2bc1a06.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox