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.
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.albe@xxxxxxxxxxx> 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
