MySQL tracing session/thread activity

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.

Capture 01

Ok let’s give it a try :

Capture 02

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 :

Capture 03

This file can be used to graph the event hierarchy :

Capture 05

 

stack-2019-03-21-15_16_20

 

That’s it 😀

5 thoughts on “MySQL tracing session/thread activity

  1. 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 ^_~

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s