Different settings work best for servers with light, predictable loads, versus servers that are running near full capacity all the time, or that experience spikes of high activity.
InnoDB storage engine performs
many of its optimizations automatically, many performance-tuning
tasks involve monitoring to ensure that the database is
performing well, and changing configuration options when
performance drops. See
Section 14.16, “InnoDB Integration with MySQL Performance Schema” for information
InnoDB performance monitoring.
For a list of the most important and most recent
InnoDB performance features, see
Section 14.1.1, “Features of the InnoDB Storage Engine”. Even if you have
InnoDB tables in prior versions, these
features might be new to you, because they are from the
“InnoDB Plugin”. The Plugin co-existed alongside
InnoDB in MySQL 5.1, and becomes
the default storage engine in MySQL 5.5 and higher.
The main configuration steps you can perform include:
InnoDB to use high-performance
memory allocators on systems that include them. See
Section 14.6.3, “Configuring the Memory Allocator for InnoDB”.
Controlling the types of DML operations for which
InnoDB buffers the changed data, to avoid
frequent small disk writes. See
Section 14.6.4, “Configuring InnoDB Change Buffering”.
Because the default is to buffer all types of DML
operations, only change this setting if you need to reduce
the amount of buffering.
Turning the adaptive hash indexing feature on and off using
option. See Section 18.104.22.168, “Adaptive Hash Indexes” for more
information. You might change this setting during periods of
unusual activity, then restore it to its original setting.
Setting a limit on the number of concurrent threads that
InnoDB processes, if context switching is
a bottleneck. See
Section 14.6.5, “Configuring Thread Concurrency for InnoDB”.
Controlling the amount of prefetching that
InnoDB does with its read-ahead
operations. When the system has unused I/O capacity, more
read-ahead can improve the performance of queries. Too much
read-ahead can cause periodic drops in performance on a
heavily loaded system. See
Section 22.214.171.124, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”.
Increasing the number of background threads for read or write operations, if you have a high-end I/O subsystem that is not fully utilized by the default values. See Section 14.6.6, “Configuring the Number of Background InnoDB I/O Threads”.
Controlling how much I/O
in the background. See
Section 14.6.7, “Configuring the InnoDB Master Thread I/O Rate”. The
amount of background I/O is higher than in MySQL 5.1, so you
might scale back this setting if you observe periodic drops
Controlling the algorithm that determines when
InnoDB performs certain types of
background writes. See
Section 126.96.36.199, “Configuring the Rate of InnoDB Buffer Pool Flushing”. The
algorithm works for some types of workloads but not others,
so might turn off this setting if you observe periodic drops
Taking advantage of multicore processors and their cache memory configuration, to minimize delays in context switching. See Section 14.6.8, “Configuring Spin Lock Polling”.
Preventing one-time operations such as table scans from
interfering with the frequently accessed data stored in the
InnoDB buffer cache. See
Section 188.8.131.52, “Making the Buffer Pool Scan Resistant”.
Adjusting log files to a size that makes sense for
reliability and crash recovery.
log files have often been kept small to avoid long startup
times after a crash. Optimizations introduced in MySQL 5.5.4
speed up certain steps of the crash
In particular, scanning the
redo log and applying
the redo log are faster due to improved algorithms for
memory management. If you have kept your log files
artificially small to avoid long startup times, you can now
consider increasing log file size to reduce the I/O that
occurs due recycling of redo log records.
Configuring the size and number of instances for the
InnoDB buffer pool, especially important
for systems with multi-gigabyte buffer pools. See
Section 184.108.40.206, “Using Multiple Buffer Pool Instances”.
Increasing the maximum number of concurrent transactions, which dramatically improves scalability for the busiest databases. See Section 14.5.3, “InnoDB Multi-Versioning”. Although this feature does not require any action during day-to-day operation, you must perform a slow shutdown during or after upgrading the database to MySQL 5.5 to enable the higher limit.
Moving purge operations (a type of garbage collection) into a background thread. See Section 14.6.9, “Configuring InnoDB Purge Scheduling”. To effectively measure the results of this setting, tune the other I/O-related and thread-related configuration settings first.
Reducing the amount of switching that
InnoDB does between concurrent threads,
so that SQL operations on a busy server do not queue up and
form a “traffic jam”. Set a value for the
option, up to approximately 32 for a high-powered modern
system. Increase the value for the
option, typically to 5000 or so. This combination of options
sets a cap on the number of threads that
InnoDB processes at any one time, and
allows each thread to do substantial work before being
swapped out, so that the number of waiting threads stays low
and operations can complete without excessive context