Re: Postgresql - Pgbouncer Connection and Query Performance Problem

Thank you for the information. What would you recommend as the value for
this parameter? Would 100 be too large of a number? Or maybe 50?

Additionally, before lowering these parameters, I had a lot of "*LWLocks*"
in my database. Normally, I know that these lightweight locks don't cause
significant issues, but during the slowdown, I was consistently seeing *70-80
LWLocks*. After lowering the parameters and restarting pooling, these locks
disappeared. Is there any correlation?

Best regards.

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, 2 Nis 2024 Sal, 16:26 tarihinde
şunu yazdı:

> 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
>