Witaj Gościu! ( Zaloguj | Rejestruj )

Forum PHP.pl

 
 Reply to this topicStart new topic
> Postgresql - wydajność
Ziels
post 4.08.2008, 11:07:20
Post #1





Grupa: Zarejestrowani
Postów: 235
Pomógł: 2
Dołączył: 30.06.2006

Ostrzeżenie: (0%)
-----

Witam

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

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
Go to the top of the page
+Quote Post
Sedziwoj
post 4.08.2008, 15:10:22
Post #2





Grupa: Zarejestrowani
Postów: 793
Pomógł: 32
Dołączył: 23.11.2006
Skąd: Warszawa

Ostrzeżenie: (0%)
-----

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.


--------------------
Algorytmy w PHP, czy ktoś o tym słyszał?
Dlaczego tak mało kobiet programuje? ponieważ nie zajmują się głupotami.
Go to the top of the page
+Quote Post
Ziels
post 4.08.2008, 15:19:02
Post #3





Grupa: Zarejestrowani
Postów: 235
Pomógł: 2
Dołączył: 30.06.2006

Ostrzeżenie: (0%)
-----

Dzięki za odpowiedź smile.gif

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 smile.gif) i sprawdzę wyszukiwanie ponownie.

co do count(1) - nie wiedziałem nawet, dzięki smile.gif 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.

Ten post edytował Ziels 6.08.2008, 17:10:21
Go to the top of the page
+Quote Post
Sedziwoj
post 4.08.2008, 15:37:59
Post #4





Grupa: Zarejestrowani
Postów: 793
Pomógł: 32
Dołączył: 23.11.2006
Skąd: Warszawa

Ostrzeżenie: (0%)
-----

indeks na te kolumny:
[SQL] pobierz, plaintext 
  1. CREATE INDEX test_no1_no2_index ON test USING btree ( no1, no2 );
[SQL] pobierz, plaintext

Wyszukiwanie praktycznie nie widoczne.


--------------------
Algorytmy w PHP, czy ktoś o tym słyszał?
Dlaczego tak mało kobiet programuje? ponieważ nie zajmują się głupotami.
Go to the top of the page
+Quote Post
Ziels
post 4.08.2008, 16:00:08
Post #5





Grupa: Zarejestrowani
Postów: 235
Pomógł: 2
Dołączył: 30.06.2006

Ostrzeżenie: (0%)
-----

Zadziałało - dzięki wielkie winksmiley.jpg Szukanie takie o którym pisałem działa tak jak powinno smile.gif

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.
Go to the top of the page
+Quote Post
Sedziwoj
post 4.08.2008, 16:36:02
Post #6





Grupa: Zarejestrowani
Postów: 793
Pomógł: 32
Dołączył: 23.11.2006
Skąd: Warszawa

Ostrzeżenie: (0%)
-----

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ż.


--------------------
Algorytmy w PHP, czy ktoś o tym słyszał?
Dlaczego tak mało kobiet programuje? ponieważ nie zajmują się głupotami.
Go to the top of the page
+Quote Post
Ziels
post 4.08.2008, 19:32:18
Post #7





Grupa: Zarejestrowani
Postów: 235
Pomógł: 2
Dołączył: 30.06.2006

Ostrzeżenie: (0%)
-----

Dzięki winksmiley.jpg

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 
  1. SELECT DISTINCT test1.*,
  2. test2_in_test1.*,
  3. test2.*
  4. FROM test1 LEFT JOIN test2_in_test1 ON test1.ID=test2_in_test1.test1_ID
  5.  
  6. LEFT JOIN test2 ON test2.ID=test2_in_test1.test2_ID
  7.  
  8. LEFT JOIN test3 ON test1.COLUMN=test.OTHER_COLUMN
  9. ;
[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?

Ten post edytował Ziels 4.08.2008, 19:35:30
Go to the top of the page
+Quote Post
Sedziwoj
post 4.08.2008, 21:39:16
Post #8





Grupa: Zarejestrowani
Postów: 793
Pomógł: 32
Dołączył: 23.11.2006
Skąd: Warszawa

Ostrzeżenie: (0%)
-----

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


--------------------
Algorytmy w PHP, czy ktoś o tym słyszał?
Dlaczego tak mało kobiet programuje? ponieważ nie zajmują się głupotami.
Go to the top of the page
+Quote Post
Ziels
post 6.08.2008, 13:29:38
Post #9





Grupa: Zarejestrowani
Postów: 235
Pomógł: 2
Dołączył: 30.06.2006

Ostrzeżenie: (0%)
-----

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 smile.gif

Ten post edytował Ziels 6.08.2008, 13:31:42
Go to the top of the page
+Quote Post
Sedziwoj
post 6.08.2008, 14:28:59
Post #10





Grupa: Zarejestrowani
Postów: 793
Pomógł: 32
Dołączył: 23.11.2006
Skąd: Warszawa

Ostrzeżenie: (0%)
-----

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 biggrin.gif Aby z nich korzystało (zawsze sprawdzaj)

Ten post edytował Sedziwoj 6.08.2008, 14:30:16


--------------------
Algorytmy w PHP, czy ktoś o tym słyszał?
Dlaczego tak mało kobiet programuje? ponieważ nie zajmują się głupotami.
Go to the top of the page
+Quote Post
Ziels
post 6.08.2008, 14:31:37
Post #11





Grupa: Zarejestrowani
Postów: 235
Pomógł: 2
Dołączył: 30.06.2006

Ostrzeżenie: (0%)
-----

Dokładnie tamtą stronę czytałem smile.gif

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:)
Go to the top of the page
+Quote Post
« Następny starszy · PostgreSQL · Następny nowszy »
 

Reply to this topicStart new topic
1 Użytkowników czyta ten temat (1 Gości i 0 Anonimowych użytkowników)
0 Zarejestrowanych:

 

Tryb wyświetlania: Standardowy · Przełącz na: Linearny+ · Przełącz na: Drzewo

Śledź ten temat · Wyślij temat na e-mail · Wydrukuj ten temat · Subskrybuj to forum

RSS Wersja Lo-Fi Aktualny czas: 18.04.2024 - 07:07
Powered By IP.Board © 2024  IPS, Inc.
All changes by PHP.pl Administrators
Hosting zapewnia NQ.pl hosting, trac, svn