Size of MYI index smaller on slave server

Question

I've recently altered a huge MyISAM table (80 million entries) by adding a new value in an enum field. After I did this I've checked the health of the table on 2 slave servers which are used to replicate the master server. I've noticed that MYI file is much smaller on one of the slave servers. I've repaired the table with myisamchk and REPAIR but the size of the index has not changed.

Please see below the results returned by running myisamchk -dvv on these 3 servers for "Keyfile length":

Master Server
Auto increment key:              1  Last value:              80098340
Data records:             79375556  Deleted blocks:                 0
Datafile parts:           79375556  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:        9635014668  **Keyfile length:       18945252352**
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                 1110

Slave Server 1
Auto increment key:              1  Last value:              80097611
Data records:             79374828  Deleted blocks:                 0
Datafile parts:           79394418  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:        9635788652  **Keyfile length:       18024821760**
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719
Recordlength:                 1110


Slave Server 2 - Here the size of Keyfile is much smaller
Auto increment key:              1  Last value:              80098312
Data records:             79375002  Deleted blocks:                 0
Datafile parts:           79375002  Deleted data:                   0
Datafile pointer (bytes):        6  Keyfile pointer (bytes):        6
Datafile length:        9634942908  **Keyfile length:       11092404224**
Max datafile length: 281474976710654  Max keyfile length: 288230376151710719

What could possibly cause such a significant difference?

Answer 1

If you ran a repair on the a MyISAM, it should reduce the size of the MYI. Why ?

When a mysqldump creates and load a MyISAM table, think of the mechanical steps to make mytable:

CREATE TABLE mytable ...
LOCK TABLE mytable ...
ALTER TABLE mytable DISABLE KEYS; (shuts off updates to non-unique indexes)
INSERT INTO ...
INSERT INTO ...
.
.
.
ALTER TABLE mytable ENABLE KEYS; (rebuild all indexes)
UNLOCK TABLES;

During the ALTER TABLE ... ENABLE KEYS, you run SHOW PROCESSLIST;

You will see the info of that process say Repair by sorting

When done you should have an MYI with 95% of more BTREE nodes filled to capacity.

Why does so much space vanish when you repair it ? Look at the opposite case.

You are loading a table in numerical order on some auto_increment id. Loading data in some ordered fashion produces lopsided key distribution in BTREE indexes. In some cases, all nodes can be up to 45% fragmented.

EXAMPLE: If you have a binary tree (the worst BTREE) loaded in order, you get a binary tree leaning all the way to the right that should look like a linked list with a negative slope.

I once mentioned this crazy phenomenon in my DBA StackExchange posts

• Aug 03, 2011 : When should I rebuild indexes?
• Jun 28, 2012 : Benefits of BTREE in MySQL
• Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?

Running a REPAIR TABLE in the mysql client, running myisamchk -r or reloading a mysqldump of a MyISAM should produce a smaller MyISAM index file always.