Systemy zarządzania bazami danych

18. Strojenie interfejsu

Aplikacja

Indeksy Współbieżność Procesor zapytań Podsystem składu Odtwarzanie System operacyjny Oryginał: Shasha & Bonnet Sprzęt [Procesor(y), Dysk(i), Pamięć] 18. Strojenie interfejsu 2

Dostęp do bazy danych

• 4GL – Power++, Visual Basic, Oracle Forms • Język programowania + CLI – ODBC: Open DataBase Connectivity – JDBC: Java API – OCI (C++/Oracle), CLI (C++/ DB2) – Perl/DBI – ... [ to co kochacie, np. ] django, Ruby on Rails, Python, etc. Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 3

Lapsusy wokół API

• Koszt przenośności – Dodatkowa warstwa abstrakcji nad sterownikami ODBC, która ukrywa różnice między sterownikami o różnych poziomach zgodności – Uwaga na problemy wydajnościowe związane z tą warstwą: • Użycie meta-danych przy wysyłaniu zapytań i uzyskiwaniu dostępu do wyniku • Iteracja po wyniku Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 4

80 60 40 20 0 0

ODBC a OCI

OCI ODBC 2000 4000 6000 Number of records transferred 8000 10000

• ODBC a OCI w Oracle8iEE na Windows 2000 • Iteracja po zbiorze wyników po jednym rekordzie x

prefetchingiem

• Małe narzuty OCI, gdy liczba rekordów do przesłania jest mała • ODBC zachowuje się lepiej przy większej liczbie rekordów. Lepiej wykorzystuje

prefetching

Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 5

Między klientem a serwerem

• Pula połączeń i przełączanie, gdy wielu klientów łączy się z serwerem • Bufor komunikacyjny na serwerze. Jest jeden na połączenie.

– Jeśli klient nie konsumuje wyników wystarczająco szybko, zasoby serwera są zajęte do czasu wypisania wyniku.

– Dane są wysyłane, gdy bufor komunikacyjny jest pełny lub zapytanie się zakończyło • Mały bufor – narzuty na częstą komunikację • Duży bufor – dłuższy czas oczekiwania na pierwszy rekord • Nie ma zbyt wielkiego znaczenia w sieci >100Mb. Istotne w sieciach o niższej przepustowości Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 6

Ortodoksja obiektowa szkodzi

• authorized(user, type) • doc(id, type, date) • Jakie dokumenty może zobaczyć użytkownik?

• SQL: select doc.id, doc.date

from authorized, doc where doc.type = authorized.type

and authorized.user = :usr • Jeśli dokumenty są hermetyzowane w obiekty: – Znajdź typy dokumentów :

t

dostępne dla użytkownika

:usr

select doc.type as t from authorized where user = :usr; – Dla każdego :

t

wyślij zapytanie: select id, date from doc where type = :

t

; – Aplikacja a nie SZBD realizuje to złączenie!

Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 7

Unikaj interakcji z użytkownikiem w ramach transakcji

• Interakcja z użytkownikiem w transakcji sprawia, że zamki są trzymane przez bardzo długi czas • Starannie projektuj transakcje (może podziel je na mniejsze?), aby nie wpaść w te sidła Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 8

Minimalizuj liczbę komunikatów

• Unikaj pętli: – Języki programowania aplikacji oferują pętle (zdanie SQL, przeglądanie kursora, pozycjonowana modyfikacja) – Ortodoksyjne programowanie obiektowe może wymuszać takie pętle • Paczkuj kilka zdań SQL w jednym żądaniu do SZBD – Wsady – Programowanie składowane na serwerze w języku mającym wbudowany SQL i instrukcje sterujące (Transact SQL, PL/SQL, pgPL/SQL, SQL/PL) Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 9

600 500 400 300 200 100 0 loop no loop

Pętle

• Pobierz 2000 rekordów • Pętla: 200 zapytań • Bez pętli: 1 zapytanie • Zbyt częste przekraczanie interfejsu aplikacji pogarsza wydajność Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 10

5000 4000 3000 2000 1000 0 cursor

Oryginał: Shasha & Bonnet

SQL

Kursory

• Zapytanie pobiera 200000 56-bajtowych rekordów • Czas odpowiedzi dla SQL wynosi kilka sekund, a iteracja po kursorze trwa więcej niż godzinę 18. Strojenie interfejsu 11

Funkcje użytkownika

3 2 1 0 6 5 4 20%

Oryginał: Shasha & Bonnet

UDF application function 80%

• Funkcja oblicza liczbę dni roboczych między dwiema datami • Funkcja ta wykonana na serwerze bazy danych (UDF) lub po stronie aplikacji • Użycie UDF poprawia wydajność, gdy pomaga istotnie zmniejszyć ilość danych wysyłanych do aplikacji 18. Strojenie interfejsu 12

1.75

1.5

1.25

1 0.75

0.5

0.25

0

Pobieraj tylko potrzebne kolumny

all covered subset no index index

• Nie przesyłaj niepotrzebnych danych • Może to uniemożliwić użycie indeksu pokrywającego • Eksperyment dotyczy przesyłu ¼ atrybutów – Redukcja ilości danych przekraczających granice interfejsu aplikacji daje sporą poprawę wydajności Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 13

Pobieraj tylko potrzebne wiersze

• Jeśli użytkownik ogląda tylko niewielki podzbiór dużego zestawu danych, lepiej jest: – Przesyłać tylko ten podzbiór – Obliczać tylko ten podzbiór • Aplikacje pozwalające na formułowanie zapytań

ad-hoc

, powinny pozwalać także na ich przerywanie Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 14

Minimalizuj kompilacje zapytań

0.6

0.4

0.2

0 0 1 2 3

Oryginał: Shasha & Bonnet

4 direct prepared 5 6

Eksperyment wykonany na Oracle8iEE na Windows 2000.

•

Prepared statement

daje lepszą wydajność, gdy zapytanie jest wykonywane więcej niż jeden raz – Brak kompilacji – Brak dostępu do słownika danych • Zachowane plany zapytań stają się nieaktualne, gdy dodano indeksy lub zmieniły się statystyki relacji 18. Strojenie interfejsu 15

Strojenie interfejsu aplikacji

• Unikaj interakcji z użytkownikiem w ramach transakcji • Minimalizuj liczbę komunikatów między aplikacją i bazą danych • Pobieraj tylko potrzebne kolumny • Pobieraj tylko potrzebne wiersze • Minimalizuj kompilacje zapytań (

hard parse

,

soft parse

,

prepared statement

) Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 16

Masowe ładowanie danych

• W SZBD są narzędzia do masowego ładowania danych • Parametry ładowania – Ominięcie procesora zapytań – Rezygnacja z wpisów do dziennika – Rezygnacja z aktualizacji indeksów – Rezygnacja z kontroli więzów integralności – Częstotliwość zatwierdzania Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 17

Ścieżka bezpośrednia

50000 40000 30000 20000 10000 0 conventional direct path

Oryginał: Shasha & Bonnet

65 insert

Eksperyment wykonany na Oracle8iEE na Windows 2000.

• Ładowanie 600000 rekordów do tabeli

lineitem

z zestawu TPCH • Ścieżka bezpośrednia omija procesor zapytań i menadżera składowania. • Jest o rzędy wielkości szybsza niż konwencjonalna (z zatwierdzanie co 100 rekordów) i wstawianiem z zatwierdzaniem każdego rekordu 18. Strojenie interfejsu 18

Wielkość wsadu

5000 4000 3000 2000 1000 0 0 100000 200000 300000 400000 500000 600000

Eksperyment wykonany na SQL Server 2000 na Windows 2000.

Oryginał: Shasha & Bonnet • Masowe ładowanie 600000 rekordów • Przepustowość równomiernie rośnie aż do wielkości wsadu 100000 rekordów. Potem pozostaje stała • Kompromis między wydajnością i ilością danych do przeładowania w przypadku awarii 18. Strojenie interfejsu 19

Parametry podsystemu składu

8000 6000 4000 2000 0

• Masowe ładowanie 600000 rekordów • Zgodnie z oczekiwaniem Eksperyment wykonany na IBM DB2 UDB V7.1

na Windows 2000.

Oryginał: Shasha & Bonnet 18. Strojenie interfejsu – Wyłączenie dziennika pomaga – Zbieranie statystyk szkodzi – Przyrostowa aktualizacja indeksów szkodzi bardzo 20

Łączenie z wieloma bazami danych

• Dzielone połączenia obniżają wysokie koszty inicjacji – Pule połączeń • Wysyłaj zapytania żywcem, gdy zajętość procesora klienta jest krytyczna – Eliminacja przepisywania zapytania, aby dostosować się do konkretnego dialektu SQL • Przesyłaj duże ilości danych, gdy wydajność nie jest ograniczona przepustowością łącza Oryginał: Shasha & Bonnet 18. Strojenie interfejsu 21