This is a proposal to add 2 new fields to the DBStats struct:

// Counters
// ...
MaxRetries              int64 // The total number of operation retries
MaxRepreparedStatements int64 // The total number of times a prepared statement has been reprepared due to not existing on the chosen connection

Implementation would be with atomic.AddInt64 (similar to this PR for another new DBStat). I'm happy to handle implementation here.

MaxRepreparedStatements

An int64 field that exposes the cumulative number of times a prepared statement was executed but the connection used did not already have the statement prepared on it so it had to re-prepare it. This happens here in database/sql.

Why?

Prepared statements offer a performance benefit since the server can use more efficient protocols and/or skip re-analyzing and planning the query (PostgreSQL docs, MySQL docs). This benefit can be lost though if the statement needs to be reprepared before being executed (as it’s another network roundtrip and the server will potentially need to replan and analyze the query).

This metric would be useful to monitor to see if it correlates with increased latencies. It’s also useful in finding the optimal values for settings like MaxConnLifetime. If MaxRepreparedStatements is constantly increasing it might mean you need to increase the lifespan of your connections so statements don’t need to be reprepared on new connections.

What about using server metrics for this?

Some SQL databases expose a counter on how often PREPARE is called (e.g. Com_stmt_prepare in MySQL). While this is useful, it does not cover the same use case as a driver metric. Driver metrics can be more specific (since each service might have their own driver instance you could attach that as a metric tag). Com_stmt_prepare can also increase in cases where MaxRepreparedStatements doesn’t: if a driver is used in only thread, prepares a statement, executes it 5 times synchronously, and then closes the statement then Com_stmt_prepare will increase on the database but MaxRepreparedStatements shouldn’t increase in the driver.

MaxRetries

An int64 field that exposes the cumulative number of times an operation has been retried. For example, when calling ExecContext if the initial attempt fails with ErrBadConn then database/sql will retry once with a cached or new connection and then once more with a new connection. Each of these retries would increment the field by 1.

Why?

This metric is useful for determining the source of tail latencies. Retries can lead to having to establish an entirely new connection which has overhead. This would be a useful metric to monitor to see if it’s correlating with increased latencies. It could indicate networking issues or other problems connecting and sending data to the DB.