Databases Reference
In-Depth Information
innodb_autoinc_lock_mode
This option controls how InnoDB generates autoincrementing primary key values,
which can be a bottleneck in some cases, such as high-concurrency inserts. If you
have many transactions waiting on the autoincrement lock (you can see this in
SHOW ENGINE INNODB STATUS
), you should investigate this setting. We won't repeat
the manual's explanation of the options and their behaviors.
innodb_buffer_pool_instances
This setting divides the buffer pool into multiple segments in MySQL 5.5 and
newer, and is probably one of the most important ways to improve MySQL's scal-
ability on multicore machines with a highly concurrent workload. Multiple buffer
pools partition the workload so that some of the global mutexes are not such hot
contention points.
It is not yet clear what kind of guidelines we should develop for choosing the num-
ber of buffer pool instances. We have run most of our benchmarks with eight
instances, but we probably won't understand some of the subtleties of multiple
buffer pool instances until MySQL 5.5 has been deployed more widely for a longer
time.
We don't mean that to imply that MySQL 5.5 isn't deployed widely in production.
It's just that the most extreme cases of mutex contention we've helped solve have
been for very large, very conservative users, for whom an upgrade can require many
months to plan, validate, and execute. These users are sometimes running a highly
customized version of MySQL, which makes it doubly important for them to be
careful with upgrades. When more of these folks upgrade to MySQL 5.5 and stress
it in their own unique ways, we'll probably learn some interesting things about
multiple buffer pools that we haven't seen yet. Until then, we can say that it appears
to be very beneficial to run with eight buffer pool instances.
It's worth noting that Percona Server takes a different approach to solving InnoDB's
mutex contention issues. Instead of partitioning the buffer pool—an admittedly
tried-and-true approach in many systems like InnoDB—we opted to divide some
of the global mutexes into smaller, more special-purpose mutexes. Our bench-
marks show that the best improvement of all comes from a combination of the two
approaches, which is available in Percona Server version 5.5: multiple buffer pools
and more fine-grained mutexes.
innodb_io_capacity
InnoDB used to be hardcoded to assume that it ran on a single hard disk capable
of 100 I/O operations per second. This was a bad default. Now you can inform
InnoDB how much I/O capacity is available to it. InnoDB sometimes needs this
set quite high (tens of thousands on extremely fast storage such as PCI-E flash
devices) to flush dirty pages in a steady fashion, for reasons that are quite complex
to explain.
