Systemy zarzdzania bazami danych 17 Monitorowanie SZBD Orygina

Systemy zarządzania bazami danych 17. Monitorowanie SZBD Oryginał: Shasha & Bonnet 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 użytkowników Oryginał: Shasha & Bonnet Monitorowanie (metoda prewencyjna) LUB Likwiduj problemy, gdy wykryte (metoda reakcyjna) 17. Monitorowanie SZBD 2

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! Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 3

Producenci i konsumenci zasobów SZBD SQL Konsumenci wysokiego poziomu polecenia Parser Optymalizator Podsystem wykonawczy Podsystem dyskowy Menadżer cache miejsca pomiaru wskaźników Pamięć Oryginał: Shasha & Bonnet Procesor Podsystem zamków Pośredni konsumenci zasobów Podsystem dzienników Dyski 17. Monitorowanie SZBD Sieć Podstawowe zasoby 4

Powtarzający się wzorzec problemów Efekty nie zawsze są widocznie najpierw tam, gdzie są ich przyczyny! • Przeciążający konsument wysokiego poziomu • Źle sparametryzowany podsystem • Przeciążony podstawowy zasób Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 5

Systematyczne podejście do monitorowania Pobierz wskaźniki by odpowiedzieć na następujące pytania: • 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? Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 6

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 Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 7

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. Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 8

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. Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 9

Przykładowy monitor zdarzeń • Wskaźniki użycia procesora posortowane przez Trace Data Viewer (Oracle) • Podobne narzędzia: Event Monitor (DB 2) i Server Profiler (MSSQL) Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 10

Diagnoza kosztownych zapytań: zanalizuj plany wykonania Parser/optymalizator SQL parsuj przepisz generuj plany emituj wybrany plan • 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. Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 11

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. Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 12

Przykładowy wyjaśniacz planów • Plan zapytania wg Query Analyzer (MSSQL) • Podobne narzędzia: Visual Explain (DB 2) i SQL Analyze Tool (Oracle) Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 13

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 Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 14

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) Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 15

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 Czas procesora Logiczne odczyty stron Fizyczne odczyty stron Logiczne odczyty indeksu Fizyczne odczyty indeksu Oryginał: Shasha & Bonnet 5 sek. 143, 075 stron 6, 777 stron 136, 319 stron 7 stron 17. Monitorowanie SZBD Użycie indeksu 76 sek. 272, 618 stron 131, 425 stron 273, 173 stron 552 stron 16

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 Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 17

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 Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 18

Przykład monitora wydajności (zapytanie) • Pomiary użycia bufora i procesora w zapytaniu wg Benchmark (DB 2) • Podobne narzędzia: SET STATISTICS (MSSQL) i SQL Analyze Tool (Oracle) Oryginał: Shasha & Bonnet 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 Statement # Time (s) Fetched Printed 1 76. 349 6. 670 136308 0 17. Monitorowanie SZBD 19

Przykład monitora wydajności (system) • Zmiany wskaźnika konsumpcji wejściawyjścia w czasie zgodnie z System Monitor (DB 2) • Podobne narzędzia: Performance Monitor (MS Windows) i Performance Manager (Oracle) Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 20

Badanie konsumentów najwyższego poziomu – podsumowanie Znajdź krytyczne zapytania Zbadaj niższe poziomy nie Są jakieś? Odpowiedz dla nich na pytanie 1 tak nie Oryginał: Shasha & Bonnet Nadmierna konsumpcja? 17. Monitorowanie SZBD tak Strój problematyczne zapytania 21

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 Przyda się tu systemowy monitor wydajności • • Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 22

Wskaźniki użycia procesora dla SO 100% % użycia procesora 70% Całkowite użycie Użycie przez SZBD czas Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD Ś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ł. 23

Wskaźniki wydajności dysków w SO Powinna być bliska zeru Średnia wielkość kolejki Liczba transferów na sekundę Nowe żądania Średni czas oczekiwania też powinien być bliski zeru Bezrobotny dysk z oczekującymi zleceniami? Sprawdź rywalizację o sterownik. Żądania powinny być zrównoważone między dyskami. Oryginał: Shasha & Bonnet Kolejka oczekiwania 17. Monitorowanie SZBD 24

Wskaźniki użycia pamięci (SO) 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 Oryginał: Shasha & Bonnet pamięć fizyczna pamięć wirtualna Plik wymiany 17. Monitorowanie SZBD 25

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 mogą się przydać także specyficzne narzędzia • • Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 26

Wskaźniki wydajności pamięci podręcznej Pełny odczyt Daj stronę Menadżer pamięci podręcznej Strategia wyboru ofiary Strony danych 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%. Wolne ranki Odczyty i zapisy stron Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD Strony są regularnie wymiatane na dysk, żeby zrobić wolne miejsce. Liczba wolnych ramek powinna być zawsze >0 27

Wskaźniki wydajności dysku Przemieszczanie wierszy powinno dotyczyć co najwyżej 5% z nich wiersze Hierarchia składowania (uproszczona) strona Strony z małą ilością wolnego miejsca nie powinny być na liście wolnych stron. ekstent Fragmentacja danych: idealnie pliki z danymi SZBD powinny składać się z jednego lub kilku (< 5) ciągłych ekstentów plik Położenie pliku powinno równoważy obciążenie dysków dysk Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 28

Wskaźniki wydajności podsystemu zamków Żądanie zamka Obiekt Rodzaj Lista zamków oczekujących Transakcja Tablica zamków Oryginał: Shasha & Bonnet 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. Zakleszczenia i przeterminowania nie powinny w ogóle występować lub być bardzo rzadkie (< 1% transakcji) 17. Monitorowanie SZBD 29

Badanie zasobów podstawowych i pośrednich – podsumowanie Odpowiedz na pytanie 3 Odpowiedz na pytanie 2 Strój podsystemy Oryginał: Shasha & Bonnet tak Problematic subsystems? nie 17. Monitorowanie SZBD Problemy z SO? tak Strój zasoby niskiego poziomu Zbadaj poziom wyższy 30

Podsumowanie • Monitorowanie wydajności SZBD można robić systematycznie – Łańcuch producentów i konsumentów pomaga w rozróżnieniu przyczyn od skutków – Istnieją narzędzia do wydobywania wartości wskaźników wydajnościowych – Trzy pytanie kierują całym procesem monitorowania Oryginał: Shasha & Bonnet 17. Monitorowanie SZBD 31