Użytkownicy narzekają, że system działa wolno, gdy trwa mysqldump

Baza danych MYSQL (ibdata1) ma rozmiar 73 GB i jest skonfigurowana do działania jako dedykowany serwer bazy danych w systemie Windows 2008 O / S dla tabel INNODB. Wykonujemy kopię zapasową przy użyciu mysqldump mysqldump --skip-opt --quick --single-transakcja --create-options --extended-insert --disable-keys --add-drop-table --complete-insert - set-charset - skompresuj --log-error = Proddb0635.err -u root -pjohndoe Proddb> \ devNas \ devNas \ sqlbackup \ LIVE \ db \ Proddb0635.sql

Plik kopii zapasowej Proddb0635.sql jest przechowywany na innym serwerze niż serwer bazy danych. Pamięć RAM wynosi 12 GB. Rozmiar puli buforów INNODB wynosi 6 GB. Dodatkowa pula pamięci wynosi 32 MB. Rozmiar bufora zapytań wynosi 2 GB Długość bufora netto wynosi 16 M Max. wielkość pakietu 1 GB.

Wersja mysql to 5.0.67.

Gdy kopia zapasowa nie działa, użytkownicy są zadowoleni z wydajności.

Gdy kopia zapasowa jest uruchomiona, wskaźnik trafień puli buforów INNODB jest wysoki blisko 100%. Brak oczekujących odczytów lub oczekujących zapisów. innodb czeka za darmo na 0. Zużycie procesora nie jest wysokie min. 9% do maks. 15% Wskaźnik trafień w pamięć podręczną zapytania jest niski około 40% z uruchomionym mysqlbackup lub bez niego. Obecnie Menedżer zadań Windows wyświetla, że ​​używa 10 GB pamięci RAM. Czy należy zwiększyć pamięć podręczną zapytań, mając do dyspozycji tylko 2 GB pamięci RAM? mysqlld-nt zajmuje 9,2 GB pamięci RAM, a mysqldump zajmuje 5 MB pamięci RAM. Alos, zauważył, że rozmiar pliku zrzutu jest taki sam w obecności lub bez opcji --compress.

Czy powinienem zmniejszyć rozmiar puli buforów iNNODB?

Dzięki

W systemie Windows znany jest problem polegający na tym, że po wypchnięciu dużego pliku na inny serwer cała pamięć zostaje przydzielona do pamięci podręcznej systemu zamiast procesów użytkownika. W sekcji Pamięć fizyczna (MB) menedżera zadań można sprawdzić, ile pamięci jest przydzielone do pamięci podręcznej systemu.

Można to rozwiązać, wykonując kopię zapasową na dysku lokalnym, a następnie ściągając ten plik ze zdalnego komputera.

Oto kilka przemyśleń na temat poprawy wydajności mysqldump, biorąc pod uwagę twoje okoliczności. Oto twoje polecenie:

mysqldump --skip-opt --quick --single-transaction --create-options --extended-insert --disable-keys --add-drop-table --complete-insert --set-charset --compress - -log-error = Proddb0635.err -u root -pjohndoe Proddb> \ devNas \ devNas \ sqlbackup \ LIVE \ db \ Proddb0635.sql

Pierwszą rzeczą, jaką zauważam, jest przekierowanie danych wyjściowych do systemu plików. Mówi „devNas”, więc zakładam, że jest to Network Attached Storage . Jestem fanem NAS do tworzenia kopii zapasowych, ale musi być podłączony na osobnej fizycznej karcie sieciowej od ruchu produkcyjnego . Być może nie nasycasz przepustowości, ale nadal konkurują. Będzie to stanowić większy problem ze względu na flagę --quick, ponieważ opróżnia każdy wiersz zamiast trzymać go w pamięci.

Następną rzeczą, którą widzę, jest to, że wywołałeś - kompresuj. Wygląda na to, że używasz mysql lokalnie, ponieważ nie użyłeś przełącznika -h. Może to wykorzystywać lokalny procesor, który jest niepotrzebny w tym kontekście. Czy konieczne jest --kompres? Kompresuje tylko dane między klientem mysqldump a serwerem mysql, a nie zawartość pliku.

Następnie widzę, że używasz flagi --single-transaction. Spowoduje to dodatkowy procesor, ponieważ testuje on każdy wybór jako część mysqldump.

Nie ma to nic wspólnego z wydajnością, ale używasz --disable-keys, która działa tylko w MyISAM ( manual ).

Możesz poeksperymentować z uruchomieniem mysqldump zdalnie z hosta offline i przeniesieniem pliku zrzutu na serwer NAS po zakończeniu, aby usunąć jak najwięcej tej operacji z pasma.

OBSERWACJA # 1

Należy o tym pamiętać, wykonując mysqldump przeciwko InnoDB.

Jakiekolwiek brudne strony istnieją w puli buforów InnoDB, należy je najpierw opróżnić na dysk. Mysqldump uruchomi opróżnianie tabeli InnoDB, która wciąż zawiera brudne strony.

Istnieje opcja serwera o nazwie innodb_max_dirty_pages_pct . Domyślna wartość to 75 to MySQL 5.5 i 90 w wersjach MySQL wcześniejszych niż 5.5. W środowisku produkcyjnym można pozostawić tę liczbę z wartością domyślną.

Aby sprawdzić, czy masz wiele brudnych stron w puli buforów InnoDB, uruchom to:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

BTW strona ma 16K, jak wynika z tego:

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

Jeśli chodzi o InnoDB i mysqldump, możesz obniżyć tę liczbę w dwóch okolicznościach.

OKOLICA 1: Ustaw na stałe na 0

Po prostu dodaj to do my.ini:

[mysqld]
innodb_max_dirty_pages_pct=0

Dzięki temu pula buforów InnoDB będzie uboga i wredna. Etap opróżniania tabeli InnoDB, który jest zrzucany, będzie szybki, ponieważ kilka brudnych stron, jak to możliwe (być może 0), będzie musiało zostać opróżnionych przed uruchomieniem na nim mysqldump.

Jedyną wadą jest to, że jeśli wykonujesz mysqldump z bazy danych o dużym natężeniu ruchu, może wystąpić mniejszy wzrost liczby operacji we / wy zapisu z powodu częstszego usuwania brudnej strony. Możesz ustalić, czy tak jest bez restartowania mysql, uruchamiając to:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Pozostaw ustawienie na 12–24 godzin, jeśli wydajność zapisu jest do zaakceptowania, możesz zacząć. Jeśli nie, ustaw go z powrotem za pomocą:

SET GLOBAL innodb_max_dirty_pages_pct = 90;

OKOLICZNOŚĆ 2: Ustaw na 0 około 1 godziny przed mysqldump

SET GLOBAL innodb_max_dirty_pages_pct = 0;

Uruchom mysqldump

SET GLOBAL innodb_max_dirty_pages_pct = 90;

OBSERWACJA # 2

Masz opcję --complete-insert jako opcję mysqldump. Spowoduje to osadzenie nazw kolumn w każdym stanie INSERT przed klauzulą ​​VALUES. Nawet z --extended-insert, w każdej wstawianej partii wierszy nazwy kolumn są wysyłane do mysqldump. Możesz zmniejszyć liczbę bajtów wysyłanych do mysqldump, usuwając --complete-insert.

REKOMENDACJE

Jeśli masz inny system Windows Server, który można skonfigurować jako urządzenie podrzędne, wykonaj mysqldumpy z tego urządzenia podrzędnego, a nie z komputera produkcyjnego.