Profiling MySQL using perf and Flame Graphs

A common task in performance analysis is identifying which code sections consume the most CPU time. This often requires profiling stack traces. Enter Flame graphs – a game-changer in how we visualize these profiles. They provide an intuitive visual representation of the application’s most CPU-intensive parts. In this article, we’ll take a look at how to generate these flame graphs using perf.

Process

1) Installing perf

Depending on your Linux distribution, the installation method may differ.

On Ubuntu:

On RHEL and its clones:

2) Capturing Performance Data

For our example, we’ll be capturing performance data from the mysqld process for 60 seconds.

In this command:

  • The -a flag allows us to capture data across all CPU cores.
  • -g facilitates call-graph (or stack trace) recording in both user and kernel spaces.
  • With -F99, we’re instructing perf to sample data 99 times every second.

3) Converting Captured Data

Once data is captured, it’s initially in a binary format. To make sense of this data, we need to convert it into something more readable.

Although it’s now in a human-readable format, visualizing this data in the form of a Flame Graph is ideal to truly glean insights with ease.

4) Installing the FlameGraph Toolkit

We need the FlameGraph toolkit by Brendan Gregg to create our flame graphs.

5) Producing the Flame Graph

We can produce our flame graph with the toolkit installed and our performance data ready.

You can now open flamegraph.svg in your preferred web browser to interactively explore the graph. Here is an example of a flame graph (the image is static since WordPress does not allow SVG files):

Further Reading

For those interested in diving deeper into profiling with perf and Flame Graphs:

In Conclusion

Flame graphs provide an invaluable tool for performance analysis. Providing a visual representation of CPU consumption allows developers and system administrators to quickly identify and troubleshoot performance bottlenecks. Using perf in combination with the FlameGraph toolkit makes the process of generating and interpreting these graphs straightforward. Happy profiling!

Written by

Vinicius Grippa

Writes this blog. Mostly about databases. Boring on purpose.

More about me →

The floor is yours.

0 comments · Moderated · civil & on-topic

First comment appears here once approved. Questions, corrections, and counterpoints welcome — just no self-promotion.

Add a comment

Your email address is never published. * required

Subscribe · Posted when ready

A quiet, technical email about databases.

One post per send, corrections when I’m wrong, nothing else. No social-media cross-posts. No “what we learned.”

Unsubscribe with any reply