Forum PHP.pl

Mamy pytanie dotyczące sensu stawiana bazy na silniku postgre danych w przypadku gdy :

- baza zawiera ok. 400 milionów rekordów. Rozmiar rekordu od 120 do 950 bajtów.
- nie można tworzyć kluczy primary key bo dane w kolumnach nie są unikalne.
- baza ma pracować online wystawiona w intenrnecie z średnio 4,5 userami online.
- zapytania do bazy danych odwołują się do 3-6 tabel jednocześnie.

Baza zawiera wszystkie części samochodowe świata.

Czy jedynym wyjściem jest tutaj oracle?

W tym momencie zapytanie o wyświetelenie 30 rekordów w pgadminie do jednej z głównych tabel trwa około 4 minut. (tabela przeindeksowana i klastrowana)

Może ktoś borykał się z takimi bazami.

będę wdzięczny za każdą odpowiedź.

Ten post edytował dado 15.10.2008, 08:54:18

"dane w kolumnach nie są unikalne"
To na jakiej postawie są rozróżniane?
Pytanie o index'y czy aby na pewno są dobrze założone, tzn. czy zapytania korzystają z nich. To samo klastrów się tyczy.
Miałem tabelę testową coś 30milionów rekordów i z prawidłowymi index'ami wyszukiwanie trwało milisekundy, dlatego mam te pytania.

no właśnie to jest dziwne że tam gdzie ma być primary key dane dla tej kolumny się powtarzają i wywala błąd więc primary key nie ma są tylko indexy.

Mam pytanie gdybym dodał sam dodatkową kolumnę której dałbym primary_key i autoincrement a następnie włączył do indexu to coś to da?
Tak naprawdę rozchodzi się o główna tabelę gdzie dane z innych tabel są łączone wygląda ona w uproszczeniu tak:


gdzie :

artnr - kod części
genartnr - kod w drzewie części
krintnr - rodzaje grup pojazdów (2-osobowe, 16-ciężarówy, 5 -silniki, 7-hamulce itd)
kritwert - kod samochodu
sortnr - sortowanie wyników dla tego artnr
lfdnr - kod części z które wchodzą w skład części główne artnr

i tak jest zrobiony index btree na artnr,genartnr,kritwert

ta tabela zawiera trochę ponad 100 milionów rekordów i zapytanie, takie jak poniżej, trwa z 150 sekund

[SQL] pobierz, plaintext 
SELECT a.ArtNr, a.DLNr, a.GenArtNr, a.LfdNr,  d.Bez AS opis FROM tbl_400 a 
LEFT OUTER JOIN tbl_320 b ON (a.GenArtNr = b.GenArtNr)
LEFT OUTER JOIN tbl_323 c ON (b.NartNr = c.NartNr) 
LEFT OUTER JOIN tbl_030 d ON (c.BezNr = d.BezNr)
LEFT OUTER JOIN tbl_325 e ON (b.VerwNr = e.VerwNr)
WHERE a.genartnr="34" AND a.krintnr="2" AND a.kritwert="12344" AND d.SprachNr='19' ORDER BY a.sortnr ASC
[SQL] pobierz, plaintext 

Ten post edytował dado 20.10.2008, 09:25:18

Napewno. Nawet jezeli to id ci sie nigdy do niczego ine przyda to dla bazy to duzy skok wydajnosci.
I nie auto_increment (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) Auto increment jest w mysql (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) w pg są sekwencję (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) Tworzysz kolumne, dajesz jej typ "serial" i to wszystko (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

Może to głupie pytanie sprawdzić czy zrobione indexy są w użyciu
bo wydajność I/) dla tabel mam nieciekawe - fakt że tabela dopiero powstała


Ten post edytował dado 20.10.2008, 12:34:56

Sprawdzanie co robi zapytanie:

[SQL] pobierz, plaintext 
EXPLAIN SELECT...
[SQL] pobierz, plaintext 

To ze powiedziales że nie ma unikanlej wartości mowi o tym ze nie do konca ta tablica jest odpowiedno znormalizowana i może udałoby sie cos utargowac jakims rozbiciem, ale z tego co podajesz pozniej wyglada na to ze jest chyba ok.
Przy takiej ilosci rekordow pamietaj o problemie z powtarzalnoscia danych..

Nie znam sie na postgresie ale btree to chyba najprostsza forma indeksu nie ma tam czegos bardziej zmyslnego zeby operowal na wiekszych wycinkach wartosci kolumny? Takie drzewo to zamulacz i predzej trafisz skanujac tablice za pelna wartoscia jak chodzac po drzewie.

Muszisz krok po kroku robic sobie te zapytania i analizowac EXPLAIN oraz czas wykonania zapytania.
Na poczatku pytaj po prostu jedna kolumne bez zadnych ORDER itp,bez indeksow itp i analizuj sobie jak to dziala.
Nie daj sie oszukac buforowi bazy bo moze Ci cos zapamietac (zobacz jaka jest komenda na wyczyszczanie).

Na koniec jesli bedziesz wykonywac takie zapytania jak np
tab.kategoria=KLOCKI
tab.marka=VOLVO
tab.rok between X and Y

To mozesz utworzyc sobie indeksy na wielu kolumnach. Przeczytaj dokumentacje bo one dzialaja w momencie kiedy pytasz o 3 kolumny a jak juz zapytasz o 1 to ten indeks nie dziala (jakos tak).

Optymalizatory sa dziwne i trzeba patrzec co robia.

Np mysql nie radzi sobie z GROUP BY podczas laczenia 3 tablic a mogloby sie wydawac ze takie przypadki juz bedzie w stanie sobie rozpracowac.

Na koniec mozesz sie zastanowic nad cachem takich zapytan do talic pomocniczych oraz nad silnikiem wyszukiwania po frazie tez na tablicy pomocniczej.

PS. Tez jestem z Czestochowy wiec jak bedziesz potrzebowal wiecej pomocy to wal

Ten post edytował calebos 21.10.2008, 09:56:44

Hej no witam zioma

Znalazłem sposób na ograniczenie rekordów w tabeli głównej z 120 mln to 37 mln poprzez zawężania do kraju
więc jestem już jakby w domu dorobiłem jedną kolumnę która na 100 będzie unkalne (serial) klucz główny na podstawowe kolumny tabeli
oraz index btree na kolumny najczęściej używane w zapytaniach.

Teraz poczekam z dzionek na sparsowanie nowej tabelki i oby sie nic nie wykrzaczyło

Z tego zapytania co pisałeś wynika (nie wnikam w jego sens czy też sposób gromadzenia danych), że potrzebujesz takie index'y na:
GenArtNr
NartNr
BezNr
VerwNr
genartnr,krintnr,kritwert,SprachNr (index 4 kolumnowy)
sortnr

To tylko te indexy z których powinna wykorzystać baza danych w czasie wykonywania tej kwerendy, ogólnie moim zdaniem problem wymaga lepszego przebadania, ale nie mam czasu aby tego zrobić, zresztą nie mi za to płacą.
Pamiętaj że klastry opłaca się budować jeśli jedno zapytanie prawie zawsze trafia do jednego z klastrów. Co do klucza głównego, to pamiętaj że może być wielokolumnowy i taki powinieneś założyć, dodanie jednej kolumny typu serial nic nie da, a nawet będzie tylko dodatkowy narzut.

Nie zgodzę się. Prawda jest taka, że pierwsza kolumna z indexu wielokolumnowego determinuje użycie danego indeksu. Jeżeli natomiast w warunku zostanie użyta np. druga kolumna to indeks również będzie pomocny ale jego "skuteczność" będzie niższa niż rozpoczęcie listy warunków od pierwszej kolumny indeksu. Nie można jednak powiedzieć, że indeks w ogóle nie zostanie użyty.

Po pierwsze nie bój się sztucznego primary key czyli tego co w MySql nazywa się polem auto_increment a w Postgresie sekwencją. Indeksy ustaw tylko na polach po których wykonuje się wyszukiwania lub złaczenia - nadmiar indeksów to też nie dobrze bo przecież serwer bazy danych musi je aktualizować przy każdej zmianie danych. Rozumiem, że ciężko zmieniać strukturę bazy w sytuacji kiedy istnieją w niej już dane, ale często ten wysiłek się opłaca. Więc jeśli to możliwe zoptymalizuj schemat. Nadaj kolumnom właściwe typy i np. długości, wyeliminuj redundancje wykonaj normalizację. Jak masz nie taką strukturę to i Oracle Ci nie pomoże.

Tez nie do konca piszesz prawde. Chodzi o roznice w zasadzie dzialania indeksu. W przypadku Postgre i uzyciu B-tree ten indeks zostanie uzyty tak jak napisales ale przy uzyciu gist juz jest uwarunkowany uzyciem pierwszej kolumny w warunkach.
MySQL tez nie uzyje indeksu przy uzyciu tylko 2 kolumny z indeksu musi miec pierwsza.

@calebos
Pierwsza, druga? a gdzie optymalizator, coś kręcisz.

@bigZbig
"nie bój się sztucznego primary key" po co kolumna która nie jest używana, to można dodać tysiące, one nic nie pomogą a tylko będą przeszkadzać, do tego w każdej tabeli jest jakiś klucz główny, inaczej nie można było by rozróżnić krotek, czyli nie było można ich rozróżniać, czyli powielanie danych.
Co do normalizacji, to się nie zgodzę, tu raczej właśnie stawiał bym na denormalizację w celu przyspieszenia działania. A raczej bym stworzył podwójne dane, jedne znormalizowane do edycji i drugie zdenormalizowane do wyszukiwania. (jeśli jest jakaś edycja tych danych)

Sedziwoj,

Jak dasz indeks na 2 kolumnach ( nie btree) to optymalizator go nie uzyje w przypadku kiedy podajesz warunek tylko dla drugiej kolumny ktora jest w indeksie.

Nic nie krece tylko cytuje dokumentacje.

@calebos
Dlatego jak występuje czasem tylko druga kolumna to dodajemy index na nią. (a swoją drogą, teraz wyjaśniłeś się jasno i nawet nie musiałeś wspomnieć że tak piesze w dokumentacji bo to wiem)
Kłopotem jest jak jest dużo OR w kwerendzie, wtedy to już jest na prawdę problem.

Dzięki za cenne informacje

W dokumentacji mam napisane że w skład klucza primary wchodzą kolumny



problem w tym że jak próbuje utworzyć primary dla tych kolumn to wywala mi że dane nie są unikalne - czyli coś mogło pójść nie tak przy parsowaniu danych do tabeli
i teraz pytanie jak znaleźć z duplikowane wartości które przeszkadzają w utworzeniu prmiary key ? (tabela ma 27 mln rekordów)

[SQL] pobierz, plaintext 
SELECT artnr, sortnr, lfdnr, genartnr, count(*) FROM nazwa_tabeli GROUP BY artnr, sortnr, lfdnr, genartnr HAVING count(*) > 1
[SQL] pobierz, plaintext 

Nie wiem czy najszybsza, ale na pewno skuteczna.

Dzięki zaraz zapuszczę (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

Udało mi się zrobić index w ten sposób że do kolumn artnr,genartnr,lfdnr,sortnr dodałem pole typu serial - na ile to będzie efektywne sie zaraz przekonam

Daj potem znać które rady okazały się skuteczne. I jak ostatecznie to wyszło.

Cały czas się z tym męcze ale mam już spore postępy
- ograniczyłem liczbę rekordów w głównej tabeli aplikacji do 118 milionów
- z parsowałem ją od nowa (4 dni (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) ) dodając kolumnę table_id BIGSERIAL
- utworzyłem index główny z kolumn które będę używał w zapytaniach + kolumna z "sztucznym" indexem BIGSERIAL
- potem index btree na kolumny uzwane w zapytaniach ale już bez table_id (BIGSERIAL)
- nie klastrowałem indexów (nie jestem pewien czy mi to pomoże czy zaszkodzi)

[SQL] pobierz, plaintext 
EXPLAIN ANALYZE SELECT count(1) FROM tecdoc_400b WHERE genartnr='8' AND artnr='1152'
[SQL] pobierz, plaintext 

daje



serwer: procek INTEL CORE 2 DUO E8400 2x3.0Ghz, ram 2 gb

- trochę gorzej w połączeniu z tabelami dołączanym joinem, nie stosuje mi w nich indexów, (a jak odwołuję się tylko do nich stosuje indexy), więc tu się jeszcze męcze,
- całościowe pobranie wszystkich danych dla aplikacji www w najdłuższym zapytaniu trwa od 10-27 sekund, ale docelowo aplikacja będzie stał na bardziej wydajnym serwerze.

[SQL] pobierz, plaintext 
SELECT a.ArtNr, a.DLNr, a.GenArtNr, a.LfdNr, d.Bez AS opis FROM tbl_400 a
LEFT OUTER JOIN tbl_320 b ON (a.GenArtNr = b.GenArtNr)
LEFT OUTER JOIN tbl_323 c ON (b.NartNr = c.NartNr)
LEFT OUTER JOIN tbl_030 d ON (c.BezNr = d.BezNr)
LEFT OUTER JOIN tbl_325 e ON (b.VerwNr = e.VerwNr)
WHERE a.genartnr="34" AND a.krintnr="2" AND a.kritwert="12344" AND d.SprachNr='19' ORDER BY a.sortnr ASC
[SQL] pobierz, plaintext 

co do tego zapytanie ktore podaleś to mam 3 uwagi:

- dlaczego łączysz się z tabelą: tbl_325 skoro ona nie jest Ci na nic potrzebna ?
- dlaczego nie stosujesz INNER JOIN zamiast LEFT OUTER JOIN, przecież z zapytania jasno wynika, iż tak właśnie powinno być.
- czy pola typu: genartnr, krintnr, kritwert, SprachNr maja ustawiony typ tekstowy czy liczbowy ?, bo z tego zapytania wynika ze tekstowe

jeszcze jedna sprawa, sprawdz czy kolumny po ktorych laczysz tabele maja taki sam typ