key_buffer_size oraz myisam_sort_buffer_size

Idąc za ciosem doczytałem się jeszcze o dwóch zmiennych

key_buffer_size

The key_buffer_size is probably the most useful single variable to tweak. The larger you set it, the more of your MyISAM table indexes you store in memory. With most queries making use of an index, and memory being an order of magnitude faster than disk, the importance of this variable cannot be overestimated.
On dedicated MySQL servers, the rule-of-thumb is to aim to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server)


myisam_sort_buffer_size
used as buffer when alter table & myisam sorting is req. 

Z testow wynika, ze drugi parametr jest mniej istotny.
W kazdym razie wartosci zostaly zmieniony z
key_buffer_size - z 512MB na 1 i 2 GB
myisam_sort_buffer_size - z 8MB na 256MB
4 GB pamięci na serwerze

1.myisam_max_sort_file_size=10G key_buffer_size=1GB myisam_sort_buffer_size=256 MB

mysql> insert into historia_test2 select NULL,PK_nazwa,PK_wartosc,data_transakcji,typ_trasakcji,opis,id_user from historia;
Query OK, 4555320 rows affected (39.38 sec)
Records: 4555320  Duplicates: 0  Warnings: 0

mysql> insert into historia_test2 select NULL,PK_nazwa,PK_wartosc,data_transakcji,typ_trasakcji,opis,id_user from historia;
Query OK, 4555320 rows affected (43.46 sec)
Records: 4555320  Duplicates: 0  Warnings: 0

mysql> alter table historia_test2 enable keys;
Query OK, 0 rows affected (2 min 2.44 sec)

2.myisam_max_sort_file_size=10G key_buffer_size=2GB myisam_sort_buffer_size=256 MB

mysql> insert into historia_test2 select NULL,PK_nazwa,PK_wartosc,data_transakcji,typ_trasakcji,opis,id_user from historia;
Query OK, 4555320 rows affected (40.78 sec)
Records: 4555320  Duplicates: 0  Warnings: 0

mysql> insert into historia_test2 select NULL,PK_nazwa,PK_wartosc,data_transakcji,typ_trasakcji,opis,id_user from historia;
Query OK, 4555320 rows affected (43.70 sec)
Records: 4555320  Duplicates: 0  Warnings: 0

mysql> alter table historia_test2 enable keys;
Query OK, 0 rows affected (1 min 56.42 sec)

Jak widać zarówno na insertach jak i odbudowie indexow udało sie jeszcze urwać parę sekund ;)