Wolna baza MySQL: jak znaleźć ciężkie zapytania

Typowy scenariusz wygląda tak: strona ładuje się coraz wolniej, panel administracyjny męczy się bardziej niż część publiczna, a wykres obciążenia serwera nie pokazuje niczego dramatycznego. W większości takich przypadków winne jest kilka zapytań, które przeglądają całą tabelę zamiast sięgnąć po indeks, oraz tabele, które rosną od lat, bo nikt ich nigdy nie sprzątał. Poniżej praktyczna kolejność działań, od znalezienia winowajcy do naprawy.

Zacznij od pomiaru, nie od zgadywania

Pierwszym krokiem nie jest optymalizacja, tylko log wolnych zapytań. Na serwerze, gdzie masz dostęp do konfiguracji, włączasz go tak:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Ustawienie long_query_time = 1 zapisuje wszystko, co trwa dłużej niż sekundę. Na początek to dobry próg. Jeśli plik po godzinie jest pusty, obniż do 0,3 sekundy i poczekaj na ruch produkcyjny, bo to on ujawnia problemy, a nie Twoje własne kliknięcia.

Na hostingu współdzielonym konfiguracji serwera nie zmienisz, ale w WordPressie możesz posłużyć się wtyczką profilującą zapytania w ramach jednego żądania. To wystarczy, żeby zobaczyć, która wtyczka odpowiada za najcięższe odpytania.

EXPLAIN mówi, dlaczego zapytanie jest wolne

Mając kandydata, poprzedź go słowem EXPLAIN:

EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = 'cena' AND meta_value > 100;

Patrz na trzy kolumny. type o wartości ALL oznacza przejście przez całą tabelę, czyli najgorszy możliwy przypadek. rows pokazuje, ile wierszy baza spodziewa się przejrzeć: jeśli to dziesiątki tysięcy przy zapytaniu zwracającym dziesięć wyników, masz odpowiedź. key mówi, którego indeksu użyto, a pusta wartość znaczy, że żadnego.

Powyższe zapytanie jest przy okazji klasykiem sklepów: wp_postmeta bywa największą tabelą w całej bazie, a porównywanie wartości tekstowej jak liczby uniemożliwia sensowne użycie indeksu.

Indeks pomaga, dopóki nie zaczyna szkodzić

Indeks przyspiesza odczyt i spowalnia zapis, bo przy każdym dodaniu wiersza trzeba go zaktualizować. Stąd kilka praktycznych zasad.

Zakładaj indeks na kolumnach, po których faktycznie filtrujesz i sortujesz, a nie na wszystkich naraz. Przy warunkach łączących kilka kolumn jeden indeks złożony jest skuteczniejszy niż kilka pojedynczych, ale kolejność kolumn w takim indeksie ma znaczenie. Nie dubluj indeksów: jeśli istnieje indeks na (a, b), osobny indeks na (a) jest zbędny.

Sprawdź też, co już masz, poleceniem SHOW INDEX FROM nazwa_tabeli. W bazach, które przeszły przez kilka wtyczek i kilka migracji, regularnie znajdują się indeksy założone dwa razy pod różnymi nazwami.

Tabele, które rosną, gdy nikt nie patrzy

W WordPressie jest kilka stałych podejrzanych:

• wp_postmeta i wp_options przy sklepach oraz przy wtyczkach trzymających tam swoje dane,
• rewizje wpisów, domyślnie zapisywane bez ograniczenia liczby,
• tabele logów dokładane przez wtyczki bezpieczeństwa i formularze kontaktowe,
• pozostałości po wtyczkach odinstalowanych lata temu, których tabele nikt nie usunął,
• wpisy w wp_options z ustawioną automatyczną wczytywaną wartością, ładowane przy każdym żądaniu.

Ostatni punkt jest wyjątkowo kosztowny, bo takie wpisy baza czyta przy generowaniu każdej strony. Kilkanaście megabajtów danych ładowanych bez potrzeby przy każdym żądaniu potrafi zrobić z szybkiego serwisu wolny. Liczbę rewizji ograniczysz jedną linią w wp-config.php: define('WP_POST_REVISIONS', 5);.

Zanim cokolwiek skasujesz, zrób kopię bazy i przećwicz jej odtworzenie, a nie tylko utworzenie. Procedurę opisaliśmy we wpisie o backupie bazy danych.

Kiedy to już nie jest wina bazy

Są objawy, po których widać, że optymalizacja zapytań niczego nie da, bo problemem jest ilość pamięci.

Baza trzyma w pamięci bufor z najczęściej używanymi danymi. Gdy pamięci jest mało, bufor jest mały i te same dane czytane są z dysku w kółko. Objaw: zapytania są wolne równomiernie, a nie punktowo, i przyspieszają dopiero po kilku powtórzeniach.

Warto zestawić to z limitami pakietu. Cloud Starter ma 512 MB RAM, a Cloud Elite 1,5 GB, i te wartości dzielą się na PHP oraz wszystko pozostałe. Sklep z dużym katalogiem po prostu nie zmieści się sensownie w dolnym pakiecie, niezależnie od tego, jak dobrze napiszesz zapytania. Wtedy właściwym ruchem jest wyższy plan albo serwer VPS, gdzie pamięć przydzielasz sam i możesz dostroić konfigurację bazy pod swój profil ruchu.

Podsumowanie

Optymalizacja bazy danych MySQL ma zawsze tę samą kolejność: zmierz, znajdź najcięższe zapytanie, sprawdź je poleceniem EXPLAIN, dołóż brakujący indeks, posprzątaj tabele, które urosły bez kontroli. Dopiero gdy to nie pomoże, patrz na zasoby.

Jeśli serwis mieści się w ramach pakietu, hosting RapidDC obsłuży go z wersjami PHP do 8.3 i kopią zapasową do trzech dni wstecz. Gdy baza wyraźnie nie mieści się w pamięci, sensowniejszy jest VPS z pełną wirtualizacją KVM i zasobami przydzielonymi wyłącznie Tobie. Podstawy zakładania i obsługi bazy zebraliśmy we wpisie o tworzeniu bazy danych na hostingu.

FAQ – najczęściej zadawane pytania o optymalizację MySQL

Czy warto uruchamiać OPTIMIZE TABLE?

Czasami, ale rzadziej niż się sądzi. Polecenie odzyskuje miejsce po usuniętych wierszach i bywa przydatne po dużym czyszczeniu. Na tabeli aktywnie używanej zakłada blokadę, więc uruchamiaj je poza godzinami szczytu.

Ile rewizji wpisów powinienem zostawić?

Od trzech do pięciu w zupełności wystarcza w redakcyjnej pracy. Domyślnie WordPress nie ma żadnego limitu, przez co pojedynczy długi wpis potrafi mieć kilkadziesiąt kopii w bazie.

Czy wtyczki do czyszczenia bazy są bezpieczne?

Te popularne tak, pod warunkiem że przed uruchomieniem zrobisz kopię. Ostrożnie podchodź do funkcji usuwających osierocone metadane, bo część wtyczek trzyma tam dane celowo i skasowanie ich bywa nieodwracalne.

Jak duża baza jest za duża na hosting współdzielony?

Nie decyduje sam rozmiar, tylko relacja między aktywnie używanymi danymi a dostępną pamięcią. Baza o wielkości kilkuset megabajtów z kilkoma ciężkimi zapytaniami sprawi więcej kłopotu niż kilkugigabajtowa baza odczytywana rzadko.

Czy przejście na nowszą wersję PHP przyspieszy bazę?

Nie przyspieszy samej bazy, ale skróci czas przetwarzania wyników po stronie aplikacji, co bywa odczuwalne. To zmiana warta wykonania niezależnie, choć nie rozwiąże problemu zapytania przeglądającego całą tabelę.

Artykuł powstał z wykorzystaniem narzędzi sztucznej inteligencji (AI) pod nadzorem zespołu RapidDC. Grafika ilustracyjna została wygenerowana przez AI.