Systemy zarządzania bazami danych
17. Monitorowanie SZBD
Cele monitorowania wydajności
• Monitorowanie służy sprawdzeniu, czy
parametry bazy danych mające wpływ na
wydajność są poprawnie ustalone, i jeśli nie,
wskazaniu, gdzie leży problem
• Alternatywą jest ciche czekanie na nieuniknione żale wściekłych
LUB
Monitorowanie (metoda prewencyjna)
Likwiduj problemy, gdy wykryte (metoda reakcyjna)
Jak monitorować SZBD?
• Pobierając stan wskaźników wydajności (liczników,
zegarów i szczegółowych informacji o działaniu
wnętrza SZBD)
• Porównując otrzymane wartości z wartościami idealnymi/oczekiwanymi
• Ale przecież... jest ich tak wiele!
Producenci i konsumenci zasobów SZBD
Konsumenci wysokiego poziomu
Pośredni konsumenci
zasobów
Podstawowe zasoby
Parser OptymalizatorParser Optymalizator
Podsystem wykonawczy Podsystem wykonawczy Podsystem
dyskowy Podsystem
dyskowy
Menadżer cache Menadżer
cache
Podsystem dzienników Podsystem dzienników
Podsystem zamków Podsystem zamków
Sieć Dyski
Procesor Pamięć
SQL polecenia
miejsca pomiaru wskaźników
Powtarzający się wzorzec problemów
• Przeciążający konsument wysokiego poziomu
• Źle sparametryzowany podsystem
• Przeciążony podstawowy zasób
Efekty nie zawsze są widocznie najpierw tam, gdzie są ich przyczyny!
Systematyczne podejście do monitorowania
• Pytanie 1: Czy krytyczne zapytania są wykonywane w najbardziej efektywny
sposób?
• Pytanie 2: Czy podsystemy optymalnie korzystają z zasobów?
• Pytanie 3: Czy podstawowych zasobów jest wystarczająco wiele?
Pobierz wskaźniki by odpowiedzieć na następujące pytania:
Badanie konsumentów najwyższego poziomu
• Odpowiedź na pytanie 1:
“Czy krytyczne zapytania są wykonywane w najbardziej efektywny sposób?”
1. Zidentyfikuj krytyczne zapytania 2. Zanalizuj ich plan wykonania
3. Zmierz parametry ich wykonania
Identyfikacja krytycznych zapytań
Krytyczne zapytania to zwykle te, które:
• Mają długi czas wykonania
• Są często wykonywane
• Często wskazówką będą też
narzekania użytkowników.
Użycie monitorów zdarzeń do identyfikacji krytycznych zapytań
• Jeśli użytkownicy się nie skarżą...
• Rejestruj pomiary na końcu każdego
zapytania i posortuj je względem iloczynu czasu wykonania i częstości użycia
• Narzut jest mniejszy niż przy innych narzędziach, bo wskaźniki i tak są
generowane przez monitorowane operacje i są dostępne w krótkim czasie
• Zwykle pomiary dotyczą użycia procesora,
wejścia-wyjścia i zdobytych zamków.
Przykładowy monitor zdarzeń
• Wskaźniki użycia
procesora posortowane przez Trace Data Viewer (Oracle)
• Podobne narzędzia:
Event Monitor (DB2) i Server Profiler (MSSQL)
Diagnoza kosztownych zapytań:
zanalizuj plany wykonania
• Zdania SQL są tłumaczone na wewnętrzny język wykonawczy, zanim zostaną wykonane
• Po analizie składniowe, optymalizator generuje pewną liczbę planów wykonania zapytania i szacuje ich koszty.
• Plan najlepszy (zgodnie z aktualnymi statystykami) zostaje wybrany.
SQLSQL parsuj przepisz generuj
plany
emituj
wybrany plan planplan
Parser/optymalizator
Narzędzia do odczytu planów
• Narzędzia do odczytu planów często pokazują plan graficznie jako drzewo, którego liśćmi są indeksy i tabele, a węzłami wewnętrznymi są operatory
• Plan stanowi taśmę montażową (w istocie drzewo) krotek wynikowych
• Większość narzędzi pokaże też szacowane użycie procesora, wejścia-wyjścia oraz wielkość wyniku każdego operatora.
Przykładowy wyjaśniacz planów
• Plan zapytania wg Query Analyzer (MSSQL)
• Podobne narzędzia:
Visual Explain (DB2) i SQL Analyze Tool (Oracle)
Znajdowanie dziwności w planach
Na co zwrócić uwagę w planie?
• Ścieżka dostępu do każdej tabeli
• Sortowanie wyników pośrednich
• Kolejność operatorów
• Algorytmy użyte do implementacji
operatorów
Użyć czy nie użyć? (indeksu)
select c_name, n_name from CUSTOMER join NATION on c_nationkey=n_nationkey where c_acctbal > 0
Który plan będzie lepszy?
(nation_pk is jest indeksem niepogrupowanym na n_nationkey, a acctbal_ix na c_acctbal)
Indeks niepogrupowany może być kłopotem
Każdy dostęp do takiego indeksu powoduje swobodny dostęp do stron tabeli. Przy niskiej selektywności predykatu, zwykle będą to
dostępy wielokrotnie do tej samej strony.
Efektem jest większa liczba odczytów stron niż jest stron w tabeli – odczyt pełny jest wtedy
znacznie lepszy.
Odczyt pełny Użycie indeksu 5 sek.
143,075 stron 6,777 stron 136,319 stron
76 sek.
272,618 stron 131,425 stron 273,173 stron Czas procesora
Logiczne odczyty stron Fizyczne odczyty stron Logiczne odczyty indeksu
Pomiar wykonania zapytania
• Zapytanie uznane za krytyczne, ale jego plan wygląda nieźle. Co jest grane? Wykonaj je (Tak ja my przed chwilą)
• Pomiar wykonania pozwoli ustalić ilość
zasobów użytych przez zapytanie i określić jak efektywne było to użycie
• Zasoby
– Podsystemy SZBD: cache, dysk, zamki, dziennik – System operacyjny: procesor
Monitory wydajności w służbie pomiaru wykonania zapytania
• Pozwalają na odczyt lub wyliczenie
wskaźników wydajności w dowolnym czasie
• Bardzo wiele możliwości
– Ogólne (wszystkie wskaźniki) lub szczególne
(wskaźniki dla jednego podsystemu lub zapytania) – Migawkowe, ciągłe, alarmowe
– Tekstowe i graficzne
Przykład monitora wydajności (zapytanie)
• Pomiary użycia bufora i procesora w
zapytaniu wg
Benchmark (DB2)
• Podobne narzędzia:
SET STATISTICS (MSSQL) i SQL
Analyze Tool (Oracle)
Statement number: 1 select C_NAME, N_NAME
from DBA.CUSTOMER join DBA.NATION on C_NATIONKEY = N_NATIONKEY where C_ACCTBAL > 0
Number of rows retrieved is: 136308 Number of rows sent to output is: 0 Elapsed Time is: 76.349 seconds
…
Buffer pool data logical reads = 272618
Buffer pool data physical reads = 131425
Buffer pool data writes = 0
Buffer pool index logical reads = 273173
Buffer pool index physical reads = 552
Buffer pool index writes = 0
Total buffer pool read time (ms) = 71352
Total buffer pool write time (ms) = 0
… Summary of Results ================== Elapsed Agent CPU Rows Rows Statement # Time (s) Time (s) Fetched Printed 1 76.349 6.670 136308 0
Statement number: 1 select C_NAME, N_NAME from DBA.CUSTOMER join DBA.NATION on C_NATIONKEY = N_NATIONKEY where C_ACCTBAL > 0 Number of rows retrieved is: 136308 Number of rows sent to output is: 0 Elapsed Time is: 76.349 seconds … Buffer pool data logical reads = 272618
Buffer pool data physical reads = 131425
Buffer pool data writes = 0
Buffer pool index logical reads = 273173
Buffer pool index physical reads = 552
Buffer pool index writes = 0
Total buffer pool read time (ms) = 71352
Total buffer pool write time (ms) = 0
… Summary of Results ================== Elapsed Agent CPU Rows Rows Statement # Time (s) Time (s) Fetched Printed 1 76.349 6.670 136308 0
Przykład monitora wydajności (system)
• Zmiany wskaźnika konsumpcji wejścia- wyjścia w czasie zgodnie z System Monitor (DB2)
• Podobne narzędzia:
Performance Monitor (MS Windows) i
Performance Manager (Oracle)
Badanie konsumentów najwyższego poziomu – podsumowanie
Znajdź krytyczne zapytania
Są jakieś?
Zbadaj niższe poziomy
Odpowiedz dla nich na pytanie 1
Nadmierna konsumpcja?
Strój problematyczne
zapytania tak
tak nie
nie
Badanie podstawowych zasobów
• Odpowiedz na pytanie 3:
“Czy podstawowych zasobów jest wystarczająco wiele?”
• Podstawowe zasoby to: procesor, dyski, pamięć i sieć
• Zanalizuj podstawowe wskaźniki SO by
wykryć wąskie gardła
Wskaźniki użycia procesora dla SO
100%
% użycia
procesora 70%
czas
Średnie użycie powyżej 70% powinno
oznaczać alarm.
Użycie przez SZBD nie powinno być większe niż 40%.
SZBD (na niededykowanej
maszynie) musi dostawać znaczny
udział.
Całkowite użycie
Użycie przez SZBD
Wskaźniki wydajności dysków w SO
Kolejka oczekiwania Średnia wielkość kolejki
Nowe żądania Liczba transferów
na sekundę
Powinna być bliska zeru
Średni czas oczekiwania też powinien
być bliski zeru Bezrobotny dysk z
oczekującymi zleceniami?
Sprawdź rywalizację o
sterownik.
Żądania powinny
Wskaźniki użycia pamięci (SO)
Plik wymiany
pamięć fizyczna
pamięć wirtualna Liczba błędów braku strony
na sekundę powinna być bliska zeru. Jeśli zdarza się wymiana nie może dotyczyć
bufora stron SZBD.
Ułamek pliku wymiany w użyciu pokaże, ile brakuje
fizycznej pamięci
Badanie pośrednich producentów i konsumentów
• Odpowiedz na pytanie 2:
“Czy podsystemy optymalnie korzystają z zasobów?”
• Główne podsystemu: menadżer pamięci podręcznej, podsystem dysków, podsystem zamków i podsystem dziennika o odtwarzania
• Podobnie jak przy pytaniu 3 odczytaj i zanalizuje odpowiednie wskaźniki wydajności
• Zwykły monitor wydajności jest przydatny, ale
Wskaźniki wydajności pamięci podręcznej
Pełny odczyt
Daj stronę
Wolne ranki
Odczyty i zapisy stron
Strategia wyboru
ofiary Strony
danych
Menadżer pamięci podręcznej
Jeśli strony nie ma w pamięci, odczyt strony
(logiczny) powoduje (fizyczne) żądanie wejścia-wyjścia. Ułamek
żądań logicznych, które nie powodują żądań fizycznych powinien być
powyżej 90%.
Strony są regularnie wymiatane na dysk, żeby
zrobić wolne miejsce.
Liczba wolnych ramek powinna być zawsze >0
Wskaźniki wydajności dysku
wiersze
strona
ekstent
plik
Hierarchia składowania (uproszczona)
dysk
Przemieszczanie wierszy powinno dotyczyć co najwyżej
5% z nich
Strony z małą ilością wolnego miejsca nie powinny być na
liście wolnych stron.
Fragmentacja danych: idealnie pliki z danymi SZBD powinny składać się z jednego lub kilku
(< 5) ciągłych ekstentów Położenie pliku powinno
równoważy obciążenie
Wskaźniki wydajności podsystemu zamków
Żądanie zamka
Obiekt Rodzaj Transakcja
Tablica zamków
Lista zamków oczekujących
Zakleszczenia i
przeterminowania nie powinny w ogóle występować lub być
bardzo rzadkie (< 1%
Czas oczekiwania na zamek przez transakcje
powinien być małym ułamkiem całkowitego czasu działania transakcji.
Liczba zamków
oczekujących powinna być małym ułamkiem liczby
wszystkich zamków.
Badanie zasobów podstawowych i pośrednich – podsumowanie
Odpowiedz na pytanie 3
Problemy z SO?
Odpowiedz na pytanie 2
Strój zasoby niskiego
poziomu tak
nie
Problematic
Strój Zbadaj
poziom
tak nie
