MySQL – optymalizacja i wydajność

W poprzednim poście opisywałem w jaki sposób skonfigurować replikację pomiędzy serwerami MySQL w sytuacji, gdy oba serwery są puste. Wiemy, że do skonfigurowania replikacji potrzebujemy informacje o tym, w którym miejscu binlogów powinna się ona rozpocząć. Dziś bardziej życiowy problem – mamy jeden serwer na którym produkcyjnie działa jakaś baza danych. Tą bazę chcemy replikować. Jak się za to zabrać? Problem sprowadza się do tego, że trzeba uzyskać na slave dokładny obraz bazy mastera dla danego, konkretnego miejsca w binlogu. Nie jeden INSERT później, czy też wcześniej. Co można zrobić?

Opcji jest kilka. Najprostszą jest po prostu wyłączenie serwera. Zatrzymujemy serwer MySQL, kopiujemy binarnie zawartość bazy danych z mastera na slave, uruchamiamy mastera tak, aby nie był dostępny dla aplikacji (np. można uruchomić MySQL z opcją skip-networking, zablokować dostęp na firewallu) i uruchomić polecenie SHOW MASTER STATUS; – podane namiary na plik binlogów i pozycję w tym pliku wykorzystujemy do uruchomienia serwera slave. Po uzyskaniu tych danych restartujemy mastera i uruchamiamy go produkcyjnie, a slave można uruchomić w dowolnym momencie. Problem jest taki, że czasami nie da się przenieść danych binarnie, lub też nie jest to wygodne (przykładowo, InnoDB z współdzielonym tablespace w sytuacji gdy na serwerze master działa kilka baz wykorzystujących ten silnik). Trzeba wtedy próbować inaczej.

Drugim rozwiązaniem, będącym wariacją poprzedniego, jest wykonanie snapshotu LVM – zatrzymujemy mastera, robimy snapshot, uruchamiamy mastera a snapshot wykorzystujemy do utworzenia serwera slave (np. tworzymy i uruchamiamy gdzieś na boku kopię serwera master i z tej kopii pobieramy dane dostępowe).

Trzecie, najbardziej chyba popularne, to wykorzystanie mysqldump. Gdy uruchomimy go z opcją ‚–master-data=2’, w komentarzu w zrzucie znajdziemy odpowiednie dane potrzebne do uruchomienia serwera slave. Wygląda to np. tak:

Aby było to możliwe, na czas wykonywania zrzutu wszystkie dumpowane tabele są blokowane tylko do odczytu. W ten sposób zapewnione jest to, że od momentu pobrania danych o pozycji i pliku binlogów do końca wykonywania zrzutu danych żaden rekord nie zostanie zmodyfikowany.

Wszystkie trzy powyższe sposoby mają jedną wspólną cechę. Wymagają zablokowania dostępu do modyfikacji danych na serwerze. Czy to będzie się odbywało przez wyłączenie serwera, czy też przez nałożenie LOCKów na wszystkie tabele, na pewien czas dostęp do serwera master będzie ograniczony. To jest nieodłączny element konfigurowania replikacji od zera, na działających bazach danych – jeśli planujemy dodać drugi serwer i uruchomić replikację, trzeba w planach uwzględnić przestój. Czy będzie to chwila, jak w przypadku wykonywania snapshotu LVM, czy też kilka (naście, dziesiąt) minut, jeśli kopiujemy dane binarnie (lub zrzucamy je mysqldump’em), przestój musi być.

Na koniec taka drobna podpowiedź. Konfigurując replikację można natknąć się na ustawienia takie jak:

O co z tym chodzi? W ogólności, chodzi tu o możliwość filtrowania tego, co jest replikowane. Możemy ustawić, jaka baza danych będzie replikowana, bądź też jakie nie powinny być. To samo możemy definiować na poziomie tabel. Podobne zmienne (binlog-do-db, binlog-ignore-db) można ustawiać dla binlogów – chodzi tu o konfigurację tego, co w ogóle do binlogów trafia, lub trafić nie powinno. W teorii wszystko ładnie działa. W praktyce, lepiej nie korzystać z tego typu możliwości. Dlaczego? Załóżmy, że mamy ustawione takie coś:

Wykonujemy następujące zapytanie:

Ku zdziwieniu co poniektórych administratorów, zapytanie to nie replikuje się. Dlaczego? Przecież dodajemy rekord do bazy ‚replikowana_baza’!? Problem w tym, że dla tego typu filtrowania MySQL bierze pod uwagę tylko i wyłącznie ustawienie aktywnej bazy przy pomocy USE  baza_danych;. Jeśli wykorzystamy, skąd innąd zupełnie poprawną składnię: baza_danych.tabela MySQL nie załapie, że właśnie odwołujemy się do innej bazy niż ta, na którą ostatnio przeszliśmy przy pomocy USE;. Dlatego też właśnie zalecane jest ograniczenie korzystania z tego typu funkcjonalności do minimum, najlepiej do zera. Szczególnie tyczy się to środowiska, w którym nie możemy być pewni jakie zapytanie pójdzie do bazy. Może się szybko okazać, że tak ładnie skonfigurowana replikacja rozjedzie się i trzeba będzie całość ustawiać od zera.