Forum PHP.pl > Postgresql

Pomoc - Szukaj - Użytkownicy - Kalendarz

Forum PHP.pl > Forum > Bazy danych > PostgreSQL

Ziels

4.08.2008, 11:07:20

Witam

Jakiś czas temu opisałem problem z ładowaniem wpisów do bazy - rozwiązaniem okazał się serwer dedykowany i podzielenie pliku na pakiety

Aktualnie mierzę się z takim problemem: baza zawiera 30 mln wpisów i zwykły count zajmuje 8 sekund (tak, wiem, w postgresqlu są wolne, ale bez przesady!). Jak można rozwiązać problem wydajności przy tej ilości danych? Czytałem trochę o partycjonowaniu tabel, trochę o widokach - jednak nie wiem które rozwiązanie przyniesie największe korzyści wydajnościowe. Tabela zawiera 4 kolumny - 3 integery i 1 boolean.

Jeśli to ma znaczenie: wyszukiwanie w niej planowo ma się opierać na czymś takim: WHERE col1 = 5 AND col2 BETWEEN 400 AND 94200

Sedziwoj

4.08.2008, 15:10:22

A kiedy miałeś ostatnio VACUUM na niej robione?

Co do wyszukiwania to indeks dwukolumnowy w takim przypadku musi być typu btree. Ale niech ktoś jeszcze lepiej się znający może wypowie.
Co do count to jakoś tak dziwnie, skoro u mnie tsearch2 w ~200k krotkach każda po kilka kilo tekstu wyszukiwał na normalnym kompie w ciągu milisekund.

Spróbuj count(1) działa szybciej, powinien być paru krotnie szybszy.

Ziels

4.08.2008, 15:19:02

Dzięki za odpowiedź

VACUUM - dzisiaj całą bazę postawiłem i uzupełniłem ją danymi, potem zrobiłem VACUUM FULL.

Typ indeksu - ok, dzięki, zmienię (mam nadzieję że da się bez czyszczenia tabeli

) i sprawdzę wyszukiwanie ponownie.

co do count(1) - nie wiedziałem nawet, dzięki

niestety nie zmienia zbyt wiele :/

Kod

portal=# explain analyze select count(1) from table;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=495164.33..495164.34 rows=1 width=0) (actual time=8134.547..8134.547 rows=1 loops=1)
   ->  Seq Scan on table  (cost=0.00..426028.26 rows=27654426 width=0) (actual time=0.009..4575.271 rows=27654426 loops=1)
Total runtime: 8134.594 ms
(3 rows)

portal=# explain analyze select count(*) from table;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=495164.33..495164.34 rows=1 width=0) (actual time=8023.854..8023.855 rows=1 loops=1)
   ->  Seq Scan on table  (cost=0.00..426028.26 rows=27654426 width=0) (actual time=0.013..4521.300 rows=27654426 loops=1)
Total runtime: 8023.903 ms
(3 rows)

Dziwi mnie taki rezultat, szczególnie że komputer to nie taki słaby serwer dedykowany ze świeżo postawionym systemem. Zgodnie ze wskazówkami z wiki postgresqla zmieniłem mu limity pamięci ale to również nie pomogło. Całość jest na Ubuntu server 8.04 na ReiserFS - wcześniej był umieszczony na ext3 i był identyczny problem.

=========EDIT==========
Indexes:
"table_pkey" PRIMARY KEY, btree (id) CLUSTER

Czyli porządany typ już jest. Pozostałe dwie kolumny numeryczne to po prostu klucze obce.

Sedziwoj

4.08.2008, 15:37:59

indeks na te kolumny:

[SQL] pobierz, plaintext 
CREATE INDEX test_no1_no2_index ON test USING btree ( no1, no2 );
[SQL] pobierz, plaintext

Wyszukiwanie praktycznie nie widoczne.

Ziels

4.08.2008, 16:00:08

Zadziałało - dzięki wielkie

Szukanie takie o którym pisałem działa tak jak powinno

A nie wiesz może jak tego counta na całości przyspieszyć? Mógłbym zrobić osobną tabelę na county i triggerem to załatwić ale zawsze zostaje kwestia warunków.

Sedziwoj

4.08.2008, 16:36:02

Cytat(Ziels @ 4.08.2008, 16:19:02 )

=========EDIT==========
Indexes:
"table_pkey" PRIMARY KEY, btree (id) CLUSTER

Czyli masz CLUSTER założony na index na id?
Jeśli tak, to trochę to bez sensu jest ;]

Co do count(*) to u mnie (normalny komp 3gb ram'u) zajmuje 11-12s (na 30milionach krotek o takim samym rodzaju jak Twoje)

Co do count, poczytaj:
http://www.varlena.com/GeneralBits/120.php
http://wiki.postgresql.org/wiki/Slow_Counting

może coś wybierzesz. Ale ogólnie jeśli w większości zapytań z count, ogranicza się do małego zbioru wyników, to nie będzie to takie obciążające.

A zapomniałem o tym, że PostgreSQL ma już optymalizację, i count(*) działa na innych zasadach niż w niektórych bazach, więc "trik" z 1 nie działa już.

Ziels

4.08.2008, 19:32:18

Dzięki

To ja moze jeszcze skorzystając z tego że założyłem temat dopytam - jak sobie poradzic z zapytaniami zwracającymi dużo rekordów przy tej ilości danych?

przykładowe zapytanie:

[SQL] pobierz, plaintext 
SELECT DISTINCT test1.*,
test2_in_test1.*,
test2.*
FROM test1 LEFT JOIN test2_in_test1 ON test1.ID=test2_in_test1.test1_ID
 
LEFT JOIN test2 ON test2.ID=test2_in_test1.test2_ID
 
LEFT JOIN test3 ON test1.COLUMN=test.OTHER_COLUMN
;
[SQL] pobierz, plaintext

test1 - 22 tys. wpisów
test2- 13 tys. wpisów
test2_in_test1 - 30 mln wpisów
test3 - 40 tys wpisów

Z czego każdy rekord z test1 może mieć około 7 tysięcy zaleznosci w tabeli łączącej.

Zapytanie przedstawione powyżej jest niesamowicie wolne - co polecacie żeby sobie z tym poradzić? Czytałem o Materialized view - wydaje mi się że to mogło by znacznie przyspieszyć działanie.
Dalej - może jakieś partycjonowanie bazy?

Sedziwoj

4.08.2008, 21:39:16

Proste pytanie, czy jest konieczne aby tyle danych na raz zostało zwróconych?

Ziels

6.08.2008, 13:29:38

Nie - zwykle to będą "paczki" maksymalnie po 100 wierszy, ale przy takim zapytaniu z joinami ustawienie limitu na 10 nic nie da bo i tak musi przeszukać całą tabelę, zrobić łączenia i zajmuje to strasznie dużo czasu.

Stworzyłem zmaterializowany widok żeby troche czasu zaoszczędzić na łączeniu tabel - ale nawet przy takim rozwiązaniu wykonanie zapytania trwa kilkanaście sekund - czyli czas nie do zaakceptowania. Nie masz może jakiegoś pomysłu na to?

===EDIT===
spróbuję założyć indeks na ten widok

Sedziwoj

6.08.2008, 14:28:59

Chodziło mi o takie coś, czy przed złączeniem nie możesz przefiltrować jednej z tabel i do niej dołączyć resztę danych, czy potrzebujesz złączonych tabel, aby móc wybrać te potrzebne?

P.S. Czytałeś http://jonathangardner.net/tech/w/PostgreS...erialized_Views

EDIT co do indeksu, to zrób tylko to porządnie

Aby z nich korzystało (zawsze sprawdzaj)

Ziels

6.08.2008, 14:31:37

Dokładnie tamtą stronę czytałem

Ano dokładnie - łączenie jest mi potrzebne do wybrania danych. Założenie indeksu na zmaterializowany widok (aż się zdziwiłem że tak można) załatwiło sprawę szukania - idzie błyskawicznie:)

To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.