pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpgbouncer logs and its efficiency
5+ messages / 2 participants
[nested] [flat]
* pgbouncer logs and its efficiency
@ 2022-01-28 04:14 Victor Sudakov <vas@sibptus.ru>
0 siblings, 1 reply; 5+ messages in thread
From: Victor Sudakov @ 2022-01-28 04:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear Colleages,
I have two questions about pgbouncer logs, can you please share your
knowledge.
1. What is the meaning of those IDs in the log like
"S-0xaaaae460fcb0", "C-0xaaaae45d9fb0" ? I suspect that "S-" and "C-"
mean "server" and "client", but what are those HEX numbers?
2. How would you calculate the efficiency of pgbouncer, like
client_conns/server_conns ratio (current and average over over time)?
I have a case where pgbouncer is probably doing more bad than good,
but I would like to be able to prove or disprove it.
--
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgbouncer logs and its efficiency
@ 2022-02-02 07:08 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Victor Sudakov <vas@sibptus.ru>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Eisentraut @ 2022-02-02 07:08 UTC (permalink / raw)
To: Victor Sudakov <vas@sibptus.ru>; pgsql-admin@lists.postgresql.org
On 28.01.22 05:14, Victor Sudakov wrote:
> 1. What is the meaning of those IDs in the log like
> "S-0xaaaae460fcb0", "C-0xaaaae45d9fb0" ? I suspect that "S-" and "C-"
> mean "server" and "client", but what are those HEX numbers?
These are the internal addresses of the server and client structures.
You can use those to track a particular server or client connection
through the logs and through SHOW SERVERS and SHOW SERVERS.
> 2. How would you calculate the efficiency of pgbouncer, like
> client_conns/server_conns ratio (current and average over over time)?
This depends heavily on what you are using pgbouncer for and what
metrics are relevant for your application.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgbouncer logs and its efficiency
@ 2022-02-02 08:45 Victor Sudakov <vas@sibptus.ru>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 5+ messages in thread
From: Victor Sudakov @ 2022-02-02 08:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Peter Eisentraut wrote:
> On 28.01.22 05:14, Victor Sudakov wrote:
> > 1. What is the meaning of those IDs in the log like
> > "S-0xaaaae460fcb0", "C-0xaaaae45d9fb0" ? I suspect that "S-" and "C-"
> > mean "server" and "client", but what are those HEX numbers?
>
> These are the internal addresses of the server and client structures.
> You can use those to track a particular server or client connection
> through the logs and through SHOW SERVERS and SHOW SERVERS.
I see. So they are probably not very useful for my task.
>
> > 2. How would you calculate the efficiency of pgbouncer, like
> > client_conns/server_conns ratio (current and average over over time)?
>
> This depends heavily on what you are using pgbouncer for and what
> metrics are relevant for your application.
The relevant metric would be the client_conns/server_conns ratio. If
there have been 1000 client connections/disconnections and they have
caused only 100 new backends to start, I'd consider it a good ratio.
The question is how to estimate it.
--
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgbouncer logs and its efficiency
@ 2022-02-03 09:39 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Victor Sudakov <vas@sibptus.ru>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Eisentraut @ 2022-02-03 09:39 UTC (permalink / raw)
To: Victor Sudakov <vas@sibptus.ru>; pgsql-admin@lists.postgresql.org
On 02.02.22 09:45, Victor Sudakov wrote:
>>> 2. How would you calculate the efficiency of pgbouncer, like
>>> client_conns/server_conns ratio (current and average over over time)?
>>
>> This depends heavily on what you are using pgbouncer for and what
>> metrics are relevant for your application.
>
> The relevant metric would be the client_conns/server_conns ratio. If
> there have been 1000 client connections/disconnections and they have
> caused only 100 new backends to start, I'd consider it a good ratio.
>
> The question is how to estimate it.
You can count the number of rows in the SHOW CLIENTS and SHOW SERVERS
output and chart them using your favorite metrics collection system.
There are various tools and recipes out there on how to collect metrics
from pgbouncer and push them into metrics or graphing systems.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: pgbouncer logs and its efficiency
@ 2022-02-03 15:15 Victor Sudakov <vas@sibptus.ru>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 0 replies; 5+ messages in thread
From: Victor Sudakov @ 2022-02-03 15:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Peter Eisentraut wrote:
> On 02.02.22 09:45, Victor Sudakov wrote:
> >>> 2. How would you calculate the efficiency of pgbouncer, like
> >>> client_conns/server_conns ratio (current and average over over time)?
> >>
> >> This depends heavily on what you are using pgbouncer for and what
> >> metrics are relevant for your application.
> >
> > The relevant metric would be the client_conns/server_conns ratio. If
> > there have been 1000 client connections/disconnections and they have
> > caused only 100 new backends to start, I'd consider it a good ratio.
> >
> > The question is how to estimate it.
>
> You can count the number of rows in the SHOW CLIENTS and SHOW SERVERS
> output and chart them using your favorite metrics collection system.
> There are various tools and recipes out there on how to collect metrics
> from pgbouncer and push them into metrics or graphing systems.
Hello Peter,
If I were going to continuously monitor the pgbouncer efficiency, it
would be a good idea. Maybe there is even a Prometheus exporter for
pgbouncer.
However, do you think this information can be inferred from pgbouncer
logs in some way? Because I need it only occasionally.
If, for example, I divide the number of "login attempt" lines in the
log by the number of "new connection to server" lines, will this be
the ratio I'm looking for?
--
Victor Sudakov VAS4-RIPE
http://vas.tomsk.ru/
2:5005/49@fidonet
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-02-03 15:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-28 04:14 pgbouncer logs and its efficiency Victor Sudakov <vas@sibptus.ru>
2022-02-02 07:08 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2022-02-02 08:45 ` Victor Sudakov <vas@sibptus.ru>
2022-02-03 09:39 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2022-02-03 15:15 ` Victor Sudakov <vas@sibptus.ru>
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