Zoptymalizuj PostgreSQL pod kątem szybkich testów

Najpierw Zawsze używaj najnowszej wersji PostgreSQL. Ulepszenia wydajności są zawsze nadchodzi, więc prawdopodobnie tracisz czas, jeśli strojenie starej wersji. Na przykład PostgreSQL 9.2 znacznie poprawia szybkość TRUNCATE i oczywiście dodaje skany tylko indeksów. Nawet drobne wydania powinny być zawsze przestrzegane; patrz Polityka wersji .

Don ' ts

Czy nie kłaść łyżkę stołową na ramdysku lub innym nietrwałym przechowywanie .

Jeśli stracisz tablespace cała baza danych może być uszkodzona i trudna w użyciu bez znaczącej pracy. Jest to bardzo mało korzystne w porównaniu z używaniem tabel UNLOGGED i posiadaniem dużej ilości pamięci podręcznej.

Jeśli naprawdę chcesz mieć system oparty na ramdisku, initdb całkiem nowy klaster na ramdisku przez initdb ing nową instancję PostgreSQL na ramdisku, więc masz całkowicie jednorazową instancję PostgreSQL.

Serwer PostgreSQL konfiguracja

Podczas testowania można skonfigurować serwer do nietrwałej, ale szybszej pracy .

Jest to jeden z jedynym dopuszczalnym zastosowań dla fsync=off ustawienie w PostgreSQL. To ustawienie mówi Postgresqlowi, aby nie przejmował się uporządkowanymi zapisami lub innymi paskudnymi kwestiami ochrony integralności danych i bezpieczeństwa przed awarią, dając mu pozwolenie na całkowite zniszczenie danych w przypadku utraty zasilania lub awarii systemu operacyjnego.

Nie trzeba dodawać, że ty nigdy nie należy włączać fsync=off w produkcji, chyba że używasz PG jako tymczasowej bazy danych dla danych, które możesz ponownie wygenerować z innego miejsca. Jeśli chcesz wyłączyć fsync, możesz również włączyć full_page_writes wyłączony, ponieważ nie robi już nic dobrego wtedy. Uwaga, że fsync=off i full_page_writes działają na poziomie klastra , więc wpływają na wszystkie bazy danych w Twojej instancji PostgreSQL.

Do użytku produkcyjnego możesz ewentualnie użyć synchronous_commit=off i ustawić commit_delay, ponieważ otrzymasz wiele takich samych korzyści jako fsync=off bez gigantycznego ryzyka uszkodzenia danych. Masz małe okno utraty ostatnich danych, jeśli włączysz commit asynchroniczny - ale to wszystko.

Jeśli masz możliwość nieznacznej zmiany DDL, możesz również użyć tabel UNLOGGED w Pg 9.1+, aby całkowicie uniknąć rejestrowania WAL i uzyskać rzeczywisty wzrost prędkości kosztem kasowania tabel w przypadku awarii serwera. Nie ma opcji konfiguracji pozwalającej na odblokowanie wszystkich tabel, należy ją ustawić podczas CREATE TABLE. Oprócz bycia dobre do testowania jest to przydatne, jeśli masz tabele pełne wygenerowanych lub nieistotnych danych w bazie danych, która w przeciwnym razie Zawiera rzeczy, które musisz być bezpieczny.

Sprawdź dzienniki i sprawdź, czy nie dostajesz ostrzeżeń o zbyt wielu punktach kontrolnych. Jeśli tak, powinieneś zwiększyć swój checkpoint_segments. Możesz również dostosować swój checkpoint_completion_target do wygładzania zapisów.

Dostrój shared_buffers aby dopasować się do twojego obciążenia. Jest to zależne od systemu operacyjnego, zależy od tego, co jeszcze się dzieje z Twoją maszyną i wymaga prób i błędów. Wartości domyślne są bardzo konserwatywne. Może być konieczne zwiększenie maksymalnego limitu pamięci współdzielonej systemu operacyjnego, jeśli zwiększysz shared_buffers w PostgreSQL 9.2 i poniżej; 9.3 i powyżej zmieniło sposób korzystania z pamięci współdzielonej, aby tego uniknąć.

Jeśli używasz tylko kilku połączeń, które wykonują dużo pracy, zwiększ work_mem, aby dać im więcej pamięci RAM do zabawy dla sortów itp. Uważaj, że zbyt wysokie ustawienie work_mem może powodować problemy z brakiem pamięci ponieważ jest to sortowanie, a nie połączenie, więc jedno zapytanie może mieć wiele zagnieżdżonych sortów. Tylko naprawdę musisz zwiększyć work_mem jeśli widzisz sortowanie rozlewające się na dysk w EXPLAIN lub zalogowane z log_temp_files ustawienie (zalecane), ale wyższa wartość może również pozwolić Pg wybrać mądrzejsze plany.

Jak powiedział inny plakat tutaj, mądrze jest umieścić xlog i główne tabele / indeksy na oddzielnych dyskach twardych, jeśli to możliwe. Oddzielne partycje są dość bezcelowe, naprawdę chcesz oddzielne dyski. Ta separacja ma znacznie mniejsze korzyści, jeśli używasz tabel fsync=off i prawie żadnych, jeśli używasz tabel UNLOGGED.

Wreszcie dostroić swoje zapytania. Upewnij się, że twoje random_page_cost i seq_page_cost odzwierciedlają wydajność Twojego systemu, upewnij się, że twoje effective_cache_size jest poprawne itp. Użyj EXPLAIN (BUFFERS, ANALYZE) do zbadania indywidualnych planów zapytań i Włącz moduł auto_explain, aby zgłosić wszystkie wolne zapytania. Często można znacznie poprawić wydajność zapytań, tworząc odpowiedni Indeks lub poprawiając koszty parametry.

AFAIK nie można ustawić całej bazy danych lub klastra jako UNLOGGED. Byłoby ciekawie móc to zrobić. Rozważ pytanie na liście mailingowej PostgreSQL.

Host OS tuning

Jest trochę strojenia, które możesz zrobić na poziomie systemu operacyjnego. Najważniejszą rzeczą, którą możesz chcieć zrobić, to przekonać system operacyjny, aby nie spłukiwał zapisów na dysk agresywnie, ponieważ naprawdę nie obchodzi cię, kiedy / czy trafią na dysk.

W Linuksie możesz kontrolować to z ustawieniami podsystemu pamięci wirtualnej dirty_*, Jak dirty_writeback_centisecs.

Jedyny problem z dostrajaniem ustawień writeback jest taki, że spłukanie przez inny program może spowodować spłukanie wszystkich buforów Postgresqla, powodując duże przestoje podczas gdy wszystko blokuje zapis. Możesz to złagodzić uruchamiając PostgreSQL na innym systemie plików, ale niektóre spłuczki mogą być na poziomie urządzenia lub całego hosta, a nie na poziomie systemu plików, więc nie możesz polegać robi się.

To strojenie naprawdę wymaga zabawy z ustawieniami, aby zobaczyć, co działa najlepiej dla Twojego obciążenia.

W nowszych jądrach, możesz chcieć upewnić się, że vm.zone_reclaim_mode jest ustawione na zero, ponieważ może to powodować poważne problemy z wydajnością systemów NUMA (większość systemów w dzisiejszych czasach) z powodu interakcji z tym, jak PostgreSQL zarządza shared_buffers.

Tuning zapytań i zadań

Są to rzeczy, które wymagają zmian w kodzie; mogą Ci nie odpowiadać. Niektóre są rzeczy, które możesz być w stanie aplikować.

Jeśli nie mieszasz pracy do większych transakcji, zacznij. Wiele małych transakcji jest kosztownych, więc powinieneś wsadować rzeczy, kiedy tylko jest to możliwe i praktyczne. Jeśli używasz zatwierdzania asynchronicznego, jest to mniej ważne, ale nadal wysoce zalecane.

W miarę możliwości użyj tabel tymczasowych. Nie generują ruchu WAL, więc są dużo szybsze dla wstawek i aktualizacji. Czasami warto wsadzić kilka danych do tabeli tymczasowej, manipulując nim jednak trzeba, a następnie wykonując INSERT INTO ... SELECT ... skopiować go do stołu finałowego. Jeśli sesja się zakończy lub stracisz połączenie, tabela tymczasowa zniknie, a żadne inne połączenie nie będzie mogło zobaczyć zawartości tabeli tymczasowej sesji.

Jeśli używasz PostgreSQL 9.1 lub nowszego możesz użyć UNLOGGED tabele danych, na które możesz sobie pozwolić, np. stan sesji. Są one widoczne w różnych sesjach i zachowane pomiędzy znajomości. Są one obcinane, jeśli serwer wyłączy się nieczysto, więc nie mogą być używane do niczego, czego nie można odtworzyć, ale są świetne dla pamięci podręcznych, zmaterializowanych widoków, tabel stanu itp.

Ogólnie rzecz biorąc, nie DELETE FROM blah;. Zamiast tego użyj TRUNCATE TABLE blah;; jest to o wiele szybsze, gdy wyrzucasz wszystkie wiersze w tabeli. Obcinaj wiele tabel w jednym wywołaniu TRUNCATE, jeśli możesz. Jest jednak zastrzeżenie, jeśli robisz wiele TRUNCATES małych tabel w kółko; zobacz: PostgreSQL Truncation prędkość

Jeśli nie masz indeksów na kluczach obcych, DELETEs z kluczami głównymi, do których odnoszą się te klucze obce, będą strasznie wolne. Pamiętaj, aby utworzyć takie indeksy, jeśli kiedykolwiek spodziewasz się DELETE z tabeli (- ów) odniesienia. Indeksy nie są wymagane dla TRUNCATE.

Nie twórz indeksów, których nie potrzebujesz. Każdy indeks ma koszt utrzymania. Spróbuj użyć minimalnego zestawu indeksów i pozwól skanom indeksów bitmapowych łączyć je, a nie utrzymywać zbyt wielu ogromnych, drogich indeksy wielokolumnowe. Tam, gdzie wymagane są indeksy, spróbuj najpierw wypełnić tabelę, a następnie utworzyć indeksy na końcu.

Hardware

Posiadanie wystarczającej ilości pamięci RAM do przechowywania całej bazy danych to ogromna wygrana, jeśli możesz nią zarządzać.

Jeśli nie masz wystarczającej ilości pamięci RAM, im szybsza pamięć, tym lepiej. Nawet tani dysk SSD robi ogromną różnicę w stosunku do wirującej rdzy. Nie ufaj jednak tanim dyskom SSD do produkcji, często nie są one bezpieczne i mogą zjadać Twoje data.

Nauka

Książka Grega Smitha, PostgreSQL 9.0 High Performance pozostaje aktualna pomimo odniesienia do nieco starszej wersji. To powinno być przydatne odniesienie.

Dołącz do ogólnej listy mailingowej PostgreSQL i śledź ją.

Czytanie:

• dostrajanie serwera PostgreSQL-PostgreSQL wiki
• liczba połączeń z bazą danych - PostgreSQL wiki