Please add new pgbouncer-v1.21.0 metrics related to prepared statements

[ale-dd]

It would be great if someone could kindly cut a new release including the new pgbouncer-v1.21.0 metrics related to prepared statements.

I would be happy to submit a simple PR but in order to do so I would first have to go through some painful bureaucratic hoops. Hope you understand and I don't come across as entitled brat... :-)

Thank you!

[SuperQ]

If you could provide more documentation on the feature that would help anyone willing to implement. What are the queries, what is the output format of the queries, what the metrics mean. Links to documentation would also help.

Note, the Prometheus project uses the DCO for attestation of contribution and does not have a CLA. So there are no company agreements to sign.

[renchap]

Starting with pgbouncer 1.21.0, there is a new prepared_statements field in SHOW SERVERS: https://www.pgbouncer.org/usage.html#show-servers

I do not think that SHOW SERVERS stats are returned at the moment (only DATABASES, POOLS and CLIENTS), but it could be a nice addition to expose the other stats from this command when adding support for prepared statements stats.

It would be useful to also expose the max_prepared_statements configuration setting to be able to graph how much of the prepared statements cache is used.

[sboschman]

If I am not mistaken the SHOW CLIENTS and SHOW SERVERS stats are not returned by the exporter atm. They can potentially lead to high cardinality metrics, depending on your configuration, which I assume is the reason they are not included.

Besides, I am not sure how useful the prepared_statements field from SHOW CLIENTS/SHOW SERVERS is as a metric. Both represent the number of elements in a per client/server connection cache.
The upper limit for a server connection is max_prepared_statements, so after an initial ramp up the prepared_statements field will always be equal to max_prepared_statements. Which doesn't tell you much about the impact of enabling prepared statements for your workload with your configuration.
The upper limit for a client connection is determined by the pg driver used by your application. For example the pg jdbc driver uses preparedStatementCacheQueries (default 256) to set this limit.

The more useful internal PgBouncer counters to track imo are the number of prepared statements created by clients, the number of prepared statements PgBouncer had to create on the server, and how many of these prepared statements you re-use. Unfortunately these counters are currently not exposed by any SHOW command, see pgbouncer #1192.

[renchap]

In pgbouncer 1.24:

Expose prepared statement usage counters in SHOW STATS.

This could provide an easier way to expose those metrics.

[sboschman]

In pgbouncer 1.24:

Expose prepared statement usage counters in SHOW STATS.

This could provide an easier way to expose those metrics.

Yes that was the idea, I will try to find some time to test my accompanying exporter change, as pgbouncer 1.24 is released.