Marko Mäkelä

One of the innovations in InnoDB was the change buffer (originally, insert buffer), which aims to convert random I/O to more sequential I/O, by buffering certain changes to secondary index B-tree leaf pages.

Due to its design and nature, any bugs related to the change buffer are extremely hard to reproduce. The change buffer is also becoming irrelevant, as the difference between random and sequential I/O is disappearing along with rotational storage (HDDs).

Thanks to the rr debugger and some improvements to InnoDB data structures, we have been able to reproduce and fix several tricky bugs related to the InnoDB change buffer.

We shortly explain what the InnoDB change buffer is and how it is supposed to work.

We describe the challenges of testing the change buffer and some bug scenarios at a high level.

Focusing on one example, we show how instead of debugging a core dump and guessing what might have led to the problem, we can use rr replay to analyze a deterministic execution trace from rr record leading to the failure. We can set breakpoints and data watchpoints and examine the state of the traced process at any point of time of execution. This even works across process boundaries, for crash recovery bugs.

The persistent circular buffer (the ib_logfile0) is the fundament of the persistent InnoDB buffer pool.

Over the years, the log file format has been changed in MariaDB Server to improve the performance. A well-designed file format imposes minimal write amplification and is easy to parse.

The 512-byte block size of the InnoDB log was a perfect match for the industry standard that was defined by the venerable Seagate ST-225. Alas, the industry moved on, and now block sizes range from 64 bytes (the size of a memory cache line) to 4096 bytes. Therefore, a format that works efficiently with any block size is needed.

We present a flexible format where each mini-transaction (comprising log records) is a block on its own, with a checksum that is calculated in a local buffer, reducing contention on the mutex that protects the global log buffer. The old 12-byte block header is shrunk to a 1-bit sequence number, for detecting the end of the circular log. The additional overhead is 4 bytes per mini-transaction for a CRC-32C checksum.

No-op records may be written to pad the log buffer to match the physical block size. Encryption will only cover data. The length of each record is explicitly stored in clear, which allows consistent hot backups without having any encryption keys. For encrypted log, 8-byte part of initialization vector will be written after each mini-transaction.

In any block-oriented, update-in-place database with log-based recovery, two kinds of page flushing are needed. Eviction flushing is necessary when all least recently used blocks are dirty (modifications exist in the buffer pool). Checkpoint flushing (writing out the oldest modified page first) allows the log checkpoint to advance, shortening the potential recovery time by logically discarding the start of the log. Any write of a persistent page must be preceded by a corresponding log write and optional doublewrite, to guarantee that crash recovery works.

We will cover some improvements in this area in MariaDB Server's version of InnoDB storage engine. We will also discuss how a database could be configured to minimize write amplification.

MariaDB 10.5 simplified page flushing and fixed bottlenecks that had originally been worked around by introducing multiple buffer pool instances and multi-threaded flushing. It turns out that a single buffer pool and page flushing thread (assisted by a log flushing thread) can still saturate most contemporary I/O subsystems. Data structures were simplified, some synchronous writes replaced with asynchronous ones, and mutex contention reduced through more use of atomic memory operations.

ALTER TABLE in MySQL used to copy the table contents row by row. We can do much better; in the best case, allow instantaneous schema changes, even for nontrivial changes, such as ADD COLUMN…AFTER and DROP COLUMN. This talk describes how ALTER TABLE has been improved over the years for the InnoDB storage engine in MySQL 5.1, 5.5, 5.6, 5.7, and MariaDB Server 10.2, 10.3, 10.4, 10.5, mostly by the presenter.

The talk enumerates different classes of ALTER TABLE operations:

1. operations not involving other than metadata
2. operations that can be performed instantly by introducing a backward-compatible data file format change and 'faking' the operation (ADD or DROP COLUMN)
3. operations that can avoid rebuilding a table
4. operations that must rebuild the table
5. variations of the previous 2 classes that allow concurrent modifications to the table

We also show how ALTER TABLE can be executed concurrently on multiple nodes in statement-based replication. Finally, we cover some theoretical limits of what kind of ALTER TABLE operations can be supported without rebuilding the table, by introducing an optional validation step and on-demand conversion of records in previous schema versions of the table.