MySQL, 10 na 90

Trochę dziwny tytuł i z pewnością nie każdy na pierwszy rzut oka rozumie o co chodzi, wyjaśnię zatem po krótce.

Nie jest to punktacja dla tejże bazy danych. Jest to tak zwana zasada o lokalności danych.
Twierdzi ona iż 10% kodu wykonuje się 90% czasu. Równie dobrze mógłbym tutaj wrzucić zasadę ‘pareto’ jednak nazwa czy drobna różnica w liczbach zupełnie nie ma tutaj znaczenia. Nie bedę jednak opowiadał o trzewiach tego DBMSa, powiem zaś o czymś co dotyczy każdego użytkownika bazy oraz jego danych.

Wszelkiego rodzaju systemy które teraz wytwarzamy, skoncentrowane są zazwyczaj wokół jednego użytkownika, dla którego gromadzone są dane, lub też wokół czasu w jakim są otrzymywane. Oznacza to, że zalogowanemu użytkownikowi pokazujemy dane które dotyczą tylko jego osoby (wiadomości, koszyki, produkty), lub też dane które dotyczą danego czasu (najświeższe wiadomości, najnowsze promocje, etc). Nieczęsto się zdarza iż przy generowaniu głównej strony serwisu pokazujemy wszystko co tylko znajduje się w bazie. Zazwyczaj jest jakieś ‘ziarno’ wokół którego należy się skupić.
Niestety pomimo iż skupiamy sie na tym ‘ziarnie’ podczas generowania zawartości strony, nie skupiamy się na tym podczas opiekowania się wytworzonym systemem.

Dane które gromadzimy w ciagu cyklu ich życia znajdują sobie miejsce w różnych miejscach dysku. Progamiści budują odpowiednie indeksy tak aby był szybki dostęp do nich, jednak to nie wszystko co można zrobić. Jeżeli dane gromadzone były tygodniami to odczytanie naszych 10, czy 20% zasobów może wygenerować dziesątki odczytów z IO (odczytów z dysków, czy macierzy dyskowych). Nieistotne czy dane są ladnie poukładane czy nie. Nieczęsto się zdarza iż do pobierania danych wykorzystany zostaje tylko i wyłącznie indeks, koniec końców i tak chcemy się dobić do całego wiersza danych (lub sporej jego części).
Z pewnością wielu czytelnikom zaświta już w głowie ‘defragmentacja’ a zaraz po tym ‘optimize’. Niestety to nie wszystko.

Rozwiązaniem tego ciekawego problemu jest cykliczne sortowanie danych wg specyficznego klucza, który to wczesniej nazwany został przezemnie ziarnem.
Sortowanie to może przebiegać następująco:

w przypadku MyISAM używając jednego z mechanizmów

 ALTER TABLE nazwa_tabeli ORDER BY [ziarno]
myisamchk --sort-records ....


w przypadku InnoDB używając 'ziarna' jako PK (Primary Key)

Zazwyczaj systemy DBMS poruszają się na granicy możliwości mechanizmów IO (input/output) co się w języku angielskim ładnie nazywa ‘io bound’. Dbanie o poprawne uporządkowanie danych znakomicie oddala nas w wielu przypadkach od krytycznej granicy.

Z pewnością wielu dostawców hostingowych stosuje praktykę sortowania danych klientów po PK, jeżeli nie jesteście pewni czy tak się dzieje, przeczytajcie dokumentacje lub zapytajcie. Jeżeli nie ma takiej możliwości sami dodajcie zapytanie do cyklicznych wywołań (np crona). W zależności od ilości danych napływających do systemu należy odpowiednio dobrać okres co jaki bazy się optymalizują. Tym samym odradzam wykonywać takie zabawy codziennie, zazwyczaj jedna optymalizacja w tygodniu lub w miesiącu będzie wystarczająca.

To była jedna część opowieści, z której jasno wypływa stwierdzenie: trzymaj najczęściej używane dane tak blisko siebie, jak tylko się da. Jednak temat ten nie jest wyczerpany. Jeżeli przyjrzycie się dokumentacji MySQL, w wielu miejscach da się znaleźć informacje z których jasno wynika, iż należy być w dziedzinie przechowywania danych minimalistą. Przechowywać tylko konieczne dane i to w postaci najbardziej dopasowanej do samych danych. Oznacza to iż, jeżeli pole ‘type’ zawiera tylko wartości od 1 – 10, to nie jest konieczne stosowanie dla niego typu INTEGER. Warto zmienić typ pola na mniejsze.
Praktyka jednak często jest inna, dodajemy informacje i kolumny na zapas, gromadzimy wiecej danych niż jest to potrzebne w danej chwili. Nie mogę negować takiego zachowania, nie zawsze wiemy z czego bedziemy generować statystyki, czy też jakie wielkości pojawią sie w stworzonych przez nas kolumnach. Nie jest to jednak patowa sytuacja.

W przypadku gromadzenia zawyżonej ilości danych warto w przypadku ich pobierania zawęzić ilość pobieranych kolumn do niezbędnego minimum, oraz przesunąć wszelkie kolumny, z których aktywnie korzystacie na poczatek wiersza.
Przykładowo, jeżeli mamy taki oto układ tabeli:

id: integer, count: integer; referer: varchar; cookie: varchar; link: varchar

podczas tworzenia raportów wykorzystujemy TYLKO pola: id, count, link; to warto te kolumny trzymać obok siebie.

Jeżeli przechowujemy znaczną ilość danych, szczególnie w polach tekstowych i cierpimy z powodu niedostatków operacji IO, natomiast mamy spory zapas CPU, doradzałbym użycie kompresji. Z pewnością mocniej obciąży CPU, jednakże okazać się może iż wszystko zacznie pracować o wiele wydajniej.

W przypadku gdy na początku projektu nie można było określić odpowiednich typów danych, warto jest co jakiś czas analizować to co znajduje sie w tabelach i odpowiednio modyfikować jej strukture
W tym celu należy wykorzystac polecenie:

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

Wygenerowany raport może okazać się bardzo przydatny.

Na koniec chciałbym przeprosić iż cały artykuł pisany jest tak na sucho bez przykładów i wykresów. Zostanie on zmodyfikowany i uzupełniony w skończonym czasie