„Mam MySQL uruchomiony na serwerze fizycznym/VPS o następujących parametrach: <ciach parametry>.
Co zrobić, aby działał on z maksymalną wydajnością?

<ciach przeklejoną zawartość my.cnf>”

Znacie to, drodzy Czytelnicy? Tego typu pytania pojawiają się co pewien czas na różnorakich forach dotyczących webdeveloperki czy hostingu. Co pewien czas, coraz to nowe osoby szukają Graala, jakim jest „optymalna konfiguracja MySQL”. Uczynni, a lepiej orientujący się w temacie członkowie forum, sugerują poszukującym różne rozwiązania. Czy te poszukiwania uda się kiedykolwiek zakończyć?

Podstawowym problemem jest to, że stwierdzenie „optymalna konfiguracja” jest tak ogólne, że w zasadzie nie znaczy nic. Dla jednego będzie to zminimalizowane zapotrzebowanie na pamięć (bo jego vps ma 256MB RAMu), dla kogoś innego osiągnięcie maksymalnej możliwej wydajności w zapytaniach typu INSERT do tabeli na silniku MyISAM. Ktoś jeszcze inny potrzebuje dokładnie to samo, ale tabela działa na silniku InnoDB. To są trzy zupełnie inne przypadki wymagające zupełnie innego podejścia do konfiguracji MySQL – bez dokładnej wiedzy do czego dana baza będzie służyć, nie ma możliwości aby cokolwiek sensownego zasugerować.

Na niektórych forach dyskusyjnych polecane są skrypty mysqltuner.pl i tuning-primer.sh. Skrypty te działają na prostej zasadzie. Pobierają statystyki serwera, sprawdzają jakie wartości przyjmują zmienne w konfiguracji MySQL i na podstawie tych danych podają różnorakie sugestie dotyczące tego, co można w konfiguracji poprawić. Problem w tym, że bez wiedzy o tym, co się dzieje w bazie, korzystanie z takich podpowiedzi może potencjalnie przynieść więcej szkody niż pożytku. Dla przykładu, mamy serwer MySQL, którego cache zapytań jest bardzo szybko zapełniany. W efekcie przepełnienia cache, część zapytań w nim przechowywanych jest usuwana. Na pierwszy rzut oka dobrą sugestią będzie zwiększenie wielkości cache. W praktyce, wiedząc że baza obsługuje tylko SELECTy, w których jednym z warunków WHERE jest aktualny timestamp, sugestią poprawną będzie w ogóle wyłączyć cache. Cache zapytań w MySQL działa na zasadzie porównywania treści zapytania. Jeśli do bazy idzie zapytanie:

SELECT  a,b FROM tabela WHERE c=10;

to serwer sprawdza czy w cache znajduje się zapytanie o dokładnie takiej treści. Nie z WHERE c=9 czy c=11, tylko dokładnie takie jak aktualnie wykonywane. Jako że timestamp zmienia się co sekundę, znakomita większość zapytań nie będzie miała okazji skorzystać z cache, natomiast do cache będą wrzucane. Po co zajmować pamięć, skoro pożytku z tego żadnego nie będzie?

Nie da się zaproponować dobrej konfiguracji bez posiadania dokładnej wiedzy na temat danej bazy i zapytań do niej idących. Trzeba także wiedzieć, jakie są założenia działania aplikacji. W konfiguracji InnoDB występuje ciekawy parametr –  innodb_flush_log_at_trx_commit. Określa on w jaki sposób MySQL ma zapisywać na dysk zawartość dziennika transakcji (log buffer). W domyślnym ustawieniu, zapewniającym integralność danych i zgodność z ACID, zapis na dysk wykonywany jest po każdej transakcji. Zmiana tego parametru skutkuje znacznym zwiększeniem wydajności ze względu na mniejsze obciążenie dysku zapisami. Problem w tym, że buforowanie zapisów i wykonywanie ich raz na sekundę może w przypadku awarii serwera skutkować utratą tej sekundy danych. Czy dla danej aplikacji taka strata jest do zaakceptowania? Jeśli do bazy zapisujemy np. logi dostępu do strony, to utrata jednej sekundy logów nie powinna być tragedią, a działanie bazy znacznie przyspieszy. W przypadku sklepu internetowego decyzja może być już inna.

Nie można także zapomnieć o tym, że sama konfiguracja MySQL to tylko jeden z elementów układanki. Żaden plik my.cnf nie zagwarantuje sprawnego działania serwera, jeśli problemem będą zapytania czy struktura bazy danych. Aby określić co jest przyczyną spowolnień, konieczne jest dokładne przeanalizowanie wszystkich elementów – konfiguracji, owszem, ale także i ruchu, jaki idzie do bazy. Trzeba skonfigurować serwer tak, aby wszystkie zapytania trafiały do slowlogów, zbierać te zapytania przez pewien czas, a potem je przeanalizować.

Jeśli któryś z Was, drodzy Czytelnicy, stanie przed problemem kiepskiej wydajności serwera MySQL, niech nie ogranicza się do zmieniania wielkości cache zapytań, buforów na indeksy, cache tabel itp. Najpierw trzeba zrozumieć, gdzie leży przyczyna problemu – dopiero wtedy można podjąć decyzję w jaki sposób ją zlikwidować. Gdy zaś to Was będą się pytali „co zrobić, żeby serwer działał szybciej”, nie odsyłajcie ich do automatycznych „konfiguratorów”. To nie tak, że są nieprzydatne – jak najbardziej, mogą się przydać. Okazało się, że jednak nie mogą się przydać. One są po prostu złe. Tego typu skrypt nie patrzy na to, w czym leży problem. Jeśli JOIN nie jest poindeksowany, to sugestią będzie podbijanie join_buffer_size. Bezsens, w dodatku szkodliwy bo skutkuje konfiguracją bardziej podatną na wysycenie pamięci serwera i w efekcie pad całości. Jeśli za mały jest key_buffer_size to w niektórych przypadkach nie będzie sugestii o konieczności jego zwiększenia. Idiotyzm, pewnie, przecież lepiej czytać dane z dysku lub cache systemu zamiast z dedykowanego buforu w obrębie MySQL.

Chodzi tylko o to, że przy ich wykorzystaniu trzeba także myśleć, a nie mechanicznie wdrażać podane przez skrypt sugestie. Tu nie ma co myśleć nad wynikami skryptu, te wyniki prawdopodobnie będą się nadawać na śmieci. Trzeba myśleć samodzielnie. A jeśli trzeba myśleć, to najpierw trzeba zrozumieć co się dzieje. Starajmy się pamiętać, że wydajność MySQL to nie tylko konfiguracja.