Allow inspecting connections and statements

[xaprb]

As far as I can tell neither database/sql nor this driver let you do things like see how many connections are open, inspect them, or look at what statements have been prepared but not closed. It is often very helpful to do that in order to debug problems in the application. For example, I'm working on such a thing right now -- I think I'm leaking prepared statements but it's hard to tell without adding a lot of logging around every db.Prepare() and stmt.Close() call.

Is something like this reasonable to consider adding into the driver so that you could get those kinds of things and emit metrics to a monitoring system, write to a log, or something else as a result?

[julienschmidt]

The driver itself does not count the number of its own connections nor does it keep references to its connections or statements.
This logic is part of the database/sql package. I have some unpublished work, where I separated the the pooling logic with the goals to a) make the pooling more memory efficient and b) make the logic exchangeable in a future version of Go (no public interface implemented yet). You could then just just wrap the pooling logic to emit the metrics you want.

But it could definitely be done with the current database/sql package, the data is there, but not available via an exported API.
You could either just modify the database/sql a bit to get that data (just ask, if you need help) or do it via reflection and unsafe stuff - @arnehormann is an expert for that, see https://github.com/arnehormann/sqlinternals 😄

[arnehormann]

For the reflection way of doing things, also take a look at https://github.com/arnehormann/mirror - it enables safe usage of unsafe and is imported by sqlinternals. Just send me an email If you need pointers or any other kind of help with the code (at gmail.com).

[julienschmidt]

@xaprb Is there still demand for this?
If yes, what specific metrics should the driver emit?

[xaprb]

The best way to do this IMO is to offer cumulative counters since the beginning of time. These counters could be subtracted from each other in some cases to offer snapshots of the state right now:

• Number of connections opened
• Number of connections closed
• Number of statements begun
• Number of statements sent to the server
• Number of statements that have received a response from the server (start of response)
• Number of statements completed

You could e.g. subtract statements_begun - statements_sent to find out how many statements are blocked in the driver (awaiting a connection from database/sql?) or similar.

There are probably other ideas but that's my ideas at the moment.

[julienschmidt]

Unless we lock the complete driver, we can't read all those values atomically. So if you read the number of connections opened first, then the number of connections closed, the difference might be negative.

[xaprb]

I'm not sure I agree the complete driver has to be locked. Make a struct with fields for these stats and guard it with a mutex. But you know better than I.

[thwarted]

Can the expvar package be leveraged for this? In my projects that have http interfaces, that's where I'd want to expose these values.

[methane]

I feel non of them are MySQL specific.
Why don't you request add them to DB.Stats` ?

[julienschmidt]

DB.Stats is already the first step in this direction. This kind information should be provided by the database interface, not by the underlying driver. And as @methane said, this is not specific to MySQL.

On a driver level we could only collect global stats, since we don't know to which pool a connection or statement belongs.

Please request it for addition in database/sql instead.