In the Enterprise Team we had the need to track how many connections to the MySQL server in parallel. By "used" I mean running queries and not just idling around.
And to make it more complicated we needed the information not just every few seconds (while true; do echo "SHOW PROCESSLIST" | mysql; sleep 1;done) but when it happens.
Proxy to the rescue we now have a script which tracks the global state of all connections going through the proxy and dump them to stdout when their state changes:
2007-08-28 21:39:55
#connections: 20, #active trx: 3, max(active trx): 4
[792] (merlin@merlin) (COM_QUERY) "SET autocommit=0" (state=started)
[794] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)
[795] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)
- when the query comes in, we add it with its thread-id to the global table
- when the query is done, we track its state (end of transaction)
- if the connection is closed, we remove the entry from the global table
It looked like:
2007-08-28 21:39:45
#connections: 4, #active trx: 1, max(active trx): 4
[795] (merlin@merlin) (COM_QUERY) "SELECT name, value FROM ...." (state=started)
2007-08-28 21:39:45
#connections: 4, #active trx: 1, max(active trx): 4
[795] (merlin@merlin) (state=in_trans)
2007-08-28 21:39:45
#connections: 4, #active trx: 1, max(active trx): 4
[795] (merlin@merlin) (COM_QUERY) "commit" (state=started)
2007-08-28 21:39:45
#connections: 4, #active trx: 0, max(active trx): 4
This way we were able to prove the the connection pool is not just maintained, but really used. It also helps to track race-conditions and gives an idea how and why deadlock happen.
Check out trunk/lib/active-queries.lua to get an idea how it works.
Comments