Jak znaleźć obciążające zapytania MySQL/MariaDB i dodać właściwy indeks

Objaw: równoległe zapytania trwające kilkanaście minut

Gdy aplikacja długo czeka na odpowiedź bazy, użytkownicy i zadania działające w tle mogą uruchamiać kolejne kopie tego samego zapytania. Jeżeli każde wykonanie trwa 10–20 minut, nowe kopie zaczynają działać, zanim poprzednie się zakończą. Baza ma wtedy coraz więcej pracy, a czas odpowiedzi rośnie jeszcze bardziej.

Dwa wzorce zasługują na szczególną uwagę. Pierwszy pobiera ostatni wpis historii dla wskazanego rekordu:

SELECT *
FROM history_entries
WHERE parent_id = ?
ORDER BY id DESC
LIMIT 1;

Drugi wzorzec wyszukuje dokument po UUID, czyli jego identyfikatorze. Aplikacja może używać zwykłego porównania = albo operatora LIKE. Jeżeli parametr LIKE zawiera pełny UUID bez symboli wieloznacznych % i _, jest to w praktyce wyszukiwanie dokładnej wartości:

SELECT *
FROM documents
WHERE uuid = ?
  AND deleted_at IS NULL
LIMIT 1;

SELECT COUNT(*)
FROM documents
WHERE uuid LIKE ?
  AND deleted_at IS NULL;

Sam długi czas wykonania nie wskazuje jeszcze przyczyny. Zapytanie może czytać całą tabelę z powodu braku indeksu, czekać na blokadę założoną przez inną transakcję albo zwalniać, ponieważ dysk nie nadąża z odczytem danych. Dlatego najpierw zbieramy informacje o bazie i systemie, nie zmieniając ich konfiguracji.

Najpierw diagnostyka tylko do odczytu

Najpierw sprawdzamy, co MariaDB wykonuje w tej chwili. SHOW FULL PROCESSLIST pokazuje aktywne połączenia i pełną treść zapytań. Ten sam zestaw informacji można pobrać z information_schema.PROCESSLIST i od razu posortować od najdłużej trwających operacji:

SHOW FULL PROCESSLIST;

SELECT ID, USER, HOST, DB, TIME, STATE, INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
ORDER BY TIME DESC;

Kolumna TIME podaje, od ilu sekund wątek pozostaje w bieżącym stanie. STATE opisuje, co wątek robi lub na co czeka, a INFO zawiera wykonywane zapytanie. Nazwa stanu Sending data jest myląca: nie musi oznaczać wyłącznie wysyłania wyniku do aplikacji. W tym stanie serwer może również czytać, filtrować i łączyć wiersze. Kilka długo działających kopii tego samego SELECT sugeruje więc kosztowny odczyt. Stan zawierający Waiting for ... lock wskazuje natomiast na oczekiwanie na blokadę; wtedy trzeba znaleźć transakcję, która ją utrzymuje, zamiast od razu dodawać indeks.

Następnie sprawdzamy budowę tabel. SHOW CREATE TABLE pokazuje definicje kolumn i kluczy, a SHOW INDEX listę istniejących indeksów. Zapytanie do information_schema.TABLES podaje używany silnik oraz przybliżony rozmiar danych i indeksów:

SHOW CREATE TABLE history_entries\G
SHOW INDEX FROM history_entries;

SELECT TABLE_NAME, ENGINE, TABLE_ROWS,
       ROUND(DATA_LENGTH / 1024 / 1024, 1) AS data_mb,
       ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS indexes_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'application_database'
  AND TABLE_NAME IN ('history_entries', 'documents');

Liczba rekordów nie wystarcza do oceny kosztu. Tabela zawierająca 85 tys. niewielkich rekordów może zajmować kilkanaście megabajtów, a tabela z taką samą liczbą dużych pól tekstowych lub binarnych — setki megabajtów. W drugim przypadku pełny skan wymaga odczytania znacznie większej ilości danych z dysku i zajmuje więcej miejsca w pamięci podręcznej InnoDB.

Połącz SQL z tym, co robi serwer

Wysokie użycie CPU przez MariaDB nie oznacza jeszcze, że brakuje mocy procesora. Trzeba sprawdzić także dysk. Wartość iowait pokazywana przez iostat oznacza czas, w którym system miał rozpoczęte operacje dyskowe i czekał na ich zakończenie. Jeżeli wynosi ona na przykład 30–40%, zapytania mogą zwalniać dlatego, że potrzebne fragmenty danych nie mieszczą się w pamięci i trzeba je wielokrotnie odczytywać z dysku.

pidstat -dru -p "$(pgrep -xo mariadbd)" 1
iostat -xz 1
free -h
df -h

pidstat pokazuje zużycie CPU oraz odczyty i zapisy procesu MariaDB. iostat pozwala ocenić obciążenie i czas odpowiedzi urządzeń dyskowych, free — wykorzystanie pamięci i swapu, a df — wolne miejsce w systemach plików. Patrzymy na te wyniki razem, ponieważ samo wysokie CPU albo sam zajęty RAM nie wyjaśniają jeszcze przyczyny problemu.

MariaDB udostępnia również liczniki, które sumują pracę wykonaną od uruchomienia usługi:

SHOW GLOBAL STATUS
WHERE Variable_name IN (
  'Uptime',
  'Slow_queries',
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads',
  'Innodb_data_read',
  'Handler_read_rnd_next'
);

Pojedyncza duża wartość nie musi oznaczać bieżącego problemu, ponieważ licznik mógł rosnąć przez wiele dni. Zapisujemy więc wyniki, po kilku minutach typowego ruchu odczytujemy je ponownie i obliczamy, o ile wzrosły. W ten sposób mierzymy pracę wykonaną właśnie w tym przedziale czasu.

Uptime podaje liczbę sekund od uruchomienia MariaDB, dzięki czemu wiadomo, jak długi okres obejmują pozostałe sumy. Slow_queries liczy zapytania, które przekroczyły ustawiony próg czasu wykonania. Innodb_buffer_pool_read_requests liczy żądania odczytu kierowane do bufora w pamięci, a Innodb_buffer_pool_reads te przypadki, których bufor nie obsłużył i dane trzeba było pobrać z dysku. Jeżeli drugi licznik szybko rośnie w stosunku do pierwszego, bufor często nie zawiera potrzebnych danych. Innodb_data_read pokazuje łączną ilość danych odczytanych przez InnoDB. Duży przyrost Handler_read_rnd_next oznacza wiele odczytów kolejnych wierszy i może wskazywać na częste pełne skany tabel. Każdy z tych sygnałów wymaga zestawienia z treścią zapytań i wynikiem EXPLAIN; sam licznik nie wskazuje jeszcze konkretnego winowajcy.

Dlaczego ostatni wpis wymaga właściwego indeksu

Przyjmijmy, że tabela historii ma około 85 tys. rekordów i zajmuje 0,7 GB, ponieważ rekordy zawierają duże pola. Jedynym indeksem jest klucz główny po id, więc baza potrafi szybko znaleźć konkretny numer id, ale nie ma uporządkowanej listy rekordów dla poszczególnych wartości parent_id.

Zapytanie WHERE parent_id = ? ORDER BY id DESC LIMIT 1 prosi o najnowszy wpis wskazanego rodzica. Bez indeksu zaczynającego się od parent_id baza może rozpocząć od największego id i przesuwać się wstecz po kluczu głównym. Przy każdym rekordzie sprawdza wtedy, czy jego parent_id jest właściwy. Jeżeli szukany rodzic ma niedawny wpis, wynik może pojawić się szybko. Jeżeli jego wpisy są rzadkie albo nie ma ich wcale, baza może sprawdzić znaczną część tabeli.

EXPLAIN
SELECT *
FROM history_entries
WHERE parent_id = 12345
ORDER BY id DESC
LIMIT 1;

Potrzebny jest więc indeks, który najpierw grupuje rekordy według warunku parent_id = ?, a wewnątrz tej grupy zachowuje kolejność id. Właściwy kandydat to:

CREATE INDEX idx_history_parent_id_id
    ON history_entries (parent_id, id);

Indeks (parent_id, id) jest uporządkowany najpierw według parent_id, a następnie — w obrębie każdego parent_id — według id. Dzięki temu baza może od razu odnaleźć rekordy wskazanego rodzica i pobrać ten z największym id. Indeks wyłącznie po id zapewnia właściwą kolejność, ale nie pozwala szybko znaleźć rekordów z określonym parent_id.

Pełny skan przy każdym UUID

Załóżmy, że tabela dokumentów ma około 1,1 mln rekordów, zajmuje 0,7 GB, a kolumna uuid nie jest indeksowana. W wyniku EXPLAIN wartość type = ALL oznacza pełny skan tabeli, czyli sprawdzanie rekordów jeden po drugim. Kolumna rows pokaże przybliżoną liczbę wierszy, które optymalizator spodziewa się odczytać — w tym przypadku około 1,1 mln.

EXPLAIN
SELECT *
FROM documents
WHERE uuid = '00000000-0000-0000-0000-000000000001'
  AND deleted_at IS NULL
LIMIT 1;

Warto sprawdzić również kod zapisujący dane. Jeżeli aplikacja przed utworzeniem rekordu kontroluje UUID przez LIKE ?, standardowy indeks może nadal pomóc. Warunkiem jest brak symbolu wieloznacznego % lub _ na początku wzorca. Wyszukiwanie LIKE '%fragment' nie pozwala zacząć od konkretnego miejsca w zwykłym indeksie, natomiast pełny UUID bez symboli wieloznacznych działa jak porównanie dokładne.

Zanim utworzymy unikalny indeks, sprawdzamy istniejące dane. Jeżeli choć dwa rekordy mają ten sam UUID, MariaDB przerwie tworzenie indeksu błędem. Kontrolujemy także wartości NULL, ponieważ indeks unikalny dopuszcza wiele takich wartości; jeżeli każdy dokument musi mieć UUID, sama unikalność nie zastępuje ograniczenia NOT NULL. Poniższe zapytania mogą przeczytać całą nieindeksowaną tabelę, dlatego na produkcji uruchamiamy je w kontrolowanym czasie i obserwujemy obciążenie:

SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT uuid) AS distinct_uuid,
       SUM(uuid IS NULL) AS null_uuid
FROM documents;

SELECT uuid, COUNT(*) AS occurrences
FROM documents
WHERE uuid IS NOT NULL
GROUP BY uuid
HAVING COUNT(*) > 1
LIMIT 20;

Pierwsze zapytanie zwraca liczbę wszystkich rekordów, liczbę różnych niepustych UUID oraz liczbę wartości NULL. Jeżeli total_rows i distinct_uuid są równe, a null_uuid wynosi zero, każdy rekord ma inny UUID. Drugie zapytanie stanowi bezpośrednią kontrolę: zwraca do 20 UUID występujących więcej niż raz. Gdy nie ma duplikatów, a reguła aplikacji mówi, że UUID ma być unikalny w całej tabeli, można utworzyć indeks unikalny:

CREATE UNIQUE INDEX idx_documents_uuid
    ON documents (uuid);

Taki indeks ma dwa zadania: przyspiesza wyszukiwanie i nie pozwala zapisać drugiego rekordu z tym samym UUID. Kontrola wykonana wyłącznie w aplikacji nie daje tej gwarancji. Dwa równoległe żądania mogą w tej samej chwili sprawdzić, że UUID jeszcze nie istnieje, a następnie oba spróbować go zapisać. Indeks unikalny rozstrzyga to wewnątrz bazy: pierwszy zapis się powiedzie, a drugi zostanie odrzucony jako duplikat.

Indeksy to nie wszystko: sprawdź bufor InnoDB

Bufor InnoDB to obszar pamięci RAM, w którym MariaDB przechowuje ostatnio używane fragmenty danych i indeksów. Odczyt z RAM jest znacznie szybszy niż z dysku. Przykładowy serwer ma 16 GB RAM, a dane i indeksy InnoDB zajmują łącznie około 5,6 GB. Przy innodb_buffer_pool_size = 128 MB bufor może pomieścić tylko około 2,3% całego zestawu.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

SELECT ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)
             / 1024 / 1024 / 1024, 2) AS innodb_gib
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB';

Sam procent nie przesądza jeszcze, że bufor jest za mały — aplikacja może regularnie korzystać tylko z niewielkiej części bazy. W opisywanym przypadku wniosek potwierdzają jednak szybki wzrost fizycznych odczytów, wysokie iowait i powtarzające się skany. Po sprawdzeniu, ile pamięci potrzebują system i pozostałe usługi, można ustawić większy bufor w osobnym pliku konfiguracyjnym MariaDB. Dla opisanych proporcji przykładowa wartość to:

[mariadbd]
innodb_buffer_pool_size = 8G

8 GB nie jest uniwersalną receptą. Liczy się aktywny zestaw danych, czyli ta część bazy, z której aplikacja regularnie korzysta, oraz pamięć potrzebna innym procesom. Zbyt duży bufor może pozostawić system bez wolnego RAM i wymusić użycie swapu, czyli znacznie wolniejszej pamięci na dysku.

W wielu wersjach MariaDB rozmiar bufora można zmienić podczas pracy serwera, ale dokładne zasady i dopuszczalne wartości zależą od wersji. W nowszych wydaniach maksymalny rozmiar dostępny dla takiego zwiększenia może być ustalany przy starcie przez innodb_buffer_pool_size_max. Przed zmianą sprawdzamy więc wersję serwera i dokumentację dla tej gałęzi. Jeżeli trwałe zastosowanie nowej wartości wymaga restartu, najpierw sprawdzamy aktywne transakcje i planujemy krótkie okno serwisowe:

SELECT trx_id, trx_started, trx_state,
       trx_rows_locked, trx_rows_modified
FROM information_schema.INNODB_TRX;

Lista aktywnych transakcji jest ważna, ponieważ restart przerywa połączenia, a niezakończone zmiany będą musiały zostać wycofane podczas ponownego uruchamiania. Po restarcie potwierdzamy stan usługi oraz wartości innodb_buffer_pool_size i — jeżeli dana wersja ją udostępnia — innodb_buffer_pool_size_max. Sprawdzamy też RSS procesu, czyli ilość fizycznej pamięci RAM faktycznie zajmowanej przez MariaDB. Bufor zapełnia się używanymi stronami danych stopniowo, dlatego RSS nie zawsze od razu rośnie o całą skonfigurowaną wartość.

Dodanie indeksów bez zatrzymywania aplikacji

Przed zmianą ponownie sprawdzamy aktywne transakcje i blokady, ilość wolnego miejsca, rozmiary tabel oraz istniejące indeksy. Budowa indeksu wymaga pracy procesora, operacji dyskowych, a czasem również dodatkowego miejsca na pliki tymczasowe. Dla UUID potwierdzamy brak duplikatów i ustalamy, czy dozwolone są wartości NULL. Indeksy dodajemy pojedynczo, aby łatwiej obserwować wpływ każdej operacji:

ALTER TABLE history_entries
    ADD INDEX idx_history_parent_id_id (parent_id, id),
    ALGORITHM=INPLACE,
    LOCK=NONE;

ALTER TABLE documents
    ADD UNIQUE INDEX idx_documents_uuid (uuid),
    ALGORITHM=INPLACE,
    LOCK=NONE;

Dodanie indeksu nie wymaga restartu MariaDB, ale baza musi przeczytać istniejące rekordy i zbudować nową strukturę. Czas zależy od wielkości tabeli, szybkości dysku i bieżącego ruchu: może wynieść kilka sekund, ale na dużej tabeli także wiele minut lub godzin.

ALGORITHM=INPLACE prosi serwer o użycie mechanizmu silnika, który w miarę możliwości unika tworzenia pełnej kopii tabeli. Nazwa nie gwarantuje jednak, że tabela w żadnym stopniu nie zostanie przebudowana. LOCK=NONE oznacza, że podczas głównej części operacji aplikacja powinna nadal móc czytać i zapisywać dane. Nadal mogą pojawić się krótkie blokady metadanych na początku lub końcu operacji.

Dostępność tych opcji zależy od wersji serwera, silnika, definicji tabeli i rodzaju zmiany. Jeżeli MariaDB odrzuca żądany algorytm lub poziom blokowania, nie usuwamy opcji automatycznie na aktywnej produkcji. Bez nich serwer mógłby wybrać bardziej blokującą metodę. Najpierw sprawdzamy zachowanie danej wersji i ponownie oceniamy wpływ operacji.

Indeks nie jest darmowy. Zajmuje miejsce i zwiększa koszt INSERT, UPDATE oraz DELETE. Dodajemy indeksy pod potwierdzone zapytania, a nie profilaktycznie do każdej kolumny.

EXPLAIN po zmianie i kontrola całej usługi

Po utworzeniu indeksu historii w wyniku EXPLAIN sprawdzamy trzy rzeczy. key powinno wskazywać idx_history_parent_id_id, type = ref powinno oznaczać wyszukanie grupy rekordów o konkretnym parent_id, a szacowana wartość rows powinna być znacznie mniejsza niż liczba rekordów w całej tabeli.

Dla LIKE zawierającego pełny UUID bez symboli wieloznacznych plan może pokazać type = range, indeks idx_documents_uuid i około jednego wiersza do odczytu. Przy porównaniu uuid = ... unikalny indeks pozwala optymalizatorowi użyć dostępu const: baza wie wtedy, że może znaleźć najwyżej jeden rekord. Konkretna nazwa typu dostępu może zależeć od wersji i postaci zapytania, dlatego najważniejsze są użyty indeks, mała liczba odczytywanych wierszy i rzeczywisty czas wykonania.

Nie kończymy jednak na samym planie. Kontrola po wdrożeniu obejmuje:

• ponowne wykonanie tych samych planów EXPLAIN;
• widoczność obu indeksów przez SHOW INDEX;
• stan usługi i dziennik błędów MariaDB;
• brak zapytań czekających na blokadę definicji tabeli, nazywaną blokadą metadanych;
• potwierdzenie docelowego rozmiaru bufora InnoDB;
• zapas pamięci i miejsca na dysku;
• obserwację zapytań pod rzeczywistym ruchem.

Po kilku dniach typowego obciążenia ponownie mierzymy przyrost liczników i analizujemy dziennik wolnych zapytań, czyli slow query log. EXPLAIN pokazuje przewidywany sposób wykonania jednego zapytania, natomiast obserwacja produkcji odpowiada na ważniejsze pytanie: czy cała aplikacja rzeczywiście działa szybciej i czy główna przyczyna opóźnień nie przeniosła się w inne miejsce.

Checklista diagnostyczna

1. Zacznij od fazy tylko do odczytu i zachowaj plan przed zmianą.
2. Patrz na powtarzalność i łączny koszt. Tysiąc zapytań trwających sekundę może obciążać bazę bardziej niż jedno zapytanie trwające minutę.
3. Sprawdzaj rozmiar danych w bajtach i wielkość pojedynczego rekordu, a nie tylko liczbę rekordów.
4. Projektuj indeks pod cały wzorzec zapytania: warunki WHERE oraz kolejność wymaganą przez ORDER BY.
5. Przed utworzeniem indeksu unikalnego sprawdź istniejące dane i potwierdź, że aplikacja rzeczywiście wymaga takiej reguły.
6. Porównuj plan EXPLAIN z obciążeniem procesora, dysku, pamięci i przyrostem liczników InnoDB.
7. Nie próbuj zastępować brakującego indeksu przypadkowym zwiększaniem innych buforów. Część pamięci MariaDB jest przydzielana osobno każdemu połączeniu, więc zbyt wysoka wartość może szybko wyczerpać RAM. Jeżeli używana wersja ma query cache, może on ponownie podać zapamiętany wynik identycznego zapytania, ale nie naprawi jego pierwszego wykonania ani złego sposobu odczytu danych.
8. Po zmianie wykonaj ten sam plan i powtórz pomiary w podobnym ruchu oraz przedziale czasu, aby wyniki dało się uczciwie porównać.

Największy efekt często dają proste, ale precyzyjnie dobrane indeksy oraz bufor InnoDB dopasowany do rzeczywistego sposobu używania bazy. PROCESSLIST pokazuje, które zapytania pracują lub czekają. EXPLAIN pokazuje, czy baza sięga do właściwego indeksu, czy czyta tabelę rekord po rekordzie. Narzędzia systemowe informują, ile kosztuje to procesor, pamięć i dysk. Dopiero połączenie tych informacji pozwala wskazać przyczynę, bezpiecznie wprowadzić zmianę i sprawdzić jej efekt.