tuning postgresql dla dużych ilości pamięci RAM

Mam dwa identyczne serwery (pod względem sprzętowym), oba są standardowymi instalacjami Windows Server 2008 R2 z minimalnym zainstalowanym oprogramowaniem (w zasadzie mój kod i wymagane rzeczy, takie jak JVM itp.).

Na jednym serwerze działam na serwerze SQL 2005, na drugim serwerze postgresql 9.1. Różnica w wydajności między tymi dwoma serwerami jest oszałamiająca, jest tak zły na postgresql, że żałuję mojej początkowej wypowiedzi „użyjmy postgresql zamiast płacić za licencję serwera SQL” mojemu szefowi. Mówimy o różnicach 30 sekund vs 15 minut dla tego samego polecenia i nie jest to tylko jedno polecenie, to dowolne zapytanie lub polecenie, które na niego rzucam. Oba mają prawie takie same dane (rekordy zostały wstawione w innej kolejności), a obie bazy danych mają dokładnie taką samą strukturę / indeksy itp.

Ale mam nadzieję, że to tylko kwestia strojenia wydajności. Chodzi o to, że serwer sql używa prawie 32 koncertów pamięci RAM na serwerze, podczas gdy postgresl nie używa niczego, zdecydowanie mniej niż koncert, chociaż tak naprawdę tego nie rozgryzłem.

Jak uzyskać postgresql do korzystania z ponad 20 koncertów pamięci RAM? Serwery te zostały zbudowane specjalnie dla tego rodzaju bazy danych, więc moim zdaniem RAM nieużywany przez bazę danych i procesy wspierające.

Istnieje wiele możliwych do dostosowania stałych, zainicjowanych przez postgres.conf. Najważniejsze z nich to:

• max_connections: liczba jednoczesnych sesji
• work_mem : maksymalna ilość pamięci do wykorzystania dla wyników pośrednich, takich jak tabele skrótów i do sortowania
• shared_buffers ilość pamięci poświęconej „przypiętej” przestrzeni buforowej.
• effective_cache_size ilość pamięci przyjęta do użycia przez bufory LRU systemu operacyjnego.
• random_page_cost : oszacowanie względnego kosztu poszukiwań dysku.

max_connectionspołączenia nie powinny być wyższe niż potrzebne, połączenia kosztują zasoby, nawet gdy są bezczynne; w większości przypadków połączenie spędziłoby więcej czasu czekając w środku niż czekając na zewnątrz. (w cenie współbieżności) Przyjemną formułą jest „liczba wrzecion + liczba procesorów + X”

work_memjest trudne: można zastosować do każdego podzapytania, więc zapytanie z 5 HASHJOINSmoże kosztować 5 * work_mem. W przypadku najgorszych scenariuszy powinieneś również pomyśleć o tym, że wiele sesji zużywa tę ilość (ponownie powód, aby utrzymać się na max_connectionsniskim poziomie).

shared_buffersjest przereklamowany (IMHO). Zwykle zaleca się ustawienie jej na około 1/4 ... 1/2 całej dostępnej „wolnej” pamięci, ale zazwyczaj utrzymuję ją na niskim poziomie i ustawienie effective_cache_sizecałej dostępnej „wolnej” pamięci.

random_page_costto koszt wyszukiwania + odczytu na dysku. Jest to wartość względna sequential_disk_cost, która wynosi 1. Domyślna wartość (4) dla random_page_costjest ustawiona zbyt wysoko dla nowoczesnych maszyn i pamięci sieciowej, zwykle można ją obniżyć do wartości od 2 do 1.x. W przypadku dysków SSD ustawiono go nawet na 1,0, ponieważ wyszukiwanie jest prawie bezpłatne na dyskach SSD.

Rozważ użycie pgtune, aby pomóc dostroić konfigurację PostgreSQL. Od PgFoundry:

pgtune przyjmuje domyślny postgresql.conf i rozszerza serwer bazy danych tak, aby był równie wydajny, jak sprzęt, na którym jest wdrażany

Domyślna konfiguracja PostgreSQL jest bardzo konserwatywna i to narzędzie ma pomóc w tej dokładnej sytuacji. Dokumentacja jest łatwa do odczytania, a korzystanie z narzędzia jest dość proste.

Pamiętaj, że nie ma potrzeby korzystania z dokładnych sugestii pgtune. Grając z jego ustawieniami i obserwując wynikające z tego zmiany w pliku conf, uzyskasz lepsze zrozumienie konfiguracji PostgreSQL i sposobu ręcznej modyfikacji.

Jeśli każde zapytanie lub polecenie działa powoli, podejrzewam, że:

• łączysz się z bazą danych dla każdego uruchomionego zapytania;
• masz skonfigurowaną metodę uwierzytelniania, która nie działa i zatrzymuje zapytania, dopóki ta metoda uwierzytelniania nie przekroczy limitu czasu.

Czy możesz nam powiedzieć, ile czasu zajmuje uruchomienie zapytania select version()? Jeśli powinno być natychmiastowe (0,16 ms na mojej stacji roboczej).

Jeśli KAŻDE zapytanie jest o wiele wolniejsze, coś jest strasznie nie tak z serwerem lub czymś innym. Z mojego doświadczenia wynika, że ​​każda baza danych ma kilka rzeczy, w których jest lepsza od innych, ale pod względem wydajności pgsql jest z łatwością w tej samej dziedzinie co serwer mssql.

Więc na jakim systemie operacyjnym używasz pgsql? Jaki sprzęt? Jakie ustawienia już zmieniłeś? Jak duży jest twój zestaw danych? Jaki jest przykład złej kwerendy i wynik wyjaśnienia analizy (Uruchom kwerendę w następujący sposób:

wyjaśnij analizuj wybierz ... resztę zapytania tutaj ...;

Opublikuj wynik na http://explain.depesz.com/ i opublikuj link tutaj.