What may cause MyISAM index corruption?

Question

I've had an error message telling about index corruption on the .myi file

Reparing the table fixed the issue

This table is accessed with select only, so what is likely to corrupt indexes ?

CREATE TABLE `my_table` (
 `col1` mediumint(1) unsigned NOT NULL,
 `col2` mediumint(1) unsigned NOT NULL,
 `fk_brand_trans` smallint(1) unsigned NOT NULL,
 `ref_trans` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `col3` smallint(1) unsigned NOT NULL,
 `attribute` smallint(1) unsigned NOT NULL,
 `ref` varchar(25) COLLATE latin1_general_ci NOT NULL,
 `pos` tinyint(1) NOT NULL,
 KEY `veh` (`col2`,`col1`),
 KEY `trans` (`ref_trans`,`fk_brand_trans`) USING BTREE,
 KEY `brand` (`fk_brand_trans`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=DYNAMIC

50M rows, 1.8GB data, 1.4GB index

score 1 · Answer 1 · edited Jun 15 '20 at 09:05

Since the MyISAM storage engine only caches indexes, reads from .MYI files are the norm. If the .MYI file of a given MyISAM table never changes, you have to look into external factors.

Just keep in mind that if the index blocks for a MyISAM table are not in the Key Cache, it must be read from disk, then loaded into the Key Cache.

EXTERNAL FACTOR #1 : Disk

If the disk blocks that hold the .MYI file become corrupt, there may be an error reading index blocks. Doing the REPAIR TABLE must have written the rebuilt .MYI elsewhere on disk.

EXTERNAL FACTOR #2 : Memory

If the memory chips become defective and parts of the MyISAM Key Cache is in use there, the .MYI file could possibly be considered corrupt when reading index blocks. Doing the REPAIR TABLE creates a new .MYI and is freshly read into the MyISAM Key Cache.

SUGGESTION

To reduce or eliminate the amount of reads from the .MYI file of a MyISAM table, you should look into setting up a dedicated Key Cache just for this table.

Since your table has a 1.4GB .MYI file, make a 1.5GB dedicate key cache for it:

USE mydb
SET GLOBAL my_table_keycache.key_buffer_size = 1536 * 1024 * 1024 * 1024;
CACHE INDEX mydb.my_table INTO my_table_keycache;
LOAD INDEX INTO CACHE my_table_keycache;

I have suggested this before

Sep 14, 2012 : Can I partially invalidate the MySQL table cache?
Mar 20, 2012 : optimal table design mysql with primay key and varchar value
Jan 26, 2012 : Less RAM than Index_length MyISAM

Doing this will also let you know quickly if the external factors apply in the future.

What may cause MyISAM index corruption?

1 Answers1

EXTERNAL FACTOR #1 : Disk

EXTERNAL FACTOR #2 : Memory

SUGGESTION