DB2 - planowanie zapytań
Andrzej Dąbrowski - 10-10-2007 00:01
DB2 - planowanie zapytań
Witam!
Robię testy porównawcze baz danych na sporej bazie danych (20 milionów
wpisów do jednej tabeli) i okazało się, że DB2 V9.1 jest sporo wolniejsza od
PostgreSQL i Firebirda. Wydawało mi się, że powinna być szybsza. Widzę dwa
powody takiego stanu rzeczy:
- kiepskie wykorzystanie procesora i pamięci, najczęściej od kilku do
kilkunastu procent obciażenia procesora, rzadko kilkadziesiąt
- kiepska analiza zapytania - w narzędziu Centrum sterowania potrafię
wyświetlić plan zapytania i nie widzę tam użycia indeksów (są w bazie
sprawdziłem), a analiza planu jeży wlos na głowie, jakieś zbędne sortowania
dużych tabel, itp.
Zapytania ogólnie odnoszą sie do View, które składają się z głównej tabeli,
oraz kilku tabel słownikowych. Jeśli jest sortowanie po kolumnie z głównej
tabeli (do tego indeksowanej), włączony limit wierszy (1000), łączenia
poprzez left join, oraz żadnych warunków w klauzuli WHERE (testowo, bo one
komplikują zaplanowanie zapytania) to na zdrowy rozum wystarczy wziać ta
kolumnę, wybrać po indeksie żądany 1000 wierszy (od razu posortowany), do
tego LEFT JOINem dociągnąć jeśli istnieją nazwy z tabel słownikowych i już
jest wynik. Tak robi np. PostgreSQL i zwraca wynik w ciągu 1-2s, a DB2
potrafi "bawić" się z godzinę, przy niewielkim zużyciu procesora. Co więcej
PostgreSQL ładnie korzysta z indeksów nawet przy "zwykłym" JOINie, oraz
wanukach w klauzuli WHERE.
Pytania właściwe:
Jakim narzędziem i jak najprościej "podrasowac" DB2 V9.1 aby używał wiecej
zasobó systemowych (Windows XP Prof)?
W jaki sposób wymusić plan zapytania na serwerze i "uruchomić" użycie
indeksów?
Czy mieliście podobne problemy i co sugerujecie zrobić?
Andrzej Dąbrowski - Sir Daban
Sebastian Ciesielski - 10-10-2007 00:01
=?iso-8859-2?Q?Re:_DB2_-_planowanie_zapyta=F1?=
> - kiepskie wykorzystanie procesora i pamięci, najczęściej od kilku do
> kilkunastu procent obciażenia procesora, rzadko kilkadziesiąt
> - kiepska analiza zapytania - w narzędziu Centrum sterowania potrafię
> wyświetlić plan zapytania i nie widzę tam użycia indeksów (są w bazie
> sprawdziłem), a analiza planu jeży wlos na głowie, jakieś zbędne
> sortowania dużych tabel, itp.
skoro nie widzisz w planie użycia indeksów, DB2 stwierdziło, że są one
bezużyteczne,
a o to trzeba się bardzo postarać,
analiza planu zapytania powinna wskazać, jakich potrzeba, skąd wiesz, że te
sortowania są zbędne? załóż tam indeksy i zmierz rezultat
dużo zyskasz, jak optymalnie przydzielisz sobie tabele i indeksy do
odpowiednich tejblspejsów
S.
Andrzej Dąbrowski - 10-10-2007 00:01
Użytkownik "Sebastian Ciesielski" <sebac@poczta.fm> napisał w wiadomości
news:fefqjh$del$1@inews.gazeta.pl...
>> - kiepska analiza zapytania - w narzędziu Centrum sterowania potrafię
>> wyświetlić plan zapytania i nie widzę tam użycia indeksów (są w bazie
>> sprawdziłem), a analiza planu jeży wlos na głowie, jakieś zbędne
>> sortowania dużych tabel, itp.
>
> skoro nie widzisz w planie użycia indeksów, DB2 stwierdziło, że są one
> bezużyteczne,
> a o to trzeba się bardzo postarać,
>
> analiza planu zapytania powinna wskazać, jakich potrzeba, skąd wiesz, że
> te sortowania są zbędne? załóż tam indeksy i zmierz rezultat
>
> dużo zyskasz, jak optymalnie przydzielisz sobie tabele i indeksy do
> odpowiednich tejblspejsów
Hmm, indeksy są głównie na kuczach obcych (samo DB2 zakłada je tam
automatycznie), oraz na kilku polach p októrych najczęściej są warunki WHERE
i sortowanie. Nakłamałem trochę, bo przyjrzałem się dokładniej i tabele
słownikowe sa brane po tych właśnie automatycznych indeksach (nie
skojarzyłem od razu, że SQL00018083419036129056 to indeks :) ). Jednak samo
sortowanie odbywa się późno, oraz w niektórych "dziwnych" miejscach, a
najlogiczniej by było od razu. W przypadku zapytań z wieloma warunkami to
DB2 rozbija to na skomplikowane drzewo i nawet dosć wydajnie to robi, ale w
najprostszym przypadku "select * from view fetch first 1000 rows only"
Postgres robi to tak jak mi się wydaje, że powinno sie to robić i robi to
błyskawicznie.
W jaki sposób rozdzielić tabele pomiedzy "tejblspejsy" - i tak leżą na
jednym dysku? Co mówi o tym teoria/praktyka - słowniki, czy indeksy
przerzucic?
Czy użycie perspektyw zmaterializowanych by coś pomogło i jak technicznie są
one wykonywane w DB2?
Andrzej Dąbrowski
Artur - 10-10-2007 00:50
=?iso-8859-2?q?Re:_DB2_-_planowanie_zapyta=F1?=
On 9 Paź, 14:17, "Andrzej Dąbrowski" <andr...@sims.pl> wrote:
> Użytkownik "Sebastian Ciesielski" <se...@poczta.fm> napisał w wiadomościnews:fefqjh$del$1@inews.gazeta.pl...
>
> >> - kiepska analiza zapytania - w narzędziu Centrum sterowania potrafię
> >> wyświetlić plan zapytania i nie widzę tam użycia indeksów (są w bazie
> >> sprawdziłem), a analiza planu jeży wlos na głowie, jakieś zbędne
> >> sortowania dużych tabel, itp.
>
> > skoro nie widzisz w planie użycia indeksów, DB2 stwierdziło, żesą one
> > bezużyteczne,
> > a o to trzeba się bardzo postarać,
>
> > analiza planu zapytania powinna wskazać, jakich potrzeba, skąd wiesz, że
> > te sortowania są zbędne? załóż tam indeksy i zmierz rezultat
>
> > dużo zyskasz, jak optymalnie przydzielisz sobie tabele i indeksy do
> > odpowiednich tejblspejsów
>
> Hmm, indeksy są głównie na kuczach obcych (samo DB2 zakłada je tam
> automatycznie), oraz na kilku polach p októrych najczęściej są warunki WHERE
> i sortowanie. Nakłamałem trochę, bo przyjrzałem się dokładniej i tabele
> słownikowe sa brane po tych właśnie automatycznych indeksach (nie
> skojarzyłem od razu, że SQL00018083419036129056 to indeks :) ). Jednak samo
> sortowanie odbywa się późno, oraz w niektórych "dziwnych" miejscach, a
> najlogiczniej by było od razu. W przypadku zapytań z wieloma warunkami to
> DB2 rozbija to na skomplikowane drzewo i nawet dosć wydajnie to robi, ale w
> najprostszym przypadku "select * from view fetch first 1000 rows only"
> Postgres robi to tak jak mi się wydaje, że powinno sie to robić i robi to
> błyskawicznie.
> W jaki sposób rozdzielić tabele pomiedzy "tejblspejsy" - i tak leżą na
> jednym dysku? Co mówi o tym teoria/praktyka - słowniki, czy indeksy
> przerzucic?
> Czy użycie perspektyw zmaterializowanych by coś pomogło i jak technicznie są
> one wykonywane w DB2?
>
> Andrzej Dąbrowski
Spróbuj zrobić to tak:
select ....
fetch first 1000 rows only
optimize for 1 row
Bez tej ostatniej kaluzuli baza niestety będzie optymalizowała dla
całego zbioru wynikowego, a przypuszczam, że w zapytaniu nie było
warunków filtrujących, poza limitem na ilość wierszy. Na marginesie,
lepiej użyć "optimize for 1 row" niż dla 1000 wierszy, ponieważ w ten
sposób dajemy jasno bazie do zrozumienia, że chodzi nam o jak
najszybsze zwrócenie początkowej paczki -- wtedy użyje indeksów.
W podobny sposób można zaimplementować także tzw. stronicowanie:
WITH temp AS (
SELECT id, name, dept, years, salary,
ROW_NUMBER () OVER( ORDER BY salary DESC) AS row
FROM staff
)
SELECT * FROM temp
WHERE row > 5
FETCH FIRST 10 ROWS ONLY
OPTIMIZE FOR 1 ROW;
PS. Zwróć także na wielkość buferpooli. Domyslnie baza przydziela
bardzo małą pulę buforów i stara się uczyć obciążenia, ale to trochę
trwa. Możesz ustawić to na określoną wartość, np. 10-20% pamięci RAM:
db2 connect to baza1
db2 alter bufferpool <nazwa bufferpoola> immediate size 10000
ibmdefaultbp -- nazwa domyślnego bufferpoola. rozmiar jest podany w
stronach (4 KB, 8 KB, ...) zależnie od tego jaki jest domyśny rozmiar
strony dla całej bazy (domyślnie daje to ok 40 MB).
-- Artur Wronski
Andrzej Dąbrowski - 11-10-2007 00:00
>Użytkownik "Artur" <artur.wronski@gmail.com> napisał w wiadomości
> >news:1191966637.990403.234920@r29g2000hsg.googleg roups.com...
Dziękuję za podpowiedzi.
>Spróbuj zrobić to tak:
>select ....
>fetch first 1000 rows only
>optimize for 1 row
>Bez tej ostatniej kaluzuli baza niestety będzie optymalizowała dla
>całego zbioru wynikowego, a przypuszczam, że w zapytaniu nie było
>warunków filtrujących, poza limitem na ilość wierszy. Na marginesie,
>lepiej użyć "optimize for 1 row" niż dla 1000 wierszy, ponieważ w ten
>sposób dajemy jasno bazie do zrozumienia, że chodzi nam o jak
>najszybsze zwrócenie początkowej paczki -- wtedy użyje indeksów.
Zadziałało pięknie, ale tak jak wspomniałeś dla zapytań bez warunków, lub z
małą ilościa warunków. Dla zapytań z większą ilością warunków czas wykonania
się znacznie wydłużył. Rzeczywiscie dodanie optimize for 1 row
zmienia plan zapytania i użycia indeksów. W aplikacji potrzebują zadawać
zapytania z dowolnym filtrem po dowolnych polach, czyli w klauzuli WHERE
może nie być żadnych warunków, albo bardzo dużo. Analizuję plany prostszych
zapytań bo są łatwiejsze do ogarnięcia.
>PS. Zwróć także na wielkość buferpooli. Domyslnie baza przydziela
>bardzo małą pulę buforów i stara się uczyć obciążenia, ale to trochę
>trwa. Możesz ustawić to na określoną wartość, np. 10-20% pamięci RAM:
Zmieniłem ten i kilak innych parametrów z sekcji wydajnościowej. Porpawa
ejst prawie nieodczuwalna, myślę, że problemem jest po prostu żłożenie tabel
z 20 milionami rekordów, tutaj potrzebne jest rozsadne uzycie indeksów.
Czy mogę jakoś wymusić plan zapytania?
Jeszcze raz dziekuję za wskazówki.
Andrzej Dabrowski
Artur - 11-10-2007 00:00
=?iso-8859-2?q?Re:_DB2_-_planowanie_zapyta=F1?=
On 10 Paź, 12:29, "Andrzej Dąbrowski" <andr...@sims.pl> wrote:
> >Użytkownik "Artur" <artur.wron...@gmail.com> napisał w wiadomości
> > >news:1191966637.990403.234920@r29g2000hsg.googleg roups.com...
>
> Dziękuję za podpowiedzi.
>
> >Spróbuj zrobić to tak:
> >select ....
> >fetch first 1000 rows only
> >optimize for 1 row
> >Bez tej ostatniej kaluzuli baza niestety będzie optymalizowała dla
> >całego zbioru wynikowego, a przypuszczam, że w zapytaniu nie było
> >warunków filtrujących, poza limitem na ilość wierszy. Na marginesie,
> >lepiej użyć "optimize for 1 row" niż dla 1000 wierszy, ponieważ w ten
> >sposób dajemy jasno bazie do zrozumienia, że chodzi nam o jak
> >najszybsze zwrócenie początkowej paczki -- wtedy użyje indeksów.
>
> Zadziałało pięknie, ale tak jak wspomniałeś dla zapytań bez warunków, lub z
> małą ilościa warunków. Dla zapytań z większą ilością warunków czas wykonania
> się znacznie wydłużył. Rzeczywiscie dodanie optimize for 1 row
> zmienia plan zapytania i użycia indeksów. W aplikacji potrzebują zadawać
> zapytania z dowolnym filtrem po dowolnych polach, czyli w klauzuli WHERE
> może nie być żadnych warunków, albo bardzo dużo. Analizuję plany prostszych
> zapytań bo są łatwiejsze do ogarnięcia.
>
> >PS. Zwróć także na wielkość buferpooli. Domyslnie baza przydziela
> >bardzo małą pulę buforów i stara się uczyć obciążenia, ale to trochę
> >trwa. Możesz ustawić to na określoną wartość, np. 10-20% pamięci RAM:
>
> Zmieniłem ten i kilak innych parametrów z sekcji wydajnościowej. Porpawa
> ejst prawie nieodczuwalna, myślę, że problemem jest po prostu żłożenie tabel
> z 20 milionami rekordów, tutaj potrzebne jest rozsadne uzycie indeksów.
> Czy mogę jakoś wymusić plan zapytania?
>
> Jeszcze raz dziekuję za wskazówki.
>
> Andrzej Dabrowski
Oto parametry, które mogą mieć wpływ na pojedyncze zapytanie (inne
parametry mogą być odczuwalne przy większym obciążeniu):
Configuration parameters that affect query optimization
http://publib.boulder.ibm.com/infoce...c/c0005035.htm
Z przydatnych technik do wymuszenia użycia indeksów można zaliczyć:
*) zebranie szczegółowych statystyk dla danych (z dystrybują, czyli
rozkładem danych, oraz dla indeksów). Dla każdej tabeli można
przygotować osobny profil szczegółowości zbierania statystyk
*) sterowanie poziomem optymalizacji -- można to zrobić globalnie
(parametr bazy DFT_QUERYOPT) albo z SQL-a (instrukcja SET CURRENT
QUERY OPTIMIZATION). Im poziom jest mniejszy tym bardziej baza stara
się stosować dostęp poprzez indeksy, a nie wyliczać plan na podstawie
kosztów.
*) jeśli statystyki niegdy nie są aktualne (zawartość tabeli pływa)
warto oznaczyć taką tabelę jako VOLATILE
(alter table tabela1 volatile). Statyski nie są bran pod uwagę --
prefereowany dostęp poprzez indeksy
*) istnieje także możliwość przygotowania tzw. profilu
optymalizacyjnego, który tworzysz w postaci pliku XML. Tam możesz
powiedzieć, jak mają być obsługiwane dane zapytania. Plik ładuje się
do bazy "z zewnątrz", tzn. nie modyfikuje się aplikacji.
Podeślę na Twój adres plik z opisem profilów optymalizacyjnych.
Pozdrawiam,
Artur Wroński
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
Oracle, SQL, PL/SQL. Jak =?ISO-8859-2?Q?napisa=E6_zapytanie=2C?==?ISO-8859-2?Q?_kt=F3re_zwr=F3ci_nazw=EA_atrybutu=2C_kt=F3reg o?==?ISO-8859-2?Q?_warto=B6ci_spe=B3niaj=B1_zadany_warunek?=
=?ISO-8859-2?Q?Narz=EAdzie_do_budowania_zapyta=F1_SQL=2C?==?I SO-8859-2?Q?_PL/PgSQL=2C_PL/SQL=2C_T-SQL?=
[ms sql] =?ISO-8859-2?Q?wy=B6wietlenie_pierwszych_5_rekord?==?ISO-8859-2?Q?=F3w_z_zapytania_=3F_odpowiednik_ROWNUM_w_o?== ?ISO-8859-2?Q?raclu_dla_MS_SQL=27a?=
phpMyAdmin zadaje =?ISO-8859-2?Q?z=B3e_zapytania=2E=2E=2E_Dl?==?ISO-8859-2?Q?aczego=3F?=
[MSSQL2000] Problem z =?ISO-8859-2?Q?tabel=B1/indeksem/zapytanie?==?ISO-8859-2?Q?m_czy_b=B3=B1d_w_bazie_danych=2E=2E=2E?=
[mysql 5.x] jak =?ISO-8859-2?Q?zrealizowa=E6_zapytanie=3F_cz?==?ISO-8859-2?Q?yli_podzapytanie_i_wi=EAcej_ni=BF_jeden_rz=B1? ==?ISO-8859-2?Q?d_wynik=F3w?=
[oracle] zapytanie dynamiczne z =?ISO-8859-2?Q?=22dynamiczn=B1_?==?ISO-8859-2?Q?nazw=B1_tabeli=22?=
[mysql/php] jak =?ISO-8859-2?Q?zliczy=E6_ilo=B6=E6_unikalnyc?==?ISO-8859-2?Q?h_rekord=F3w_w_jednym_zapytaniu=3F?=
[MySQL] Zapytanie z =?ISO-8859-2?Q?dw=F3ch_tabel_na_raz_?==?ISO-8859-2?Q?i_grupowanie_po_wsp=F3lnym_polu=2E_Jak_=3F?=
mecze sie i mecze i nic - zapytanie czesciowe
zanotowane.pldoc.pisz.plpdf.pisz.pllubiatowo.xlx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
