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.