MyISAM Storage Engine

Introduction

MyISAM is a non-transactional storage engine with small system footprint. This page provides an introduction to MyISAM storage engine and how to configure it.

This page is part of the MySQL administration series:

MyISAM Storage Engine

MyISAM is a non-transactional storage engine with small system footprint. It only supports table-level locking. This means it is used for read-only or read-mostly applications. It also does not have foreign keys.

A MyISAM table is stored on disk in three files.  The filename is same as table name, and the extension indicates the type of file.

  • tablename.FRM: stores the table format
  • tablename.MYD: stores the data
  • tablename.MYI: stores the indexes

These tree files contain all the information required for the table. This means that they can be copied as backup or even to another database server.

MyISAM Common Configuration

This table lists the common configuration options used for MyISAM tables:

VariablePurpose
key_buffer_sizeDetermines the size of the memory cache used for storing MyISAM indexes.

The default is 8MB, and is recommended not to set it larger than 30% of available memory.

MyISAM manages its own key cache for processing index retrievals and index sorts.  This is one of the most important MyISAM configurable variables.

concurrent_insertsDetermines the behaviour of concurrent inserts.

The default is 1.

MyISAM depends on the global table-level locking. A large number of concurrent writes will end up blocking the reads.

By setting concurrent_inserts=1 you allow inserts at the same time as reads as long as there are no data gaps. councurrent_inserts=2 allows inserts even if they produce data gaps. concurrent_insert=0 disallows concurrent inserts.

delay_key_writeDelays updating MyISAM indexes until tables are closed.

The default is ON.

This will boost performance but tables will be corrupted if mysqld crashes.

max_write_lock_countDetermines how many writes to a table can take precedence over reads.

The default is 4294967295, which gives higher priority to writes.

This can resolve read-starvation issues when there are many writes locking the table.

Corrupted MyISAM tables

The MyISAM storage engine is very reliable.  You might however end up with corrupted tables if the mysqld  process is killed in the middle of a write operation, or an unexpected shutdown occurs.

You can check the health of a MyISAM table with this command:

And you can repair a table with this statement:

You can also check and repair tables with the myisamchk command. When running myisamchk you need to ensure mysqld is shutdown as otherwise you could corrupt the tables.

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply