W poprzednich artykułach opisywałem ogólnie, na czym polega optymalizacja i jakie mogą być jej rezultaty. W tym artykule chciałbym skupić się na bardziej szczegółowym opisie części technicznej.
Najbardziej rozbudowane możliwości optymalizacji ma system bazodanowy Oracle, więc skupię się w opisie na nim. W innych systemach bazodanowych wygląda to podobnie, choć mniej jest elementów, które możemy stroić w celu zwiększenia wydajności.

Istnieje kilka metodyk optymalizacji.

Są metodyki generujące sztuczny ruch do bazy (tzw. Activ Tune), żeby zbadać problemy pojawiające się wraz ze wzrostem ruchu. Ich największą wadą jest testowanie jedynie wybranych procesów oraz modyfikacja danych w bazie w trakcie testów. Problematyczne może być skorzystanie z tej metody na bazie produkcyjnej.

Najskuteczniejszą według mnie jest metodyka bazująca na obserwacji naturalnego ruchu, jaki jest kierowany do bazy przez aplikację.
W związku z tym monitorowanie musi odbywać się na bazie produkcyjnej. Jeżeli chcielibyśmy monitorować wyłącznie bazę testową/akceptacyjną, to wyszukane tam problemy mogą być zupełnie inne niż te na produkcji. Moglibyśmy w ten sposób znakomicie podnieść wydajność bazy testowej, ale nie musi to dać takiego samego efektu na produkcji.

Optymalizację zawsze powinien poprzedzić audyt wydajności.
W trakcie audytu wydajności analizie poddawane są parametry inicjalizacyjne baz danych, zapytania SQL kierowane do baz, istniejące indeksy pod kątem ich wykorzystania przez aplikacje, sposoby przechowywania danych w bazie, wielkość i podział SGA, różnego rodzaju wskaźniki typu współczynnik trafienia w bufor (hit ratio), współczynnik odczytu (io ratio), współczynnik chybienia w bufor biblioteczny (library cache miss ratio).
Audyt trwa zazwyczaj 2-4h per baza.

Można spytać, czy taki audyt nie powinien trwać kilka tygodni, aby wyszukać wszystkie problemy.

Nie ma takiej potrzeby.

Audyt to rzut oka na bazę z wielu stron.
W trakcie audytu badane są zapytania, jakie znajdują się w cache-u, są one tam widoczne nawet do kilku dni po ich wykonaniu.
Nawet jeżeli w tej chwili problematycznego zapytania nie ma w cache, to będą ślady jego wykonania.
Analiza wielkości poszczególnych obszarów pamięci i współczynnika trafienia w bufor powie nam, że są w systemie zapytania, które powodują zbyt duże odczyty z dysku.
Współczynnik odczytu powie nam, czy dużo danych czytanych jest bez użycia indeksów.
Analiza częstości użycia poszczególnych obiektów powie nam, które tabele najczęściej są odczytywane i pozwoli oszacować, czy dzieje się to przy użyciu indeksów.
Na podstawie danych o tabelach i indeksach możemy zbadać, które z nich wymagają defragmentacji.

Jako wynik audytu powinna powstać oferta z gwarantowanym, mierzalnym wzrostem wydajności.

Przed rozpoczęciem optymalizacji należy wybrać, jakie będą kryteria sukcesu, najczęściej to jedno z:

• zmniejszenie obciążenia CPU
• zmniejszenie ilości danych pobieranych z dysku
• skrócenie czasu wykonywania procesów (10-20 wybranych procesów, trwających co najmniej 10s)

Na podstawie wybranego kryterium sukcesu będą dokonywane pomiary przed rozpoczęciem i na zakończenie optymalizacji, aby wyliczyć osiągnięty wzrost wydajności.

Moja rada – nigdy nie powierzajcie optymalizacji ludziom, którzy nie zagwarantują Wam osiągnięcie konkretnego, możliwego do zmierzenia wzrostu wydajności.
Jeżeli ktoś się na tym zna, to bez trudu zagwarantuje, jaki co najmniej wzrost wydajności osiągnie.
Zazwyczaj w takim przypadku spodziewajcie się osiągnięcia nawet 2 razy większego efektu (chyba nikt rozsądny nie zagwarantuje dokładnie tyle, ile spodziewa się osiągnąć).
Jeżeli zamiast konkretnych wartości padają stwierdzenia „będziemy starali się osiągnąć jak najwięcej”, „zrobimy, co się da”, to znaczy, że ci ludzie się po prostu słabo znają się wydajności.

W ramach kompleksowej optymalizacji systemu możemy wyróżnić trzy części:

1. Strojenie silnika bazy danych, w przypadku baz ORACLE jest to

• strojenie parametrów bazy danych pod kątem przyśpieszenia działania bazy,
• strojenie pamięci,
• strojenie optymalizatora kosztowego,
• strojenie redologów,
• strojenie przestrzeni tymczasowych,
• optymalizacja przechowywania danych w bazie (tabele partycjonowane,zmaterializowane widoki),
• przebudowa tabel i indeksów.

2. Optymalizacja zapytań SQL

• analiza i strojenie zapytań SQL generowanych przez aplikację,
• analiza istniejących indeksów pod kątem ich przydatności,
• tworzenie nowych indeksów,
• analiza i optymalizacja struktur bazodanowych.

3. Optymalizacja aplikacji – tylko i wyłącznie na życzenie klienta, jeżeli ma on możliwość modyfikacji kodu aplikacji

• analiza i modyfikacja zapytań sql generowanych przez aplikację,
• analiza i modyfikacja kodu PL/SQL (funkcje, package, procedury),
• analiza i modyfikacja struktury bazy.

Łączny czas optymalizacji pojedynczego systemu o od jednego do czterech miesięcy (w zależności od wielkości i stopnia skomplikowania systemu). Strojenie bazy danych, optymalizacja zapytań SQL oraz strojenie aplikacji może być realizowane w tym samym czasie. Pierwsze efekty powinny być widoczne po ok. 1-2 tygodniach od rozpoczęcia prac optymalizacyjnych. Największy skok wydajności najczęściej obserwowany jest w pierwszych 2-3 tygodniach od rozpoczęcia prac optymalizacyjnych.

Optymalizacja zazwyczaj wykonywana jest w cyklach monitorowanie – analiza – testy rozwiązania – implementacja na bazie produkcyjnej.

Po 6-10 tego typu cyklach dochodzi do sytuacji, gdzie dalsza optymalizacja jest jeszcze możliwa, ale nieuzasadniona biznesowo, np. osiągnęliśmy już wzrost wydajności rzędu 100%, możliwy jest dalszy wzrost wydajności o 1 procent, ale wymaga to 3 miesięcy prac. W takiej sytuacji należy zakończyć optymalizację.

Optymalizacja przeprowadzona przez kompetentne osoby jest zupełnie bezpieczna.

Optymalizacja nie zmienia kodu aplikacji (chyba że na wyraźną prośbę klienta),  nie wpływa na dane, wdrożone zmiany mogą być wycofane.

Do monitorowania produkcyjnej bazy danych wystarczy dostęp typu read-only umożliwiający odczyt wewnętrznych tabel bazy danych, tzw. słownika danych.

Prace prowadzone są na bazie testowej/akceptacyjnej, dopiero przetestowane rozwiązania implementowane są na bazę produkcyjną.

Zachęcam do samodzielnego sprawdzenia możliwości optymalizacji Waszych baz danych – przygotowaliśmy program do badania wydajności baz danych (na chwilę obecną gotowa jest wersja dla Oracle, pracujemy nad wersją dla MSSQL i PostgreSQL). Program jest bezpłatny, warunkiem jego używania jest rejestracja na stronie www.commit-it.pl/perfcheck

Jeżeli kogoś zainteresował temat optymalizacji i chciałby się dowiedzieć czegoś więcej, zachęcam do kontaktu: optymalizacja@commit-it.pl