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 😀
Hatem Mahmoud's blog 
Brilliant article.
The trace log doesn’t have SQL statement, but has more information than 10046 trace. eg, when Oracle acquired a lock (eg, library cache lock), it will not log it into 10046 trace if there is no wait. So I suspect this trace has more impact than 10046 trace does.
Looking forward to see more MySQL blog article written by you ^_~
Thanks Feng 🙂
[…] already have seen how to gather overall MySQL server diagnostics information and how to trace a specific thread activity. Let’s go to more granular level this time ! What about tracing a specific statement. Has […]
[…] already have seen how to gather overall MySQL server diagnostics information and how to trace a specific thread activity. Let’s go to more granular level this time ! What about tracing a specific statement. Has […]
[…] MySQL tracing session/thread activity […]