Friday, November 27, 2015

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.

Monday, November 23, 2015

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.

Wednesday, November 18, 2015

Amazon EBS volume lazy loading: how it influences MySQL recovery performance

Amazon EBS volumes come with a very cool feature called "lazy loading". In a nutshell: if a volume is created from an existing snapshot, it can become available almost immediately without waiting for all data to be restored. This allows for extremely fast provisioning of large data sets as long as you don't explicitly require the entire data set to be present before you start using it.

When an EBS volume is restored from snapshot, its blocks are fetched from Amazon S3. It happens either lazily in the background or explicitly on demand (think of a pagefault-like mechanism) and of course, fetching pieces of data from Amazon S3 is going to be one-two orders of magnitude slower than reading blocks directly from a volume.

In this short article, I will try to give you an idea of how this may impact the crash recovery time of your MySQL databases.

Tuesday, November 17, 2015

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.

Saturday, November 14, 2015

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.

Sunday, November 8, 2015

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.