Zmagam się z hurtowym importowaniem dość dużego stołu InnoDB składającego się z około 10 milionów wierszy (lub 7 GB) (który jest dla mnie największym stołem, z jakim pracowałem do tej pory).
Przeprowadziłem badania, jak poprawić szybkość importowania Inno i na razie moja konfiguracja wygląda następująco:
/etc/mysql/my.cnf/
[...]
innodb_buffer_pool_size = 7446915072 # ~90% of memory
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_thread_concurrency=0
innodb_doublewrite = 0
innodb_log_file_size = 1G
log-bin = ""
innodb_autoinc_lock_mode = 2
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_instances=8
import is done via bash script, here is the mysql code:
SET GLOBAL sync_binlog = 1;
SET sql_log_bin = 0;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
SET SESSION tx_isolation='READ-UNCOMMITTED';
LOAD DATA LOCAL INFILE '$filepath' INTO TABLE monster
COMMIT;Dane są dostarczane w CSVpliku.
Obecnie testuję moje ustawienia za pomocą mniejszych „zrzutów testowych” z 2 milionami, 3 milionami… rzędów każdy i używam time import_script.shdo porównania wydajności.
Wadą jest to, że otrzymuję tylko ogólny czas działania, więc muszę czekać na zakończenie pełnego importu, aby uzyskać wynik.
Moje dotychczasowe wyniki:
- 10 000 rzędów: <1 sekunda
- 100 000 rzędów: 10 sekund
- 300 000 rzędów: 40 sekund
- 2 miliony wierszy: 18 minut
- 3 miliony wierszy: 26 minut
- 4 miliony wierszy: (anulowane po 2 godzinach)
Wygląda na to, że nie ma rozwiązania „książki kucharskiej” i należy samodzielnie ustalić optymalną kombinację ustawień.
Oprócz sugestii na temat tego, co należy zmienić w mojej konfiguracji, doceniłbym również więcej informacji, w jaki sposób mógłbym lepiej porównać proces importowania / uzyskać lepszy wgląd w to, co się dzieje i gdzie może być wąskie gardło.
Próbowałem przeczytać dokumentację ustawień, które zmieniam, ale z drugiej strony nie jestem świadomy żadnych skutków ubocznych, a jeśli nawet obniżę wydajność przy źle dobranej wartości.
W tej chwili chciałbym wypróbować sugestię z czatu do użycia MyISAMpodczas importowania i później zmienić silnik tabeli.
Chciałbym tego spróbować, ale w tej chwili moje DROP TABLEzapytanie również kończy się kilka godzin. (Co wydaje się kolejnym wskaźnikiem, moje ustawienie jest mniej niż optymalne).
Informacje dodatkowe:
Aktualnie używany komputer ma 8 GB pamięci RAM i hybrydowy dysk twardy Solid State w / 5400 RPM.
Chociaż staramy się również usunąć przestarzałe dane z omawianej tabeli, nadal potrzebuję dość szybkiego importu do
a) testowania automatic data cleanup featurepodczas programowania
ib) w przypadku awarii naszego serwera chcielibyśmy użyć drugiego serwera jako zamiennika (co wymaga -data danych, ostatni import zajął ponad 24 godziny)
mysql> SHOW CREATE TABLE monster\G
*************************** 1. row ***************************
Table: monster
Create Table: CREATE TABLE `monster` (
`monster_id` int(11) NOT NULL AUTO_INCREMENT,
`ext_monster_id` int(11) NOT NULL DEFAULT '0',
`some_id` int(11) NOT NULL DEFAULT '0',
`email` varchar(250) NOT NULL,
`name` varchar(100) NOT NULL,
`address` varchar(100) NOT NULL,
`postcode` varchar(20) NOT NULL,
`city` varchar(100) NOT NULL,
`country` int(11) NOT NULL DEFAULT '0',
`address_hash` varchar(250) NOT NULL,
`lon` float(10,6) NOT NULL,
`lat` float(10,6) NOT NULL,
`ip_address` varchar(40) NOT NULL,
`cookie` int(11) NOT NULL DEFAULT '0',
`party_id` int(11) NOT NULL,
`status` int(11) NOT NULL DEFAULT '2',
`creation_date` datetime NOT NULL,
`someflag` tinyint(1) NOT NULL DEFAULT '0',
`someflag2` tinyint(4) NOT NULL,
`upload_id` int(11) NOT NULL DEFAULT '0',
`news1` tinyint(4) NOT NULL DEFAULT '0',
`news2` tinyint(4) NOT NULL,
`someother_id` int(11) NOT NULL DEFAULT '0',
`note` varchar(2500) NOT NULL,
`referer` text NOT NULL,
`subscription` int(11) DEFAULT '0',
`hash` varchar(32) DEFAULT NULL,
`thumbs1` int(11) NOT NULL DEFAULT '0',
`thumbs2` int(11) NOT NULL DEFAULT '0',
`thumbs3` int(11) NOT NULL DEFAULT '0',
`neighbours` tinyint(4) NOT NULL DEFAULT '0',
`relevance` int(11) NOT NULL,
PRIMARY KEY (`monster_id`),
KEY `party_id` (`party_id`),
KEY `creation_date` (`creation_date`),
KEY `email` (`email`(4)),
KEY `hash` (`hash`(8)),
KEY `address_hash` (`address_hash`(8)),
KEY `thumbs3` (`thumbs3`),
KEY `ext_monster_id` (`ext_monster_id`),
KEY `status` (`status`),
KEY `note` (`note`(4)),
KEY `postcode` (`postcode`),
KEY `some_id` (`some_id`),
KEY `cookie` (`cookie`),
KEY `party_id_2` (`party_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=13763891 DEFAULT CHARSET=utf8SHOW CREATE TABLE yourtable\Gaby pokazać nam strukturę tabeli tego 10-milionowego wiersza.
innodb_doublewrite = 0) powoduje, że instalacja MySQL nie jest bezpieczna w przypadku awarii: jeśli wystąpi awaria zasilania (nie awaria MySQL), dane mogą zostać po cichu uszkodzone.
