Insertion performance degrade with large index

[holy holy]

Hi everyone,

Recently, i found that one of the server have high I/O traffic on disk. The high I/O due to the writing of index on certain table after some diagnostics. I have done several evaluation test and found that mysql take high number of write when inserting records to the table which have a large index.

The Data type of indexed columns is varchar(15) and varchar(17) ,
there is only 80 writes on disk if i load 20000 records to the table which has 10000 records whereas there are 1700 writes on disk when table grow to 20 millions (which got about 1 millions distinct values on indexed columns)
even the number of records being inserted is the same.

Engine is MyISAM.

Increasing the size of the indexes also increasing number of write on disk per insert.

Is it the BTREE index behavior and how can i solve this issue?



Edited 2 time(s). Last edit at 04/11/2013 02:39AM by holy holy.