1

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

1

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

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

  • The disk is a Samsung NVMe SSD and the memory is ECC. Yet if this happens again and if the cause is Factor #2, what is the best solution (other than repair table which took a while, like running a command or restarting the server) ? – guigoz Aug 28 '18 at 20:40
  • Math wrong... Don't set the key buffer to 1.5TB. – Rick James Sep 30 '18 at 3:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.