Kontakty
IBSurgeon
Ta strona została przetłumaczona maszynowo. Przeczytaj oryginał angielski. English

Biblioteka IBSurgeon

IBAnalyst: Wskazówki i triki

This text was originally written in 2012, it is valid for version 1.0 - 2.5, in versions 3.0-5.0 there were many changes, wgcih could not be reflected. Please read documentation or contact us for support: [email protected].

Niektóre pytania, na które nie ma odpowiedzi w Rekomendacjach IBAnalyst i/lub Pomocy:

1. Jak przebudować indeksy na ograniczeniach PRIMARY, FOREIGN lub UNIQUE?

O: Dla wersji Firebird 1.0-2.5. Tak, nie można użyć ALTER INDEX xxx INACTIVE/ACTIVE na indeksach ograniczeń. Jeśli widzisz głęboki lub sfragmentowany indeks na tym ograniczeniu, możesz użyć specjalnego triku (używanego przez gbak podczas przywracania):

RDB$INDICES ma flagę RDB$INDEX_INACTIVE, która jest równa null lub 0, jeśli indeks jest aktywny (po CREATE INDEX lub ALTER INDEX ACTIVE). 1 oznacza, że indeks jest nieaktywny (po ALTER INDEX INACTIVE). Istnieje jednak również wartość 3, która jest używana do oznaczania nieaktywnych indeksów na ograniczeniach. Możesz więc ustawić RDB$INDEX_INACTIVE=3 dla tego indeksu, wykonać COMMIT, a następnie przywrócić wartość do 0 i ponownie wykonać COMMIT - indeks zostanie przebudowany.

Dla Firebird 3.0-5.0 - po prostu wykonaj ALTER INDEX nazwa_indeksu ACTIVE

2. Wykorzystałem wszystkie rekomendacje IBAnalyst, ale to nie pomogło przyspieszyć zapytań.

O: To osobny problem, w którym IBAnalyst nie może pomóc. Mogą być 2 przyczyny problemu:

  1. Indeksy mają nieaktualne statystyki. Możesz odświeżyć statystyki indeksów poleceniem SET STATISTICS INDEX xxx (więcej szczegółów http://www.ibase.ru/proc_selectivity/).

  2. Po prostu nie ma odpowiedniego indeksu dla niektórych warunków użytych w zapytaniu.

  3. Zapytania są bardzo złożone lub optymalizator nie może ich zoptymalizować, dlatego konieczne jest przekształcenie zapytania.

  4. W niektórych przypadkach zaraz po przywróceniu bazy zobaczysz “sfragmentowane tabele”.

Normalnie Firebird i InterBase (bez parametru -use_all_space) rezerwują około 25% miejsca na stronach danych dla przyszłych wstawień, aktualizacji lub usunięć (aby umieścić wersje rekordów). Jednak przy każdym rozmiarze strony bazy danych (1, 2, 4 lub 8 kB) zobaczysz około 50% fragmentacji dla tabel o małym rozmiarze rekordu (około 12-20 bajtów, na przykład tabela z 2 polami całkowitoliczbowymi ma średni rozmiar rekordu = 12 bajtów).

To jest normalne, potraktuj to jako magiczną liczbę serwera (lub zachowanie).

Jeśli więc masz takie tabele z małymi rekordami, możesz:

a) zignorować ostrzeżenie o “fragmentacji” dla tych tabel

b) obniżyć “fragmentację %” do 45%, na przykład, w oknie Opcje IBAnalyst.

4. Wersje rekordów dla tabeli, która nie powinna być aktualizowana

Jeśli widzisz wersje rekordów w tabeli, która nie powinna być aktualizowana (na przykład tabela z logiem zdarzeń) - nie martw się, te wersje są generowane przez usunięcia.

Dzięki temu dowiesz się, ile aktualnych rekordów jest w tabeli i ile rekordów zostało usuniętych.

Jest to prawdą tylko wtedy, gdy MaxVer = 1. Jeśli jest > 1, to tabela jest aktualizowana przez jakąś aplikację. Jeśli naprawdę jesteś pewien, że ta tabela nigdy nie powinna być aktualizowana, lepiej ustawić wyzwalacz “before update” z wyjątkiem, aby znaleźć aplikację wykonującą aktualizacje.

5. Obiekty BLOB mogą powodować fragmentację tabeli.

Silnik przechowuje obiekty BLOB na 3 różne sposoby:

  1. Jeśli zawartość BLOB zmieści się na stronie danych (jest wystarczająco wolnego miejsca), zostanie zapisana na tej stronie danych w pobliżu swojego rekordu (lub wersji).

  2. Jeśli zawartość BLOB nie zmieści się na stronie danych, zostanie zapisana na osobnej stronie.

  3. Jeśli w przypadku 2 BLOB nie zmieści się na jednej stronie danych, tworzona jest strona wskaźnikowa wskazująca odpowiednie strony BLOB.

Przypadek 1 występuje w zależności od rozmiaru przechowywanego BLOB i rozmiaru strony bazy danych. Na przykład, jeśli miałeś rozmiar strony 4K i obiekty BLOB o średnim rozmiarze ~5K, nie są one przechowywane na stronach danych, ale na dodatkowych stronach BLOB.

Ale jeśli wykonasz kopię zapasową bazy danych i przywrócisz ją z rozmiarem strony 8K, obiekty BLOB zmieszczą się na stronie danych i będą przechowywane razem z rekordami, powodując wysoką fragmentację rekordów.

IBAnalyst oznacza te tabele jako Pale (kolumna Records), a podpowiedź pokazuje szacunkową liczbę rekordów dla tej tabeli (na podstawie liczby stron danych) i rzeczywistą średnią wartość wypełnienia (%).

Jeśli Twoje zapytanie odczytuje z tej tabeli jakiekolwiek pola oprócz BLOB, naturalne skanowanie, złączenie lub agregacja będą działać bardzo wolno.

Jedynym rozwiązaniem, aby tego uniknąć: utwórz dodatkową tabelę (połączoną 1-1 z oryginalną tabelą) i przenieś do niej wszystkie kolumny BLOB o średnim rozmiarze mniejszym niż rozmiar strony.

W takim przypadku nie próbuj wykonywać kopii zapasowej/przywracania z większym rozmiarem strony! Spowoduje to, że obiekty BLOB, które nie mieściły się na stronach danych przy obecnym rozmiarze strony, zostaną umieszczone na stronach danych podczas przywracania z większym rozmiarem strony. Twoje tabele z obiektami BLOB będą więc bardziej sfragmentowane niż wcześniej.

Nie zaleca się również przywracania z mniejszym rozmiarem strony, ponieważ może to zmniejszyć wydajność indeksów i tabel bez BLOB.

Nie powinieneś też próbować zmieniać pól BLOB na pola varchar - pola varchar są zawsze przechowywane jako część rekordu, więc rekord może mieć 2 lub więcej fragmentów (być umieszczony na 2 lub więcej stronach danych), jeśli nie mieści się na stronie danych.

p.s. IBAnalyst może zgłaszać te tabele “przez pomyłkę”, na przykład tabela miała pola BLOB z danymi, ale zostały one usunięte ze struktury tabeli. Niestety nie ma konfigurowalnej opcji dla tego ostrzeżenia, ponieważ obliczamy je dokładnie z danych zgłaszanych przez serwer (statystyki).

6. Relacja VerLen i RecLength

a) VerLen >= 90% RecLength: wersje widoczne w kolumnie Version to głównie usunięcia rekordów. Im więcej rekordów usunięto, tym mniejszy będzie RecLength (do 0 bajtów). VerLen może być również większy niż RecLen, jeśli aktualizujesz tabelę większymi danymi tekstowymi niż te przechowywane w oryginalnych rekordach.

b) VerLen <= 80% RecLength: wersje to głównie aktualizacje rekordów.

Nie możemy dokładniej rozróżnić tych przypadków, ponieważ statystyki pokazują średni rozmiar rekordu i wersji dla całej tabeli, podczas gdy widoczna liczba wersji dla równoczesnych transakcji może się różnić.

7. Dlaczego IBAnalyst nazywa niektóre indeksy “złymi”?

Indeksy o wartości selektywności niższej niż 0.01 są oznaczone jako “złe” w IBAnalyst (patrz pomoc widoku Index). Istnieje kilka przyczyn, dla których dany indeks jest nazywany złym:

  1. Selektywność tego indeksu jest niższa niż 0.01. Teoretycznie optymalizator nie powinien używać tego indeksu, ale używa go, jeśli nie ma innych indeksów (dla where, order by lub klauzuli join, przynajmniej).

  2. Taki indeks powoduje bardzo wolne usuwanie śmieci (garbage collection). Ten problem nie istnieje w InterBase 7.1/7.5 i zostanie naprawiony w Firebird 2.0.

  3. Ten indeks sprawia, że proces przywracania jest bardzo wolny, a sam indeks jest tworzony bardzo wolno (create/alter index active). Dzieje się tak, ponieważ łańcuch numerów rekordów jest duży dla jednego klucza indeksu.

  4. Jeśli ten indeks jest używany w klauzuli where, użycie pamięci będzie zależeć od wyszukiwanej wartości (rozmiar maski bitowej). Ponieważ łańcuch rekordów może być duży (dużo duplikatów kluczy), zużycie pamięci będzie również duże.

  5. Jeśli ten indeks jest używany w “order by” i jest dużo duplikatów głównie w niższych wartościach klucza (w zależności od kolejności sortowania indeksu), będzie dużo odczytów stron indeksu, co spowolni zapytanie.

Dlatego IBAnalyst nie może zignorować istnienia takich indeksów.

Najgorszy przypadek dla indeksu to sytuacja, gdy ma kolumnę Uniques = 1, tj. wszystkie wartości dla indeksowanej kolumny są takie same. Te indeksy są wymienione w “Bezużyteczne indeksy” na stronie Podsumowanie.

Oczywiście dla Twojej aplikacji taki indeks może być “dobry”. Na przykład, jeśli rekordy mają flagę “archiwum” w jakiejś kolumnie, a Twoja aplikacja wyszukuje po indeksie na tej kolumnie tylko bieżące, nie zarchiwizowane dane. Zatem to Ty decydujesz, czy mamy rację, nazywając ten indeks “złym”, czy nie.

8. Co jeśli “zły” indeks został utworzony przez ograniczenie Foreign Key?

Cóż, poprzedni akapit pokazuje, że lepiej usunąć “złe” indeksy (jeśli nie używasz ich do wyszukiwania kluczy mających mniej duplikatów niż inne klucze). Ale jeśli taki indeks został utworzony przez klucz obcy, możesz go usunąć tylko poprzez usunięcie klucza obcego. Usunięcie klucza obcego wyłączy ograniczenie sprawdzania relacji, co może być nie do zaakceptowania.

Możesz zastąpić FK wyzwalaczami, ale z pewnymi ograniczeniami. FK kontroluje relacje rekordów za pomocą indeksu, a indeks “widzi” wszystkie klucze dla wszystkich rekordów niezależnie od stanu transakcji. Ale wyzwalacze działają tylko w kontekście transakcji klienta. Zastępując FK wyzwalaczami, musisz być pewien, że:

  • Rekordy nie będą usuwane z tabeli nadrzędnej lub będą usuwane w trybie “snapshot table reserving”
  • Kolumna używana przez PK w tabeli nadrzędnej nigdy nie zostanie zmodyfikowana. Możesz to ograniczyć wyzwalaczem before update.

Jeśli spełnisz te warunki, możesz usunąć konkretny klucz obcy. Oczywiście nie twórz ręcznie indeksu na tej kolumnie.

9. Dlaczego w wierszu procentu wersji danych jest tylko 12 megabajtów danych, a mam bazę 140 megabajtów?

  1. IBAnalyst pokazuje tutaj “czystą” objętość danych, bez uwzględnienia innych struktur bazy danych (indeksy, metadane…) i fragmentacji stron.

  2. Po przywróceniu InterBase i Firebird pozostawiają trochę wolnego miejsca (15-25%) na stronach danych, aby przyspieszyć przyszłe aktualizacje/usunięcia.

  3. Istnieje specyficzne zachowanie serwera, gdy pozostawia strony danych sfragmentowane w około 50%, jeśli rozmiar rekordu tabeli jest mały, około 11-22 bajtów.

10. Jak poprawić wydajność optymalizatora w przypadku częstych aktualizacji

Statystyki indeksów są przechowywane w kolumnie RDB$INDICES.RDB$STATISTICS i są aktualizowane na 3 sposoby:

  1. SET STATISTICS INDEX

  2. ALTER INDEX ACTIVE lub CREATE INDEX …

  3. proces przywracania (wszystkie indeksy są przebudowywane, jak również “ALTER INDEX ACTIVE”)

Optymalizator używa tych informacji statystycznych do przygotowania zapytań. Na podstawie wartości statystyk optymalizator może zdecydować, że indeks jest “wystarczająco dobry” lub “nieprzydatny” do pobierania rekordów.

Jeśli statystyki nie były aktualizowane przez długi czas, optymalizator może wygenerować zły plan, ponieważ istniejące wartości statystyk nie odpowiadają rzeczywistemu stanowi rzeczy, ponieważ dane w tabeli mogły się znacząco zmienić (na przykład liczba rekordów wzrosła 5-10 razy lub odwrotnie, wszystkie rekordy zostały usunięte).

Możesz zastąpić zły automatyczny plan zapytania jawnym PLANEM dla konkretnego zapytania, ale to nie jest dobre podejście, ponieważ dane mogą się znacząco zmienić po opracowaniu planu.

Alternatywnym (i właściwym) sposobem jest okresowe odświeżanie statystyk poprzez zastosowanie polecenia SET STATISTICS dla wszystkich indeksów. Możesz zaplanować uruchomienie skryptu SQL w celu odświeżenia statystyk za pomocą ISQL lub gotowego narzędzia gidx (tylko Windows).

Jeśli masz tabele z okresowo przeładowywanymi różnymi rekordami, to podejście nie pomoże. Rozważmy przykład:

  • Tabela A jest ładowana danymi 4-5 razy dziennie.
  • Po przetworzeniu załadowanych danych wszystkie rekordy w tabeli A są usuwane.

W tym przypadku możemy zobaczyć 2 poprawne wartości statystyk dla indeksów na tabeli A - gdy jest załadowana danymi i gdy jest pusta. Statystyki przeliczone na załadowanej tabeli będą bezużyteczne, gdy tabela jest pusta, i odwrotnie.

Aby tego uniknąć, musisz przeliczać statystyki dla indeksów na tabeli A tylko wtedy, gdy tabela jest wypełniona danymi. Najlepiej przed uruchomieniem zapytań na tej tabeli.

Od wersji 1.91 IBAnalyst pokazuje różnicę statystyk indeksów i pozwala na ich przeliczenie w dowolnym momencie. Najpierw musisz sprawdzić informacje o rekordach tabeli - czy to zwykła średnia liczba rekordów, czy nie. Jeśli tak, możesz bezpiecznie przeliczyć selektywność indeksów. Jeśli nie - może lepiej nie dotykać statystyk indeksów, ponieważ może to spowodować, że optymalizator wygeneruje jeszcze gorsze plany zapytań.