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:
- MySQL Components
- MySQL Configuration
- InnoDB Storage Engine
- MyISAM Storage Engine
- MySQL Backup and Restore with mysqldump
- MySQL Date Types and Time Zone support
- MySQL User Management and Access Control
- MySQL Table Maintenance
- Administration Commands
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:
|key_buffer_size||Determines 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_inserts||Determines 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_write||Delays 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_count||Determines 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:
CHECK TABLE tablename
And you can repair a table with this statement:
REPAIR TABLE tablename
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.
- MySQL 5.6 Reference Manual. Oracle, 2015
- MySQL: Developer’s Library. Paul DuBois, Pearson Education, 2009
- MySQL Administrator’s Bible. Sheeri Cabral and Keith Murphy, Wiley Publishing, 2009