Rozmiar transakcji MySQL - jak duży jest za duży?

Mam proces importowania, który przebiega tak często i chcę, aby była to transakcja typu „wszystko albo nic”, czyli transakcja.

Istnieje wiele aspektów, a import może przynieść wyniki w przedziale od 100 tys. Odpowiada to ładowności od kilku MB do kilkuset MB danych.

Wiem, że tabele tymczasowe są inną opcją - ale ta metoda wydaje się bardzo przydatna.

Czy są jakieś zastrzeżenia, które należy brać pod uwagę w związku z tego rodzaju praktyką z dużą ilością manipulacji danymi między zatwierdzeniami? (Poza typową serią obciążenia zapisu / indeksowania po zatwierdzeniu)

— cienki lód
źródło

Osobiście lubię mieć równowagę. Importuję w transakcjach 1k lub 10k, ponieważ po prostu wiem, że dotarłby do około 900k wierszy, a następnie zawiesił się z powodu rozmiaru bufora lub czegoś innego śmiesznego. Całkiem łatwo się z tego wyłapać, a nie tyle I / O.

— Kapitan Hypertext

Jednym z wąskich gardeł, o których należy pamiętać, jest bufor dziennika InnoDB. Rozmiar jest ustawiany przez innodb_log_buffer_size . Oto, co mówi o tym Dokumentacja MySQL:

Rozmiar w bajtach bufora używanego przez InnoDB do zapisywania w plikach dziennika na dysku. Wartość domyślna to 8 MB. Duży bufor dziennika umożliwia uruchomienie dużych transakcji bez konieczności zapisywania dziennika na dysku przed zatwierdzeniem transakcji. Tak więc, jeśli masz duże transakcje, zwiększenie bufora dziennika oszczędza dyskowe operacje we / wy.

Bufora dziennika InnoDB nie należy mylić z pulą buforów InnoDB. Główną różnicą między nimi jest ich cel. Bufor dziennika InnoDB zasadniczo rejestruje zmiany krótkoterminowe, które są zapisywane w dziennikach ponawiania (ib_logfile0, ib_logfile1). Pula buforów InnoDB (rozmiar według innodb_buffer_pool_size ) buforuje dane i strony indeksowe, które mają zostać zatwierdzone (jeśli strony są brudne) i ostatecznie zapisane na dysk. Po zatwierdzeniu strony zmian pozostają w pamięci RAM, dopóki nie zostaną usunięte za pomocą reguł LRU.

Duże transakcje muszą przechodzić przez bufor dziennika. Jak wspomniano, większy bufor dziennika zmniejszy dyskowe operacje we / wy. Tylko duże zatwierdzenie stanowiłoby wąskie gardło.

Możesz zajrzeć do innych opcji InnoDB do skonfigurowania.

innodb_flush_method : To decyduje o tym, jak obsługiwane jest opróżnianie i czy zezwolić systemowi na buforowanie zapisów do InnoDB ( musisz eksperymentować między O_DSYNC i O_DIRECT )
innodb_read_io_threads (użyj maks. 64)
innodb_write_io_threads (użyj maks. 64)
innodb_io_capacity (użyj wyższych wartości, takich jak 5000 lub 10000, ale sprzęt da ci przewagę , innymi słowy, pozwól sprzętowi zdecydować o wydajności IO po przypisaniu dużej liczby)
innodb_thread_concurrency : Wartość domyślna to 0. Nauczyłem się z pierwszej ręki od Ronalda Bradforda z Percona NYC 2011, aby pozostawić tę liczbę na 0. Pozwala to InnoDB wybrać najlepszą liczbę biletów współbieżności, jaką może obsłużyć)

Mam inne posty na temat optymalizacji InnoDB do dalszych badań

— RolandoMySQLDBA
źródło

jakoś wiedziałem, że będziesz na tym. Dzięki za dokładne odpowiedzi, które zawsze wydajesz. Pytanie poboczne: Czy masz jakieś zasoby dotyczące korzystania z innodb_io_capacity? Kiedy dokumentacja sugeruje, że SATA konsumenckie 5400 / 7200RPM ma wartość 100, czy sugerujesz strategię, aby po prostu „usunąć limit”, ustawiając tak wysoką wartość?

— cienki

Zazwyczaj ustawiam innodb_io_capacity na wyższą i pozwalam sprzętowi na to. Dodam to teraz do mojej odpowiedzi.

— RolandoMySQLDBA