MySQL Performance: MyISAM

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.

1. Determining when to use MyISAM tables versus InnoDB tables
2. 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.

1. Calculate key_buffer_size from ALL MyISAM Index Lengths
2. Dedicated Server: Calculate key_buffer_size from total physical memory (MySQL Recommended)
3. 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.