While troubleshooting MySQL performance problems it is important to find out where CPU time is spent inside mysqld process. The process of investigation should have as small influence as possible on the server we try to troubleshoot. Ideally, the source code should be instrumented for that.
Performance_schema introduced in MySQL 5.5 is supposed to provide detailed enough instrumentation. But it comes with a cost, require careful sizing of performance counters, and the process of instrumenting the code is not yet complete even for MySQL itself, to say nothing about 3rd party storage engines, plugins and libraries like Galera.
This is when profilers come handy. Poor Man's Profiler (PMP, aka pt-pmp), perf and oprofile on Linux can be easily used while studying MySQL performance problems. Basic usage steps are presented and several typical use cases are discussed.
In case of any performance problems with MySQL is important to find out where CPU time is spent inside mysqld process. Is it waiting on disk I/O, some lock or internal mutex, or maybe it performs complex computation that should be optimized? You can not figure out until you know how much time each important step takes, and how often it occurs. To investigate that, one may use different tools and approaches, from tracing the code to using simple OS level utilities that show disk and memory usage in total or per device, to more advanced programs that collect all kinds of information about specific program running (these are often called profilers). The process of investigation should have as small influence as possible on the server we try to troubleshoot.
Ideally, the source code should be instrumented for that and provide a "domain-specific" performance metrics. It may be not enough to know how much time specific function runs if its code is huge and complex. Performance_schema introduced in MySQL 5.5 was supposed to provide detailed enough instrumentation. But it comes with a cost (performance penalty, that can be notable), require careful sizing of performance counters, force investigator to use advanced SQL to get the information needed. What's even worse, the process of instrumenting the code is not yet complete even for MySQL itself, to say nothing about 3rd party storage engines, plugins and libraries like Galera.
This is when profilers come handy. During this talk we'll discuss the use of Poor Man's Profiler (PMP, aka pt-pmp), perf and oprofile on Linux to study MySQL performance problems. Basic usage steps are presented and several typical use cases are discussed. These including several famous bugs (like https://bugs.mysql.com/bug.php?id=68079), profiling for Galera, MyRocks storage engine etc. Quick review of the most interesting MySQL bug reports where profilers played a key role in the investigation is also presented.
