The
InnoDB
Buffer Pool
702
The best performance for I/O operations is achieved when the size of read buffers is equal to the size
of the native operating system I/O buffers. But setting the size of key nodes equal to the size of the I/
O buffer does not always ensure the best overall performance. When reading the big leaf nodes, the
server pulls in a lot of unnecessary data, effectively preventing reading other leaf nodes.
To control the size of blocks in the
.MYI
index file of
MyISAM
tables, use the
--myisam-block-
size
[415]
option at server startup.
8.6.1.6. Restructuring a Key Cache
A key cache can be restructured at any time by updating its parameter values. For example:
mysql>
SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;
If you assign to either the
key_buffer_size
[458]
or
key_cache_block_size
[460]
key cache
component a value that differs from the component's current value, the server destroys the cache's old
structure and creates a new one based on the new values. If the cache contains any dirty blocks, the
server saves them to disk before destroying and re-creating the cache. Restructuring does not occur if
you change other key cache parameters.
When restructuring a key cache, the server first flushes the contents of any dirty buffers to disk. After
that, the cache contents become unavailable. However, restructuring does not block queries that need
to use indexes assigned to the cache. Instead, the server directly accesses the table indexes using
native file system caching. File system caching is not as efficient as using a key cache, so although
queries execute, a slowdown can be anticipated. After the cache has been restructured, it becomes
available again for caching indexes assigned to it, and the use of file system caching for the indexes
ceases.
8.6.2. The
InnoDB
Buffer Pool
InnoDB
maintains a buffer pool for caching data and indexes in memory.
InnoDB
manages the pool
as a list, using a least recently used (LRU) algorithm incorporating a midpoint insertion strategy. When
room is needed to add a new block to the pool,
InnoDB
evicts the least recently used block and adds
the new block to the middle of the list. The midpoint insertion strategy in effect causes the list to be
treated as two sublists:
• At the head, a sublist of “new” (or “young”) blocks that have been recently used.
• At the tail, a sublist of “old” blocks that are less recently used.
As a result of the algorithm, the new sublist contains blocks that are heavily used by queries. The old
sublist contains less-used blocks, and candidates for eviction are taken from this sublist.
The LRU algorithm operates as follows by default:
• 3/8 of the buffer pool is devoted to the old sublist.
• The midpoint of the list is the boundary where the tail of the new sublist meets the head of the old
sublist.
• When
InnoDB
reads a block into the buffer pool, it initially inserts it at the midpoint (the head of the
old sublist). A block can be read in as a result of two types of read requests: Because it is required
(for example, to satisfy query execution), or as part of read-ahead performed in anticipation that it will
be required.
• The first access to a block in the old sublist makes it “young”, causing it to move to the head of the
buffer pool (the head of the new sublist). If the block was read in because it was required, the first
access occurs immediately and the block is made young. If the block was read in due to read-ahead,
the first access does not occur immediately (and might not occur at all before the block is evicted).
• As long as no accesses occur for a block in the pool, it “ages” by moving toward the tail of the list.
Blocks in both the new and old sublists age as other blocks are made new. Blocks in the old sublist
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...