Zwiększenie work_mem i shared_buffers w Postgres 9.2 znacznie spowalnia zapytania

Mam instancję PostgreSQL 9.2 działającą na 8-rdzeniowym komputerze RHEL 6.3 z 16 GB pamięci RAM. Serwer jest dedykowany dla tej bazy danych. Biorąc pod uwagę, że domyślny postgresql.conf jest dość konserwatywny w odniesieniu do ustawień pamięci, pomyślałem, że dobrym pomysłem może być zezwolenie Postgresowi na użycie większej ilości pamięci. Ku mojemu zaskoczeniu, postępując zgodnie z radami na wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server znacznie spowolniłem praktycznie każde zapytanie, które wykonuję, ale jest to oczywiście bardziej zauważalne w przypadku bardziej złożonych zapytań.

Próbowałem również uruchomić pgtune, który dał następującą rekomendację z większą ilością dostrojonych parametrów, ale to nic nie zmieniło. Sugeruje wspólne bufory wielkości 1/4 RAM, co wydaje się zgodne z poradami w innych miejscach (w szczególności na wiki PG).

default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80

Próbowałem ponownie zindeksować całą bazę danych po zmianie ustawień (za pomocą reindex database), ale to też nie pomogło. Bawiłem się z shared_buffers i work_mem. Stopniowo zmieniając je z bardzo konserwatywnych wartości domyślnych (128 k / 1 MB), stopniowo zmniejszano wydajność.

Uruchomiłem EXPLAIN (ANALYZE,BUFFERS)kilka zapytań i sprawcą wydaje się być to, że Hash Join jest znacznie wolniejszy. Nie jest dla mnie jasne, dlaczego.

Aby podać konkretny przykład, mam następujące zapytanie. Działa w ~ 2100ms w konfiguracji domyślnej i ~ 3300ms w konfiguracji ze zwiększonymi rozmiarami buforów:

select count(*) from contest c
left outer join contestparticipant cp on c.id=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
left outer join staffmember sm on cp.id=sm.contestparticipantid
left outer join person p on p.id=cp.personid
left outer join personinfo pi on pi.id=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';

EXPLAIN (ANALYZE,BUFFERS) dla powyższego zapytania:

• Domyślne bufory: http://explain.depesz.com/s/xaHJ
• Większe bufory: http://explain.depesz.com/s/Plk

Pytanie brzmi: dlaczego obserwuję zmniejszoną wydajność, gdy zwiększam rozmiary buforów? W urządzeniu na pewno nie brakuje pamięci. Alokacja, jeśli pamięć współdzielona w systemie operacyjnym jest ( shmmaxi shmall) ustawiona na bardzo duże wartości, nie powinno stanowić problemu. Nie otrzymuję też żadnych błędów w dzienniku Postgres. Używam autovacuum w domyślnej konfiguracji, ale nie oczekuję, że ma to coś wspólnego z tym. Wszystkie zapytania były uruchamiane na tym samym komputerze w odstępie kilku sekund, tylko ze zmienioną konfiguracją (i zrestartowano PG).

Edycja: Właśnie znalazłem jeden szczególnie interesujący fakt: kiedy wykonuję ten sam test na moim komputerze iMac z połowy 2010 roku (OSX 10.7.5) również z Postgres 9.2.1 i 16 GB pamięci RAM, nie odczuwam spowolnienia. Konkretnie:

set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms

Kiedy wykonuję dokładnie to samo zapytanie (powyższe) z dokładnie tymi samymi danymi na serwerze, otrzymuję 2100 ms z work_mem = 1 MB i 3200 ms z 96 MB.

Mac ma dysk SSD, więc jest zrozumiale szybszy, ale zachowuje się tak, jakbym się spodziewał.

Zobacz także dalszą dyskusję na temat wydajności pgsql .

Przede wszystkim pamiętaj, że work_mem jest na operację, więc może dość szybko ulec nadmiernemu obciążeniu. Ogólnie rzecz biorąc, jeśli nie masz problemów z powolnym działaniem, zostawiłbym work_mem w spokoju, dopóki go nie potrzebujesz.

Patrząc na twoje plany zapytań, jedną rzeczą, która mnie uderza, jest to, że trafienia w buforze są bardzo różne, patrząc na dwa plany, i że nawet sekwencyjne skany są wolniejsze. Podejrzewam, że problem dotyczy buforowania z wyprzedzeniem i mniejszej ilości miejsca na to. Oznacza to, że popychasz pamięć do ponownego użycia indeksów i nie czytasz tabel na dysku.

Rozumiem, że PostgreSQL będzie szukał strony w pamięci podręcznej przed odczytaniem jej z dysku, ponieważ tak naprawdę nie wie, czy pamięć podręczna systemu operacyjnego będzie zawierała tę stronę. Ponieważ strony pozostają w pamięci podręcznej, a pamięć podręczna jest wolniejsza niż pamięć podręczna systemu operacyjnego, zmienia to rodzaj zapytań, które są szybkie w porównaniu z tymi, które są wolne. W rzeczywistości czytanie planów, oprócz problemów work_mem, wygląda na to, że wszystkie informacje o zapytaniach pochodzą z pamięci podręcznej, ale jest to pytanie, która pamięć podręczna.

work_mem : ile pamięci możemy przydzielić na sortowanie lub powiązaną operację łączenia. Odnosi się to do operacji, a nie do instrukcji lub zaplecza, więc pojedyncze złożone zapytanie może zużyć wiele razy tyle pamięci. Nie jest jasne, czy osiągasz ten limit, ale warto o tym pamiętać i być tego świadomym. zbyt duże zwiększenie spowoduje utratę pamięci, która może być dostępna dla bufora odczytu i buforów współdzielonych.

shared_buffers : ile pamięci należy przydzielić do faktycznej kolejki stron PostgreSQL. Teraz idealnie interesujący zestaw twojej bazy danych pozostanie w pamięci podręcznej tutaj i w buforach odczytu. Jednak zapewnia to, że najczęściej używane informacje we wszystkich backendach zostaną zbuforowane i nie zostaną zrzucone na dysk. W Linuksie ta pamięć podręczna jest znacznie wolniejsza niż pamięć podręczna systemu operacyjnego, ale daje gwarancję, że pamięć podręczna systemu operacyjnego nie jest dostępna i jest przezroczysta dla PostgreSQL. To jest całkiem jasne, gdzie jest twój problem.

Tak więc dzieje się tak, że kiedy mamy prośbę, najpierw sprawdzamy współdzielone bufory, ponieważ PostgreSQL ma głęboką wiedzę o tym buforze i szukamy stron. Jeśli ich tam nie ma, prosimy OS o otwarcie ich z pliku, a jeśli OS buforuje wynik, zwraca kopię z pamięci podręcznej (jest to szybsze niż bufor współdzielony, ale Pg nie może stwierdzić, czy jest buforowany, czy włączony dysk, a dysk jest znacznie wolniejszy, więc PostgreSQL zazwyczaj nie wykorzystuje tej szansy). Pamiętaj, że wpływa to również na losowy i sekwencyjny dostęp do strony. Możesz więc uzyskać lepszą wydajność dzięki niższym ustawieniom shared_buffers.

Mam przeczucie, że prawdopodobnie uzyskasz lepszą lub przynajmniej bardziej spójną wydajność w środowiskach o wysokiej współbieżności z większymi ustawieniami bufora współdzielonego. Pamiętaj również, że PostgreSQL pobiera tę pamięć i przechowuje ją, więc jeśli w systemie działają inne rzeczy, bufory odczytu będą przechowywać pliki odczytywane przez inne procesy. To bardzo duży i złożony temat. Większe ustawienia wspólnego bufora zapewniają lepszą gwarancję wydajności, ale w niektórych przypadkach mogą zapewniać mniejszą wydajność.

Oprócz pozornie paradoksalnego efektu, że zwiększenie work_memwydajności zmniejsza ( @Chris może mieć wyjaśnienie), możesz poprawić swoją funkcję na co najmniej dwa sposoby.

• Przepisz dwa fałszywe LEFT JOINza pomocą JOIN. Może to wprowadzić w błąd planisty zapytań i prowadzić do gorszych planów.

SELECT count(*) AS ct
FROM   contest            c
JOIN   contestparticipant cp ON cp.contestId = c.id
JOIN   personinfo         pi ON pi.id = cp.personinfoid
LEFT   JOIN teammember    tm ON tm.contestparticipantid = cp.id
LEFT   JOIN staffmember   sm ON sm.contestparticipantid = cp.id
LEFT   JOIN person        p  ON p.id = cp.personid
WHERE (pi.firstname LIKE '%a%'
OR     pi.lastname  LIKE '%b%')

• Zakładając, że rzeczywiste wzorce wyszukiwania są bardziej selektywne, użyj indeksów trygramowych pi.firstnamei pi.lastnameobsługuj LIKEwyszukiwania niezakotwiczone . ( '%a%'Obsługiwane są również krótsze wzorce, ale indeks raczej nie pomoże w nieselektywnych predykatach.):

CREATE INDEX personinfo_firstname_gin_idx ON personinfo USING gin (firstname gin_trgm_ops);
CREATE INDEX personinfo_lastname_gin_idx  ON personinfo USING gin (lastname gin_trgm_ops);

Lub jeden indeks wielokolumnowy:

CREATE INDEX personinfo_name_gin_idx ON personinfo USING gin (firstname gin_trgm_ops, lastname gin_trgm_ops);

Powinieneś sprawić, że twoje zapytanie będzie nieco szybsze. W tym celu musisz zainstalować dodatkowy moduł pg_trgm . Szczegóły w ramach powiązanych pytań:

• Jak wdraża się LIKE?
• Dopasowywanie wzorców z LIKE, SIMILAR TO lub wyrażeniami regularnymi w PostgreSQL

Czy próbowałeś także ustawić work_mem lokalnie - tylko dla bieżącej transakcji ?

SET LOCAL work_mem = '96MB';

To powstrzymuje równoczesne transakcje przed zjedzeniem większej ilości pamięci RAM, być może głodowaniem.