Pewien czas temu miałem okazję zetknąć się z kolejnym przeciążonym serwerem. Ni z tego ni z owego obciążenie podsystemu dyskowego wzrosło kilkukrotnie, co spowodowało praktycznie pad całej maszyny fizycznej. Na szczęście serwer był monitorowany, tak więc miałem dostęp do danych z pewnego okresu czasu dotyczących stanu serwera – widać w nich było pewien wzrost zapotrzebowania na pamięć przez serwer Apache, który współdzielił serwer z MySQL. Co się stało?

Po krótkiej analizie slowlogów sprawa okazała się jasna. Do serwera MySQL szła spora ilość zapytań typu:

DELETE FROM tabela WHERE kolumna='%cos%';

Dodatkowo, tabela ta działała na silniku MyISAM. Co tu jest nie tak? Po pierwsze, tego typu zapytanie zawsze wiązać się będzie z pełnym skanem tabeli. W tym wypadku było to ponad 100 tysięcy rekordów i kilkanaście MB danych. Pisałem pewien czas temu, że zapytania modyfikujące dane typu UPDATE czy DELETE, w którym stosowany jest warunek WHERE może korzystać z indeksu. W tym konkretnym wypadku, jako że twórca zapytania zastosował lewostronny wildcard, wykorzystanie indeksu nie było możliwe.

Do momentu kiedy zawartość tabeli `tabela` znajdowała się w cache systemu operacyjnego, serwer działał poprawnie – dane brane były z pamięci. W momencie gdy inny element systemu zwiększył swoje zapotrzebowanie na pamięć okazało się, że na dane z MySQL miejsca pozostało mniej – zarówno Apache jak i MySQL musiały sobie poradzić z mniejszym cache dyskowym. Poskutkowało to zwiększeniem obciążenia na dysku – zawartość problematycznej tabeli zamiast siedzieć w pamięci musiała być odczytana z dysku. To okazało się za dużo dla serwera.

Dlaczego wspominałem o silniku tej tabeli? MyISAM nie posiada bufora dla danych, potrafi buforować tylko indeksy. Jako bufor dla danych wykorzystuje cache systemu, o który musi konkurować z innymi usługami działającymi na danym serwerze. InnoDB potrafi przechowywać w pamięci także dane. Buffer pool jest alokowany przez MySQL i nie jest w żaden sposób współdzielony. Gdyby dana tabela działała na silniku InnoDB byłaby możliwość trzymania wszystkich potrzebnych danych w pamięci. Odbywałoby się to oczywiście kosztem wolnej pamięci przeznaczonej przez system na cache dyskowy, tak więc nie mam pewności czy nie poskutkowałoby to tym samym, ale byłaby przynajmniej szansa spróbować coś z tym zrobić.

Oczywiście, jedynym sensownym rozwiązaniem tego typu problemu nie jest kombinowanie z cache tylko przepisanie zapytań. Zamiast szukać tekst z obustronnym wildcardem można przecież zastosować wyszukiwanie pełnotekstowe dostępne w MyISAM i najpierw pobrać SELECTem id rekordów, które będą kasowane (przeszukując tabelę `tabela` przy pomocy indeksu pełnotekstowego), a następnie uruchomić odpowiednie DELETE, wybierając odpowiednie rekordy przy pomocy klucza głównego.

Jaki wniosek z powyższego można wyciągnąć? Niedoróbki w zapytaniach i nieoptymalne rozwiązania można zamaskować sprzętem – większą ilością rdzeni w procesorze, większą ilością pamięci, szybszymi dyskami. Prędzej czy później jednak wyjdą one na jaw i problem się pojawi. Optymalizujmy wszystko od zera, w momencie tworzenia. Zaoszczędzimy sobie nerwów gdy okaże się że wdrożony już produkcyjnie system przestanie wydalać i trzeba będzie na wczoraj szukać jakichś rozwiązań aby serwis funkcjonował