MySQL Performance: MyISAM
- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: System Config & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
- MySQL Performance: MyISAM
- MySQL Performance: MySQL/MariaDB Indexes
- MySQL Performance: Intro to JOINs in SQL
MyISAM is a table-locking based engine. Table-locking prevents all other processes from writing data to the target table. This locking behavior encompasses the entire MyISAM table, no matter how minor the data change. This type of locking preserves the integrity of the data but at the cost of performance. The performance penalty for using table-locking based engines like MyISAM become more laborious as the row count in the table grows. Very large tables will see a significant delay in performance when data needs to be written to a MyISAM table.
Unlike InnoDB, there is little that can be adjusted on the server level to optimize MyISAM tables for performance. Instead, MyISAM-based performance relies heavily on query construction using best practices. There are a number of session level variables that can be adjusted on the fly to boost the performance of the invoking query. However, optimization on the query level is beyond the purview of this article.
MyISAM's optimization on the dedicated server level comes down to two major practices.
- Determining when to use MyISAM tables versus InnoDB tables
- MyISAM Key Cache optimization & maintenance
When to Use MyISAM vs. InnoDB Tables
Although MyISAM is not intended as a performance solution, it does have its niche roles where it outperforms the more robust InnoDB engine. Identifying these niches in your database structure can help squeeze the best performance out of your database and associated applications. To determine whether a table should be MyISAM or InnoDB, it’s best to keep the following rule of thumb in mind.
MyISAM Key Cache Optimization & Maintenance
The MyISAM Key Cache (also called the Key Buffer), represents the amount of memory pre-allocated for storing index data of MyISAM tables. Optimizing the Key Cache is done by setting an adequate key_buffer_size, ensuring it is large enough to include as much of the total length of all MyISAM table indexes as well as a minor portion for administrative overhead.

The amount of overhead the key_buffer_size required for administrative tasks is small but grows along with the size of table indexes. Using a generic value of 5% of total index length of all MyISAM tables ensures enough room is available in the Key Cache for overhead. However, on extremely large database setups, the required overhead can be lowered further by increasing key_cache_block_size. This is due to more rows being accessed at once, reducing the need for administrative overhead to frame those additional rows.
How to Calculate MyISAM Key Cache Size (key_buffer_size)
There are a couple of different methods used for calculating a proper key_buffer_size. These are listed and detailed below.
- Calculate key_buffer_size from ALL MyISAM Index Lengths
- Dedicated Server: Calculate key_buffer_size from total physical memory (MySQL Recommended)
- Shared Server: Calculate key_buffer_size from available system memory
Calculate key_buffer_size from ALL MyISAM Index Lengths
To keep the memory usage of the key_buffer_size as small as possible while having little risk of setting the value too small. This method relies on calculating the total length of the Indexes for all MyISAM tables currently on the server.
Script: Calculate key_buffer_size from Index Lengths
mysql -e "set @overhead = 5 / 100;
select count(INDEX_LENGTH) as Indexes,
sum(INDEX_LENGTH) as Total_Index_Length,
floor(@overhead * 100) as PCT,
floor(sum(INDEX_LENGTH)*@overhead) as Overhead,
floor(sum(INDEX_LENGTH)*(1+@overhead)) as key_buffer_size
FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';"
Example Output:
+---------+--------------------+------+----------+-----------------+
| Indexes | Total_Index_Length | PCT | Overhead | key_buffer_size |
+---------+--------------------+------+----------+-----------------+
| 744 | 5775360 | 5 | 288768 | 6064128 |
+---------+--------------------+------+----------+-----------------+
Dedicated Server: Calculate key_buffer_size from total physical memory (MySQL Recommended)
MySQL also has some recommendations for finding an appropriate key_buffer_size value. Their approach only considers total physical system memory. Though this is an acceptable method, it accounts little for the actual size of the server’s MyISAM indexes and often results in over-tuning the buffer size. Below is the rule and a script to help calculate this value for any given server.
Script: Calculate key_buffer_size for Dedicated Servers
awk '/MemTotal/{$3="GB";$2=sprintf("%.0f",$2/1048576); print;$1=" Mem80%:";$2=sprintf("%.0f",$2*.25);print}' /proc/meminfo
Example Output:
MemTotal: 15 GB
Mem80%: 3.75 GB
Shared Server: Calculate key_buffer_size from available system memory
This rule is akin to the previous MySQL recommended rule. However, this one is modified to use available system memory instead of total physical memory. This makes the rule much more suitable for shared servers, yet is still rather generic.
Script: Calculate key_buffer_size for Shared Servers
awk '/MemAvail/{$3="G";_=$2;$2=sprintf("% 3.0f",_/1048576);print;
for (i=30;i>=15;i-=5) {$1="MemAvail_"i"%:"; $2=sprintf("%3.0f",_*(i/100)/1048576); $4=sprintf("| %.0f M",_*(i/100)/1024);print}}' /proc/meminfo
Example Output:
MemAvailable: 10 G
MemAvail_30%: 3 G | 3087 M
MemAvail_25%: 3 G | 2573 M
MemAvail_20%: 3 G | 2058 M
MemAvail_15%: 3 G | 1544 M
MyISAM Key Cache Maintenance (Performance Tuning)
As with most MySQL optimization, it is important to re-evaluate performance periodically. MySQL makes it possible to examine the performance of the Key Cache by using the SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES statements and reviewing some specific variables. This capability is instrumental in making sure the Key Cache is performing well. There are a few different metrics to consider when evaluating the Key Cache Performance. These metrics are outlined in the following sub-sections.
Calculating Key Cache Usage Rate
One useful metric is the Key Cache Usage Rate. This measures the percentage of the Key Cache that is filled, a helpful detail when determining if the key_buffer_size is large enough or if memory is being wasted on a key_buffer_size that is too large for the server’s MyISAM tablespace. Calculating the Key Cache Usage Rate requires variables from both SHOW STATUS and SHOW VARIABLES statements. Below is the list of needed variable names and the equation used to calculate Key Cache Usage Rate.

Script: Calculate Key Cache Usage Rate
mysql -e '
select VARIABLE_VALUE into @Key_blocks_unused FROM information_schema.global_status
WHERE VARIABLE_NAME = "Key_blocks_unused";
select @Key_blocks_unused as Key_blocks_unused,
@@key_cache_block_size as key_cache_block_size,
@@key_buffer_size as key_buffer_size\G
select truncate((1-((@Key_blocks_unused*@@key_cache_block_size)/@@key_buffer_size))*100, 2)
as "(1-((Key_blocks_unused × key_cache_block_size) ÷ key_buffer_size)) × 100"\G
'
Example Output:
*************************** 1. row ***************************
Key_blocks_unused: 26476
key_cache_block_size: 1024
key_buffer_size: 33554432
*************************** 1. row ***************************
(1-((Key_blocks_unused × key_cache_block_size) ÷ key_buffer_size)) × 100: 19.20
Calculating Key Cache Miss Rate
Another useful Key Cache statistics is the Key Cache Miss Rate. This shows how often items that could be in the Key Cache are being read from a physical disk. The larger the rate, the more often items are read from disk instead of the Key Cache which indicates the current key_buffer_size is too small and should be increased. While a lower rate here indicates the Key Cache is being under-utilized. This indicates the key_buffer_size is too large and consuming excess memory needlessly.

Script: Calculate Key Cache Miss Rate
mysql -e '
select VARIABLE_VALUE into @Key_reads FROM information_schema.global_status
WHERE VARIABLE_NAME = "Key_reads";
select VARIABLE_VALUE into @Key_read_requests FROM information_schema.global_status
WHERE VARIABLE_NAME = "Key_read_requests";
select @Key_reads as"Key_reads (From Disk)",
@Key_read_requests as "Key_read_requests (Total)",
truncate(@Key_reads/@Key_read_requests*100, 2) as "Key_Cache_Miss_Rate%"\G
'
Example Output:
*************************** 1. row ***************************
Key_reads (From Disk): 1275
Key_read_requests (Total): 1209967
Key_Cache_Miss_Rate%: 0.10
Calculating Key Cache Flush Rate
The Key Cache Flush Rate measures how frequently the Key Cache is written to disk. Frequent flushing can slow down MyISAM operations as writing data to a physical disk is much slower than writing to the Key Cache in memory. The closer the Key Cache Flush Rate is to 100% the more frequently MyISAM flushes to disk. The lower the rate, the less often disk writes are conducted.

Script: Calculate Key Cache Miss Rate
mysql -e '
select VARIABLE_VALUE into @Key_reads FROM information_schema.global_status
WHERE VARIABLE_NAME = "Key_reads";
select VARIABLE_VALUE into @Key_read_requests FROM information_schema.global_status
WHERE VARIABLE_NAME = "Key_read_requests";
select @Key_reads as"Key_reads (From Disk)",
@Key_read_requests as "Key_read_requests (Total)",
truncate(@Key_reads/@Key_read_requests*100, 2) as "Key_Cache_Miss_Rate%"\G
'
Example Output:
*************************** 1. row ***************************
Key_reads (From Disk): 1275
Key_read_requests (Total): 1209967
Key_Cache_Miss_Rate%: 0.10
Improving Key Cache Flush Rate
The Key Cache Flush Rate can be improved (reduced) in a couple of different ways. One method is on the query level. Making sure that UPDATE queries on MyISAM tables are changing multiple rows in one single query whenever possible. This reduces the amount of flushing needed to conclude writing changed data by addressing them in a group together with a single write operation to disk instead of individually.
The other method for reducing the Key Cache Flush Rate is intended for large MyISAM tables. These tables should take advantage of the DELAY_KEY_WRITE create table attribute. This attribute only functions on MyISAM tables and instructs the engine to delay all Key Cache writing operations until after tables are closed, instead of at the moment data changes. This provides the biggest performance increase when enabled on large or medium tables with large amounts of volatile (changing) data.
Enabling DELAY_KEY_WRITE should be done on a per table basis and should be part of the initial CREATE TABLE query for any MyISAM table expected to be large or volatile. It is also simple to modify an existing MyISAM table so that it will start using the delayed write enhancement. This is done with a simple ALTER TABLE query like so:
Example: Enable DELAY_KEY_WRITE on an existing MyISAM table
mysql -e 'ALTER TABLE databaseName.tableName DELAY_KEY_WRITE = 1;'
Example Output:
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
One final method still relates to the DELAY_KEY_WRITE table attribute. It is possible, using the DELAY_KEY_WRITE system variable, to force DELAY_KEY_WRITE behavior on for ALL MyISAM tables. This is done by setting the DELAY_KEY_WRITE system variable within the system configuration file to ALL instead of the default ON.

Risk versus Reward
MySQL stresses the potential risk when planning on using the DELAY_KEY_WRITE property with MyISAM.
Example Output:
[mysqld]
myisam-recover-options=BACKUP,FORCE
As with most performance enhancement, there is usually a sacrifice to be made for speed. It is important to understand that data integrity, particularly the integrity of your table indexes are more corruptible when running with the DELAY_KEY_WRITE feature enabled. For this reason, it is important to have alternative means of warding off corruption with other solutions like the --myisam-recover-options feature to create a backup of indexes. Another common practice with high-performance database server is using a secondary power source or battery backup, so services have time to shut down properly during a power loss scenario.
Other MyISAM Directives
There are several other techniques for tuning your MyISAM Cache Key and its behavior. These techniques fall outside the scope of the current article. However, more details about these techniques are available on the MySQL website here: 16.2.1 MyISAM Startup Options.
Related Articles:
- Using MySQL Command Line to Create a User
- How to Use Disk Quotas in Dedicated Linux Servers With cPanel
- How to Use Disk Quotas in Dedicated Linux Servers with Plesk
- Remove a MySQL User on Linux via Command Line
- Remove Permissions for a MySQL User on Linux via Command Line
- Grant Permissions to a MySQL User on Linux via Command Line

About the Author: Jason Potter
A veteran of the IT Support field, I have more than a decade of experience in systems administration, web hosting, and cPanel servers. I enjoy writing and providing complicated technical concepts in layman terms. On my free time, I enjoy playing several types video games, automation scripting and just living life with my wife and two kids.
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.
Latest Articles
How to Install WordPress on Linux (AlmaLinux)
Read ArticleWhat is CentOS? Everything You Need to Know
Read ArticleWhat is CentOS? Everything You Need to Know
Read ArticleRedis as Cache: How It Works and Why You Should Use It
Read ArticleRefer-a-Friend Program for Website Hosting: Get $100 for Each Friend!
Read Article