In my last blog post we have seen how we can easily collect general MySQL status information using the sys.diagnostics() procedure. What about a single session/thread ? Is there built-in way to easily trace a target thread/session activity (ex: transactions/ statements/stage/wait events) like using an oracle SQL trace ?
The procedure “ps_trace_thread()” introduced in MySQL 5.7 allow monitoring a specific thread. This procedure is based on Mark Leith work in his blog post A Big Bag of Epic Awesomeness. (I love the title :))
It will captures as much information on the thread activity as possible depending on the actual configuration and how busy the system is.
Ok let’s give it a try :
This procedure will monitor Thread with id 114 for 60 second and poll the event tables every 1 second (So it’s not actually tracing because there is a chance that we miss some events). It generate a “dot” formatted file which will look like :
This file can be used to graph the event hierarchy :
That’s it 😀