Miałem okazję pracować przez wiele miesięcy na stanowisku administratora bazy danych, w kilku projektach – małych i dużych. Mam więc bagaż doświadczeń „małej, rozkapryszonej primadonny”, jak chce mnie widzieć Woyciech ;). Na co dzień odpowiadałem za zapewnienie odpowiedniej wydajności bazy danych (cokolwiek by to nie miało znaczyć), czyli pracy było … mnóstwo. Okazuje się (możecie mi wierzyć lub nie), że wolna baza danych to problem admina, a kierownik zespołu zwykle doskonale o tym wie i nie daje o tej prawdzie zapomnieć

Po nieco przydługim wstępie, przejdźmy do konkretów. Artykuł ten jest w zamierzeniu uzupełnieniem artykuły Woyciecha (Optymalizacja zapytań SQL na przykładzie bazy Oracle z dn. 01/02/2008). Wskazana jest jego lektura, przed przeczytaniem niniejszego artykułu. Moja wiedza o Oracle’u jest niestety powierzchowna, rozważania oprę zatem na przykładzie SZRBD lub jak kto woli RDBMS firmy IBM, czyli niezrównanego DB2.

DB2 tak samo jak Oracle wyposażony jest w narzędzie służące do ustalania, który z możliwych planów wykonania zapytania używany jest przez optymalizator. Informację zwrotną otrzymuje się w postaci opisu z dołączonym grafem:

Optimizer Plan:

                  RETURN

                  (   1)

                    |

                  NLJOIN

                  (   2)

              /–/      \-\

        FETCH              IXSCAN

        (   3)             (   2)

       /      \           /      \

  IXSCAN   Table:    Index:    Table:

  (   3)   DB2ADMIN  DB2ADMIN  DB2ADMIN

    |      B         TAB_A_PK  A

 Index:

 DB2ADMIN

 TAB_B_PK

Podobnie jak w systemie Oracle, w DB2 wygenerowanie optymalnego planu wykonania zapytania jest niemożliwe w sytuacji, gdy optymalizator nie dysponuje aktualnym opisem zawartości bazy danych, czyli statystykami. Do aktualizacji statystyk służy polecenie RUNSTATS. Statystyki mogą uwzględniać rozkład danych, a zbiera się je bądź na podstawie całej zawartości tabeli, bądź też tylko jej próbki (np. losowo wybranej 10 procentowej zawartości).

Kilka nieznanych być może faktów związanych z zagadnieniem odświeżania statystyk:

W starszych wersjach DB2 o wszczęciu procedury aktualizacji statystyk decydował administrator. W nowszych systemach istnieje możliwość sterowania tym procesem przez sam RDBMS. W przyszłych wersjach, odświeżanie statystyk nie będzie już niezależnym procesem i zostanie zintegrowane z normalną obsługą zapisu do bazy danych.

Procedura odświeżania statystyk w środowisku produkcyjnym na tabeli o 400 milionach wierszy (ok. 10-15 kb jeden) trwa około 2 godzin (32 procesory/rdzenie, 60 GB RAM). Odświeżanie statystyk znacznie spowalnia system, nie powodując jednak całkowitej jego niedostępności dla klientów. O stopniu spowolnienia systemu decyduje administrator/RDBMS. Im mniejsze jest spowolnienie tym dłużej trwa aktualizacja statystyk.

Możliwości poprawiania wydajności zapytań przez samego programistę są w DB2 niestety bardziej ograniczone niż w przypadku Oracle’a. DB2 nie posiada hintów (pozwalających programiście decydować o wykonaniu zapytania). Stosuje się dość skomplikowane techniki ‘wpływania’ na optymalizator, takie jak np. wstawianie sztucznego warunku do klauzuli WHERE, tak by wymusić użycie istniejącego indeksu. Przykład warunku raczej pozbawionego znaczenia z punktu logiki systemu natomiast wskazującego ‘właściwą drogę’ optymalizatorowi:

WHERE txtime > ‘1950-01-01-00.00.00.00000′ and txtime < ‘2050-01-01-00.00.00.000000′

‘Oracle dla ubogich’ nie posiada również indeksów funkcyjnych (tzn. posiada ale nie w środowiskach LUW – Linux, Unix, Windows). Powoduje to elementarne trudności z zapytaniami typu:

SELECT address FROM tabela WHERE UCASE(nazwisko)=’BRZĘCZYSZCZYKIEWICZ’

Zapytania tego typu zawsze powodują full table scan. Poradzić się na to da niewiele, jeśli nie liczyć dostawienia kolumny do tabeli, która przechowywać będzie nazwiska wielkimi literami. Pewnym ułatwieniem może być tylko to, że owa kolumna może być wypełniana automatycznie przez DB2.

Tych kilka powyższych ciekawostek miało na celu rozszerzenie informacji zawartych w znakomitym artykule Piotra. Teraz chciałem opisać parę interesujących technik, które zastosowane być mogą przez zaawansowanych użytkowników baz danych, by poprawić wydajność bazy danych. Stosowanie tych technik najczęściej wymaga dobrej znajomości aplikacji odpytujących bazę danych. Stąd wynika konieczność dość bliskiej współpracy osoby piszącej program, z osobą strojącą bazę.

Pierwszym pomysłem, jaki pojawia się, gdy zapytanie działa wolno jest - „dołóżmy indeks”. Słusznie. Na ogół jest to najlepsze rozwiązanie, taki bowiem urok relacyjnych baz danych. Sam to wielokrotnie zalecałem i będę zalecał dalej. Na uwadze mieć trzeba jednak, kilka ważnych spraw – w szczególności gdy mamy do czynienia z dużym systemem produkcyjnym:

·         Indeks zajmuje miejsce na dysku. Zdarza się, że zajmuje prawie tyle samo miejsca co właściwa tabela. Przed założeniem indeksu należy sprawdzić, czy zmieści się na istniejącej przestrzeni dyskowej. Planować należy na zapas (tabela może przecież rosnąć).

·         Założenie indeksu może trwać długo i jest ‘zasobożerne’. Zakładając indeks na mocno obciążonym środowisku należy to zawczasu uwzględnić.

·         Indeks pogarsza wydajność aplikacji. Choć brzmi to paradoksalnie – tak jest faktycznie. Oczywiście dotyczy to wydajności zapisu – każdy insert wymusza dodanie liścia lub nawet węzła do indeksu a to zwiększa (znacznie) czasy wykonania transakcji, które coś w bazie modyfikują. Konieczne jest każdorazowe przeanalizowanie tego aspektu sprawy.

·         Operacje na indeksach nie są na ogół rejestrowane w dzienniku transakcji bazy danych. Są jednak środowiska, w których każda modyfikacja (w tym również utworzenie indeksu) wymaga zapisu do logu. W DB2 wymusza to na przykład HADR (High Availability and Disaster Recovery) – często stosowany na produkcji. Logowanie operacji indeksowych istotnie pogarsza wydajność systemu. Tym bardziej im więcej jest indeksów, lepiej ich zatem nie nadużywać.

Życie wielokrotnie już pokazało, że utworzenie dodatkowego indeksu może przynieść więcej szkód niż pożytku. Oczywiście zdarza się to rzadko, ale się zdarza. Warto o tym pamiętać i … odpowiednio zawczasu przetestować system :-).

Systemy bazodanowe wyposażone są w pamięć cache. Działa to prosto: każde żądanie odczytu powoduje, że odczytywana dana trafia z dysku do pamięci operacyjnej do specjalnego obszaru zwanego z angielskiego bufferpool. Dane często odczytywane w tym obszarze przebywają dłużej i nie są odczytywane z dysku. Odczyt z tego obszaru jest oczywiście szybszy. Czasem jednak dane z bufferpoola bywają usuwane. Kolejny ich odczyt znów będzie odbywał się z dysku. Jak wspomóc pracę cache’u?

Dużą rolę ma tu do odegrania twórca aplikacji. Powinien on pomóc opiekunowi bazy podzielić tabele w schemacie wedle następujących kryteriów:

1. małe tabele, które nie będą rosły
2. spośród tabel podpunktu 1 wyróżnić te, które są często odczytywane
3. średnie tabele (które zazwyczaj powoli ale przyrastają)
4. spośród tabel podpunktu 3 wyróżnić te, które są często odczytywane
5. spośród tabel podpunktu 4 wyróżnić te, które odczytywane są długimi blokami (wiele wierszy naraz)
6. wielkie tabele (które zazwyczaj ciągle i szybko przyrastają).
7. spośród tabel podpunktu 6 wyróżnić te, które są często odczytywane
8. spośród tabel podpunktu 7 wyróżnić te, które odczytywane są długimi blokami (wiele wierszy naraz)

Każda baza danych pozwala na stworzenie w pamięci operacyjnej wielu obszarów typu bufferpool o różnych rozmiarach. Powyższa klasyfikacja ułatwi ich utworzenie dzięki następującemu rozumowaniu:

·         Tabele małe (kategoria 1 i 2) powinny mieć swój wydzielony obszar, tak wyliczony, by mógł je pomieścić w całości. Sukcesywne odczyty z tych tabel spowodują przeniesienie ich zawartości w całości (lub prawie) do pamięci RAM, z której nie będzie ona usunięta – przy najmniej do momentu zatrzymania systemu. Odczyty z tych tabel będą szybkie. Zwracam uwagę, że w tabelach tego typu trzymane są zazwyczaj dane często wykorzystywane (referencyjne, słownikowe)

·         Tabele średnie typu 3 i wielkie typu 6 powinny być trzymane razem w stosunkowo do ich wielkości niewielkim, wydzielonym obszarze. Niewielka ilość odczytów nie spowoduje szybkiego przepełnienia bufferpoola i w konsekwencji usuwania z niego danych.

·         Tabele typu 4, 5, 7 i 8 trafiają do osobnego obszaru dla każdego z typów . Rozmiary ustalamy prosto: dla kolejnych typów coraz więcej – najwięcej jak się da. Pamiętać przy tym należy, że powiększanie rozmiarów dla typów 5 i 8 najmniej się opłaca (choć z drugiej strony nie mogą to być obszary małe).

·         Ostateczną konfigurację sprawdzamy w testach analizując współczynniki trafień (cache hit ratio). Dążymy do 100%. Niezmiernie się radujemy gdy mamy > 90%. Tabele typu 5 i 8 to zazwyczaj cache hit ratio rzędu 40-60% i nic z tym się nie zrobi, no chyba że ktoś dysponuje RAMem większym od zawartości bazy.

Powyższa klasyfikacja tabel, jest również przydatna przy rozmieszczaniu ich na dysku. DB2 podobnie jak inne motory bazodanowe posiada obiekty o nazwie tablespace (przestrzeń tabel). Działa w tym przypadku zasada: tabele przechowywane są w przestrzeni tabel, a przestrzenie tabel przechowywane są na dysku. Tabele małe 1 i 2 kategorii składujemy w wydzielonych dla nich niedużych przestrzeniach tabel, umieszczając je na nośnikach gdziekolwiek (np. na tej samej partycji co pliki konfiguracyjne bazy). Tabele średniej wielkości (3 i 4) oddzielamy od dużych. Kategorię 3 przechowujemy gdziekolwiek, natomiast te tabele, które odczytywane są blokowo staramy się umieszczać na nośnikach szybkich. Identycznie postępujemy z wielkimi tabelami, pamiętając, że należy im się odpowiednio duża porcja gigabajtów.

Kolejnym obszarem optymalizacji jest sortowanie. Oczywiście, stosując wskazówki i rady światłych umysłów konstruujemy zapytania tak, by nie sortowały. Ale ‘to se ne da’ jak mówią Czesi. I tu znowu programista spotyka się z zarządcą bazy i ustalają wspólnie, które zapytania powodują najwięcej sortowań i jak dużo danych przetwarzanych jest w takiej operacji. UWAGA: nie jest to zadanie trywialne – często największe sortowania są wykonywanie niejawnie, tj. nie są wyspecyfikowane explicite w zapytaniu klauzulą ORDER BY a wynikają na przykład ze złączeń.

Opiekun bazy danych mając wiedzę o tym, jakie operacje sortowania są podejmowane w systemie działa trzyetapowo. Po pierwsze, dodaje brakujące indeksy i odczynia wszelkie magie by usunąć sortowanie z planu wykonania. Gdy to zawiedzie, zaczyna starania o to, by pamięć operacyjna przeznaczona dla operacji sortowania była jak największa (zazwyczaj nie daje rady jej powiększyć ponad 500MB). Powiększenie tego obszaru odbywa się oczywiście kosztem innych elementów systemu w tym między innymi wspomnianych wcześniej bufferpooli. Czasem to również nie wystarcza, operacje sortowania nie mieszczą się w RAM. Wtedy admin wydziela specjalne obszary dyskowe (często pozbawione systemu plików), tak by mogły tam się zapisywać sortowane dane… i znosi cierpliwie narzekania „wolno działa”. Na szczęście zdarza to się rzadko – a już we współczesnych systemach OLTP prawie nigdy.

Jedna z cech zoptymalizowanej bazy danych to równomierne rozmieszczenie danych na dyskach w obrębie tabel. Wszyscy znają zapewne Windowsowe polecenie defrag służące do porządkowania dysków. Polecenie o zbliżonej funkcjonalność w DB2 to REORG. REORG analizuje fizyczne rozmieszczenie danych na dyskach i przemieszcza je tak, aby zachowały ciągłość na nośniku, co przyspiesza odczyty z tabeli.

Oczywiście uruchomienie REORGa, choć daje natychmiastowo dobre wyniki wiąże się z kosztami: trwa długo i bardzo spowalnia pracę systemu (bardziej niż aktualizacja statystyk). W dodatku, w środowisku produkcyjnym należy zachować szaloną wręcz ostrożność, gdy uruchamiamy go na wielkich tabelach. Jego „abnormal termination” równa się prawie na pewno konieczności odtwarzania bazy z kopii zapasowej. A przerwanie może nastąpić łatwo z wielu powodów: przepełnienie dziennika transakcji (reorg w DB2 zapisuje gigantyczne ilości danych do logów – sam nie wiem dlaczego), przepełnienie obszarów sortowania, przepełnienie dysku twardego, itp., itd.

Tu również dużo zależy od ustaleń pomiędzy zespołem programistów a zespołem administratorów. Jak uchronić system przed koniecznością częstego uruchamiania REORGa? Odpowiedź jest zaskakująco prosta: jak najrzadziej używać DELETE’a. Unikać również stosowania takich operacji UPDATE, które zmieniają długości łańcuchów w polach VARCHAR, przy czym jest mniej szkodliwe niż kasowanie.

Stosowanie polecenia DELETE wpływa na ciągłość danych w sposób tak samo oczywisty jak usuwanie pliku z dysku – tworzy ‘dziurę’ i narusza ciągłość danych na nośniku. Z punktu widzenia aplikacji warto rozważyć możliwość pozostawiania w bazie danych, które z logicznego punktu widzenia są już nieistotne. Jeśli powoduje to zbyt gwałtowny przyrost objętości w tabelach, możliwe jest wprowadzenie cyklicznego procesu czyszczenia tabel z danych oznaczonych jako nieistotne. Proces ten uruchamiany na przykład w nocy, będzie mógł usunąć dane szybciej (np. przy wyłączonym logowaniu transakcji), dodatkowo przeprowadzając niezbędne porządkowanie.

Typowa baza skonfigurowana dla optymalnej obsługi OLTP (online transaction processing), przetwarzająca w większości krótkie transakcje, które odczytują i modyfikują stan bazy, bardzo źle znosi zapytania OLAP (online analytical processing). Dla uściślenia - zapytanie: select order_value from orders where order_id=10202 jest typowe dla OLAP podczas gdy select sum(order_value) from orders jest typowe dla OLTP. Przyczyn opisanego zjawiska jest wiele, najistotniejsze według mnie to te:

·         Zapytania agregujące typowe dla OLAP mocno zużywają zasoby maszyn, spowalniając pracę zapytań krótkich.

·         Zapytania agregujące poprzez nieoszczędne gospodarowanie blokadami znacznie pogarszają ‘współbieżność’ systemu. (termin ten stosuję troszkę instynktownie, mam nadzieję, że Czytelnik to czuje).

·         Konfiguracja bazy danych jest tylko jedna – trudno jest tak dobrać parametry, by optymalizować wykonanie obu typów zapytań na raz.

Zarysowany powyżej problem to kolejne pole do współpracy pomiędzy twórcą systemów a osobą odpowiedzialną za prace bazy. Muszą oni przede wszystkim ustalić, czy istnieje sposób na uniknięcie konieczności stosowaniu obu typów zapytań na raz. Jeśli okaże się to niemożliwe, powinni zastanowić się nad rozdzieleniem zapytań obu typów. Najprościej można tego dokonać poprzez wyznaczenie przedziałów czasowych. Przykładowo krótkie transakcje finansowe przeprowadzane są w dzień (użytkownicy dokonują zakupów, transferów lub sprawdzają salda) natomiast obszerne transakcje raportujące, księgowe, oraz zabiegi czyszczące bazę danych z informacji archiwalnych przeprowadzane są w nocy. Z punktu widzenia administratora bazy danych, kusząca mogłaby się wydawać zmiana konfiguracji bazy wieczorem, po zamknięciu jej dla użytkowników, przed uruchomieniem raportowania. Osobiście zniechęcałbym do takiego podejścia – zmiana konfiguracji bazy jest zawsze operacją silnie uzależnioną od środowiska runtime – i w konsekwencji ryzykowną. Ewentualne niepowodzenia przy przechodzeniu od jednej konfiguracji do drugiej mogą spowodować zatrzymanie pracy systemu.

Przedstawiłem, jedynie kilka najprostszych zagadnień z zakresu strojenia i optymalizacji baz danych. Z ciekawszych problemów, jakie pojawiają się jeszcze w praktyce wymienić można: włączenie replikacji w systemie (zwłaszcza w trybie pełnej synchronizacji) – możne spowolnić system nawet kilkunastokrotnie, czasy transakcji zależą tu bowiem od… przepustowości łącza sieciowego; optymalizacja przetwarzania OLAP – temat rzeka; klastrowanie wielowymiarowe; zapytania materializowane; partycjonowanie. Zainteresowanych zachęcam do pogłębiania wiedzy.