Databases Reference
In-Depth Information
Increasing InnoDB's I/O capacity
Flash supports much higher concurrency than conventional hard drives, so you
can increase the number of read and write I/O threads to as high as 10 or 15 with
good results. You can also increase the
innodb_io_capacity
option to between
2000
and
20000
, depending on the IOPS your device can actually perform. This is
especially necessary with the official InnoDB from Oracle, which has more internal
algorithms that depend on this setting.
Making the InnoDB log files larger
Even with the improved recovery algorithms in recent versions of InnoDB, you
don't want your log files to be too large on hard drives, because the random I/O
required for crash recovery is slow and can cause recovery to take a long time. Flash
storage makes this much faster, so you can have larger InnoDB log files, which can
help improve and stabilize performance. This is especially necessary with the offi-
cial InnoDB from Oracle, which has trouble maintaining a consistent dirty page
flush rate unless the log files are fairly large—4 GB or larger seems to be a good
range on typical servers at the time of writing. Percona Server and MySQL 5.6
support log files larger than 4 GB.
Moving some files from flash to RAID
In addition to making the InnoDB log files larger, it can be a good idea to store the
log files separately from the data files, placing them on a RAID controller with a
battery-backed write cache instead of on the solid-state device. There are several
reasons for this. One is that the type of I/O the log files receive isn't much faster
on flash devices than it is on such a RAID setup. InnoDB writes the log files se-
quentially in 512-byte units and never reads them except during crash recovery,
when it reads them sequentially. It's kind of wasteful to use your flash storage for
this. It's also a good idea to move these small writes to the RAID volume because
very small writes increase the write amplification factor on flash devices, which can
be a problem for some devices' longevity. A mixture of large and small writes can
also cause increased latency for some devices.
It's also sometimes beneficial to move your binary log files to the RAID volume,
for similar reasons; and you might consider moving your
ibdata1
file, too. The
ibdata1
file contains the doublewrite buffer and the insert buffer. The doublewrite
buffer, in particular, gets a lot of repeated writes. In Percona Server, you can remove
the doublewrite buffer from the
ibdata1
file and store it in a separate file, which
you can place on the RAID volume.
There's another option, too: you can take advantage of Percona Server's ability to
write the transaction logs in 4-kilobyte blocks instead of 512-byte blocks. This can
be more efficient for flash storage as well as for the server itself.
All of the above advice is rather hardware-specific, and your mileage may vary, so
be sure you understand the factors involved—and test appropriately—before you
make such a large change to your storage layout.
