Nie jestem pewny, czy dobrze tłumaczę z angielskiego frazę “rule of thumb”, ale w dzisiejszym poście o takich właśnie zasadach chciałbym kilka słów napisać. Jeśli chodzi o konfigurację MySQL to kilka tego typu zaleceń można w internecie znaleźć. Czy mają one sens? Czy faktycznie są to “dobre praktyki”?
Dwa przykłady – InnoDB buffer pool. Szybkie zapytanie w Google podaje nam prostą receptę na ustawienie wielkości tego buforu – 80% dostępnej na serwerze pamięci. Załóżmy że mamy serwer z 96 GB pamięci. 80% to 76,8 GB co pozostawia nam 19,2 GB. W przypadku serwera z 128 GB pamięci 80% to 102,4 GB i zostaje nam 25,6 GB.
Skoro kilka lat wcześniej, przy serwerach z 4, 8, 16 GB pamięci wystarczał zapas 1, 2 czy 4 GB to przy takim samym poziomie ruchu zapotrzebowanie na pamięć nie mogło skoczyć dwudziestokrotnie. Jasne, MySQL potrzebuje także pamięć na dodatkową działalność – bufor sortowania, JOINy, tabele tymczasowe. Tylko ilość pamięci na to potrzebna jest zależna od poziomu ruchu, a ten wcale nie wzrasta automatycznie wraz z rozbudową serwera o dodatkowy RAM czy wymianą starego serwera na nowy. Tak więc alokujemy te 80% a pozostałe 20%, jeśli na serwerze działa tylko i wyłącznie MySQL z InnoDB, po prostu się marnuje.
Drugi taki przykład, z którym ostatnio miałem okazję się zetknąć, to wielkość key_buffer_size. Pojawiło się pytanie jak wygląda ogólna zasada konfiguracji wielkości buforu indeksów w momencie gdy na serwerze działaja tylko MySQL z MyISAM. Nie słyszałem o tego typu regule, ale zacząłem z ciekawości szperać w internecie. Słowa kluczowe “key_buffer_size” “rule of thumb” zwracają całą serię stron z dokładnie tym samym zdaniem, przepisanym prawdopodobnie z tego artykułu w Database Journal:
Mowa w nim o następującym ustawieniu – minimum 25% całości pamięci, nie więcej niż 50%. Ok, jakaś reguła jest. Dlaczego, moim zdaniem, jest ona niewłaściwa? Wyobraźmy sobie następującą bazę danych, działającą oczywiście na silniku MyISAM. Ilość danych to 20 GB, indeksy łącznie zajmują 10 GB. Wszystko działa na serwerze z 16 GB pamięci. Podczas normalnego ruchu aktywny zestaw indeksów (czyli te indeksy, które są na bieżąco potrzebne), zajmuje 2 GB a aktywny zestaw danych to 14 GB. Co pewien czas, w nocy, wykonywane są zestawienia, które potrzebują dostęp do kolejnych 6 GB indeksów i wszystkich, 20 GB danych. Ich wydajność nie jest istotna, ważne aby zmieściły się w nocnym okienku. Zazwyczaj nie ma z tym problemów.
Ile pamięci przeznaczyć na bufor dla indeksów? Zgodnie z zasadą – minumum 4 gigabajty, maksymalnie do 8 gigabajtów. Nie koniecznie. Trzeba pamiętać o tym, że w przypadku MyISAM dane (i indeksy, które nie zmieszczą się do wewnętrznego bufora) buforowane są w pamięci przez system operacyjny – im większy bufor indeksów, tym mniej pozostaje miejsca na dane. Drugą cechą buforów jest to, że MySQL chętnie je zapełnia, gorzej ze zwalnianiem aktualnie nieużywanych danych. Z jednej strony jest to zaleta – dane (indeksy) raz wczytane z dysku pozostaną w pamięci (a przynajmniej dopóki jest jeszcze wolne miejsce w buforze), z drugiej wada – indeksy zajmują pamięć pozostawiając mniej miejsca na cache dyskowy systemu, czyli na dane.
Przyglądnijmy się jak to wygląda w naszym przykładzie. Key buffer ustawiamy na 4 gigabajty, najmniejsza sugerowana wielkość. W ciągu dnia alokowanych jest 2 GB pamięci na indeksy, reszta – 14 GB pozostaje do dyspozycji serwera jako cache dyskowy na dane. Wszystko ładnie mieści się w pamięci. W nocy generowane jest zestawienie i ilość pamięci przeznaczonej na indeksy rośnie do maksimum – 4 GB. Na dane pozostaje 12 gigabajtów. Rano ilość pamięci zajmowanej przez indeksy nie zmienia się, nadal jest to 4 GB. W efekcie, w przeciwieństwie do poprzedniego dnia, cały aktywny zestaw danych w cache się już nie upchnie – część będzie musiała być doczytywana z dysku.
Jeśli natomiast key buffer ustawimy na 2 GB, okaże się że w każdym wypadku w ciągu dnia będziemy w stanie przechowywać w pamięci wszystkie potrzebne dane. Co prawda odbije się to na szybkości generowania nocnego zestawienia (technicznie rzecz biorąc łącznie będzie dokładnie tyle samo wolnej pamięci na dane i indeksy, ale większa część indeksów niż wcześniej będzie trzymana w cache systemu operacyjnego, który nie jest tak szybki w dostępie jak wewnętrzny bufor MySQL), ale tamte zapytania mogą być wykonywane wolniej. Ważne tylko żeby skończyły się zanim ludzie przyjdą do pracy.
Ten krótki przykład ma na celu pokazanie jednego. Nie ma czegoś takiego jak dobra, ogólna zasada. Za każdym razem trzeba przyglądnąć się danej sytuacji – dopiero wtedy można podjąć decyzję o wprowadzeniu jakiejś zmiany w konfiguracji. Owszem, w części przypadków takie zasady mogą się sprawdzić (bardziej w sensie tego że nie szkodzą nadmiernie, a nie że są optymalne). Ba, powiem nawet że w większości wypadków sprawdzą się lepiej niż domyślna konfiguracja MySQL (huge.cnf i cała reszta). W takiej sytuacji będzie to jednak wybór mniejszego zła a nie optymalne rozwiązanie.
Komentarze