Oracle 5.0 Reference Manual Download

Page: 718 / 3232

Buffering and Caching

698

collect statistics using one method, but

myisam_stats_method

[476]

is set to the other method

when a table's statistics are collected automatically later, the other method will be used.

• There is no way to tell which method was used to generate statistics for a given

MyISAM

table.

•

myisam_stats_method

[476]

applies only to

MyISAM

tables. Other storage engines have only one

method for collecting table statistics. Usually it is closer to the

nulls_equal

method.

8.6. Buffering and Caching

MySQL uses several strategies that cache information in memory buffers to increase performance.

8.6.1. The

MyISAM

Key Cache

To minimize disk I/O, the

MyISAM

storage engine exploits a strategy that is used by many database

management systems. It employs a cache mechanism to keep the most frequently accessed table
blocks in memory:

• For index blocks, a special structure called the key cache (or key buffer) is maintained. The structure

contains a number of block buffers where the most-used index blocks are placed.

• For data blocks, MySQL uses no special cache. Instead it relies on the native operating system file

system cache.

This section first describes the basic operation of the

MyISAM

key cache. Then it discusses features

that improve key cache performance and that enable you to better control cache operation:

• Access to the key cache no longer is serialized among threads. Multiple sessions can access the

cache concurrently.

• You can set up multiple key caches and assign table indexes to specific caches.

To control the size of the key cache, use the

key_buffer_size

[458]

system variable. If

this variable is set equal to zero, no key cache is used. The key cache also is not used if the

key_buffer_size

[458]

value is too small to allocate the minimal number of block buffers (8).

When the key cache is not operational, index files are accessed using only the native file system
buffering provided by the operating system. (In other words, table index blocks are accessed using the
same strategy as that employed for table data blocks.)

An index block is a contiguous unit of access to the

MyISAM

index files. Usually the size of an index

block is equal to the size of nodes of the index B-tree. (Indexes are represented on disk using a B-tree
data structure. Nodes at the bottom of the tree are leaf nodes. Nodes above the leaf nodes are nonleaf
nodes.)

All block buffers in a key cache structure are the same size. This size can be equal to, greater than, or
less than the size of a table index block. Usually one these two values is a multiple of the other.

When data from any table index block must be accessed, the server first checks whether it is available
in some block buffer of the key cache. If it is, the server accesses data in the key cache rather than
on disk. That is, it reads from the cache or writes into it rather than reading from or writing to disk.
Otherwise, the server chooses a cache block buffer containing a different table index block (or blocks)
and replaces the data there by a copy of required table index block. As soon as the new index block is
in the cache, the index data can be accessed.

If it happens that a block selected for replacement has been modified, the block is considered “dirty.” In
this case, prior to being replaced, its contents are flushed to the table index from which it came.

Usually the server follows an LRU (Least Recently Used) strategy: When choosing a block for
replacement, it selects the least recently used index block. To make this choice easier, the key cache
module maintains all used blocks in a special list (LRU chain) ordered by time of use. When a block

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands