Mam ten ogromny zrzut 32 GB SQL, który muszę zaimportować do MySQL. Nie musiałem wcześniej importować tak dużego zrzutu SQL. Zrobiłem zwykle:

mysql -uroot dbname < dbname.sql

To trwa zbyt długo. Jest stół z około 300 milionami wierszy, osiągnął 1,5 miliona w około 3 godziny. Wygląda więc na to, że cała sprawa zajęłaby 600 godzin (to 24 dni) i jest niepraktyczna. Więc moje pytanie brzmi: czy jest na to szybszy sposób?

Dalsze informacje / ustalenia

1. Wszystkie tabele są InnoDB i nie zdefiniowano żadnych kluczy obcych. Istnieje jednak wiele indeksów.
2. Nie mam dostępu do oryginalnego serwera i bazy danych, więc nie mogę wykonać nowej kopii zapasowej ani wykonać „gorącej” kopii itp.
3. Ustawienie innodb_flush_log_at_trx_commit = 2sugerowane tutaj wydaje się nie wprowadzać (wyraźnie widocznej / wykładniczej) poprawy.
4. Statystyki serwera podczas importu (z MySQL Workbench): https://imgflip.com/gif/ed0c8 .
5. Wersja MySQL to społeczność 5.6.20.
6. innodb_buffer_pool_size = 16M i innodb_log_buffer_size = 8M. Czy muszę je zwiększyć?

Vadim Tkachenko z Percony wykonał tę wspaniałą graficzną reprezentację InnoDB

Zdecydowanie musisz zmienić następujące elementy

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Dlaczego te ustawienia?

• innodb_buffer_pool_size będzie buforować często odczytywane dane
• innodb_log_buffer_size : Większy bufor zmniejsza zapis operacji we / wy w dziennikach transakcji
• innodb_log_file_size : Większy plik dziennika zmniejsza punkt kontrolny i zapisuje operacje we / wy
• innodb_write_io_threads : Operacje zapisu usługi do .ibdplików. Zgodnie z dokumentacją MySQLConfiguring the Number of Background InnoDB I/O Threads , każdy wątek może obsłużyć do 256 oczekujących żądań We / Wy. Domyślna wartość dla MySQL to 4, 8 dla serwera Percona. Max ma 64 lata.
• innodb_flush_log_at_trx_commit
  • W przypadku awarii zarówno 0, jak i 2 mogą utracić jedną sekundę danych.
  • Kompromis polega na tym, że zarówno 0, jak i 2 zwiększają wydajność zapisu.
  • Wybieram 0 powyżej 2, ponieważ 0 opróżnia bufor dziennika InnoDB do dzienników transakcji (ib_logfile0, ib_logfile1) raz na sekundę, z zatwierdzeniem lub bez. Ustawienie 2 opróżnia bufor dziennika InnoDB tylko po zatwierdzeniu. Istnieją inne zalety ustawienia 0, o którym wspomina @jynus, były instruktor Percona .

Uruchom ponownie mysql w ten sposób

service mysql restart --innodb-doublewrite=0

To wyłącza podwójny bufor zapisu InnoDB

Zaimportuj swoje dane. Po zakończeniu uruchom ponownie mysql normalnie

service mysql restart

Włącza to podwójny bufor zapisu InnoDB

Spróbuj !!!

UWAGA BOCZNA : Należy zaktualizować do wersji 5.6.21, aby uzyskać najnowsze poprawki bezpieczeństwa .

Czy naprawdę potrzebujesz przywrócić całą bazę danych? Jeśli nie, mój 2c:

Możesz wyodrębnić określone tabele, aby wykonać przywracanie „fragmentów”. Coś takiego:

zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql

Zrobiłem to raz i zajęło mi to 10 minut, aby wyodrębnić tabelę, której potrzebowałem - moje pełne przywrócenie zajęło 13 ~ 14 godzin, z zrzutem 35 GB (zgzipowanym).

Parametr /pattern/,/pattern/pwith -ntworzy plasterek „między wzorami” - łącznie z nimi.

W każdym razie, aby przywrócić 35 GB, użyłem maszyny AWS EC2 (c3,8xlarge), zainstalowałem Percona przez yum (Centos) i właśnie dodałem / zmieniłem następujące linie my.cnf:

max_allowed_packet=256M
wait_timeout=30000

Myślę, że liczby są o wiele za wysokie, ale działały na moją konfigurację.

Najszybszym sposobem na zaimportowanie bazy danych jest skopiowanie plików (.frm, .MYD, .MYI), jeśli MyISAM, bezpośrednio do / var / lib / mysql / „nazwa bazy danych”.

W przeciwnym razie możesz spróbować: mysql > use database_name; \. /path/to/file.sql

To kolejny sposób na import danych.

Jednym ze sposobów przyspieszenia importu jest zablokowanie tabeli podczas importowania. użyj opcji --add-locks do mysqldump.

mysqldump --add-drop-table --add-locks --database db > db.sql

lub możesz włączyć kilka przydatnych parametrów za pomocą --opt to włącza kilka przydatnych rzeczy dla zrzutu.

mysqldump --opt --database db > db.sql

Jeśli masz inne urządzenie pamięci na serwerze, skorzystaj z niego - kopiowanie z jednego urządzenia na drugie jest sposobem na przyspieszenie transferu.

możesz również odfiltrować tabele, które nie są wymagane za pomocą --ignore-table