Optymalizacja PostgreSQL.

Odpowiedz Nowy wątek
2006-09-09 23:05
0

W nawiązaniu do wątku "OpenSource sux" przetestowałem jak radzi sobie PostgreSQL z tabelą 50 mln rekordów (ok 2-3 GB danych, z indeksami zjadlo mi ok. 5 GB wolnego miejsca z dysku).

CREATE TABLE transakcja (id_transakcji INT4 PRIMARY KEY, data TIMESTAMP, kwota FLOAT);
COPY transakcja (id_transakcji, data, kwota) FROM stdin;
1 2004-08-12 150
2 2003-01-10 331
3 2005-11-02 24
....
itd. lacznie 50 milionow rekordow
"data" losowa z przedziału od 3 lata wstecz do dzisiaj. "kwota" losowa z przedzialu 0 - 500.

Czas zaladowania danych do bazy: 12 minut 19 sekund.

Pierwsze naiwne zapytanie bez optymalizacji:
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
180,0 sekund; baza wykonala sekwencyjny skan całej tabeli

CREATE INDEX idx_transakcja_data_kwota ON transakcja USING btree(data, kwota);
ok. 7 minut

SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
89,5 sekundy; baza użyła indeksu

CREATE INDEX idx_transakcja_kwota ON transakcja USING btree(kwota);
ok. 6 minut

SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
84 sekundy; baza nie użyła nowego indeksu

DROP INDEX idx_transakcja_data_kwota;
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
81,5 sekundy; baza użyła indeksu idx_transakcja_kwota

Ale to wszystko nic...
Dalej było ciekawiej:

ALTER TABLE transakcja ADD COLUMN miesiac INT4;
UPDATE transakcja SET miesiac = extract(year from data) * 12 + extract(month from data);
CREATE TABLE transakcja_tmp AS SELECT id_transakcji, data, miesiac, kwota FROM transakcja ORDER BY miesiac, kwota;
DROP TABLE transakcja;
ALTER TABLE transakcja_tmp RENAME TO transakcja;
CREATE INDEX idx_transakcja_miesiac_kwota ON transakcja USING btree(miesiac, kwota);
wszystko łącznie koło 40 minut.

Ale za to teraz:

SELECT count() FROM transakcja WHERE data > '2005-09-09' AND kwota < 50 AND miesiac >= (2005 12 + 9);
19,5 sekundy (agregacja z 1,6 mln rekordow)

A np. teraz analiza dla jednego wybranego miesiaca:
select count() from transakcja where data > '2005-09-09' and data < '2005-10-09' and miesiac >= (9 + 2005 12) and miesiac <= (10 + 2005 * 12) and kwota < 50;
1,9 sekundy (agregacja ze 136 tys. rekordow)

Dość mocne - zważywszy, że to 136 tys. rekordów z 50 mln...
Na dodatek, jeśli np. po miesiącu dojdą nowe wpisy, to wystarczy teraz zrobić:
CLUSTER idx_transakcja_miesiac_kwota ON transakcja; (ok. 6 minut)
żeby zapytania stopniowo się nie spowalniały.

Jestem ciekaw, na ile da sie zoptymalizowac MS SQL, Oracla pod tym wzgledem a na ile MySQL.
Czy czasy beda tego samego rzedu, czy np. MS SQL smignie to zapytanie 10 razy szybciej na podobnym sprzecie? :>

Moj sprzet: AMD Athlon 64+ @2,75 GHz, 1GB DDR RAM @220 MHz, 250 GB HDD SATA UDMA 133.
PostgreSQL 8.1.3, dostepne mial 500 MB RAMu (ustawione w konfiguracji).

P.S. Typowe zapytania transakcyjne zwracajace kilka a nie kilkaset tysiecy rekordow zajmuja Postgresowi czas rzedu kilkunastu milisekund jesli dane NIE BYLY w cache'u i ulamki milisekundy, jesli byly (za drugim razem). Wiec przy takich zapytaniach wielkosc bazy praktycznie nie ma znaczenia.

Pozostało 580 znaków

2006-09-10 00:03
0

oczywiście po dodaniu wszelakich danych oraz po przeindeksowaniu uruchomiłeś vacuumowanie :>

Krolik napisał(a)

CREATE INDEX idx_transakcja_data_kwota ON transakcja USING btree(data, kwota);
ok. 7 minut

a jakby Ci się chciało to samo ale z indeksem założonym na początku bo zazwyczaj tak wygląda praca z bazą

CREATE INDEX idx_transakcja_kwota ON transakcja USING btree(kwota);
ok. 6 minut

SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
84 sekundy; baza nie użyła nowego indeksu

co dziwić nie powinn bo indeks do tego zapytania powinien wyglądać inaczej - powinien być po obu polach

DROP INDEX idx_transakcja_data_kwota;
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
81,5 sekundy; baza użyła indeksu idx_transakcja_kwota

przyposzczalnie jest podobna ilość rekordów dla których data > '2005-09-09' oraz kwota < 50 i te wyniki były przeszukiwane sekwencyjnie

select count() from transakcja where data > '2005-09-09' and data < '2005-10-09' and miesiac >= (9 + 2005 12) and miesiac <= (10 + 2005 * 12) and kwota < 50;

no a tu warunki są cokolwiek dziwne bo nikt o zdrowych zmysłach ne pisze w warunku (10 + 2005 * 12) a wylicza to wcześniej


- Ciemna druga strona jest.
- Nie marudź Yoda, tylko jedz tego tosta.
Google NIE GRYZIE!
Pomogłem - kliknij

Pozostało 580 znaków

2006-09-10 00:48
0

Hmmm... Ja się nie znam, ale czy silnik PostgreSQL sobie nie zauważy, że to są same stałe i nie wyliczy raz a dobrze przed wykonaniem takiego zapytania?


Grunt to uziemienie...

Pozostało 580 znaków

2006-09-10 01:38
0
Krolik napisał(a)

W nawiązaniu do wątku "OpenSource sux" przetestowałem jak radzi sobie PostgreSQL z tabelą 50 mln rekordów (ok 2-3 GB danych, z indeksami zjadlo mi ok. 5 GB wolnego miejsca z dysku).

Ja podszedłem do sprawy trochę inaczej ;)

Wykonywałem inserta po insercie żeby sprawdzić same możliwości "wkładania" danych do bazy

PostgreSQL - wykonanie for'a od 1 do 50 mln plus inserty prawie trzy godziny

MSSQL - wykonanie while'a 1 do 50 mln plus inserty prawie dwie godziny

Ja od razu przy wkładaniu danych założyłem indexy w Postgre Serialem a w MS SQL identity

Rozmiar Bazy danych: P - 4 GB M - 1 GB

Krolik napisał(a)

CREATE TABLE transakcja (id_transakcji INT4 PRIMARY KEY, data TIMESTAMP, kwota FLOAT);
COPY transakcja (id_transakcji, data, kwota) FROM stdin;
1 2004-08-12 150
2 2003-01-10 331
3 2005-11-02 24
....
itd. lacznie 50 milionow rekordow
"data" losowa z przedziału od 3 lata wstecz do dzisiaj. "kwota" losowa z przedzialu 0 - 500.

Czas zaladowania danych do bazy: 12 minut 19 sekund.

dosyć spora różnica ;) przy różnych podejściach dodawania danych

Krolik napisał(a)

Pierwsze naiwne zapytanie bez optymalizacji:
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
180,0 sekund; baza wykonala sekwencyjny skan całej tabeli

to samo zapytanie: M - 14 s. P - 350 s.

Krolik napisał(a)

CREATE INDEX idx_transakcja_data_kwota ON transakcja USING btree(data, kwota);
ok. 7 minut

SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
89,5 sekundy; baza użyła indeksu

CREATE INDEX idx_transakcja_kwota ON transakcja USING btree(kwota);
ok. 6 minut

SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
84 sekundy; baza nie użyła nowego indeksu

DROP INDEX idx_transakcja_data_kwota;
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
81,5 sekundy; baza użyła indeksu idx_transakcja_kwota

Ale to wszystko nic...
Dalej było ciekawiej:

To podejście jest nieoptymalne. indeksowac powinno się co najwyżej tabelki tymczasowe. rzeczywiste tabele od razu powinny mieć indeksy.

Krolik napisał(a)

ALTER TABLE transakcja ADD COLUMN miesiac INT4;
UPDATE transakcja SET miesiac = extract(year from data) * 12 + extract(month from data);
CREATE TABLE transakcja_tmp AS SELECT id_transakcji, data, miesiac, kwota FROM transakcja ORDER BY miesiac, kwota;
DROP TABLE transakcja;
ALTER TABLE transakcja_tmp RENAME TO transakcja;
CREATE INDEX idx_transakcja_miesiac_kwota ON transakcja USING btree(miesiac, kwota);
wszystko łącznie koło 40 minut.

Ale za to teraz:

SELECT count() FROM transakcja WHERE data > '2005-09-09' AND kwota < 50 AND miesiac >= (2005 12 + 9);
19,5 sekundy (agregacja z 1,6 mln rekordow)

A np. teraz analiza dla jednego wybranego miesiaca:
select count() from transakcja where data > '2005-09-09' and data < '2005-10-09' and miesiac >= (9 + 2005 12) and miesiac <= (10 + 2005 * 12) and kwota < 50;
1,9 sekundy (agregacja ze 136 tys. rekordow)

Nie wierzę w te czas. w ogóle. dziwne że na moim Postgresei wszystkie po kolei przeprowadzone tutaj czynności zajmowały wielokrotnie więcej czas :P

na przykład to zapytanie:

select count(*) from transakcja where data > '2005-09-09' and data < '2005-10-09' and miesiac >= (9 + 2005 * 12) and miesiac <= (10 + 2005 * 12) and kwota < 50;

wykonywało się 94 sekundy a u ciebie niecałe dwie? :|

Za to na MS SQL'u zapytanie:

declare @data_od smallDateTime;
set @data_od = dateAdd(y, -1, getDate())
select 
    sum(kwota)
    ,count(id_transakcji)
from
    transakcje
where data > @data_od and kwota < 50

wykonywało się 34 sekundy

za to zapytanie

declare @data_od smallDateTime;
set @data_od = dateAdd(y, -1, getDate())

create table #tmp(
    id_transakcji int not null primary key
    ,kwota decimal(9,2)
    ,data smallDateTime    
)

insert into #tmp
select
    id_transakcji
    ,kwota
    ,data
from
    transakcje
where
    data > @data_od

select * from #tmp where kwota < 50

wykonywało się zaledwie 3,5 sekundy

Krolik napisał(a)

Dość mocne - zważywszy, że to 136 tys. rekordów z 50 mln...

no cóż. u mnie tych rekordów z ostatniego roku jest dokładnie jedna trzecia. plus 1/10 rekordów z tej jednej trzeciej to kwoty poniżej 50 PLN czyli 1 700 000 rekordów. na tych 136 tysiącacvh to może i rzeczywoście takie czasy uzyska. ale na większych danych? :|

Po za tym mimo wszystko cięzko mi uwierzyć w Twoje czasy.

Krolik napisał(a)

Na dodatek, jeśli np. po miesiącu dojdą nowe wpisy, to wystarczy teraz zrobić:
CLUSTER idx_transakcja_miesiac_kwota ON transakcja; (ok. 6 minut)
żeby zapytania stopniowo się nie spowalniały.

Jestem ciekaw, na ile da sie zoptymalizowac MS SQL, Oracla pod tym wzgledem a na ile MySQL.
Czy czasy beda tego samego rzedu, czy np. MS SQL smignie to zapytanie 10 razy szybciej na podobnym sprzecie? :>

Moj sprzet: AMD Athlon 64+ @2,75 GHz, 1GB DDR RAM @220 MHz, 250 GB HDD SATA UDMA 133.
PostgreSQL 8.1.3, dostepne mial 500 MB RAMu (ustawione w konfiguracji).

Mój sprzęt - Pentium 4 1,6 GHz + 512 MB RAM. PostgreSQL 8.1.4 MS SQL 2005 Standard Edition, dysk 40 GB na ATA

Krolik napisał(a)

P.S. Typowe zapytania transakcyjne zwracajace kilka a nie kilkaset tysiecy rekordow zajmuja Postgresowi czas rzedu kilkunastu milisekund jesli dane NIE BYLY w cache'u i ulamki milisekundy, jesli byly (za drugim razem). Wiec przy takich zapytaniach wielkosc bazy praktycznie nie ma znaczenia.

I tu się strasznie mylisz. bo dodajmy do transakcji info o produkcie i utwórzmy drugą tabelę gdzie będzie powiedzmy 15 000 indeksów (produktów) które będzie podłączał pod tą tabelkę z 50 milionami. wtedy dopiero zacznie się zabawa. a takie selecty niczego nie dowodzą oprócz tego że źle przygotowałeś tabelkę bo nie założyłeś na początku indeksów.</quote></quote></quote>


Daj człowiekowi cracka a jutro znowu bedzie głodny. Naucz go crackować a już nigdy nie będzie głodny.

Pozostało 580 znaków

2006-09-10 18:04
0
jmail napisał(a)

Wykonywałem inserta po insercie żeby sprawdzić same możliwości "wkładania" danych do bazy

INSERT jest wolny w Postgresie, bo każdy insert idzie w osobnej transakcji. Jeśli każda transakcja jest fsyncowana, to nie ma się co dziwić, że ładuje kilka godzin. Albo robisz COPY, albo na czas ładowania danych wyłączasz fsync w konfiguracji (niezalecane).

Ja od razu przy wkładaniu danych założyłem indexy w Postgre Serialem a w MS SQL identity

Na co Ci indeksy przy wkładaniu danych???? Zwykle właśnie żeby przyspieszyć takie operacje, indeksy warto chwilowo nawet zdjąć. Dobra, wiem, normalnie nie zasila się bazy 50 mln rekordów, tylko są zbierane np. przez 3 lata. Wtedy indeksy oczywiście muszą być. Ale nawet z kilkoma indeksami na takiej tabeli dodawanie pojedynczych rekordów to ułamki sekund - u mnie ok 15-30 ms na rekord - powodowane głównie tym, że jak masz taki dwukolumnowy indeks btree na tabeli, to co najmniej 3-6 stron musi być odczytanych i 2 zapisane, czyli masz do 8 pozycjonowań głowicy dysku (a dysk ma niestety duży czas dostępu).

Rozmiar Bazy danych: P - 4 GB M - 1 GB

Zgadza się. U mnie podobnie.

Krolik napisał(a)

Pierwsze naiwne zapytanie bez optymalizacji:
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
180,0 sekund; baza wykonala sekwencyjny skan całej tabeli

to samo zapytanie: M - 14 s. P - 350 s.

Bueee, ale chyba miałeś indeksy????
Bo nie wierzę, żeby SEQ SCAN po 50 mln rekordów zajął 14 sekund.
Czas, który uzyskałeś dla Postgresa wygląda wiarygodnie. U mnie było zawsze 170 - 190 s.
Różnica jest taka, że masz 500 MB RAMu, a ja mam 1GB, więc u mnie więcej danych mogło się zmieścić w cache'u i poszło nieco szybciej.

To podejście jest nieoptymalne. indeksowac powinno się co najwyżej tabelki tymczasowe. rzeczywiste tabele od razu powinny mieć indeksy.

Nie rozumiem, przecież u mnie najpierw założyłem wszystkie indeksy, a później testowałem zapytania. Oczywiście w międzyczasie zmieniałem zestaw indeksów, bo byłem ciekaw, jak to wpływa na wydajność.

Nie wierzę w te czas. w ogóle. dziwne że na moim Postgresei wszystkie po kolei przeprowadzone tutaj czynności zajmowały wielokrotnie więcej czas :P

No, i właśnie to chciałem wykazać. OpenSource jest skonfigurowany domyślnie tak, że takiej wielkiej bazy nie pociągnie. Zmień "shared_buffers = 3000; work_mem = 500000; maintenance_mem = 200000" i zobacz efekt.

na przykład to zapytanie:

select count(*) from transakcja where data > '2005-09-09' and data < '2005-10-09' and miesiac >= (9 + 2005 * 12) and miesiac <= (10 + 2005 * 12) and kwota < 50;

wykonywało się 94 sekundy a u ciebie niecałe dwie? :|

A zrobiłeś wszystko po kolei, co robiłem wcześniej? Dane masz w tabeli poklastrowane?
Chyba nie, to by wyjaśniało ten długi czas.

Za to na MS SQL'u zapytanie:

declare @data_od smallDateTime;
set @data_od = dateAdd(y, -1, getDate())
select 
sum(kwota)
,count(id_transakcji)
from
transakcje
where data > @data_od

wykonywało się 34 sekundy

za to zapytanie


declare @data_od smallDateTime;
set @data_od = dateAdd(y, -1, getDate())

create table #tmp(
id_transakcji int not null primary key
,kwota decimal(9,2)
,data smallDateTime
)

insert into #tmp
select
id_transakcji
,kwota
,data
from
transakcje
where
data > @data_od

select * from #tmp where kwota < 50


> 
> wykonywało się zaledwie 3,5 sekundy

Zaraz zaraz, coś tu nie gra. Najpierw piszesz, że zapytanie B wykonało się 34 sekundy,  a później zapytanie postaci ABC wykonało się 3 sekundy? Przecież ten SELECT, co wykonywał się u Ciebie 34 sekundy masz tu:

> insert into #tmp
> select
>     id_transakcji
>     ,kwota
>     ,data
> from
>     transakcje
> where
>     data > @data_od

Wyjaśnij mi, jak to możliwe, że sam SELECT sum(kwota) wykonuje się 34 sekundy, a SELECT o tym samym warunku ze wstawieniem  wyników do tabeli tymczasowej mniej niż 3,5 sekundy??? Chciałbym zobaczyć QUERY PLAN obu zapytań. Chyba, że to 3,5 sekundy dotyczy samego ostatniego SELECTA po kwocie, a nie całości i nie uwzględnia czasu tworzenia tabeli tymczasowej. Ale to jest zdeczko nieuczciwe, szczególnie że tabela tymczasowa pewnie cała się zmieściła w pamięci.  

 > ##### Krolik napisał(a)
> P.S. Typowe zapytania transakcyjne zwracajace kilka a nie kilkaset tysiecy rekordow zajmuja Postgresowi czas rzedu kilkunastu milisekund jesli dane NIE BYLY w cache'u i ulamki milisekundy, jesli byly (za drugim razem). Wiec przy takich zapytaniach wielkosc bazy praktycznie nie ma znaczenia.

I tu się strasznie mylisz. bo dodajmy do transakcji info o produkcie i utwórzmy drugą tabelę gdzie będzie powiedzmy  15 000 indeksów (produktów) które będzie podłączał pod tą tabelkę z 50 milionami. wtedy dopiero zacznie się zabawa. a takie selecty niczego nie dowodzą oprócz tego że źle przygotowałeś tabelkę bo nie założyłeś na początku indeksów.</quote>

Co ma piernik do wiatraka, czy indeksy założyłem na początku czy na końcu? Wiesz w ogóle jak działają indeksy różnych typów, jak są budowane, na czym polega operacja wpisu czegoś do indeksu i jego usunięcia? Gdybym założył indeksy na początku, jedynie wydłużyłbym czas ładowania danych, bo indeks update'owałby  się po wstawieniu każdego rekordu. Ale efekt końcowy byłby taki sam - indeks typu B+ drzewo, 3 poziomowy, w ponad 50% zapełniony danymi, więc działający optymalnie. Co innego, gdybym później usunąl 49 mln rekordów - wtedy musiałbym zrobić VACUUM, bo miałbym niepotrzebnie rozepchany indeks oraz tabelę główną.

A test wykazuje bardzo wiele - m.in. to, że szybkość executora Postgresowego, choć może nieco gorsza od MS, wcale nie jest DUŻO gorsza i na pewno za zaosczędzone na licencji pieniądze kupiłbym dodatkowe 8 GB RAMu, dysk SCSI 15000 obr/min, i miałbym kilka razy szybszy system...

Dla hecy zrobię jeszzce w wolnym czasie JOINa CAŁEJ tej tabeli z tabelką 15000 produktów :> 
Przewiduję czas rzędu nie więcej niż kilku minut (SORT_MERGE JOIN to załatwi - sortowanie 50 mln transakcji nie jest takie straszne). Zresztą do takich cudów robi się widoki zmaterializowane wstępnie poagregowane. Nikt nawet na MS nie będzie wykonywał każdego zapytania na 50 mln rekordów - co najwyżej wybierze kilkaset taki zapytaniem, jakie udowodniłem że idzie w kilka sekund, a później JOINy na setkach tys. rekordów to są czasy rzędu kilkuset milisekund...

Pozostało 580 znaków

2006-09-10 19:08
0
Krolik napisał(a)

INSERT jest wolny w Postgresie, bo każdy insert idzie w osobnej transakcji. Jeśli każda transakcja jest fsyncowana, to nie ma się co dziwić, że ładuje kilka godzin. Albo robisz COPY, albo na czas ładowania danych wyłączasz fsync w konfiguracji (niezalecane).

jak to:| ? skąd ty żeś to wziął. oczywiście że całe zapytanie (razem z forem) objąłem znacznikami begin transaction i commit. Po za tym jeżeli postgre nie natrafi na takie cóś to weźmie i sam to obejmie jedną transakcją.jak piszesz sql'a z poziomu pgadmina to okienko jest jedną transakcją.

Krolik napisał(a)

Na co Ci indeksy przy wkładaniu danych????

Jak tworzenie testowo rzeczywistości to tworzenie.

Krolik napisał(a)

Bueee, ale chyba miałeś indeksy????
Bo nie wierzę, żeby SEQ SCAN po 50 mln rekordów zajął 14 sekund.
Czas, który uzyskałeś dla Postgresa wygląda wiarygodnie. U mnie było zawsze 170 - 190 s.
Różnica jest taka, że masz 500 MB RAMu, a ja mam 1GB, więc u mnie więcej danych mogło się zmieścić w cache'u i poszło nieco szybciej.

Przecież napisałem od razu że indeksy były tworzone.

Krolik napisał(a)

No, i właśnie to chciałem wykazać. OpenSource jest skonfigurowany domyślnie tak, że takiej wielkiej bazy nie pociągnie. Zmień "shared_buffers = 3000; work_mem = 500000; maintenance_mem = 200000" i zobacz efekt.

Taaaak. to jeszcze zapomniałeś na ten przykład o effective_cache_size. oczywiście że mam tyo zoptymalizowane

Krolik napisał(a)

A zrobiłeś wszystko po kolei, co robiłem wcześniej? Dane masz w tabeli poklastrowane?
Chyba nie, to by wyjaśniało ten długi czas.

Zrobiłem wszystko po kolei tak jak pisałeś. i taki wynik na końcu uzyskałem

Krolik napisał(a)

Zaraz zaraz, coś tu nie gra. Najpierw piszesz, że zapytanie B wykonało się 34 sekundy, a później zapytanie postaci ABC wykonało się 3 sekundy? Przecież ten SELECT, co wykonywał się u Ciebie 34 sekundy masz tu:

Zgadza się. źle skopiowałem zapytanie. w tym wcześniejszym jeszcze było ograniczenie do kwoty. a później do tmp wyciągałem samo ograniczeni daty i z tmp ograniczałem po kwocie

Krolik napisał(a)

A test wykazuje bardzo wiele - m.in. to, że szybkość executora Postgresowego, choć może nieco gorsza od MS, wcale nie jest DUŻO gorsza i na pewno za zaosczędzone na licencji pieniądze kupiłbym dodatkowe 8 GB RAMu, dysk SCSI 15000 obr/min, i miałbym kilka razy szybszy system...

Pokaż mi gdzie jest pokazane jaką ilość pamięci RAM potrafi zaadresować PostgreSQL. Bo mnei się zdaje że 2 GB to jest max co może użyć.

Krolik napisał(a)

Dla hecy zrobię jeszzce w wolnym czasie JOINa CAŁEJ tej tabeli z tabelką 15000 produktów :>
Przewiduję czas rzędu nie więcej niż kilku minut (SORT_MERGE JOIN to załatwi - sortowanie 50 mln transakcji nie jest takie straszne). Zresztą do takich cudów robi się widoki zmaterializowane wstępnie poagregowane. Nikt nawet na MS nie będzie wykonywał każdego zapytania na 50 mln rekordów - co najwyżej wybierze kilkaset taki zapytaniem, jakie udowodniłem że idzie w kilka sekund, a później JOINy na setkach tys. rekordów to są czasy rzędu kilkuset milisekund...

i nadal jeszcze nie sprawdziliśmy jednej rzeczy. dostęp jednego użytkownika ok. na takich testach może rzeczywiście postgre staje się równy MS SQL'owi. Zacznijmy zwiększać ilość użytkowników. 30 na początek? każdy próbujący dostać sie do tych samych danych? jedno połączenie to looz. Gorzej że te wszystkie wartości, konfigurowane (pamięć) bedą dzielone dla wszystkich użytkowników......

W MS SQL'u wystarczy że ustawię żeby wziął tyle pamięci co by serwer nie padł (mówimy o serwerze DB - więc inne programy nie działają) i śmiga na 8 GB. Standard ma ograniczenie do 4GB więc tyle mogę maksymalnie mu dać. ale enterprise już nie ma tych ograniczeń.</quote></quote></quote></quote>


Daj człowiekowi cracka a jutro znowu bedzie głodny. Naucz go crackować a już nigdy nie będzie głodny.

Pozostało 580 znaków

2006-09-10 20:51
0

Ok, z tym że wszystko dałeś w "begin" i "end" nie zauważyłem. Hmmm.... w tej sytuacji nie powinno być aż tak dużej różnicy. Widzę jednak, że testujesz na Windows. PostgreSQL na Win jest od niedawna, może coś skopali. Hehe, trzeba zajrzeć do kodu źródłowego i obadać czemu się tak u Ciebie muli. ;-P
U mnie na Linuksie serio ładowało się 12 minut, krócej niż generowanie samego pliku z SQLkami, który wygenerowałem Javą printfem i robił się ponad 40 minut. Na dodatek pewnie dałoby się szybciej, bo ZAPOMNIAŁEM zdropować indeksu na kluczu głównym, który to indeks Postgres zakłada domyślnie przy CREATE TABLE. Czyli jeden indeks był przy zasysaniu danych do tabeli, a mimo to 12 minut...

Sam EXPLAIN ANALYZE dla tych zapytan, co nie wierzysz w tak niskie czasy...

test=# explain analyze select count(*) from transakcja where data > '2005-09-09' and data < '2005-10-09' and miesiac >= (9 + 2005 * 12) and miesiac <= (10 + 2005 * 12) and kwota < 50;
                                                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=198652.70..198652.71 rows=1 width=0) (actual time=2064.804..2064.809 rows=1 loops=1)
   ->  Bitmap Heap Scan on transakcja  (cost=2950.00..198651.66 rows=417 width=0) (actual time=505.438..1346.016 rows=136193 loops=1)
         Recheck Cond: ((miesiac >= 24069) AND (miesiac <= 24070) AND (kwota < 50::double precision))
         Filter: ((data > '2005-09-09 00:00:00'::timestamp without time zone) AND (data < '2005-10-09 00:00:00'::timestamp without time zone))
         ->  Bitmap Index Scan on idx_transakcja_miesiac_kwota  (cost=0.00..2950.00 rows=83333 width=0) (actual time=504.932..504.932 rows=277052 loops=1)
               Index Cond: ((miesiac >= 24069) AND (miesiac <= 24070) AND (kwota < 50::double precision))
 Total runtime: 2064.869 ms
(7 rows)
test=# explain analyze select count(*) from transakcja where data > '2005-09-09' and miesiac >= (9 + 2005 * 12) and kwota < 50;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=601557.86..601557.87 rows=1 width=8) (actual time=20275.186..20275.191 rows=1 loops=1)
   ->  Bitmap Heap Scan on transakcja  (cost=154878.00..596928.23 rows=1851852 width=8) (actual time=2640.509..11652.127 rows=1654218 loops=1)
         Recheck Cond: ((miesiac >= 24069) AND (kwota < 50::double precision))
         Filter: (data > '2005-09-09 00:00:00'::timestamp without time zone)
         ->  Bitmap Index Scan on idx_transakcja_miesiac_kwota  (cost=0.00..154878.00 rows=5555556 width=0) (actual time=2637.123..2637.123 rows=1690402 loops=1)
               Index Cond: ((miesiac >= 24069) AND (kwota < 50::double precision))
 Total runtime: 20275.278 ms
(7 rows)

Przy tych wszystkich testach zauważyłem, że jest jeden drobny fuckup w Postgresie z obliczaniem funkcji, do których wszystkie dane są w indeksie. Postgres i tak zaglada do tabeli, nie wiedziec po jakiego grzyba, i ten skan trwa najdłużej. Gdyby tego nie robil, pierwotne zapytanie (1/3 rek po dacie, 1/10 po kwocie) wykonaloby sie w ok. 3 sekundy - tyle raportuje EXPLAIN ANALYZE jesli chodzi o odczyt samego indeksu:

         ->  Bitmap Index Scan on idx_transakcja_miesiac_kwota  (cost=0.00..154878.00 rows=5555556 width=0) (actual time=2637.123..2637.123 rows=1690402 loops=1)

Czasy sa jak najbardziej wiarygodne, bo skoro SEQ SCAN zajmuje kolo 200 sekund, to odczyt 1/30 rekordow powinien zajac kolo 8 sekund. Rekordy sa odczytywane sekwencyjnie, raptem z kilkoma przeskokami dzieki klastrowaniu. I tak wychodzi dziwnie dlugo.

Co do 8 GB w PostgreSQL na maszynie 64 bitowej nie ma żadnego problemu.
Dyrektywa effective_cache_size nie OKREŚLA rozmiaru cache'a, a sugeruje optymalizatorowi, jakiej wielkości cache dyskowy jest udostepniany przez system operacyjny Postgresowi, wiec przy tych zapytaniach nie bylo sensu jej ustawiac. Jesli ustawisz zbyt duza wartosc, optymalizator bedzie zle szacowal koszt dostepu do losowej strony i moze wykonac INDEX SCAN na niesklastrowanej tabeli, co moze sie okazac wrecz gorsze od skanu sekwencyjnego. Zbyt mala wartosc co najwyzej nie dopusci do uzycia indeksow, ale w tym przypadku jak widac nie bylo problemu.

Jesli chodzi o wielu userow, uzywamy Postgresa w systemie, gdzie na raz jest uzywanych kilkuset userow, kilkaset tysiecy transakcji na dobe, a baza ma okolo 0.5 GB i jakos nie ma z tym problemow wydajnosciowych. Powiem nawet, ze skromny pecet który to obsługuje jestz zgodnie ze wskazaniami topa przez 80% bezczynny. Tyle, ze nie robimy skanow po 50 mln rekordow, ale pisalem juz - takich zapytan nie robi sie w trybie wielouzytkownikowym, to jest zabawa typowa dla hurtowni danych. W hurtowaniach stosuje sie redundancje, widoki zmaterializowane, ktore licza sie w calosci raz (i trwa to minuty, godziny, czasem dni na bazach wielkosci setek GB), ale pozniej dostep do podsumowan jest szybki. Systemy transakcyjne moga sie schowac przy *OLAPach. Gdyby to zapytanie wrzucic w cokolwiek np. z wielowymiarowym indeksem klastrowanym typu UB-tree, to zapewne czas obliczen bylby ponizej sekundy.

Pozostało 580 znaków

2006-09-10 22:24
0

królik -> no i widzisz? :( zostałem postawiony w pozycji tego niebroniącego postgreSQL'a kiedy sam go używam i jestem zadowolony :)

krótko i na temat. Na wieloprocesorowych kombajnach, gdzie rzeczywiście ilości danych wymagają stworzenia poteżnej hurtowni, PostgreSQL mimo wszystkim się nie nadaje :( chociażby ze względuna brak narzędzi do graficznego zarządzania wieloma rzeczami.

Powiedz mi może tak. istnieje w PostgreSQL coś takeigo jak jobs w MS SQL? Zawsze mnie to nurtowało. Albo local packages. Automaty, które o określonej porze są włączane i wykonuą się i wykonuja wykonują. i możesz ustawiać zadania w kolejki jak coś się nie wykona to ma się wykonać coś innego a jak pierwsza paczka się wykona to inna pójdzie?

Przy zarządzaniu serwerem ma to kolosalne znaczenie. bo jakbym miał z ręki każdy automat uruchamiać to by mnie coś chyba trafiło.


Daj człowiekowi cracka a jutro znowu bedzie głodny. Naucz go crackować a już nigdy nie będzie głodny.

Pozostało 580 znaków

2006-09-11 00:45
0

jmail: W praktyce wystarczy do tego przecież cron i skrypty wsadowe tudzież inne tego typu. Szczególnie, jeśli poszczególne zadania (rozumiem, że zapytania SQL/komplety zapytań SQL) są stosunkowo długie.


Grunt to uziemienie...

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0