wolne ładowanie danych z mysqldump

Mam bazę danych MySQL umiarkowanej wielkości z około 30 tabelami, z których niektóre to 10 milionów rekordów, a około 100 milionów. mysqldumpWszystkich tabel (w osobnych plikach) jest dość szybka, trwa może 20 minut. Generuje około 15 GB danych. Największe zrzuty plików znajdują się w zakresie 2 GB.

Kiedy ładuję dane do MySQL na innym urządzeniu, sześciordzeniowym komputerze o pojemności 8 GB, trwa to wiecznie. Z łatwością 12 godzin zegarowych lub więcej.

Właśnie uruchamiam klienta mysql, aby załadować plik, tj

mysql database < footable.sql

bezpośrednio z pliku bezpośrednio z mysqldump

mysqldump database foo > footable.sql

Najwyraźniej robię coś złego. Od czego zacząć, aby można było zakończyć w rozsądnym czasie?

Nie używam żadnych przełączników ani do zrzutu ani obciążenia.

Weź pod uwagę kilka punktów, które mogą ci pomóc w przypadku wygenerowania zrzutu i przywrócenia go.

1. Użyj Extended insertsna zrzutach.
2. Zrzuć z --tabformatem, abyś mógł użyć mysqlimport, który jest szybszy niż mysql < dumpfile.
3. Importuj z wieloma wątkami, po jednym dla każdej tabeli.
4. Jeśli to możliwe, użyj innego silnika bazy danych. importowanie do silnie transakcyjnego silnika, takiego jak innodb, jest strasznie wolne. Wstawianie do silnika nietransakcyjnego, takiego jak MyISAM, jest znacznie szybsze.
5. Wyłącz sprawdzanie klucza obcego i włącz automatyczne zatwierdzanie.
6. Jeśli importujesz do innodb, najskuteczniejszą rzeczą, jaką możesz zrobić, to umieścić innodb_flush_log_at_trx_commit = 2plik my.cnf, tymczasowo podczas importowania. możesz ustawić go z powrotem na 1, jeśli potrzebujesz ACID

Spróbuj..

Oprócz odpowiedzi Abdula chciałbym podkreślić znaczenie --disable-keysopcji, która wyłącza klawisze, dopóki wszystkie dane nie zostaną załadowane do tabeli. Ta opcja jest włączona jako część --optprzełącznika, który jest domyślnie włączony, ale uznał, że należy to zaznaczyć.

Jeśli nie pominiesz klawiszy podczas wstawiania, to każdy wstawiony wiersz odbuduje indeks. Niezwykle wolny proces.

Ostatnio dużo z tym miałem do czynienia. Możesz zdecydowanie poprawić wydajność importu, wykonując import równolegle. Większość spowolnień opiera się na operacjach wejścia / wyjścia, ale nadal możesz uzyskać 40% poprawę, zrzucając do tabel, a następnie importując je, powiedzmy 4 na raz.

Możesz to zrobić za pomocą xargs:

ls *.sql -1c | xargs -P4 -I tbl_name sh -c "mysql --user=username --password database < tbl_name"

spakowanie plików przed wypchnięciem ich do mysql niczego nie spowalnia, głównie z powodu obniżonego wejścia / wyjścia. Moje tabele zostały skompresowane do około 10: 1, więc oszczędza dużo miejsca na dysku.

Przekonałem się, że na 4 podstawowych komputerach użycie 4 procesów jest optymalne, choć tylko nieznacznie lepsze niż użycie 3. Jeśli masz dyski SSD lub szybką macierz RAID, prawdopodobnie skalujesz się lepiej.

Kilka innych rzeczy do odnotowania. Jeśli masz dyski sektorowe 4k, upewnij się, że masz key_cache_block_size=4096i myisam_block_size=4K.

Jeśli używasz tabel MyISAM, ustaw wartość myisam_repair_threads = 2lub wyższą. Pozwoli to twoim dodatkowym rdzeniom pomóc w odbudowie indeksów.

Upewnij się, że w ogóle nie zamieniasz. Jeśli tak, zmniejsz rozmiar innodb_buffer_pool_size.

Wydaje mi się, że dzięki innnodb otrzymałem też trochę przyspieszenia:

innodb_flush_method= O_DIRECT (LINUX ONLY)
innodb_flush_log_at_commit = 0
innodb_doublewrite=0
innodb_support_xa=0
innodb_checksums=0

(ostatnich trzech nie testowałem zbytnio - wydaje mi się, że znalazłem je jako sugestie w internecie). Zauważ, że innodb_flush_log_at_commit=0może to spowodować uszkodzenie z powodu awarii mysql lub braku zasilania.

Jeśli masz głównie tabele MyISAM, powinieneś zwiększyć bufor wstawiania zbiorczego . Oto, co mówi Dokumentacja MySQL na temat ustawiania rozmiaru_wystąpienia_wstaw :

MyISAM wykorzystuje specjalną pamięć podręczną podobną do drzewa, aby przyspieszyć wstawianie zbiorcze dla WSTAW ... WYBIERZ, WSTAW ... WARTOŚCI (...), (...), ... i ZAŁADUJ DANE INFILE podczas dodawania danych do niepustych stoły Ta zmienna ogranicza rozmiar drzewa pamięci podręcznej w bajtach na wątek. Ustawienie na 0 wyłącza tę optymalizację. Wartość domyślna to 8 MB.

Musisz zrobić dwie rzeczy

1) Dodaj go do /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

2) Ustaw dla niego globalną wartość

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

Jeśli nie masz uprawnień do globalnego ustawiania wielkości luzem_wstaw_buforu, zrób to

service mysql restart

Oczywiście nie dotyczy to InnoDB.

Z innego punktu widzenia, niezależnie od tego, czy tabele to InnoDB czy MyISAM, jeśli indeksy są większe niż tabela, możesz mieć zbyt wiele indeksów. Zwykle witam gości, że ponowne załadowanie Myysam mysqldump powinno zająć 3 razy więcej czasu niż samo wykonanie mysqldump. Witam również, że ponowne załadowanie mysqldump InnoDB powinno zająć 4 razy więcej czasu niż wykonanie mysqldump.

Jeśli przekraczasz współczynnik 4: 1 w celu przeładowania mysqldump, na pewno masz jeden z dwóch problemów:

• za dużo indeksów
• indeksy są zbyt duże z powodu dużych kolumn

Możesz zmierzyć rozmiar swoich danych za pomocą silnika pamięci za pomocą tego:

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Sprawdź, czy indeksy są prawie tak duże jak dane, czy nawet większe

Możesz również rozważyć wyłączenie rejestrowania binarnego w następujący sposób:

echo "SET SQL_LOG_BIN=0;" > footable.sql
mysqldump --databases foo >> footable.sql

przed ponownym załadowaniem skryptu

Jeśli całkowicie obejdziesz system plików i po prostu potokujesz wyjście mysqldump bezpośrednio do procesu MySQL, powinieneś zauważyć zauważalną poprawę wydajności. Ile ostatecznie zależy od typu używanego dysku, ale rzadko używam plików zrzutu bez względu na rozmiar bazy danych tylko z tego powodu.

mysqldump -uxxx -pxxx -hxxx --single-transaction --routines --databases dbname | mysql -uyyy -pyyy -hyyy

Według moich doświadczeń dysk twardy jest wąskim gardłem. Zapomnij o wirujących dyskach. SSD jest lepszy, ale zdecydowanie najlepiej jest to zrobić w pamięci RAM - jeśli masz wystarczająco dużo miejsca na przechowywanie całej bazy danych przez krótki czas. W przybliżeniu:

1. zatrzymać mysqld
2. usuń istniejącą zawartość / var / lib / mysql
3. utwórz pusty katalog / var / lib / mysql
4. mount -t tmpfs -o size = 32g tmpfs / var / lib / mysql (dostosuj rozmiar)
5. utwórz pusty db (np. mysql_install_db lub przywróć poprzednią zawartość)
6. uruchom mysqld
7. import
8. zatrzymać mysqld
9. skopiuj / var / lib / mysql do mysql2
10. umount mysql; rmdir mysql
11. przenieś mysql2 do mysql
12. uruchom mysqld, bądź szczęśliwy

Dla mnie zrzut ~ 10G (/ var / lib / mysql zużywający ~ 20G) można zaimportować w około 35 minut (mydumper / myloader), 45 minut (mysqldump --tab / mysqlimport), 50 minut (mysqldump / mysql) , na Xeonie 2x6 z rdzeniem 3,2 GHz

Jeśli nie masz wystarczającej ilości pamięci RAM na jednym komputerze, ale masz kilka komputerów obok siebie z szybką siecią, ciekawie byłoby sprawdzić, czy ich pamięci RAM można połączyć z nbd (urządzenie blokujące sieć). Lub za pomocą innodb_file_per_table możesz prawdopodobnie powtórzyć powyższy proces dla każdej tabeli.