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

Enable javascript to load comments.