Understanding and profiling MySQL execution with Callgrind, Pstack and Perf

You may sometimes hear complaints about MySQL not providing good enough tools for profiling and execution analysis. A few years ago I would have agreed with such opinions, thankfully MySQL developers have made huge efforts to improve the situation in recent major versions. MySQL DBAs now have some great native diagnostic tools at their disposal... which is totally not what this article is about :)

Native MySQL tooling (whatever it might be) is just the tip of the iceberg and if you want to be a better troubleshooter, SysAdmins are the first people you should talk to. Their toolboxes are full of awesomeness and the tools they use have one significant advantage over MySQL tools: they can analyze server execution holistically, regardless of the MySQL version you may be using.

In this article, we will have a look at three OS-level tools: pstack, perf and callgrind (Valgrind tool).

Profiling MySQL memory usage with Valgrind / Massif

High memory usage scenarios may sometimes be trivial to troubleshoot e.g. when memory parameters are explicitly set too high. Investigations into such issues may also prove to be very difficult when memory pressure is a result of specific workload patterns or better yet, engine bugs.

Advanced memory troubleshooting in MySQL was never easy but thanks to performance_schema memory instrumentation in MySQL 5.7, we finally have some tools to work with. There are still situations when performance schema will not be sufficient:

1. On servers running MyQL <5.7 (well... vast majority),
2. When the component you're interested in is not instrumented,
3. When you don't fancy reading cryptic names of performance schema instruments. While I do love performance schema, this is no joke: OS-level heap profiles are much easier to read than data in performance schema (and they point you directly to the relevant locations in the code).

In this short article, I'll show you how easy it is to use the well known Valgrind / Massif tools to profile MySQL memory usage, no matter which MySQL version you're running.

Debugging MySQL execution with server tracing

In the life of a professional MySQL DBA there comes a moment when issues are no longer trivial enough to be diagnosed using simple repros and built-in diagnostic commands. While trying to understand complex problems, you may be forced (challenged?) to look for answers at the lowest level, by analyzing the server's source code.

If you're a seasoned database engineer who killed several keyboards reporting MySQL bugs, you can stop reading now. If you're just about to begin your journey into the source, you could probably use all the help you can get so keep reading. In this short article I'll describe the MySQL server tracing feature I've been using as an aid during MySQL investigations.

Investigating memory usage with Performance Schema in MySQL 5.7

Performance Schema has been with us for a while now. Over the years, it has gone a long way from being a curiosity disabled by default to becoming a sophisticated diagnostic tool you may want to enable permanently in your production database.

MySQL 5.7 introduced some exciting Performance Schema features and the first one I'm going to look at is the instrumentation for server memory usage.

Memory consumption in heavily partitioned databases: MySQL 5.7 vs 5.6

MySQL introduces several improvements related to partitioning. Most importantly, work has been done to move InnoDB towards native partitioning support, which not only "paves the way for better overall partitioning" (source) in the future but already comes with measurable performance benefits.

This article focuses on memory usage in heavily partitioned InnoDB databases.

MySQL 5.6 and 5.7 crash recovery performance with large number of tables

It goes without saying that crash recovery of busy MySQL servers (and many other RDBMS for that matter) is not an extremely quick process. In MySQL context, one of the worst case scenarios is when the server is used for multi-tenant application hosting i.e. when the MySQL instance contains hundreds or thousands of schemas and (tens/hundreds of) thousands of tablespaces. In such scenario, the server may spend a considerable amount of time in the tablespace discovery phase, during which MySQL builds a mapping between tablespace IDs and names of actual tablespace files on disk.

Changes in InnoDB OPTIMIZE TABLE behavior in MySQL 5.7

The OPTIMIZE TABLE statement provides allows MySQL DBAs to reorganize physical table storage in order to achieve two main goals:

1. Improve IO efficiency for reads/writes against the table,
2. Reclaim unused storage space.

For InnoDB tables, OPTIMIZE is implemented via ALTER TABLE operation. This is true in all MySQL versions, however MySQL 5.7.4 introduced a change that may catch many users off guard and is therefore worth highlighting.

SHOW CREATE TABLE and foreign key constraints with innodb_read_only

The "innodb_read_only" parameter is perhaps not among the most commonly used MySQL parameters. It may however influence server behaviour in a way that you may not expect. This post is a record of an investigation that revealed one of MySQL quirks caused by said parameter.