Re: Postgresql - Pgbouncer Connection and Query Performance Problem

Hi Abdullah,

What pooling mode you use with PgBouncer strictly depends on the
application and what features of the database you use at the application
side. For example, if you need to use session level features of PostgreSQL,
such as setting search_path, then you need to use session mode. Most of the
time, transaction mode suffices. But, you need to consider features the
application uses before doing such a change.

Having 4096 client connections and prepared transactions on the database
raises too many questions for me. If I were the person who was hired as a
database consultant for this setup, I would want to talk to the person who
configured the database first. Those numbers may have some logical
explanation. But, I would consider those numbers to be set arbitrarily
because of the accompanying PgBouncer configuration. You set the default
pool size to 4096 while keeping max_client_conn as 2048. I don't see any
point in having a pool with twice the maximum number of client connections.

Having 15k active customers is data. But, it is not necessarily enough to
decide how many database connections you need. More important data is their
database usage pattern.

I translate "5 million data flow per day" as "5 million transactions per
day". Let's say those transactions happen during business hours only. It
means, you have approximately ~175 transactions per second
(5000000/(8*60*60)). I would say it is an ordinary number. However, it
depends on how heavy those transactions are.

Regarding lightweight locks, type of the LWLock is important. Having too
many idle connections on the database has its own costs. LWLocks you saw on
your database is probably related to idle connections.

https://www.postgresql.org/docs/16/monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE

To conclude, there is no magic number that I can give you to set so as to
get better performance right now. I recommend you to monitor the database,
talk to the person who configured the database, and talk to your software
development team to understand the database usage pattern of the
application.

Best regards.
Samed YILDIRIM

On Wed, 3 Apr 2024 at 09:25, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Tue, 2024-04-02 at 17:04 +0300, Abdullah Ergin wrote:
> > [large connection pool size]
> > 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?
>
> That depends entirely on your hardware and the database workload.
> Perhaps reading this article can help you:
>
> https://www.cybertec-postgresql.com/en/estimating-connection-pool-size-with-postgresql-database-statistics/
>
> > 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?
>
> Very likely yes.
> If you see many LWLocks, that will cause a significant performance issue.
> Essentially, it is contention inside the database.
>
> Yours,
> Laurenz Albe
>
>
>