MySQL. Configuring the MyISAM storage engine.

12 June, 2007 (12:08) | MySQL | By: clive

The MyISAM storage engine (see article on MyISAM storage engine) is one of the available storage engines on the MySQL server. It is the default storage engine and is always available however, you can switch between the different storage engines. Some storage engines may not be available on a particular server. It is therefore best to first check whether or not your preferred engine is available before designing your database. Simply use the SHOW ENGINES statement in the query browser. This will list the available storage engines.

`Configuring the MyISAM engine`

the server operating system’s filesystem determines the caching of data rows read from or written to a MyISAM table

The MyISAM engine manages its own key cache for indexing

`The operation of the key cache`

initially it is empty

when the index needs to be read, it checks the key cache.If the index exists in the cache, the in-memory values are read.If it does not exist, it reads the table’s index file to get the values which are then put into the cache.

if the cache is full, the cache buffer that has been used the least for the longest time is discarded.

if the chosen buffer has not been modified, it is overwritten. If it has been modified, it is written to the index file before being overwritten.

The key cache’s main purpose is to reduce disk access. (See article on MySQL indexes) So the more values found in the cache, the greater the performance as values not found in the cache have to be accessed from the disk.

Frequently used values stay in the key cache and reduces the need to make room for new values by discarding unused buffers. The larger the key cache, the greater the “hits” or found values. This minimises disk access. A good configuration change is therefore to increase the size of your index if you have the available memory. Do this by setting the key_buffer_size system variable in the option file. The default is 8MB but can be set to 4GB, naturally depending on your available memory. You need to balance the available memory with demands made by other resources on your server that require memory.

For example, to set the key cache to 1GB do this:

[mysqld]
key_buffer_size = 1G

`Multiple key caches`

As of MySQL 4.1.1 it is possible to have multiple key caches. The revised caching mechanism has the following features:

multiple key caches possible

total cache size, cache block size and buffer discard algorithm are controllable

tables can be assigned to specific caches

table indexes can be preloaded into a cache

You can set up a number of key caches which can be allocated to separate tables. These tables will then not all be using the same key cache at the same time.

Each key cache is associated with a set of system variables (in this format cache_name.var_name) having these components:

key_buffer_size - total size of key cache in bytes

key_cache_block_size - size of blocks in key cache, in bytes

key_cache_division_limit - influences the cache buffer reuse algorithm and is set between 1 to 100

key_cache_age_threshold - how long buffers stay unused before being made available to be overwritten. Default is 300 and minimum is 100

One key cache is the default and is used if a key cache component is referred to without using a cache name.

You can set the key cache components at startup or runtime. For example:

[mysqld]
my_cache.key_buffer_size = 12M

will create a cache named my_cache with a 12MB buffer size on startup. This statement will create the same size cache at runtime:

SET GLOBAL my_cache.key_buffer_size = 12*1024*1024;

(Key caches are GLOBAL; 1024 is the number of bytes)

You can assign MyISAM tables to key caches with CACHE INDEX. For example:

CACHE INDEX myDbase.table1, myDbase.table2 IN my_cache;

which assigns table1 and table2 from myDbase to the cache named my_cache.

You can also preload table indexes into a cache with LOAD INDEX INTO CACHE. For example:

LOAD INDEX INTO CACHE table1, table2;

You can destroy a cache by setting its size to zero. Tables assigned to this cache are then reassigned to the default cache.

Key cache assignments are destroyed when the server shuts down. You can have these assigned automatically when the server starts by placing them in a file. You then start the server with an –init-file option that names the file.