- 7 InnoDB Performance and Scalability Enhancements
- 7.1 Overview of InnoDB Performance
- 7.2 Faster Locking for Improved Scalability
- 7.3 Using Operating System Memory Allocators
- 7.4 Controlling InnoDB Change Buffering
- 7.5 Controlling Adaptive Hash Indexing
- 7.6 Changes Regarding Thread Concurrency
- 7.7 Changes in the Read-Ahead Algorithm
- 7.8 Multiple Background I/O Threads
- 7.9 Asynchronous I/O on Linux
- 7.10 Group Commit
- 7.11 Controlling the Master Thread I/O Rate
- 7.12 Controlling the Flushing Rate of Dirty Pages
- 7.13 Using the PAUSE Instruction in InnoDB Spin Loops
- 7.14 Control of Spin Lock Polling
- 7.15 Making the Buffer Pool Scan Resistant
- 7.16 Improvements to Crash Recovery Performance
- 7.17 Integration with the MySQL Performance Schema
- 7.18 Improvements to Performance from Multiple Buffer Pools
- 7.19 Better Scalability with Multiple Rollback Segments
- 7.20 Better Scalability with Improved Purge Scheduling
- 7.21 Improved Log Sys Mutex
- 7.22 Separate Flush List Mutex
When InnoDB was developed, the memory allocators supplied with
operating systems and run-time libraries were often lacking in
performance and scalability. At that time, there were no memory
allocator libraries tuned for multi-core CPUs. Therefore, InnoDB
implemented its own memory allocator in the mem
subsystem. This allocator is guarded by a single mutex, which may
become a bottleneck. InnoDB
also implements a wrapper interface around the system allocator
(malloc and free) that is
likewise guarded by a single mutex.
Today, as multi-core systems have become more widely available,
and as operating systems have matured, significant improvements
have been made in the memory allocators provided with operating
systems. New memory allocators perform better and are more
scalable than they were in the past. The leading high-performance
memory allocators include Hoard,
libumem, mtmalloc,
ptmalloc, tbbmalloc, and
TCMalloc. Most workloads, especially those
where memory is frequently allocated and released (such as
multi-table joins), benefit from using a more highly tuned memory
allocator as opposed to the internal, InnoDB-specific memory
allocator.
You can control whether InnoDB uses its own memory allocator or an
allocator of the operating system, by setting the value of the
system configuration parameter
innodb_use_sys_malloc in the
MySQL option file (my.cnf or
my.ini). If set to ON or
1 (the default), InnoDB uses the
malloc and free functions of
the underlying system rather than manage memory pools itself. This
parameter is not dynamic, and takes effect only when the system is
started. To continue to use the InnoDB memory allocator, set
innodb_use_sys_malloc to
0.
When the InnoDB memory allocator is disabled, InnoDB ignores the
value of the parameter
innodb_additional_mem_pool_size.
The InnoDB memory allocator uses an additional memory pool for
satisfying allocation requests without having to fall back to
the system memory allocator. When the InnoDB memory allocator is
disabled, all such allocation requests are fulfilled by the
system memory allocator.
On Unix-like systems that use dynamic linking, replacing the
memory allocator may be as easy as making the environment
variable LD_PRELOAD or
LD_LIBRARY_PATH point to the dynamic library
that implements the allocator. On other systems, some relinking
may be necessary. Please refer to the documentation of the
memory allocator library of your choice.
Since InnoDB cannot track all memory use when the system memory
allocator is used
(innodb_use_sys_malloc is
ON), the section “BUFFER POOL AND
MEMORY” in the output of the SHOW ENGINE INNODB
STATUS command only includes the buffer pool
statistics in the “Total memory allocated”. Any
memory allocated using the mem subsystem or
using ut_malloc is excluded.
For more information about the performance implications of InnoDB memory usage, see Buffering and Caching.
User Comments
Add your own comment.