pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql - Pgbouncer Connection and Query Performance Problem
7+ messages / 3 participants
[nested] [flat]
* Postgresql - Pgbouncer Connection and Query Performance Problem
@ 2024-04-02 12:39 Abdullah Ergin <abdullaherginwork@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Abdullah Ergin @ 2024-04-02 12:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
We are using prepared transactions in PostgreSQL. We use pgbouncer as our
pooling software. From time to time, I encountered connection issues in my
database sessions, and to resolve them, I had to restart pgbouncer. While
the *pool_mode* parameter in pgbouncer was set to *session*, I changed it
to *transaction*, and the connection issue was resolved. However, 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*
These two parameters are set to 4096 in all 15 clusters.
Do these values seem too high, and could they be causing my queries to
run slowly?
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? Especially, what should I pay attention to when setting
pgbouncer 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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql - Pgbouncer Connection and Query Performance Problem
@ 2024-04-02 13:26 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Abdullah Ergin <abdullaherginwork@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2024-04-02 13:26 UTC (permalink / raw)
To: Abdullah Ergin <abdullaherginwork@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql - Pgbouncer Connection and Query Performance Problem
@ 2024-04-02 14:04 Abdullah Ergin <abdullaherginwork@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Abdullah Ergin @ 2024-04-02 14:04 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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.albe@cybertec.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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql - Pgbouncer Connection and Query Performance Problem
@ 2024-04-03 06:24 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Abdullah Ergin <abdullaherginwork@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2024-04-03 06:24 UTC (permalink / raw)
To: Abdullah Ergin <abdullaherginwork@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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-stat...
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql - Pgbouncer Connection and Query Performance Problem
@ 2024-04-04 10:59 Samed YILDIRIM <samed@reddoc.net>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 7+ messages in thread
From: Samed YILDIRIM @ 2024-04-04 10:59 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Abdullah Ergin <abdullaherginwork@gmail.com>; pgsql-admin@lists.postgresql.org
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.albe@cybertec.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-stat...
>
> > 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql - Pgbouncer Connection and Query Performance Problem
@ 2024-04-16 15:40 Abdullah Ergin <abdullaherginwork@gmail.com>
parent: Samed YILDIRIM <samed@reddoc.net>
0 siblings, 1 reply; 7+ messages in thread
From: Abdullah Ergin @ 2024-04-16 15:40 UTC (permalink / raw)
To: Samed YILDIRIM <samed@reddoc.net>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
Hello Samed,
Thank you for the information you provided about LWLocks. When I look at
the type of these locks I see "BufferIO" and "BufferMapping" it really
slows down the system. However, I have not yet fully understood what action
I should take in this situation, especially why this situation is
happening? and what should i do?
Best Regards.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgresql - Pgbouncer Connection and Query Performance Problem
@ 2024-04-16 16:10 Samed YILDIRIM <samed@reddoc.net>
parent: Abdullah Ergin <abdullaherginwork@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Samed YILDIRIM @ 2024-04-16 16:10 UTC (permalink / raw)
To: Abdullah Ergin <abdullaherginwork@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org
Hi Abdullah,
BufferIO and BufferMapping are different types of events than previous
ones. Your server is probably suffering because of intensive IO operations.
There can be big queries who are causing big read or write operations
and/or a significant number of active connections. I would check it at the
operating system level by using top command to see what is the percentage
for IO Wait (wa), system (sy), and software interrupts (si).
Judging by PgBouncer configuration, I suspect that shared_buffers would be
set to a huge number, proportional to the available memory. I would check
this configuration too. You would want to check swap usage too.
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
Best regards.
Samed YILDIRIM
On Tue, 16 Apr 2024 at 18:41, Abdullah Ergin <abdullaherginwork@gmail.com>
wrote:
> Hello Samed,
>
> Thank you for the information you provided about LWLocks. When I look at
> the type of these locks I see "BufferIO" and "BufferMapping" it really
> slows down the system. However, I have not yet fully understood what action
> I should take in this situation, especially why this situation is
> happening? and what should i do?
>
> Best Regards.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-04-16 16:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-02 12:39 Postgresql - Pgbouncer Connection and Query Performance Problem Abdullah Ergin <abdullaherginwork@gmail.com>
2024-04-02 13:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-02 14:04 ` Abdullah Ergin <abdullaherginwork@gmail.com>
2024-04-03 06:24 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-04-04 10:59 ` Samed YILDIRIM <samed@reddoc.net>
2024-04-16 15:40 ` Abdullah Ergin <abdullaherginwork@gmail.com>
2024-04-16 16:10 ` Samed YILDIRIM <samed@reddoc.net>
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