To pytanie zostało przełożone z Przepełnienia stosu na podstawie sugestii w komentarzach, przeprosin za powielanie.
pytania
Pytanie 1: w miarę powiększania się tabeli bazy danych, jak mogę dostroić MySQL, aby zwiększyć szybkość wywołania LOAD DATA INFILE?
Pytanie 2: czy użycie klastra komputerów do załadowania różnych plików csv, poprawienie wydajności lub zabicie go? (to moje zadanie na jutro przy użyciu danych obciążenia i wkładek masowych)
Cel
Wypróbowujemy różne kombinacje detektorów funkcji i parametrów klastrowania do wyszukiwania obrazów, w wyniku czego musimy być w stanie budować duże bazy danych w odpowiednim czasie.
Informacje o maszynie
Maszyna ma 256 gigawatów pamięci RAM, a dostępne są kolejne 2 maszyny z taką samą ilością pamięci RAM, jeśli istnieje sposób na poprawę czasu tworzenia poprzez dystrybucję bazy danych?
Schemat tabeli
wygląda schemat tabeli
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| match_index | int(10) unsigned | NO | PRI | NULL | |
| cluster_index | int(10) unsigned | NO | PRI | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| tfidf | float | NO | | 0 | |
+---------------+------------------+------+-----+---------+----------------+utworzony z
CREATE TABLE test
(
match_index INT UNSIGNED NOT NULL,
cluster_index INT UNSIGNED NOT NULL,
id INT NOT NULL AUTO_INCREMENT,
tfidf FLOAT NOT NULL DEFAULT 0,
UNIQUE KEY (id),
PRIMARY KEY(cluster_index,match_index,id)
)engine=innodb;Jak dotąd porównywanie
Pierwszym krokiem było porównanie wkładek masowych z ładowaniem z pliku binarnego do pustej tabeli.
It took: 0:09:12.394571 to do 4,000 inserts with 5,000 rows per insert
It took: 0:03:11.368320 seconds to load 20,000,000 rows from a csv fileBiorąc pod uwagę różnicę w wydajności, którą poszedłem przy ładowaniu danych z binarnego pliku csv, najpierw załadowałem pliki binarne zawierające wiersze 100K, 1M, 20M, 200M, korzystając z poniższego wywołania.
LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test;Zabiłem ładowanie pliku binarnego o wielkości 200M wierszy (~ 3 GB pliku csv) po 2 godzinach.
Uruchomiłem więc skrypt, aby utworzyć tabelę i wstawiłem różne liczby wierszy z pliku binarnego, a następnie upuściłem tabelę, patrz wykres poniżej.
Wstawienie 1M wierszy z pliku binarnego zajęło około 7 sekund. Następnie zdecydowałem się na wykonanie testu porównawczego wstawiając 1M wierszy jednocześnie, aby sprawdzić, czy wystąpi wąskie gardło przy danym rozmiarze bazy danych. Po trafieniu bazy danych do około 59 milionów wierszy średni czas wstawiania spadł do około 5000 na sekundę
Ustawienie globalnego key_buffer_size = 4294967296 nieznacznie poprawiło prędkości wstawiania mniejszych plików binarnych. Poniższy wykres pokazuje prędkości dla różnych liczb rzędów
Jednak wstawienie 1M wierszy nie poprawiło wydajności.
wiersze: 1 000 000 czas: 0: 04: 13,761428 wstawek / s: 3,940
vs dla pustej bazy danych
wiersze: 1 000 000 czas: 0: 00: 6,339295 wstawek / s: 315,492
Aktualizacja
Wykonanie ładowania danych przy użyciu następującej sekwencji vs po prostu użycie polecenia ładowania danych
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
LOAD DATA INFILE '/mnt/imagesearch/tests/eggs.csv' INTO TABLE test_ClusterMatches;
SET foreign_key_checks=1;
SET unique_checks=1;
COMMIT;
Wygląda to więc dość obiecująco pod względem wielkości generowanej bazy danych, ale inne ustawienia nie wydają się wpływać na wydajność wywołania infiltracji danych ładowania.
Próbowałem następnie załadować wiele plików z różnych komputerów, ale polecenie ładowania danych infiltruje tabelę, ze względu na duży rozmiar plików powodujący przekroczenie limitu czasu przez inne maszyny
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transactionZwiększenie liczby wierszy w pliku binarnym
rows: 10,000,000 seconds rows: 0:01:36.545094 inserts/sec: 103578.541236
rows: 20,000,000 seconds rows: 0:03:14.230782 inserts/sec: 102970.29026
rows: 30,000,000 seconds rows: 0:05:07.792266 inserts/sec: 97468.3359978
rows: 40,000,000 seconds rows: 0:06:53.465898 inserts/sec: 96743.1659866
rows: 50,000,000 seconds rows: 0:08:48.721011 inserts/sec: 94567.8324859
rows: 60,000,000 seconds rows: 0:10:32.888930 inserts/sec: 94803.3646283Rozwiązanie: Wstępnie obliczaj identyfikator poza MySQL zamiast korzystać z automatycznego przyrostu
Budowanie stołu za pomocą
CREATE TABLE test (
match_index INT UNSIGNED NOT NULL,
cluster_index INT UNSIGNED NOT NULL,
id INT NOT NULL ,
tfidf FLOAT NOT NULL DEFAULT 0,
PRIMARY KEY(cluster_index,match_index,id)
)engine=innodb;z SQL
LOAD DATA INFILE '/mnt/tests/data.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"
Wydaje się, że pobranie skryptu w celu wstępnego obliczenia indeksów spowodowało zmniejszenie wydajności w miarę powiększania się bazy danych.
Aktualizacja 2 - za pomocą tabel pamięci
Około 3 razy szybciej, bez uwzględnienia kosztów przeniesienia tabeli w pamięci do tabeli opartej na dysku.
rows: 0 seconds rows: 0:00:26.661321 inserts/sec: 375075.18851
rows: 10000000 time: 0:00:32.765095 inserts/sec: 305202.83857
rows: 20000000 time: 0:00:38.937946 inserts/sec: 256818.888187
rows: 30000000 time: 0:00:35.170084 inserts/sec: 284332.559456
rows: 40000000 time: 0:00:33.371274 inserts/sec: 299658.922222
rows: 50000000 time: 0:00:39.396904 inserts/sec: 253827.051994
rows: 60000000 time: 0:00:37.719409 inserts/sec: 265115.500617
rows: 70000000 time: 0:00:32.993904 inserts/sec: 303086.291334
rows: 80000000 time: 0:00:33.818471 inserts/sec: 295696.396209
rows: 90000000 time: 0:00:33.534934 inserts/sec: 298196.501594
przez załadowanie danych do tabeli opartej na pamięci, a następnie skopiowanie jej do tabeli opartej na dysku w porcjach miało narzut 10 min 59,71 s, aby skopiować 107 356 741 wierszy za pomocą zapytania
insert into test Select * from test2;
co sprawia, że ładowanie 100 milionów wierszy zajmuje około 15 minut, co jest w przybliżeniu tym samym, co bezpośrednie wstawienie go do tabeli opartej na dysku.
idpowinna być szybsza. (Chociaż myślę, że tego nie szukasz)