Thread: pgbouncer logs and its efficiency
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
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.
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
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.
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