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 126.96.36.199, “Integration with the MySQL Performance Schema” for information
InnoDB performance monitoring.
For information about the most important and most recent
InnoDB performance features, see
Section 14.4.7, “
InnoDB Performance and Scalability Enhancements”. Even if you have used
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 188.8.131.52, “Using Operating System Memory Allocators”.
Controlling the types of DML operations for which
InnoDB buffers the changed data, to avoid
frequent small disk writes. See
Section 184.108.40.206, “Controlling 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. See Section 220.127.116.11, “Controlling Adaptive Hash Indexing”. 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 18.104.22.168, “Changes Regarding Thread Concurrency”.
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, “Changes in the Read-Ahead Algorithm”.
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 126.96.36.199, “Multiple Background I/O Threads”.
Controlling how much I/O
in the background. See
Section 188.8.131.52, “Controlling the 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 184.108.40.206, “Controlling the Flushing Rate of Dirty Pages”. 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 220.127.116.11, “Control of 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 18.104.22.168, “Making the Buffer Pool Scan Resistant”.
Adjusting your log files to a size that makes sense for
reliability and crash recovery. See
Section 22.214.171.124, “Improvements to Crash Recovery Performance”. Historically,
people have kept their
InnoDB log files
small to avoid long startup times after a crash. Internal
InnoDB make startup much
faster, so the log file size is not such a performance
factor anymore. If your log files are artificially small,
increasing the size can help performance by reducing the I/O
that occurs as redo log records are recycled.
Configuring the size and number of instances for the
InnoDB buffer pool, especially important
for systems with multi-gigabyte buffer pools. See
Section 126.96.36.199, “Improvements to Performance from Multiple Buffer Pools”.
Increasing the maximum number of concurrent transactions, which dramatically improves scalability for the busiest databases. See Section 188.8.131.52, “Better Scalability with Multiple Rollback Segments”. 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 184.108.40.206, “Better Scalability with Improved 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