Databases Reference
In-Depth Information
Disabling read-ahead
Readahead optimizes device access by noticing and predicting read patterns, and
requesting data from the device when it believes that it will be needed in the future.
There are actually two types of read-ahead in InnoDB, and in various circumstances
we've found that performance problems can actually be caused by read-ahead and
the way it works internally. The overhead is greater than the benefit in many cases,
especially on flash storage, but we don't have hard evidence or guidelines as to
exactly how much you can improve performance by disabling read-ahead.
Oracle disabled so-called “random read-ahead” in the InnoDB plugin in MySQL
5.1, then reenabled it in MySQL 5.5 with a parameter to configure it. Percona Server
lets you configure both random and linear read-ahead in older server versions as
well.
Configuring the InnoDB flushing algorithm
The way that InnoDB decides when, how many, and which pages to flush is a highly
complex topic to explore, and we don't have room to discuss it in great detail here.
This is also a subject of active research, and in fact several algorithms are available
in various versions of InnoDB and MySQL.
The standard InnoDB's algorithms don't offer much configurability that is bene-
ficial on flash storage, but if you're using Percona XtraDB (included in Percona
Server and MariaDB), we recommend setting the
innodb_adaptive_checkpoint
op-
tion to
keep_average
, instead of the default value of
estimate
. This will help ensure
more consistent performance and avoid server stalls, because the
estimate
algo-
rithm can stall on flash storage. We developed
keep_average
specifically for flash
storage, because we realized that it's possible to push as much I/O to the device as
we want without causing a bottleneck and an ensuing stall.
In addition, we recommend setting
innodb_flush_neighbor_pages
to
0
on flash
storage. This will prevent InnoDB from trying to find nearby dirty pages to flush
together. The algorithm that performs this operation can cause large spikes of
writes, high latency, and internal contention. It's not necessary or beneficial on
flash storage, because the neighboring pages can be flushed individually without
impacting performance.
Potentially disabling the doublewrite buffer
Instead of moving the doublewrite buffer off the flash device, you can consider
disabling it altogether. Some vendors claim that their devices support atomic 16
KB writes, which makes the doublewrite buffer redundant. You need to ensure that
the entire storage system is configured to support atomic 16 KB writes, which
generally requires
O_DIRECT
and the XFS filesystem.
We don't have conclusive evidence that the claim of atomicity is true, but because
of how flash storage works, we believe that the chance of partial page writes to the
data files is greatly decreased. And the gains are much greater on flash devices than
they are on conventional hard drives. Disabling the doublewrite buffer can improve
