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
220.127.116.11.26, "Integration with the MySQL Performance Schema" for information about detailed
InnoDB performance monitoring.
For information about the most important and most recent
features, see Section 18.104.22.168, "
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 the built-in
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
22.214.171.124.12, "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 126.96.36.199.13, "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 188.8.131.52.14, "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
processes, if context switching is a bottleneck. See Section
184.108.40.206.15, "Changes Regarding Thread Concurrency".
Controlling the amount of prefetching that
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 220.127.116.11.16, "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 18.104.22.168.17, "Multiple Background InnoDB I/O Threads".
Controlling how much I/O
InnoDB performs in the
background. See Section
22.214.171.124.20, "Controlling 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 in
Controlling the algorithm that determines when
performs certain types of background writes. See Section
126.96.36.199.21, "Controlling the Flushing Rate of Dirty Pages from the InnoDB Buffer Pool". The
algorithm works for some types of workloads but not others, so might turn off this setting if you
observe periodic drops in performance.
Taking advantage of multicore processors and their cache memory configuration, to minimize delays in context switching. See Section 188.8.131.52.23, "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 184.108.40.206.24, "Making the
Buffer Pool Scan Resistant".
Adjusting your log files to a size that makes sense for reliability and crash
recovery. See Section 220.127.116.11.25,
"Improvements to Crash Recovery Performance". Historically, people have kept their
InnoDB log files small to avoid long startup times after a crash.
Internal improvements in
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
18.104.22.168.27, "Improvements to Performance from Multiple Buffer Pools".
Increasing the maximum number of concurrent transactions, which dramatically improves scalability for the busiest databases. See Section 22.214.171.124.28, "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 126.96.36.199.29, "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
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
innodb_concurrency_tickets option, typically to 5000 or so, This
combination of options sets a cap on the number of threads that
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