Do testów poszła tabela trzymajaca dane historyczne zawierajaca
id | int(10) unsigned |
PK_nazwa | varchar(255) |
PK_wartosc | varchar(255)
data_transakcji | timestamp
typ_trasakcji | enum('insert','update','delete','replace')
opis | text
id_user | int(10) unsigned
Aby się za bardzo nie wysilać postanowiłem powielić dane w tabelii. (początkowo miała ok 4,5 mln rekordów)
mysql> insert into historia_test select NULL,PK_nazwa,PK_wartosc,data_transakcji,typ_trasakcji,opis,id_user from historia;
Query OK, 4555320 rows affected (2 min 3.99 sec)
Records: 4555320 Duplicates: 0 Warnings: 0
mysql> insert into historia_test select NULL,PK_nazwa,PK_wartosc,data_transakcji,typ_trasakcji,opis,id_user from historia;
Query OK, 4555320 rows affected (5 min 41.65 sec)
Records: 4555320 Duplicates: 0 Warnings: 0
Jak widać za każdym razem dodawanie nowych danych zabierało więcej czasu. Przy przejściu z 13,5 mln na 18 wynosiło to prawie 12 min.
Do testów potrzebowałem tabeli z ok 30 mln. Tak więc kolejne powielanie by mnie zabiło.
Googlując dokopałem się do informacji , ze przy wszelkich bulk insertach dobrze jest wyłączyc indeksowanie na tabeli.
mysql> alter table historia_test2 disable keys;
Query OK, 0 rows affected (0.00 sec)
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 (42.13 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 (40.27 sec)
Records: 4555320 Duplicates: 0 Warnings: 0
Spadek z 5min na 40s wygląda całkiem ok ;)
No to włączamy spowrotem indeksowanie:
mysql> alter table historia_test2 enable keys;
Query OK, 0 rows affected (15 min 33.28 sec)
Czas mnie zabił...
mysql> show processlist;
+-----+------+-----------+------+---------+------+----------------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+----------------------+----------------------------------------+
| 157 | root | localhost | test | Query | 897 | Repair with keycache | alter table historia_test2 enable keys |
+-----+------+-----------+------+---------+------+----------------------+----------------------------------------+
Na googlach doszukałem się informacji o zmiennej : myisam_max_sort_file_size
MYISAM_MAX_SORT_FILE_SIZE sets the maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes and the default value is 2GB.
Set this values a bit larger thatn the largest index file if there is disk space is available to ensure that REPAIR TABLE is be done by sort rather than repair by key cache. Sorting is much faster than repair by key cache.
No to zmieniamy:
Początkowo było 2 GB:
mysql> show variables like '%myisam_max_sort_file_size%' ;
+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| myisam_max_sort_file_size | 2146435072 |
+---------------------------+------------+
Do my.cnf poleciał wpis :
myisam_max_sort_file_size=10G
mysql> show variables like '%myisam_max_sort_file_size%' ;
+---------------------------+-------------+
| Variable_name | Value |
+---------------------------+-------------+
| myisam_max_sort_file_size | 10737418240 |
+---------------------------+-------------+
Powrótka z operacji,stworzenie tabelki,wylaczenie indeksowania,inserty,wlaczanie indeksow.
mysql> alter table historia_test2 enable keys;
Query OK, 0 rows affected (2 min 23.04 sec)
Spadek z 15 min na 2.23 brzmi bardzo ok :)
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------------------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------------------+----------------------------------------+
| 154 | root | localhost | test | Query | 261 | Repair by sorting | alter table historia_test2 enable keys |
+-----+------+-----------+------+---------+------+-------------------+----------------------------------------+
