Kompleksowa obsługa informatyczna

            Nowoczesne rozwiązania serwerowe

Optymalizacja serwera bazodanowego PostgreSQL

Poradniki

Zakupiłeś nowy serwer na który wydałeś górę pieniędzy, zainstalowałeś najnowszą wersję systemu operacyjnego, a mimo to twoje oprogramowanie korzystające z bazy danych działa wolno. Czy więc pieniądze okazały się wyrzucone w błoto? A możeda się coś z tym zrobić?

W tym artykule postaramy się przedstawić podstawy optymalizacji serwera PostgreSQL oraz wyjaśnimy jak sprawdzać plany zapytań. Poradnik ten nie skupia się na instalacji serwera bazodanowego. Jeśli jeszcze tego nie zrobiłeś, nie jest to strona dla Ciebie (w tym momencie).

Optymalizacja silnika

  • max_connections - parametr ten nie ma co prawda bezpośredniego wpływu na wydajność serwera, jednak niektóre z niżej opisywanych opcji są od niego zależne. Warto zastanowić się przez chwilę ilu użytkowników będzie jednocześnie pracowało na bazie i ustawić max_connections na rozsądnie większą wartość
  • shared_buffers - parametr niezwykle istotny dla ogólnej wydajności serwera. Domyślnie ustawiony jest na bardzo niską wartość, ponieważ wyższe ustawienia często wymagają ingerencji w konfiguracji samego systemu. Jeżeli dysponujesz >=1GB ramu, zalecane jest ustawienie shared_buffers na około 1/4 dostępnej pamięci, choć w niektórych sytuacjach baza może być wydajniejsza przy nawet wyższych wartościach. Pamiętać należy, że PostgreSQL korzysta efektywnie także z buforów systemowych, zatem niezalecane jest ustawienie tego parametru na więcej, niż 40-50% dostępnej pamięci. W przypadku systemów wyposażonych w mniejszą ilość pamięci RAM, zalecane jest ustawienie shared_buffers w okolicach 15-20% dostępnej wielkości.

    Pamiętać należy również o fakcie, iż PostgreSQL w wersjach wcześniejszych, niż 8.1 nie najlepiej zarządzał pamięcią zdefiniowaną przez opisywany parametr, z tego względu warto ograniczyć mu dostępne bufory wymuszając jednocześnie korzystanie z buforów systemowych. Jeśli używasz starszej wersji Postgresa, powinieneś ustawić shared_buffers na nie więcej, niż 400MB. Podobnie sytuacja wygląda z instalacjami na Windowsie, gdzie zalecanym przedziałem jest 64-512MB

    Jak napisaliśmy wcześniej podwyższenie parametru shared_buffers może wymagać ingerencji w konfiguracje systemu. Jeżeli PostgreSQL przestał się uruchamiać i widzisz komunikat podobny do tego:

    FATAL:  could not create shared memory segment: Invalid argument 
    DETAIL:  Failed system call was shmget(key=5432001, size=1102454784, 
    	03600).
    HINT:  This error usually means that PostgreSQL's request for a shared 
    	memory segment exceeded your kernel's SHMMAX parameter.  You can 
    	either reduce the request size or reconfigure the kernel with 
    	larger SHMMAX.  To reduce the request size (currently 1102454784 
    	bytes), reduce PostgreSQL's shared_buffers parameter (currently 
    	131072) and/or 	its max_connections parameter (currently 103). 
    i używasz Linuksa powinieneś przestawić parametry SHMMAX orax SHMALL w pliku /etc/sysctl.conf
    kernel.shmall=1100000000
    kernel.shmmax=1100000000
    Jeżeli konfigujesz server pod innym systemem, pomocna może być ta strona
  • effective_cache_size - parametr ten określa ilość pamięci dostępnej dla serwera bazodanowego. PostgreSQL wykorzystuje go do stwierdzenia, czy planowane zapytanie zmieści się w RAMie, czy też zaistnieje konieczność użycia dysku. Ustawienie tej opcji na zbyt niską wartość może powodować, iż w niektórych zapytaniach indeksy nie będą używane. Rozsądną wartością jest tu 50%-75% dostępnej pamięci. Pamiętać należy jednak o tym, że ustawienie tego parametru na wartość zbyt wysoką powodować będzie zbędne swapowanie na dysku. Jeżeli zatem twój serwer oferuje obok PostgreSQLa również inne usługi, opłacalne może być ustawienie tej zmiennej na niższą wartość.
  • checkpoint_segments - parametr ten określa jak często wywoływany jest checkpoint. Jest to proces dość 'zasobożerny', więc rozsądnie jest ustawić checkpoint_segments przynajmniej na 10. W bardziej obciążonych bazach stosuje się wartość 100 lub nawet wyższą. Efektem ubocznym wysokiej wartości jest dłuższy czas odzyskiwania bazy oraz większa zajętość dysku
  • checkpoint_completion_target - parametr po raz pierwszy wprowadzny w wersji 8.3. Rozsądną wartością jest tu poziom 0.8 lub 0.9 lecz tylko pod warunkiem zwiększenie checkpoint_segments
  • default_statistics_target - podczas pracy PostgreSQl gromadzi statystyki każdej tabeli i dzięki nim następnie układa plany zapytań. Podwyższenie tego parametru spowoduje większą ilość gromadzonych statystyk (a więc układanie lepszych planów) kosztem niewielkiego przyrostu objętości bazy. Rozsądnym ustawieniem jest tu wartość w okolicach 100.
  • autovacuum - znaczna część niedoświadczonych administratorów nie korzysta z tej funkcji, gdyż jest ona nieco zasobożerna. Vacuum jest procesem 'układającym' dane w bazie. Dzięki temu czas dostępu do poszczególnych krotek znacznie sie skraca, a cała baza zajmuje mniej miejsca na dysku. Vacuum przelicza takze wspomniane wyzej statystyki tabel. Autovacuum jest domyślnie wlączony od wersji 8.3 - we wcześniejszych należy włączyć go samemu. Do wersji 8.3 włącznie był również bardziej konfigurowalny poprzez zmienne max_fsm_pages oraz max_fsm_relations. W wersji 8.4 kod autovacuum został przepisany i ww parametry nei są już używane.
  • work_mem - jest to ilość pamięci, jaką PostgreSQL może użyć przy operacji sortowania. Zwiększenie tego parametru spowoduje, iż więcej sortowań wykonywanych będzie w RAMie bez konieczności używania wolnego dysku.
    UWAGA: parametr work_mem określa ilość pamięci na połączenie, nie zaś globalnie na serwer! Oznacza to, że ustawiając go np na 16MB i dopuszczając 100 połączeń do bazy (przez parametr max_connections), serwer może zużyć aż 1,6GB pamięci. Parametr ten powinniśmy ustawić tak wysoko, jak jest to możliwe biorąc pod uwagę ilość fizycznie zainstalowanej pamięci oraz maksymalną ilość połączeń.
    Począwszy od wersji 8.3 możesz używać parametru log_temp_files. Spowoduje ona logowanie użycia plików tymczasowych zamiast pracy w RAMie.
  • fsync - wyłączenie tej opcji spowoduje znacznie szybsze zapisy na dysk, jednak może doprowadzić do niespójności danych w przypadku braku zasilania. Nie zalecamy wyłączania jeżeli nie jest to absolutnie konieczne i/lub nie posiadasz naprawdę dobrych UPS'ów.
  • wal_buffers - zwiększenie tego parametru może pomóc w środowiskach bardzo obciążonych przy zapisach. Nie należy tu jednak przesadzać. Wartość na poziomie 1MB powinna być w zupełności wystarczająca
  • random_page_cost - parametr ten, określa koszt czasu dostępu do losowych fragmentów dysku w stosunku do kosztu do danych odczytywanych sekwencyjnie. Na tej podstawie budowane są plany zapytań. Obniżenie wartości tego parametru spowoduje częstsze wykorzystywanie indeksów. Przy obecnych prędkościach dysków domyślna wartość 4.0 jest stanowczo za duża. Należy ją zmniejszyć co najmniej o połowę, a w niektórych środowiskach opłacalne może być ustawienie jej na wartość <1.

Podgląd planu zapytania

Po konfiguracji bazy, możesz się zastanawiać jak sprawdzić efekty swojej pracy. PostgreSQL oferuje bardzo przydatne polecenie EXPLAIN, które pokazuje plan zapytania. Spójrzmy na przykładowe wyjaśnienie:

gpw=# explain select * from notowania where id_sesji<10 and id_spolki=100;
                                     QUERY PLAN                                        
-------------------------------------------------------------------------------------
 Index Scan using notowania_id_sesji_idx on notowania  (cost=0.00..115.95 rows=8 
 width=46)
   Index Cond: (id_sesji < 10)
   Filter: (id_spolki = 100)
(3 rows)

Widzimy, że serwer najpierw wyszuka w tabeli krotki, w ktorych atrybut id_sesji jest mniejszy od 10 używając w tym celu indesku, a następnie wyfiltruje krotki z id_spolki równym 100.


Liczby widoczne w pierwszym wierszu oznaczają kolejno:
  • 0.00 - szacowany koszt rozpoczęcia zapytania (np. sortowania)
  • 115.95 - szacowany całkowity koszt generowania wyniku na zapytanie
  • 8 - szacowana ilość wyników (na podstawie zgromadzonych statystyk)
  • 46 - szacowana średnia wielkość (w bajtach) pojedynczej krotki wyniku

Zobaczmy co się stanie, gdy dodamy do bazy kolejny indeks:
gpw=# create index notowania_id_spolki_idx on notowania(id_spolki);
CREATE INDEX
gpw=# explain select * from notowania where id_sesji<10 and id_spolki=100;
                                          QUERY PLAN                                           
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on notowania  (cost=38.30..46.42 rows=8 width=46)
   Recheck Cond: ((id_spolki = 100) AND (id_sesji < 10))
   ->  BitmapAnd  (cost=38.30..38.30 rows=8 width=0)
         ->  Bitmap Index Scan on notowania_id_spolki_idx  (cost=0.00..1.66 rows=54 
		        width=0)
               Index Cond: (id_spolki = 100)
         ->  Bitmap Index Scan on notowania_id_sesji_idx  (cost=0.00..36.39 rows=3485 
		        width=0)
               Index Cond: (id_sesji < 10)
(7 rows)

Szacowany czas generowania odpowiedzi znacznie się zmniejszył za sprawą wykorzystania drugiego indeksu zamiast filtrowania wyników. Dodatkowo widzimy tu, iż zostaną wykonane 2 podzapytania (widzimy również ich szacunkowe koszty oraz ilość krotek), a następnie ich wyniki zostaną połączone.

Do tej pory operaowaliśmy dość abstrakcyjnym pojęciem, jakim jest koszt. Dodatkowo wszystkei uzyskiwane wartości były tylko szacunkowe. Okazuje się jednak, że polecenie EXPLAIN może faktycznie wykonać zapytanie i zwrócić dokładne wartości. Efekt ten uzyskamy poprzez dodanie słowa kluczowego ANALYZE:

gpw=# explain analyze select * from notowania where id_sesji<10 and id_spolki=100;
                                         QUERY PLAN                                                                 
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on notowania  (cost=38.30..46.42 rows=8 width=46) 
 (actual time=1.276..1.311 rows=8 loops=1)
 
   Recheck Cond: ((id_spolki = 100) AND (id_sesji < 10))
   ->  BitmapAnd  (cost=38.30..38.30 rows=8 width=0) (actual time=1.262..1.262 
          rows=0 loops=1)
         ->  Bitmap Index Scan on notowania_id_spolki_idx  (cost=0.00..1.66 
		        rows=54 width=0) (actual time=0.191..0.191 rows=57 loops=1)
               Index Cond: (id_spolki = 100)
         ->  Bitmap Index Scan on notowania_id_sesji_idx  (cost=0.00..36.39 
		        rows=3485 width=0) (actual time=1.044..1.044 rows=3485 loops=1)
               Index Cond: (id_sesji < 10)
 Total runtime: 1.398 ms
(8 rows)

W wygenerowanej odpowiedzi znalazły się teraz informacje na temat dokładnego czasu generowania poszczególnych wyników na podzapytania, a także sumaryczny czas uzyskania odpowiedzi (Total runtime).

UWAGA: pamiętać należy, żepolecenie EXPLAIN ANALYZE w przeciwieństwie do samego EXPLAIN wykonuje zadane operacje na bazie! Oznacza to, że jeśli chcesz przetestować polecenia zmieniające dane (UPDATE, INSERT, DELETE), zmiany naprawdę odniosą skutek. Oczywiście istnieje prosty sposób, aby tego uniknąć - należy zamknąć zapytanie w bloku BEGIN ... ROLLBACK, np:

gpw=# BEGIN;
BEGIN
gpw=# explain analyze delete from notowania where id_sesji<10 and id_spolki=100;
                                          QUERY PLAN                                                                 
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on notowania  (cost=38.30..46.42 rows=8 width=6) 
 (actual time=0.886..0.925 rows=8 loops=1)
   Recheck Cond: ((id_spolki = 100) AND (id_sesji < 10))
   ->  BitmapAnd  (cost=38.30..38.30 rows=8 width=0) (actual time=0.866..0.866 
          rows=0 loops=1)
         ->  Bitmap Index Scan on notowania_id_spolki_idx  (cost=0.00..1.66 
                rows=54 width=0) (actual time=0.047..0.047 rows=57 loops=1)
               Index Cond: (id_spolki = 100)
         ->  Bitmap Index Scan on notowania_id_sesji_idx  (cost=0.00..36.39 
                rows=3485 width=0) (actual time=0.792..0.792 rows=3485 loops=1)
               Index Cond: (id_sesji < 10)
 Total runtime: 2.896 ms
(8 rows)

gpw=# ROLLBACK;
ROLLBACK