Database proof-of-concept & migration best practices

This post tries to answer some of the most popular questions I receive from customers, regarding the best practices for database proof-of-concept and migration projects.

Quick look: Performance impact of General and Slow Query Logging

General query logs and slow query logs remain one of the most popular sources of auditing and diagnostic information in MySQL databases. Customers often ask about the cost of general and slow query logging so I went googling for existing research I could point them to. The data I found was not quite what I hoped for, which is why I decided to do some more testing.

MySQL 8.0 Information Schema performance improvements

FRM-less, transactional data dictionary is arguably the most significant feature change announced MySQL 8.0 development release. The server still has two separate dictionaries (MySQL, InnoDB) but the infamous FRM files are finally being replaced with transactional, InnoDB-based storage.

While this is interesting for various reasons, this particular post will focus on the impact this change has on data dictionary performance.

Investigating InnoDB FULLTEXT cache performance issues in MySQL <5.6.30

I'm not a huge fan of the InnoDB FULLTEXT feature and I admit I wasn't too keen to play with it in the past. Apparently, the feeling is mutual and so FULLTEXT issues haven't popped up in too many projects I worked on... until last week.

This post describes the troubleshooting process of a FULLTEXT cache performance issue. Quite inconspicuous at the beginning, it proved to be a lot of fun in the end.

Ready, Set, GDB!

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.

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.