Databases Reference
In-Depth Information
MySQL's overall performance on flash storage by a factor of 50% or so, so although
we don't know that it's 100% safe, it's something you can consider doing.
Restricting the insert buffer size
The insert buffer (or change buffer, in newer versions of InnoDB) is designed to
reduce random I/O to nonunique secondary index pages that aren't in memory
when rows are updated. On hard drives, it can make a huge difference in reducing
random I/O. For some workloads, the difference may reach nearly two orders of
magnitude when the working set is much larger than memory. Letting the insert
buffer grow large is very helpful in such cases.
However, this isn't as necessary on flash storage. Random I/O is much faster on
flash devices, so even if you disable the insert buffer completely, it doesn't hurt as
badly. You probably don't want to disable it, though. It's better to leave it enabled,
because the I/O is only one part of the cost of updating index pages that aren't in
memory. The main thing to configure on flash devices is the maximum permitted
size. You can restrict it to a relatively small size, instead of letting it grow huge; this
can avoid consuming a lot of space on your device and help prevent the
ibdata1
file from growing very large. At the time of writing you can't configure the maxi-
mum size in standard InnoDB, but you can in Percona XtraDB, which is included
in Percona Server and MariaDB. MySQL 5.6 will add a similar option, too.
In addition to the aforementioned configuration suggestions, some other optimizations
have been proposed or discussed for flash storage. However, these are not all as clear-
cut, so we will mention them but leave you to research their benefit for your specific
case. The first is the InnoDB page size. We've found mixed results, so we don't have a
definite recommendation yet. The good news is that the page size is configurable
without recompiling the server in Percona Server, and this will also be possible in
MySQL 5.6. Previous versions of MySQL required you to recompile the server to use
a different page size, so the general public has by far the most experience running with
standard 16 KB pages. When the page size becomes easier for more people to experi-
ment with, we expect a lot more testing with nonstandard sizes, and it's likely that we'll
learn a great deal from this.
Another proposed optimization is alternative algorithms for InnoDB's page checksums.
When the storage system responds very quickly, the checksum computation can ac-
tually start to take a significant amount of time relative to the I/O operation, and for
some people this has become the bottleneck instead of the I/O being the bottleneck.
Our benchmarks haven't shown repeatable results that are applicable to a broad spec-
trum of use cases, so your mileage may vary. Percona XtraDB permits you to change
the checksum algorithm, and MySQL 5.6 will also have this capability.
You might have noticed that we've referred a lot to features and optimizations that
aren't available yet in standard InnoDB. We hope and believe that many of the
improvements we've built into Percona Server and XtraDB will eventually become
available to a wider audience. In the meantime, if you're using the official MySQL
