Re: Postgresql - Pgbouncer Connection and Query Performance Problem

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