Re: delay_key_write with midpoint insertion strategy (key_cache_division_limit)

[Rick James]

(depending on number of drives, type of raid)

2. [If possible] Sort the incoming data so that it hits the data/index in a "clustered" way.

3. ALTER TABLE ... ORDER BY ...; so that the data is clustered.

4. Cut down on the number of keys. (Assuming the key_cache is the issue)

5. Switch engines...
MyISAM, if you every delete, will start putting new rows in the holes. This leads to random access in the data.
In InnoDB, the data is "clustered" on the PRIMARY KEY (unlike MyISAM). Hence, it that key goes hand-in-hand with the data.

6. increase key_buffer_size -- but remember to leave room for data block caching

7. Shrink the size of the fields -- MEDIUMINT vs INT, etc.

8. Show us the queries involved; sometimes redesigning them is the 'real' solution.

9. SHOW TABLE STATUS and SHOW CREATE TABLE -- often there are clues there.