Konfigurowanie PostgreSQL pod kątem wydajności zapisu

Jeden z moich serwerów PostgreSQL obsługuje kilka (1–3) baz danych, które otrzymują stały strumień danych. Dane nie są specjalnie ustrukturyzowane, odpowiadają bieżącemu czasowi i różnorodności obserwowanych danych dla tej konkretnej chwili. Szybkość transmisji danych jest dość wysoka; dla jednej bazy danych to około gigabajta dziennie, a dla jednej dziesiątej około jednej dziesiątej. Nie oczekuję, że ta stopa wzrośnie. Wydajność odczytu ma znacznie niższy priorytet i jest obecnie akceptowalna.

W logach mam ten komunikat:

LOG:  checkpoints are occurring too frequently (15 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

Ta wartość jest obecnie ustawiona na 16, co jest możliwe dzięki uprzejmości pgtune.

Jakie ustawienia powinienem rozważyć, aby poprawić wydajność zapisu? Wolałbym zachować jak najwięcej bezpieczeństwa. Biorąc pod uwagę ilość napływających danych, mogłem zaakceptować utratę niektórych ostatnich danych w wyniku awarii, o ile większość danych pozostała nienaruszona.

Edycja: Na razie korzystam z PostgreSQL 9.0, ale planuję aktualizację do wersji 9.1. Nie publikuję szczegółów dotyczących sprzętu, ponieważ chociaż potwierdzam ich znaczenie, ostatecznie będę musiał dokonać tej optymalizacji na kilku komputerach z bardzo zróżnicowanym sprzętem. Jeśli sprzęt jest niezbędny do udzielenia odpowiedzi, proszę podać mi ogólne informacje, aby móc zastosować odpowiedź na komputerach o różnych konfiguracjach sprzętowych.

1 gigabajt dziennie nie jest tak wysoki jak na obciążenie zapisu. Rozłóż się w ciągu dnia, co daje około 50 kilobajtów na sekundę. Wolny napęd USB może sobie z tym poradzić. Zakładam jednak, że jest bardziej wybuchowy. Jak sugeruje a_horse_w_no_name, zwiększ segmenty punktów kontrolnych. Około 100 nie jest niczym niezwykłym.

Następnie zwiększ swój czas checkpoint_timeoutdo 1 godziny, a także spójrz na zwiększenie go checkpoint_completion_targetdo wartości bliższej 1,0 (100%). Cel ukończenia mówi PostgreSQL, jak agresywnie pisać w tle, aby x% ukończono przed uruchomieniem punktu kontrolnego, co zmusza wszystkie dane do wypisania jednocześnie z WAL i spowolni system podczas indeksowania.

Powodem, dla którego zwykle nie ustawia się go na 100%, jest to, że dość często zapisuje się ten sam blok więcej niż jeden raz, a opóźniając zapisywanie WAL do głównego magazynu, zapobiega się dwukrotnemu zapisywaniu tego samego bloku bez żadnego powodu.

Jeśli jest mało prawdopodobne, że będziesz pisał do tego samego bloku więcej niż jeden raz przed upływem limitu czasu, tzn. Wszystko, co robisz, to wstawianie, a następnie ustawienie go dość wysoko, warto podnieść go do około 0,9. Najgorsze, co się stanie, to, że będziesz pisał trochę częściej, niż byś musiał, ale wpływ punktów kontrolnych zostanie znacznie zmniejszony.

W systemie bardzo „ciężkiego zapisu” istnieje prawdopodobieństwo, że szybkość zapisu WAL może być zapisana podczas szczytowej aktywności.

Jeśli naprawdę możesz „zaakceptować utratę niektórych ostatnich danych w wyniku awarii”, możesz wyłączyć zatwierdzanie synchroniczne, które:

może być użyteczną alternatywą, gdy wydajność jest ważniejsza niż dokładna pewność co do trwałości transakcji

Jeśli jesteś w stanie zmienić sprzęt, możesz rozważyć dowolną z tych metod optymalizacji zapisu:

• RAID10 ponad RAID5
• Dużo wrzecion (może oznaczać na przykład 2,5 "zamiast 3,5")
• SAS przez SATA
• 15K ponad 10K dysków
• SSD

--edytować

W oparciu o Twój komentarz do doskonałej odpowiedzi @ Scotta : „Wolumin zapisu jest prawie całkowicie jednolity” i sugerowana szybkość przesyłania danych wynosząca „50 kilobajtów na sekundę”, wątpię, czy musisz zrobić coś, co grozi utratą danych. Być może pomogłoby to wiedzieć, na jakie ustawione są niektóre inne parametry konfiguracyjne.

Możesz także sprawdzić częstotliwość / rozmiar swoich zatwierdzeń: Ostatnio natknąłem się na problem, w którym próbowałem zaktualizować> 1 milion rekordów w jednej transakcji. Otrzymałem komunikaty dziennika podobne do tych opisanych przez OP, ale transakcja nie mogła zakończyć się nawet po kilku godzinach. Kiedy zepsułem zapis na kilka mniejszych transakcji (około 10 000 rekordów), całkowity wymagany czas spadł do około 15 minut.

Co ja myślę stało, że Postgres spędził tyle czasu pisać dzienniki że checkpoint_timeout upłynęły zanim mogła dokonać znacznego postępu zapisywania rekordów. Nie jestem pewien, czy to wytłumaczenie się utrzymuje. Nadal otrzymuję ostrzeżenia, ale wszystkie zapisy są w końcu przetwarzane. Potrzebowałem jednak (i ​​znalazłem) programowego obejścia zamiast tego, które wymaga rekonfiguracji bazy danych.

Zobacz także http://www.postgresql.org/docs/9.3/static/wal-configuration.html