MySQL – optymalizacja i wydajność

Dłuższa chwila minęła od ostatniego postu. Niestety, obawiam się że częstotliwość raczej się nie zwiększy znacząco. Przyczyną jest zwykły, prozaiczny brak czasu. Postaram się jednak od czasu do czasu wrzucić coś nowego na bloga. Dziś o profilowaniu alokacji pamięci przez MySQL

Jednego z Klientów dotknął specyficzny problem – mysqld puchł, alokował coraz więcej pamięci. Buffer pool ustawiony na 16G a mysqld alokował ponad 30GB i co pewien czas umierał zabity przez OOM killera. Jako że działo się to na serwerach produkcyjnych, w szczególności na masterze, nie była to sytuacja, którą można było zignorować.

Pierwsze kroki w takiej sytuacji zawsze należy skierować do SHOW ENGINE INNODB STATUS. Znajduje się tam sekcja opisująca zużycie pamięci przez InnoDB (tylko taki silnik był w użyciu w sprawiającym problemy klastrze). Jako że nie tylko buffer pool alokuje pamięć, dobrze się upewnić jak to w praktyce wygląda i czy inne elementy (jak choćby adaptive hash index) nie zjadają brakującej pamięci. Tym razem nic ciekawego nie udało się tam znaleźć – łączna wykazywana alokacja pamięci to było ok. 17-18GB. Trzeba było znaleźć inne rozwiązanie.

Jeden ze slave’ów został wyłączony z produkcji i przeznaczony na potrzeby testów. Wcześniej, gdy jeszcze obsługiwał ruch, zebraliśmy precyzyjne slowlogi, tak aby była możliwość odtworzenia ruchu produkcyjnego. Jak już slave dostał się w moje ręce, można było zacząć zabawę. Przy pomocy pt-log-player odtworzyłem ruch read only (same SELECTy, bez DML’i – cały czas działała replikacja i nie mogłem pisać po tej maszynie) i obserwowałem zużycie pamięci. W momencie gdy udało się potwierdzić przy pomocy próbki danych, że problem występuje, mogłem przejść do dalszych testów.

Tu na scenę wkracza Valgrind, a w szczególności Massif, który służy do profilowania zużycia pamięci przez proces. Instalacja jest prosta – ściągnąć źródło z http://valgrind.org/downloads/valgrind-3.8.1.tar.bz2, zainstalować narzędzia do kompilacji, skompilować na zasadzie ./configure && make && make install i gotowe.

Następnie należy uruchomić MySQL poprzez valgrinda. Zrobić można to np. tak:

Gdy MySQL ruszy, staramy się robić wszystko co się da, żeby wywołać problem. W moim przypadku wystarczyło uruchomienie pt-log-player i czekanie aż alokacja pamięci skoczy wystarczająco zauważalnie. Po wywołaniu problemu zatrzymujemy MySQL i sprawdzamy co się udało zebrać przy pomocy następującego polecenia:

W moim przypadku fragment raportu wyglądał tak:

Tak się składało, że akurat nie mogłem się doliczyć ok. 700MB. Jak widać, pamięć została zaalokowana przez funkcję handler::ha_open, która odpowiada za otwieranie tabeli. Można wnioskować, że ten memory leak związany jest z partycjonowaną tabelą (handler::ha_open wywołany jest przez funkcję ha_partition::open). To akurat nie wiele mi pomogło, jako że nie bardzo dało by się zrezygnować z partycjonowania tabel na tym hoście, ale przynajmniej udało się stwierdzić konkretną przyczynę problemu. Dzięki temu można było uzasadnić wykonanie upgrade całego klastra do najnowszej wersji MySQL, co w tym konkretnym przypadku załatwiło sprawę.

Podstawowym ograniczeniem jest to, że do wykonania testu konieczny jest restart MySQL i uruchomienie go przy pomocy Valgrinda. Nie da się (a przynajmniej ja nie znalazłem na to sposobu) podpiąć do działającego serwera i jeśli nie udało się opracować metody na wywołanie problemu, nie będziemy w stanie nic sprawdzić. Oczywiście, czysto teoretycznie patrząc można uruchomić Valgrinda i rzucić ruch produkcyjny na taki MySQL. Nie jest to coś, co bym polecał, co najwyżej gdy jesteśmy już zdesperowani a problem jest poważny. Zasadniczo nie powinno się uruchamiać produkcyjnie bazy danych działającej w środowisku debugującym jej działanie. Drugim, znacznie mniejszym już problemem jest konieczność doinstalowania pakietu z symbolami dla MySQL. Bez tego nie zobaczymy ładnych (i mówiących cokolwiek) nazw funkcji. Problem ten jest mniejszy, bo zazwyczaj da się te informacje doinstalować (np. dla Percona Server są dostępne w formie osobnego RPM: Percona-Server-55-debuginfo-5.5.29-rel30.0.451.rhel6.x86_64.rpm). W każdym razie, trzeba przyznać że Valgrind/Massif to narzędzie ciekawe i przydatne w pewnych sytuacjach.