InnoDB has a strategic approach when it comes to updating a data page.

Understanding InnoDB’s Change Buffer

InnoDB has a strategic approach when it comes to updating a data page:

  1. If the data page is already in memory, it updates directly in the memory.
  2. If the data page isn’t in memory, and the update doesn’t violate data consistency, then InnoDB caches this update in the change buffer. This means it doesn’t have to fetch the data page from the disk right away. When the data page is accessed later, it’s loaded into memory, and the operations from the change buffer associated with this page are executed. This ensures the correct logic of the data.

Although it’s named change buffer, it’s a persistent kind of data, meaning it can be written to disk. It should be noted that any update operation must first verify whether it violates the unique constraint. This check requires loading the data page into memory. If the data is already loaded, updating directly in memory is faster, so there’s no need to use the change buffer. Hence, only non-unique indexes can utilize the change buffer.

The size of the change buffer can be dynamically adjusted using the innodb_change_buffer_max_size parameter. Random I/O access, especially when data is read from the disk into memory, is among the most expensive operations in databases. The change buffer can significantly improve update performance by reducing these random disk accesses.

When to Use the Change Buffer

The primary purpose of the change buffer is to cache record changes. The more changes to a page it caches before merging, the greater the benefit. This makes the change buffer especially useful for scenarios where writes are frequent, but reads are infrequent.

However, if an application’s update pattern involves querying immediately after a write, even if the update is initially logged in the change buffer, the subsequent immediate access to this data page would trigger a merge. This doesn’t reduce the number of random I/O accesses and adds the overhead of maintaining the change buffer.

Optimizer Logic in Databases

The goal of an optimizer when selecting an index is to find the most efficient execution plan and minimize the execution cost. One of the factors that influence this cost is the number of rows scanned. Fewer rows scanned means fewer disk accesses and less CPU consumption.

MySQL can’t precisely determine the number of rows that satisfy a condition before executing a statement. It relies on statistical data, specifically the “cardinality” of an index, which refers to the number of distinct values in it. A higher cardinality indicates a better distinguishing capacity for the index.

InnoDB will typically sample a set of data pages to estimate the distinct values, get an average, and then multiply by the total number of pages in the index to determine the index’s cardinality. As tables get updated, the statistical data also changes. When the number of modified rows exceeds 1/M of the total, it triggers a re-evaluation of the index statistics.

In MySQL, there are two ways to store index statistics, which can be determined by the innodb_stats_persistent setting:

  • When set to on, the statistics are persisted. By default, N is 20, and M is 10.
  • When set to off, the statistics are only in memory. Here, N is 8, and M is 16.

Sometimes, MySQL might choose the wrong index because it fails to accurately estimate the rows to be scanned.

Index Selection Issues and Solutions

  • Force Index:

    • Use the force index directive to explicitly choose a particular index.

  • SQL Statement Modification:

    • Adjust your SQL statement to guide MySQL into using the desired index.

  • Manage Indices:

    • Create a new, more appropriate index.
    • Delete any misused index.

  • Prefix Indexing on Strings:

    • Implement prefix indexing for string columns to save space and query costs, while sacrificing the benefits of covering indexes.

Buffer Management and Flushing Strategies in InnoDB

Dirty Pages

  • Dirty pages arise when the contents of memory pages differ from disk pages.
  • Writing the contents of these memory pages to disk makes them “clean”.
  • The process of writing these pages to disk is termed flushing.

Triggers for Flushing

  1. Redo Log is Full:

    • System halts all update operations and advances the checkpoint to free up space in the redo log.

  2. System Memory is Insufficient:

    • New memory pages are needed. If evicting a dirty page, it must be flushed to disk.

  3. MySQL System is Idle.

  4. MySQL Shutdown:

    • All dirty pages are flushed to ensure a quick startup next time.

Buffer Pool

  • InnoDB manages memory using a buffer pool.
  • Pages within the buffer pool can be:
    1. Unused.
    2. Used and clean.
    3. Used and dirty.

InnoDB’s Flushing Strategy

  • Inform InnoDB about the IO capacity of the host to determine how fast dirty pages can be flushed.
  • innodb_io_capacity informs InnoDB of the disk’s capabilities. This value is often set to the disk’s IOPS.
  • Flushing speed depends on two factors:
    1. The proportion of dirty pages.
    2. Redo log write speed.

Optimizations

  • innodb_flush_neighbors: Determines whether to flush “neighboring” dirty pages when flushing a particular dirty page.
    • 1: Enables the “guilt by association” behavior.
    • 0: Each page is flushed independently.
    • This optimization was crucial in the era of mechanical hard drives to minimize random IO. However, for high IOPS devices like SSDs, it’s often better to set this value to 0 to reduce SQL statement response times.