Problem optymalizacji staje się bardzo pilna w przypadku występowania opóźnień w działaniu systemu, a przy tym jest bardzo mało dokumentacji na ten temat, a zwłaszcza w języku rosyjskim. Poniżej spróbujemy omówić główne aspekty optymalizacji PostgreSQL.
Dyski i systemy plików
Jeśli Twój serwer posiada kilka fizycznych dysków, możesz rozdzielić pliki bazy danych i dziennik transakcji na różne dyski:
– Zatrzymaj serwer.
– Przenieś katalogi pg_clog i pg_xlog, znajdujące się w katalogu z bazami danych, na inny dysk.
– Utwórz dowiązanie symboliczne w miejscu oryginalnym.
– Uruchom serwer.
Konfiguracja (poprzez edycję pliku postgresql.conf)
Konfiguracja zasobów
shared_buffers
Rozmiar pamięci współdzielonej między procesami PostgreSQL, która jest potrzebna do wykonywania aktywnych operacji. Nie należy ustawiać zbyt dużej wartości, ponieważ PostgreSQL wykorzystuje również pamięć podręczną dysku.
Wartości:
Średnia ilość danych i 256-512 MB dostępnej pamięci: 16-32 MB
Duża ilość danych i 1-4 GB dostępnej pamięci: 64-256 MB
temp_buffers
Bufor dla obiektów tymczasowych, głównie dla tabel tymczasowych.
Można ustawić około 16 MB.
max_prepared_transactions
Liczba jednocześnie przygotowywanych transakcji.
Dla działania systemu 1C parametr ten nie ma znaczenia, ponieważ nie są tam używane instrukcje PREPARE TRANSACTION.
Można pozostawić domyślną wartość – 5.
work_mem
Pamięć specjalna, używana do sortowania i buforowania tabel dla jednego zapytania.
Przy ustawianiu tego parametru należy uwzględnić liczbę równoległych zapytań wykonywanych w tym samym czasie.
Dla pamięci 1-4 GB zaleca się ustawienie wartości 32-128 MB.
maintenance_work_mem
Pamięć używana do operacji VACUUM, CREATE INDEX, ALTER TABLE i FOREIGN KEY.
Należy ustawić większą wartość niż dla work_mem. Zbyt duże wartości mogą spowodować użycie pamięci wymiany (swap).
Dla pamięci 1-4 GB zaleca się ustawienie wartości 128-512 MB.
max_stack_depth
Specjalny stos dla serwera; w idealnym przypadku powinien on odpowiadać rozmiarowi stosu ustawionemu w jądrze systemu operacyjnego. Ustawienie wartości większej niż w jądrze może prowadzić do błędów.
Zaleca się ustawienie wartości 2-4 MB.
max_fsm_relations
Maksymalna liczba tabel, dla których będzie śledzone miejsce wolne w ogólnej mapie wolnego miejsca. Dane te są zbierane przez VACUUM.
Ustaw parametr zgodnie z liczbą tabel w Twojej bazie, z zapasem. (Dotyczy wersji przed 8.4)
max_fsm_pages
Liczba bloków, dla których będzie przechowywana informacja o wolnym miejscu. Informacje te są przechowywane w pamięci współdzielonej, a dla każdego rekordu wymagane jest 6 bajtów.
Użycie tego parametru pozwala uniknąć używania VACUUM FULL dla bazy; wystarczy zwykłe VACUUM. (Dotyczy wersji przed 8.4)
Parametr ten powinien być nie mniejszy niż 16 * max_fsm_relations.
Parametr ten jest ustawiany automatycznie podczas tworzenia bazy za pomocą narzędzia initdb.
Można go również ustawić ręcznie: jako przybliżoną wartość początkową można przyjąć połowę średniej liczby rekordów modyfikowanych (UPDATE lub DELETE) między uruchomieniami polecenia VACUUM.
Ocenę tej wartości (baza powinna już działać przez pewien czas) można wykonać, wykonując:
vacuum full analyze;
NOTICE: number of page slots needed (196272) exceeds max_fsm_pages (153600)
WSKAZÓWKA: Rozważ zwiększenie parametru konfiguracyjnego "max_fsm_pages" do wartości powyżej 196272.
max_files_per_process
Maksymalna liczba plików otwieranych przez proces i jego procesy potomne w tym samym momencie.
Zmniejsz ten parametr, jeśli podczas pracy pojawia się komunikat "Too many open files".
Zapis dzienników transakcji na dysk
commit_delay i commit_siblings
Wartość commit_delay wyrażana jest w mikrosekundach (domyślnie 0). Wartość commit_siblings wyrażana jest w sztukach (domyślnie 5).
commit_delay określa opóźnienie między umieszczeniem wpisu w buforze dziennika transakcji a jego zapisem na dysk. Jeśli przy pomyślnym zakończeniu transakcji aktywnych jest co najmniej commit_siblings transakcji, wpis zostanie opóźniony o czas commit_delay. Jeśli w tym czasie zakończy się inna transakcja, jej zmiany zostaną zapisane na dysk razem, za pomocą jednego wywołania systemowego. Parametry te przyspieszą działanie, jeśli równolegle wykonywanych jest wiele „drobnych” transakcji.
fsync
Parametr ten odpowiada za wymuszenie zapisu danych z pamięci podręcznej na dysk po zakończeniu transakcji.
Jeśli ustawisz jego wartość
fsync=off
to dane nie będą natychmiast zapisywane na nośnikach dyskowych po zakończeniu operacji. Może to znacznie zwiększyć szybkość operacji insert i update, ale istnieje ryzyko uszkodzenia bazy danych w przypadku awarii (nagłej utraty zasilania, awarii systemu operacyjnego lub awarii podsystemu dyskowego).
Korzystaj z tej opcji tylko wtedy, gdy posiadasz niezawodne zasilacze UPS oraz oprogramowanie kończące pracę systemu przy niskim poziomie naładowania baterii.
Nie należy wyłączać fsync podczas pracy Postgre SQL? na platformie Windows ze względu na niestabilność systemu
wal_sync_method
Metoda używana do wymuszonego zapisu danych na dysk.
Jeśli fsync=off, to ten parametr nie jest używany.
Możliwe wartości:
open_datasync – zapis danych metodą open() z parametrem O_DSYNC
fdatasync – wywołanie metody fdatasync() po każdym commicie
fsync_writethrough – wywoływanie fsync() po każdym commicie, ignorując procesy współbieżne
fsync – wywoływanie fsync() po każdym commicie
open_sync – zapis danych metodą open() z parametrem O_SYNC
Nie wszystkie metody są dostępne na określonych platformach. Domyślnie ustawiana jest pierwsza metoda dostępna w systemie.
full_page_writes
Ustaw ten parametr na off, jeśli fsync=off. W przeciwnym razie
Gdy ten parametr ma wartość on, Postgre SQL? zapisuje zawartość każdej strony do dziennika transakcji podczas pierwszej modyfikacji tabeli po kontrolnym punkcie (checkpoint). Jest to konieczne, ponieważ strony mogą zostać zapisane częściowo, jeśli w trakcie procesu system operacyjny „awariuje”. Prowadzi to do tego, że na dysku pojawiają się nowe dane zmieszane ze starymi. Poziomu zapisu w dzienniku transakcji może być niewystarczający do pełnej odbudowy danych po „awarii”. full_page_writes gwarantuje poprawną odbudowę, kosztem zwiększenia ilości danych zapisywanych w dzienniku transakcji. (Ponieważ dziennik transakcji zawsze zaczyna się od kontrolnego punktu. Jedynym sposobem zmniejszenia objętości zapisu jest zwiększenie interwału checkpoint).
wal_buffers
Ilość pamięci używana w SHARED MEMORY do prowadzenia dzienników transakcji.
Przy dostępnej pamięci 1-4 GB zaleca się ustawienie 256-1024 KB
Optymalizacja zapytań
enable_nestloop
Określa użycie planisty zapytań. W niektórych przypadkach wyłączenie planisty może skrócić czas wykonania poszczególnych zapytań, a nawet znacznie. Jednak w zdecydowanej większości przypadków planista pozwala skrócić czas wykonania zapytań. Nie należy go wyłączać, przynajmniej na stałe.
random_page_cost
Ustawia u planisty ocenę „kosztu” nieciągłego przeglądania danych. Domyślnie 4,0. Zmniejszenie tej wartości w stosunku do seq_page_cost spowoduje, że planista będzie preferował skanowanie indeksu, zwiększenie – odwrotnie, sprawi, że skanowanie indeksu będzie „droższe”. Możesz zmienić obie wartości, aby zmienić stosunek „kosztu” operacji wejścia/wyjścia dysku w stosunku do „kosztu” użycia procesora, który zostanie opisany przez następujące parametry.
Na serwerach z szybkimi macierzami dyskowymi warto zmniejszyć domyślną konfigurację do 3,0, 2,5 lub nawet do 2,0. Jeśli natomiast aktywna część Twojej bazy danych jest znacznie większa niż pamięć operacyjna, spróbuj zwiększyć wartość parametru. Doboru optymalnej wartości można dokonać również na podstawie wydajności zapytań. Jeśli planista zapytań częściej niż to konieczne preferuje skanowania sekwencyjne (sequential scans) zamiast skanowań z użyciem indeksu (index scans), obniż wartość. I odwrotnie, jeśli planista wybiera skanowanie po wolnym indeksie, gdy nie powinien tego robić, warto zwiększyć konfigurację. Po zmianie dokładnie przetestuj wyniki na jak najszerszym zbiorze zapytań. Nigdy nie obniżaj wartości random_page_cost poniżej 2,0; jeśli wydaje Ci się, że random_page_cost należy dalej obniżać, w tym przypadku rozsądniej jest zmienić konfigurację statystyk planisty.
cpu_tuple_cost
Ustawia u planisty ocenę „kosztu” obliczeń związanych z przetwarzaniem każdego wiersza podczas wykonywania zapytania. Domyślnie 0,01.
cpu_index_tuple_cost
Ustawia u planisty ocenę „kosztu” obliczeń związanych z przetwarzaniem każdego elementu indeksu podczas operacji skanowania indeksu. Domyślnie 0,005
cpu_operator_cost
Ustawia u planisty ocenę „kosztu” obliczeń związanych z wykonaniem każdego operatora lub funkcji podczas wykonywania zapytania. Domyślnie 0,0025.
effective_cache_size
Przekazuje dane planiście zapytań dotyczące objętości pamięci, której używa system operacyjny do buforowania plików, dla jednego zapytania.
Ten parametr w systemie operacyjnym można sprawdzić w ustawieniach:
Dla Windows: w Menedżerze zadań, zakładka Wydajność, Pamięć fizyczna – Bufor systemowy.
Dla Linux: wpisz polecenie free, potrzebna wartość w kolumnie cached (w kB)
Wartość tę należy podzielić przez liczbę konkurencyjnych zapytań w danym momencie (średnia liczba połączeń do bazy + zapas).
default_statistics_target
Ustawia głębokość statystyk dla tabel. Większe wartości mogą zwiększyć czas wykonania polecenia ANALYZE, ale poprawią generowanie planu zapytania.
Zaleca się ustawienie wartości około 100.
constraint_exclusion
Włącza lub wyłącza użycie przez planistę ograniczeń CONSTRAINT w tabelach podczas generowania zapytań.
Zaleca się ustawienie wartości on, przy czym jeśli zmieniasz CONSTRAINT w tabelach, musisz zaktualizować ich statystyki, wykonując ANALYZE, w przeciwnym razie zostaną wygenerowane nieprawidłowe plany zapytań.
Zbieranie statystyk
stats_command_string
Czy zbieracz statystyk ma otrzymywać informacje o aktualnie wykonywanej komendzie oraz czasie jej rozpoczęcia.
Ustawić on
stats_start_collector
Czy włączyć zbieranie statystyk.
Ustawić on
stats_row_level, stats_block_level
Czy zbierać informacje o aktywności na poziomie rekordów i bloków odpowiednio.
Ustawić
stats_row_level=on
stats_block_level=off
stats_reset_on_server_start
Czy zerować statystyki przy ponownym uruchomieniu serwera
Ustawić off
Autovacuum
VACUUM – „sprzątanie” (zbieranie śmieci). VACUUM odzyskuje miejsce zajęte przez „martwe” dane. Podczas zwykłych operacji na danych, Postgre SQL? nie usuwa danych fizycznie z tabel; dzieje się to w operacji VACUUM.
autovacuum
Czy włączyć autovacuum (automatyczne uruchamianie VACUUM), ustawić on
autovacuum_naptime
Czas przerwy między uruchomieniami Autovacuum.
Zależy to od tego, jak często aktualizowane są dane w Twoich tabelach. Może wynosić około 5 minut, domyślnie 1 minuta.
autovacuum_analyze_threshold
Określa minimalną liczbę wstawień, aktualizacji lub usunięć niezbędną do wysłania sygnału do uruchomienia ANALYZE w dowolnej tabeli. Ten parametr można zmienić dla poszczególnych tabel poprzez zmianę parametrów przechowywania.
autovacuum_vacuum_threshold
Określa minimalną liczbę wstawień, aktualizacji lub usunięć niezbędną do wysłania sygnału do uruchomienia VACUUM w dowolnej tabeli. Ten parametr można zmienić dla poszczególnych tabel poprzez zmianę parametrów przechowywania.
Blokady
max_locks_per_transaction
Liczba blokad na jedną transakcję: ustawić około 250
deadlock_timeout
Czas życia wzajemnych blokad. Ustawić około 2 sekund.
Potencjalny błąd
Po poprawieniu pliku konfiguracyjnego Postgre SQL może nie uruchomić się, wyświetlając błąd:
FATAL: could not create shared memory segment:...
Failed system call was shmget(key=5432001, size=140075008, 03600).
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 140075008 bytes), reduce PostgreSQL's shared_buffers parameter (currently 16384) and/or its max_connections parameter (currently 10).
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration.
Aby to naprawić, należy zwiększyć parametr SHMMAX w systemie. Można to zrobić w następujący sposób (dla systemu Linux):
Wykonaj polecenie:
echo 150829120 > /proc/sys/kernel/shmmax
podając swoją nową wartość dla parametru (jaką dokładnie – można sprawdzić w logu błędu Postgre SQL?). Ta operacja ustawia parametr w locie, ale po ponownym uruchomieniu systemu zmiany zostaną utracone. Aby tego uniknąć, dodaj do pliku /etc/sysctl.conf następującą linię:
kernel.shmmax = 150829120
podając swoją wartość.
Przykład konfiguracji dla serwera z 2 GB pamięci RAM
/var/lib/pgsql/data/postgresql.conf:
to be done
/etc/sysctl.conf:
to be done
Średnia konfiguracja
Średnia konfiguracja zapewniająca maksymalną wydajność. W konkretnym przypadku mogą być lepsze inne ustawienia. Dokładnie przeanalizuj to przewodnik i skonfiguruj PostgreSQL, opierając się na tych informacjach.
RAM — rozmiar pamięci
* shared_buffers = 1/8 RAM lub więcej (ale nie więcej niż 1/4);
* work_mem = 1/20 RAM;
* maintenance_work_mem = 1/4;
* max_fsm_relations = planowana liczba tabel w bazach * 1,5;
* max_fsm_pages = max_fsm_relations * 2000;
* fsync = true;
* wal_sync_method = fdatasync;
* commit_delay = od 10 do 100;
* commit_siblings = od 5 do 10;
* effective_cache_size = 0,9 wartości cached, którą pokazuje free;
* random_page_cost = 2 dla szybkich CPU, 4 dla wolnych;
* cpu_tuple_cost = 0,001 dla szybkich CPU, 0,01 dla wolnych;
* cpu_index_tuple_cost = 0,0005 dla szybkich CPU, 0,005 dla wolnych;
* autovacuum = on
* autovacuum_vacuum_threshold = 1800
* autovacuum_analyze_threshold = 900
Obsługa bazy danych