IBAnalyst: Zrozumienie Twojej bazy danych

Dmitri Kuzmenko, [email protected], ostatnia aktualizacja 31 marca 2014

Pracuję z InterBase od 1994 roku. W tamtych czasach większość baz danych była mała i nie wymagała żadnego strojenia. Oczywiście zdarzały się sytuacje, gdy musiałem zmienić ibconfig na serwerze oraz przekonfigurować sprzęt lub system operacyjny, ale to było prawie wszystko, co mogłem zrobić, aby dostroić wydajność.

Cztery lata temu nasza firma zaczęła świadczyć wsparcie techniczne i szkolenia dla użytkowników InterBase. Praca z wieloma produkcyjnymi bazami danych nauczyła mnie również wielu różnych rzeczy. Jednak większość z tego, czego się nauczyłem, dotyczyła aplikacji - użycia parametrów transakcji, optymalizacji zapytań i zestawów wyników.

Oczywiście od dłuższego czasu wiedziałem o gstat - narzędziu, które dostarcza informacji o statystykach bazy danych. Jeśli kiedykolwiek zaglądałeś do wyników gstat lub czytałeś o nim w opguide.pdf, wiesz, że wynik statystyczny wygląda jak zbiór liczb i nic więcej. OK, możesz odkryć informacje o fragmentacji dla konkretnej tabeli lub indeksu, ale jakie inne przydatne informacje można uzyskać?

Na szczęście, zanim zacząłem pracować z InterBase, interesowałem się różnymi strukturami danych, tym, jak są przechowywane i jakich algorytmów używają. To pomogło mi interpretować wyniki gstat. W tamtym czasie postanowiłem napisać narzędzie, które mogłoby analizować wyniki gstat, aby pomóc w strojeniu bazy danych lub przynajmniej zidentyfikować przyczynę problemów z wydajnością.

Długa historia, ale efektem było stworzenie IBAnalyst. Mimo mojego doświadczenia, nadal pozwala mi znajdować bardzo interesujące rzeczy lub problemy z wydajnością w różnych bazach danych.

Realne systemy mają wydajność w czasie rzeczywistym, która waha się jak fala. Amplituda takich „fal” może być niska lub wysoka, więc możesz zobaczyć, jak wydajność różni się z dnia na dzień (lub z godziny na godzinę). Rzeczywista wydajność zależy od wielu czynników, w tym od projektu aplikacji, konfiguracji serwera, współbieżności transakcji, wersjonowania śmieci w bazie danych i tak dalej. Aby dowiedzieć się, co dzieje się w bazie danych (zarówno pozytywne, jak i negatywne aspekty wydajności), powinieneś przynajmniej od czasu do czasu zajrzeć do statystyk bazy danych.

Przyjrzyjmy się możliwościom IBAnalyst. IBAnalyst może pobierać statystyki z gstat lub Services API i kompilować je w raport, dając pełne informacje o bazie danych, jej tabelach i indeksach. Ma wbudowane ostrzeżenia, które są dostępne podczas przeglądania statystyk; zawiera również komentarze z podpowiedziami oraz raporty z zaleceniami.

Informacje o bazie danych

Rysunek 1 Podsumowanie statystyk bazy danych

Podsumowanie pokazane na Rysunku 1 dostarcza ogólnych informacji o Twojej bazie danych. Ostrzeżenia lub komentarze są oparte na starannie zebranej wiedzy uzyskanej z dużej liczby rzeczywistych produkcyjnych baz danych.

Uwaga: Wszystkie rysunki w tym artykule zawierają statystyki gstat pobrane z rzeczywistej produkcyjnej bazy danych (za zgodą jej właścicieli).

Jak powiedziałem wcześniej, surowe statystyki bazy danych wyglądają tajemniczo i są trudne do interpretacji. IBAnalyst podświetla wszelkie potencjalne problemy wyraźnie na żółto lub czerwono, a szczegóły problemu można odczytać, po prostu umieszczając kursor nad odpowiednim wpisem i czytając wyświetloną podpowiedź.

Co możemy odkryć z powyższego rysunku? To baza danych w dialekcie 3 z rozmiarem strony 4096 bajtów. Sześć do ośmiu lat temu programiści używali domyślnego rozmiaru strony 1024 bajty, ale w nowszych czasach tak mały rozmiar strony może prowadzić do wielu problemów z wydajnością. Ponieważ ta baza danych ma rozmiar strony 4k, nie jest wyświetlane żadne ostrzeżenie, ponieważ ten rozmiar strony jest odpowiedni.

Następnie widzimy, że parametr Forced Write jest ustawiony na OFF i oznaczony na czerwono. InterBase 4.x i 5.x domyślnie miał ten parametr włączony. Forced Writes to metoda buforowania zapisu: gdy jest włączony, zapisuje zmienione dane natychmiast na dysk, ale gdy jest wyłączony, zapisy będą przechowywane przez nieokreślony czas przez system operacyjny w jego pamięci podręcznej plików. InterBase 6 tworzy bazy danych z Forced Writes wyłączonym.

Dlaczego jest to oznaczone na czerwono w raporcie IBAnalyst? Odpowiedź jest prosta - użycie zapisu asynchronicznego może spowodować uszkodzenie bazy danych w przypadku awarii zasilania, systemu operacyjnego lub serwera.

Wskazówka: Interesujące jest to, że nowoczesne interfejsy dysków twardych (ATA, SATA, SCSI) nie wykazują większej różnicy w wydajności przy Forced Write ustawionym na On lub Off(1).

Następnie w raporcie jest tajemniczy „interwał zamiatania” (sweep interval). Jeśli jest dodatni, ustawia rozmiar luki między najstarszą (2) a najstarszą transakcją snapshot, przy której silnik jest ostrzegany o potrzebie rozpoczęcia automatycznego usuwania śmieci. W niektórych systemach osiągnięcie tego progu spowoduje efekt „nagłej utraty wydajności”, w wyniku czego czasami zaleca się ustawienie interwału zamiatania na 0 (całkowite wyłączenie automatycznego zamiatania). Tutaj interwał zamiatania jest oznaczony na żółto, ponieważ wartość luki zamiatania jest ujemna, co może mieć miejsce w statystykach InterBase 6.0, Firebird i Yaffil, ale nie w InterBase 7.x. Gdy wartość luki zamiatania jest większa niż interwał zamiatania (jeśli interwał zamiatania nie jest 0), wpis raportu dla interwału zamiatania będzie oznaczony na czerwono z odpowiednią podpowiedzią.

Przeanalizujemy kolejne 8 wierszy jako grupę, ponieważ wszystkie wyświetlają aspekty stanu transakcji bazy danych:

Najstarsza transakcja to najstarsza niezatwierdzona transakcja. Wszystkie niższe numery transakcji dotyczą transakcji zatwierdzonych i dla takich transakcji nie są dostępne żadne wersje rekordów. Numery transakcji wyższe niż najstarsza transakcja dotyczą transakcji, które mogą być w dowolnym stanie. Jest to również nazywane „najstarszą interesującą transakcją”, ponieważ zamraża się, gdy transakcja kończy się wycofaniem, a serwer nie może w tym momencie cofnąć jej zmian.
Najstarszy snapshot - najstarsza aktywna (tj. jeszcze niezatwierdzona) transakcja, która istniała na początku transakcji, która jest obecnie najstarszą „interesującą” transakcją. Wskazuje najniższy numer transakcji snapshot, która jest zainteresowana wersjami rekordów.
Najstarsza aktywna - najstarsza obecnie aktywna transakcja (3).
Następna transakcja - numer transakcji, który zostanie przypisany nowej transakcji.
Aktywne transakcje - IBAnalyst wyświetli ostrzeżenie, jeśli numer najstarszej aktywnej transakcji jest o 30% niższy niż dzienna liczba transakcji. Statystyki nie mówią, czy istnieją inne aktywne transakcje między najstarszą aktywną a następną transakcją, ale takie transakcje mogą istnieć. Zwykle, jeśli najstarsza aktywna utknie, istnieją dwie możliwe przyczyny: a) jakaś transakcja jest aktywna przez długi czas lub b) projekt aplikacji pozwala na długotrwałe transakcje. Obie przyczyny uniemożliwiają usuwanie śmieci i zużywają zasoby serwera.
Transakcje dziennie - jest to obliczane z następnej transakcji, podzielone przez liczbę dni, które upłynęły od utworzenia bazy danych do momentu pobrania statystyk. Może to być poprawne tylko dla produkcyjnych baz danych lub dla baz, które są okresowo przywracane z kopii zapasowej, co powoduje resetowanie numeracji transakcji.

Jak już się dowiedziałeś, jeśli istnieją jakiekolwiek ostrzeżenia, są one wyświetlane jako kolorowe linie, z jasnymi, opisowymi podpowiedziami, jak naprawić lub zapobiec problemowi.

Należy zauważyć, że statystyki bazy danych nie zawsze są przydatne. Statystyki zebrane podczas pracy i operacji porządkowych mogą być bez znaczenia.

Nie zbieraj statystyk, jeśli:

Właśnie przywróciłeś bazę danych
Wykonałeś kopię zapasową (gbak -b db.gdb) bez przełącznika -g
Niedawno wykonałeś ręczne zamiatanie (gfix -sweep)

Statystyki uzyskane w takich sytuacjach będą praktycznie bezużyteczne. Prawdą jest również, że podczas normalnej pracy mogą być momenty, w których baza danych jest w idealnym stanie, na przykład gdy aplikacje obciążają bazę mniej niż zwykle (użytkownicy są na lunchu lub jest to spokojny czas w dniu pracy).

Jak możesz stwierdzić, kiedy coś jest nie tak z bazą danych?

Twoje aplikacje mogą być zaprojektowane tak dobrze, że zawsze będą działać poprawnie z transakcjami i danymi, nie tworząc luk zamiatania, nie gromadząc wielu aktywnych transakcji, nie utrzymując długotrwałych snapshotów i tak dalej. Zwykle tak się nie dzieje (przepraszam, koledzy).

Najczęstszym powodem jest to, że programiści testują swoje aplikacje przy zaledwie dwóch lub trzech jednoczesnych użytkownikach. Gdy aplikacja jest następnie używana w środowisku produkcyjnym z piętnastoma lub więcej jednoczesnymi użytkownikami, baza danych może zachowywać się nieprzewidywalnie. Oczywiście tryb wieloużytkownikowy może działać dobrze, ponieważ większość konfliktów wieloużytkownikowych można przetestować przy dwóch lub trzech jednocześnie uruchomionych aplikacjach. Jednak przy większej liczbie użytkowników mogą pojawić się problemy z usuwaniem śmieci. Takie potencjalne problemy można wykryć, jeśli zbierzesz statystyki bazy danych w odpowiednich momentach.

Informacje o tabelach

Przyjrzyjmy się innemu przykładowi wyników IBAnalyst.

![](/images/article_IBAnalyst (1).jpg)

Rysunek 2 Statystyki tabel

Widok statystyk tabel w IBAnalyst jest również bardzo przydatny. Może pokazać, które tabele mają dużo wersji rekordów, gdzie dokonano dużej liczby aktualizacji/usunięć, tabele pofragmentowane, z fragmentacją spowodowaną aktualizacją/usuwaniem lub przez bloby, i tak dalej. Możesz zobaczyć, które tabele są często aktualizowane i jaki jest rozmiar tabeli w megabajtach. Większość tych ostrzeżeń jest konfigurowalna.

W tym przykładzie bazy danych istnieje kilka problemów. Po pierwsze, żółty kolor w kolumnie VerLen ostrzega, że przestrzeń zajmowana przez wersje rekordów jest większa niż ta zajmowana przez same rekordy. Może to wynikać z aktualizacji wielu pól w rekordzie lub z masowego usuwania. Zobacz wiersze, w których kolumna MaxVers jest oznaczona na niebiesko. To pokazuje, że przechowywana jest tylko jedna wersja na rekord, a zatem problem wynika z masowego usuwania. Wartość w kolumnie Versions pokazuje, ile rekordów zostało usuniętych.

Długo żyjące aktywne transakcje uniemożliwiające usuwanie śmieci są głównym powodem degradacji wydajności. Dla niektórych tabel może być wiele wersji, które są nadal „w użyciu”. Serwer nie może zdecydować, czy naprawdę są w użyciu, ponieważ aktywne transakcje potencjalnie potrzebują jednej lub wszystkich tych wersji. W związku z tym serwer nie traktuje tych wersji jako śmieci, a konstruowanie poprawnego rekordu z wielu wersji zajmuje coraz więcej czasu, gdy transakcja go odczytuje. Na Rysunku 2 możesz zobaczyć dwie tabele, które mają liczbę wersji trzy razy większą niż liczba rekordów. Korzystając z tych informacji, możesz również sprawdzić, czy fakt, że Twoje aplikacje tak często aktualizują te tabele, wynika z projektu, czy z błędu.

Widok indeksów

Indeksy są używane przez silnik bazy danych do egzekwowania ograniczeń klucza podstawowego, klucza obcego i unikalności. Przyspieszają również pobieranie danych. Unikalne indeksy są najlepsze do pobierania danych, ale poziom korzyści z nieunikalnych indeksów zależy od różnorodności indeksowanych danych.

Na przykład spójrz na ADDR_ADDRESS_IDX6. Po pierwsze, sama nazwa indeksu sugeruje, że został utworzony ręcznie. Jeśli statystyki zostały pobrane przez Services API z informacjami o metadanych, możesz zobaczyć, które kolumny są indeksowane (w IBAnalyst 1.83 i nowszych). Dla badanego indeksu możesz zobaczyć, że ma 34999 kluczy, TotalDup wynosi 34995, a MaxDup wynosi 25056. Obie kolumny duplikatów są oznaczone na czerwono. Dzieje się tak, ponieważ wśród wszystkich kluczy w tym indeksie są tylko 4 unikalne wartości kluczy, jak widać w kolumnie Uniques. Co więcej, największy łańcuch duplikatów (klucz wskazujący na rekordy z tą samą wartością kolumny) wynosi 25056 - czyli prawie wszystkie klucze przechowują jedną z czterech unikalnych wartości. W rezultacie ten indeks może:

Zmniejsz szybkość procesu przywracania. OK, trzydzieści pięć tysięcy kluczy to nie problem dla nowoczesnych baz danych i sprzętu, ale wpływ i tak warto odnotować.
Spowolnij zbieranie śmieci. Indeksy z małą liczbą unikalnych wartości mogą spowalniać zbieranie śmieci nawet dziesięciokrotnie w porównaniu z całkowicie unikalnym indeksem. Problem ten został rozwiązany w InterBase 7.1/7.5 i Firebird 2.0.
Generuj niepotrzebne odczyty stron, gdy optymalizator czyta indeks. Zależy to od wartości wyszukiwanej w konkretnym zapytaniu - wyszukiwanie według indeksu z większą wartością MaxDup będzie wolniejsze. Wyszukiwanie według wartości w kolumnie z mniejszą liczbą zduplikowanych wartości będzie szybsze, ale tylko Ty wiesz, że kolumna jest indeksowana.

Dlatego IBAnalyst zwraca Twoją uwagę na takie indeksy, oznaczając je na czerwono i żółto oraz uwzględniając je w raporcie Rekomendacje. Niestety większość „złych” indeksów jest tworzona automatycznie w celu egzekwowania ograniczeń kluczy obcych. W niektórych przypadkach problem ten można rozwiązać, zapobiegając - za pomocą wyzwalaczy - usuwaniu lub aktualizacjom kluczy głównych w tabelach słownikowych. Jeśli jednak wdrożenie takich zmian nie jest możliwe, IBAnalyst będzie pokazywał „złe” indeksy na kluczach obcych za każdym razem, gdy przeglądasz statystyki.

Raporty

Nie ma potrzeby przeglądania całego raportu za każdym razem, wypatrując kolorów komórek i czytając podpowiedzi dotyczące nowych ostrzeżeń. Bardziej bezpośrednie i szczegółowe informacje można uzyskać, korzystając z funkcji Rekomendacje w IBAnalyst. Wystarczy załadować statystyki i przejść do menu Raporty/Wyświetl rekomendacje. Raport ten zapewnia analizę krok po kroku, zawierającą bardziej szczegółowe opisy ostrzeżeń dotyczących wymuszonych zapisów, interwału zamiatania, aktywności bazy danych, stanu transakcji, rozmiaru strony bazy danych, zamiatania, stron inwentarza transakcji, pofragmentowanych tabel, tabel z wieloma wersjami rekordów, masowych usunięć/aktualizacji, głębokich indeksów, indeksów nieprzyjaznych dla optymalizatora, bezużytecznych indeksów, a nawet pustych tabel. Wszystkie te informacje oraz towarzyszące im sugestie są dynamicznie tworzone na podstawie załadowanych statystyk.

Jako przykład wyniku raportu przyjrzyjmy się raportowi wygenerowanemu dla statystyk bazy danych, które widziałeś wcześniej w tym artykule:

„Całkowity rozmiar stron inwentarza transakcji (TIP) jest duży - 94 kilobajty lub 23 strony. Transakcja Read_committed używa globalnego TIP, ale transakcje snapshot tworzą własne kopie TIP w pamięci. Duży rozmiar TIP może spowolnić wydajność. Spróbuj uruchomić zamiatanie ręcznie (gfix -sweep), aby zmniejszyć rozmiar TIP.”

Oto kolejny cytat z części raportu dotyczącej tabel/indeksów:

„Liczba tabel z wersjami: 8. Duża liczba wersji rekordów zwykle spowalnia wydajność. Jeśli w tabeli jest wiele wersji rekordów, oznacza to, że zbieranie śmieci nie działa lub rekordy nie są odczytywane przez żadne polecenie select. Możesz spróbować wykonać select count(*) na tych tabelach, aby wymusić zbieranie śmieci, ale może to zająć dużo czasu (jeśli jest wiele wersji i istnieją nieunikalne indeksy) i może się nie powieść, jeśli istnieje co najmniej jedna transakcja zainteresowana tymi wersjami.

Oto lista tabel ze stosunkiem wersji do rekordów większym niż 3:


Tabela	Rekordy	Wersje	Rozmiar rek/wers
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Podsumowanie

IBAnalyst to nieocenione narzędzie, które pomaga użytkownikowi w przeprowadzaniu szczegółowej analizy statystyk bazy danych Firebird lub InterBase oraz identyfikowaniu potencjalnych problemów z bazą danych pod względem wydajności, konserwacji i sposobu, w jaki aplikacja współdziała z bazą danych. Przekształca enigmatyczne statystyki bazy danych i wyświetla je w łatwy do zrozumienia, graficzny sposób, a także automatycznie formułuje sensowne sugestie dotyczące poprawy wydajności bazy danych i ułatwienia jej konserwacji.

1 InterBase 7.5 i Firebird 1.5 mają specjalne funkcje, które mogą okresowo opróżniać niezapisane strony, jeśli Wymuszone zapisy są wyłączone.

2 Najstarsza transakcja to ta sama najstarsza interesująca transakcja, o której mowa wszędzie. Wynik gstat nie pokazuje tej transakcji jako „interesującej”.

3 Ann Harrison mówi, że najstarsza aktywna to najstarsza transakcja, która była aktywna, gdy rozpoczęła się bieżąca najstarsza aktywna transakcja. Dla aplikacji nie ma tu dużej różnicy.