Replikacja MySQL jako zwiększenie wydajności systemu

Replikacja jako zwiększenie wydajności MySQL.

W poprzednim artykule omówiłem podstawy replikacji serwera MySQL, oraz sposób jej uruchomienia i podstawowej konfiguracji. Poza tworzeniem kopii bezpieczeństwa na bieżąco, można użyć replikacji również do zwiększenia wydajności systemu.

Najpierw nieco teorii...

MySQL (omawiam tu tabele MyISAM których używam) może odczytywać dane współbieżnie, dla wielu klientów, jednak proces zapisu musi odbywać się jako pojedynczy proces (w dość dużym uogólnieniu). Niesie to za sobą następujące konsekwencje - instrukcja wstawienia/aktualizacji danych musi czekać aż zakończą się wszystkie rozpoczęte instrukcje odczytu. Podczas instrukcji wstawiania/aktualizacji danych wszystkie zgłoszone instrukcje odczytywania danych oczekują na zakończenie aktualizacji.
Nie ma problemu jeśli aktualizacje danych są sporadyczne, jednak w przypadku serwera który administruje, aktualizacje danych są dość częste (insert około 1000, update około 7000, delete około 5000 na godzinę - a są to wartości średnie, faktyczne przetwarzanie odbywa się między 10:00 a 21:00). Przy około 70 jednocześnie połączonych klientach (ponad 50000 wszystkich zapytań na godzinę) spadek wydajności był już nie tylko widoczny, ale stał się chwilami nieco uciążliwy - w najbliższej perspektywie przewidywane jest zwiększenie ilości danych o minimum 30%. Należało więc podjąć kroki nie tylko w kierunku rozwiązania obecnych problemów, ale również zabezpieczyć system przed przeciążeniem po spodziewanym przyroście danych. Po oczywistej w takim przypadku optymalizacji zapytań oraz dokładnej analizie indeksów, przyszedł czas na dalsze kroki...

Mamy już uruchomiony i wydajnie pracujący serwer replikacji, kolejnym krokiem jest więc użycie go do zwiększenia wydajności całego systemu. Jak wiemy, replikacja odbywa się w oparciu o dzienniki binarne, które odczytywane sa przez wątek serwera replikacji - konieczne więc były pewne nakłady sprzętowe (niewielkie) dla podniesienia wydajności sprzętowej replikacji - taką 'inwestycją' była instalacja w serwerze replikacji karty sieciowej 1GB (serwer główny już taką posiadał, jak na porządny serwer przystało) - tak więc ruch miedzy serwerami MySQL odbywa sie bardzo wydajnym łączem 1GB - opóźnienie replikacji jest więc praktycznie niezauważalne.

Teraz przejdźmy do sedna sprawy - jak za pomocą replikacji zwiększyć wydajnoć całego systemu? Pamietamy, że problem powstaje w chwili operacji zapisu, które blokują tabele. Rozwiązanie jest więc banalnie proste - należy rozdzielić zapytania modyfikujące i czytające z bazy danych. Wszelkie operacje zmieniające (wstawiające, kasujące) dane wykonywane są na serwerze MASTER, natomiast instrukcje 'select' wykonywane są na serwerze SLAVE. Serwer MASTER nie jest blokowany dużą ilością odczytów (ponad 30.000 na godzinę), natomiast tabele na serwerze SLAVE nie są blokowane przez operacje wstawiania/modyfikacji danych. Tabele zawierające dane konfiguracyjne aktualizowane są sporadycznie, nie ma więc znaczenia z którego serwera są odczytywane (np. słowniki, uprawnienia itp...).

Operacja ta wymaga oczywiście zmiany aplikacji klienckich, ale to koszt który warto ponieść, co więcej, zmianę taką można realizować stopniowo. Tu może pare słów o samym rozwiązaniu. Postanowiłem potraktować sprawę rozwojowo, tak aby możliwe było w przyszłości dodanie kolejnych serwerów replikacji i rozdzielanie zapytań pomiędzy kilka serwerów SLAVE. Wszystkie instrukcje SQL które wykonywane są przez aplikacje klienckie od początku przechowywane są w osobne tabeli i odczytywane z niej kiedy są potrzebne. Ma to oczywiście ten minus, ze zwiększa ilość instrukcji 'select', jednak plusem który przeważył przy podejmowaniu decyzji o takim rozwiązaniu jest niezwykła łatwość modyfikacji/optymalizacji instrukcji bez żadnej ingerencji w kod aplikacji klienckich, a nawet bez ich zatrzymywania. Konieczna była modernizacja tej tabeli, a konkretnie uzupełnienie jej o jedno pole wskazujące na jakim (celowo pisze na jakim, a nie na którym) serwerze instrukcja ma zostać wykonana. Otwiera to możliwoć przypisywania w dowolnej chwili różnych serwerów w zależnoci od obciążenia systemu. Koszt aplikacji która zarządzać będzie przydzielaniem serwerów dla instrukcji 'select' jest praktycznie zerowy - pamiętajmy, że aplikacja ta będzie pobierać dane nie z serwera MASTER, ale z serwerów SLAVE - będą to dane dotyczące uruchomionych procesów, oraz inne, mówiące o obecnym obciążeniu serwera. Okresy czasu w jakim należy pobierać te dane można dobrać eksperymentalnie, a nawet zmieniać w trakcie działania automatycznie (prosty algorytm porównujący zmiany w czasie). Nawet przy aktualizacji prowadzonej co 30 sekund, ilość zapytań wzrasta zaledwie o 120 na godzinę na każdy serwer - niewiele, prawda?

Na obecnym etapie MySQL (5.0) nie polecam aktualizacji danych na wielu serwerach jednocześnie, chociaż replikacja w układzie A->B B->C C->A jest jak najbardziej możliwa, to jednoczesne wstawianie rekordów gdzie klucz główny autoincrement jest jednocześnie kluczem dla tabeli podrzędnej może spowodować konflikt tych kluczy i trudną do przewidzenia utratę spójności danych. Oczywiście nic nie stoi na przeszkodzie, aby zestawić replikacje jak powyżej, przy czym wydzielić tylko serwer 'A' dla instrukcji wstawiania danych operujących na 'wrażliwych' kluczach, modyfikacje na serwer 'B', a odczyty na serwer 'C' lub kilka serwerów SLAVE.

Kolejnym zadaniem które będę realizował, to utworzenie właśnie takiego łańcucha replikacji, wraz z aplikacją pozwalającą nie tylko na równomierne rozłożenie obciążenia serwerów, ale również na płynną i bezawaryjną pracę systemu w przypadku awarii lub wyłączenia jednego z serwerów.