Optymalizacja PostgreSQL dla 1C: serwer Enterprise

1. Główne parametry PostgreSQL.
2. work_mem
3. maintenance_work_mem
4. efficient_cache_size
5. fsync
6. wal_buffers
7. checkpoint_segments
8. full_page_writes
9. synchronous_commit
10. Funkcje i ograniczenia w 1C: Enterprise podczas pracy z PostgreSQL.
11. Optymalizacja wykorzystania wirtualnej tabeli SliceLast podczas pracy z PostgreSQL.
12. Rozwiązywanie problemu zawieszenia z PostgreSQL.
13. Posłowie.

Obecnie wydajność PostgreSQL w połączeniu z serwerem 1C: Enterprise w porównaniu z tym samym MS SQL pozostawia wiele do życzenia. Ten artykuł kontynuuje próby osiągnięcia przyzwoitej wydajności PostgreSQL. Chociaż w tej chwili nie udało mi się osiągnąć wydajności porównywalnej z MS SQL, ale myślę, że ten problem zostanie rozwiązany w najbliższej przyszłości.

W dalszej części artykułu wymieniono główne opcje i funkcje, na które należy zwrócić uwagę podczas optymalizacji PostgreSQL.

Główne parametry PostgreSQL.

shared_buffers

Ilość pamięci współdzielonej przydzielonej przez PostgreSQL do buforowania danych zależy od liczby stron współdzielonych buforów 8 kilobajtów każdy. Pamiętaj, że sam system operacyjny buforuje dane, więc nie ma potrzeby przydzielania całej dostępnej pamięci RAM do pamięci podręcznej. Rozmiar współdzielonych buforów zależy od wielu czynników. Na początek możesz przyjąć następujące wartości:

• 8–16 MB - zwykły komputer stacjonarny z 512 MB i małą bazą danych,
• 80–160 MB - Mały serwer przeznaczony do obsługi bazy danych z 1 GB pamięci RAM i bazą danych o pojemności około 10 GB,
• 400 MB - Serwer z wieloma procesorami, o pojemności 8 GB i bazą danych zajmującą ponad 100 GB obsługujących kilkaset aktywnych połączeń jednocześnie.

work_mem

Dla każdego żądania przydzielana jest ograniczona ilość pamięci. Ten wolumin jest używany do sortowania, łączenia i innych podobnych operacji. Po przekroczeniu tego woluminu serwer zaczyna używać plików tymczasowych na dysku, co może znacznie zmniejszyć wydajność. Możesz oszacować niezbędną wartość dla work_mem, dzieląc ilość dostępnej pamięci (pamięć fizyczna minus objętość zajmowana przez inne programy i wspólne strony współdzielonych buforów) przez maksymalną liczbę jednocześnie używanych aktywnych połączeń.

maintenance_work_mem

Ta pamięć jest używana do wykonywania zbierania statystyk ANALYZE, zbierania śmieci VACUUM, tworzenia indeksów CREATE INDEX i dodawania kluczy obcych. Rozmiar pamięci przydzielonej do tych operacji powinien być porównywalny z fizycznym rozmiarem największego indeksu na dysku.

efficient_cache_size

PostgreSQL planuje w swoich planach buforowanie plików wykonywane przez system operacyjny. Ten parametr odpowiada maksymalnemu rozmiarowi obiektu, który może zmieścić się w pamięci podręcznej systemu. Ta wartość jest używana tylko do oceny. Effect_cache_size można ustawić na ½ - 2/3 ilości dostępnej pamięci RAM, jeśli całość jest dostępna dla PostgreSQL.

UWAGA! Następujące parametry mogą znacznie zwiększyć wydajność PostgreSQL. Zaleca się jednak używanie ich tylko wtedy, gdy są niezawodne UPS i oprogramowanie wyłączające system, gdy baterie są rozładowane.

fsync

Ten parametr jest odpowiedzialny za opróżnianie danych z pamięci podręcznej na dysk po zakończeniu transakcji. Jeśli ustawisz ten parametr na wyłączony, dane nie będą zapisywane na dyskach natychmiast po zakończeniu operacji. Może to znacznie zwiększyć szybkość operacji wstawiania i aktualizacji, ale istnieje ryzyko uszkodzenia bazy danych w przypadku awarii (nieoczekiwana awaria zasilania, awaria systemu operacyjnego, awaria podsystemu dysku).

Negatywny wpływ włączonego fsync można zmniejszyć, wyłączając go i opierając się na niezawodności sprzętu. Lub, po prawidłowym wybraniu parametru wal_sync_method, metody, która jest używana do wymuszenia zapisu danych na dysku.

Możliwe wartości:

• open_datasync - zapis danych za pomocą metody open () z parametrem O_DSYNC,
• fdatasync - wywołuje metodę fdatasync () po każdym zatwierdzeniu,
• fsync_writethrough - wywołuje fsync () po każdym zatwierdzeniu ignoruje równoległe procesy,
• fsync - wywołuje fsync () po każdym zatwierdzeniu,
• open_sync - zapis danych za pomocą metody open () z parametrem O_SYNC.

UWAGA! Nie wszystkie metody są dostępne na niektórych platformach. Wybór metody zależy od systemu operacyjnego, w którym działa PostgreSQL.

PostgreSQL zawiera narzędzie pg_test_fsync , za pomocą którego można określić optymalną wartość parametru wal_sync_method.

Wykonuje serię testów dysków przy użyciu różnych metod synchronizacji. W wyniku tego testu uzyskujemy szacunki wydajności systemu dyskowego, które można wykorzystać do określenia optymalnej metody synchronizacji dla tego systemu operacyjnego.

Zdecydowałem się przeprowadzić powyższy test na moim komputerze roboczym, który ma następujące cechy:

• Procesor: Intel Core i3-3220 przy 3,30 GHz x 2
• RAM: 4 GB
• Dysk twardy: Seagate ST3320418AS 320GB

Test Windows:

• OS: Windows 7 Maksymalna x64
• FS: NTFS
• DBMS: PostgreSQL 9.4.2-1.1C x64

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 33 35 35 36 37 38 39 9.4.2-1.1C bin> pg_test_fsync 5 sekund na test O_DIRECT obsługiwany dla tego open_datasync i open_sync. Porównaj metody synchronizacji plików za pomocą jednego 8kB napisać: (w wal_sync_method kolejności preferencji, z wyjątkiem fdatasync jest domyślnym Linuksa) open_datasync 48817.440 ops / s 20 usecs / op fdatasync n / a fsync 79.688 ops / s 12549 usecs / op fsync_writethrough 80.072 ops / sec 12489 usecs / op open_sync n / a Porównanie metod synchronizacji plików przy użyciu dwóch 8kB pisze: (w wal_sync_method kolejności preferencji, z wyjątkiem fdatasync jest domyślnym Linuksa) open_datasync 24713.634 ops / s 40 usecs / op fdatasync n / a fsync 78.690 ops / s 12708 usecs / op fsync_writethrough 79,073 operacji / sek. 12406 n / a 4 * 4kB open_sync zapisuje n / a 8 * 2kB open_sync zapisuje n / a 16 * 1kB open_sync i pisze n / a Jeśli możesz porównać, możesz przeczytać fsync () synchronizuj dane zapisane na innym deskryptorze.) write, fsync, clos e 76.493 ops / s 13073 usecs / op napisz, zamknij, fsync 77.676 ops / s 12874 usecs / op Non-Sync'ed 8kB pisze: napisz 1800.319 ops / sec 555 usecs / op

Zgodnie z wynikami testów, widzimy, że dla Windows najlepszym rozwiązaniem byłoby użycie open_datasync.

Test Linuksa:

• OS: Debian 8.6 Jessie
• Jądro: Linux x86_64 3.16.0-4-amd64
• FS: ext4
• DBMS: PostgreSQL 9.4.2-1.1C amd64

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 33 34 35 36 37 38 39 /usr/lib/postgresql/9.4/bin# ./pg_test_fsync 5 sekund na test O_DIRECT obsługiwany na tej platformie dla open_datasync i open_sync. Porównaj metody synchronizacji plików przy użyciu jednego zapisu 8kB: (w kolejności preferencji wal_sync_method, z wyjątkiem fdatasync jest to domyślnie Linux) open_datasync 80.215 ops / s 12467 usecs / op fdatasync 80.349 ops / sec 12446 usecs / op fsync 39.384 ops / s 25391 usec.acdctasync oct / sec. / a open_sync 40.013 ops / s 24992 usecs / op Porównanie metod synchronizacji plików przy użyciu dwóch 8kB pisze: (w wal_sync_method kolejności preferencji, z wyjątkiem fdatasync jest domyślnym Linuksa) open_datasync 40.033 ops / s 24980 usecs / op fdatasync 77.264 ops / s 12943 usecs / oP fsync 36.325 ops / sec 27529 usecs / op fsync_writethrough n / a open_sync 19.659 ops / s 50866 usecs / op Porównaj 16kB w różnych rozmiarach zapisu open_sync.) 1 * 16kB open_sync pisze 38.697 ops / s 25842 usecs / op 2 * 8kB open_sync zapisuje 17.356 ops / s 57616 usecs / op 4 * 4kB zapisuje open_sync 8.996 ops / s 111156 usecs / op 8 * 2kB open_sync zapisuje 4.552 ops / s 219686 uses / op 8 * 2kB open_sync zapisuje 4.596 ops / s 8 * 2kB open_sync zapisuje 4.552 ops / s * 1kB open_sync zapisuje 2.218 ops / sec 450930 test usecs / op, jeśli honorowany jest fsync na deskryptorze pliku bez zapisu: (fsync ()) write, fsync, zamknij 34.341 ops / sec 29120 usecs / op write, close, fsync 35.753 ops / s 27970 Non-synceded 8kB write / write: napisz 484193.516 ops / sec 2 usecs / op

Zgodnie z wynikami testów, widzimy, że najlepszą prędkość dają metody fdatasync i open_datasync. Można również zauważyć, że na tym samym sprzęcie Linux produkował prędkość zapisu prawie o połowę mniejszą niż w systemie Windows.

Należy zauważyć, że w tych testach zastosowano system dyskowy składający się z jednego dysku. W przypadku korzystania z macierzy RAID z dużą liczbą dysków obraz może być inny.

wal_buffers

Ilość pamięci używanej w PAMIĘCI UDOSTĘPNIONE dla dzienników transakcji. Przy dostępnej pamięci 1-4 GB zaleca się instalację 256-1024 Kb. Ten parametr należy zwiększyć w systemach z dużą liczbą modyfikacji tabel bazy danych.

checkpoint_segments

Określa liczbę segmentów (po 16 MB) dziennika transakcji między punktami kontrolnymi. W przypadku baz danych z wieloma danymi modyfikującymi transakcje zalecane jest zwiększenie tego parametru. Kryterium adekwatności liczby segmentów jest brak w dzienniku ostrzeżeń (ostrzeżenie), że punkty kontrolne występują zbyt często.

full_page_writes

Włączenie tej opcji zapewnia poprawne odzyskanie, kosztem zwiększenia danych zapisywanych w dzienniku transakcji. Wyłączenie tej opcji przyspiesza pracę, ale może spowodować uszkodzenie bazy danych w przypadku awarii systemu lub awarii zasilania.

synchronous_commit

Włącza / wyłącza synchroniczne logowanie do plików dziennika po każdej transakcji. Włączenie synchronicznego nagrywania chroni przed możliwą utratą danych. Ale nakłada ograniczenie przepustowości serwera. Możesz wyłączyć synchroniczne nagrywanie, jeśli chcesz zapewnić lepszą wydajność przez liczbę transakcji. A potencjalnie niska możliwość utraty niewielkiej ilości zmian podczas awarii systemu nie jest krytyczna. Aby wyłączyć synchroniczne nagrywanie, ustaw wartość w tym parametrze.

Innym sposobem na zwiększenie wydajności PostgreSQL jest przeniesienie dziennika transakcji (pg_xlog) na inny dysk. Przydzielenie oddzielnego zasobu dysku dla dziennika transakcji pozwala uzyskać znaczny wzrost wydajności o 10% -12% dla załadowanego OLTP systemy.

W systemie Linux odbywa się to poprzez utworzenie dowiązania symbolicznego do nowej pozycji katalogu dziennika transakcji.

W systemie Windows można użyć tego narzędzia do tego celu. Junction . Do tego potrzebujesz:

1. Zatrzymaj PostgreSQL.
2. Backup C: Program Files PostgreSQL XXX dane pg_xlog.
3. Kopiuj C: Program Files PostgreSQL XXX dane pg_xlog na D: pg_xlog i usuń C: Program Files PostgreSQL XXX informacje pg_xlog.
4. Rozpakuj program Junction w C: Program Files Pliki PostgreSQL XXX.
5. Otwórz okno CMD , przejdź do C: Program Files PostgreSQL XXX i wykonaj junction -s pg_xlog D: pg_xlog.
6. Ustaw uprawnienia w folderze D: pg_xlog na użytkownika postgres.
7. Uruchom PostgreSQL.
   Gdzie XXX to wersja używana przez PostgreSQL.

Funkcje i ograniczenia w 1C: Enterprise podczas pracy z PostgreSQL.

Korzystanie z projektu PEŁNE POŁĄCZENIE ZEWNĘTRZNE.

PostgreSQL obsługuje tylko częściowo FULL OUTER JOIN (BŁĄD: „FULL JOIN jest obsługiwany tylko przy łączonych warunkach łączenia”). Aby zaimplementować pełną obsługę FULL OUTER JOIN, gdy 1C: Enterprise 8 współpracuje z PostgreSQL, takie zapytanie jest przekształcane w inną formę z równoważnym wynikiem, jednak skuteczność korzystania z projektu FULL EXTERNAL CONNECTION jest zmniejszona.

Z tego powodu nie zaleca się używania PEŁNEGO POŁĄCZENIA ZEWNĘTRZNEGO podczas pracy z PostgreSQL. W większości przypadków można zrezygnować z tej konstrukcji, przepisując oryginalne zapytanie.

Optymalizacja wykorzystania wirtualnej tabeli SliceLast podczas pracy z PostgreSQL.

Problem: Podczas korzystania z PostgreSQL, użycie połączenia ze stołem wirtualnym Slice Last może prowadzić do znacznego obniżenia wydajności. Z powodu błędu optymalizatora można wybrać nieoptymalny plan wykonania zapytania.

Rozwiązanie: Jeśli zapytanie korzysta z połączenia z wirtualną tabelą języka zapytań 1C: Enterprise SliceLast i zapytanie działa z niezadowalającą wydajnością, zaleca się, aby wywołać wirtualną tabelę w oddzielnym zapytaniu, zapisując wyniki w tabeli tymczasowej.

Rozwiązywanie problemu zawieszenia z PostgreSQL.

Podczas wykonywania pewnych rutynowych operacji (zamykanie miesiąca, obliczanie kosztów itp.), W których wykorzystywane są złożone zapytania z dużą liczbą dużych tabel, możliwe jest znaczne wydłużenie czasu wykonywania operacji. Zasadniczo problemy te są związane z działaniem optymalizatora PostgreSQL i brakiem aktualnych statystyk dotyczących tabel uczestniczących w zapytaniu.

Rozwiązania problemu:

• Zwiększ liczbę rekordów wyświetlanych podczas zbierania statystyk dotyczących tabel. Większe wartości mogą zwiększyć czas wykonania polecenia ANALYZE, ale poprawią konstrukcję planu kwerend:
  • Plik postgresql.conf to default_statistics_target = 1000 -10000.
• Wyłączenie optymalizatora do używania NESTED LOOP podczas wybierania planu wykonania zapytania w konfiguracji PostgreSQL:
  • Plik postgresql.conf to enable_nestloop = off.
  • Negatywnym skutkiem tej metody jest możliwe spowolnienie niektórych zapytań, ponieważ będą one wykorzystywać inne, droższe metody połączenia (HASH JOIN).
• Wyłączenie optymalizatora możliwość zmiany kolejności połączeń tabel w zapytaniu:
  • Plik postgresql.conf to join_collapse_limit = 1.
  • Tej metody należy użyć, jeśli masz pewność kolejności połączeń tabel w kwerendzie problemu.
• Zmiana ustawień optymalizatora:
  • Plik Postgresql.conf:
    • seq_page_cost = 0.1
    • random_page_cost = 0.4
    • cpu_operator_cost = 0.00025
• Użycie PostgreSQL w wersji 9.1.2-1.1.C i wyższej, w której gromadzenie statystyk jest niezależne od AUTOVACUUM, na podstawie informacji o zmianach danych w tabeli. Domyślnie zbieranie statystyk jest włączone tylko dla tabel tymczasowych iw wielu sytuacjach jest to wystarczające. Jeśli masz problemy z wydajnością podczas rutynowych operacji, możesz włączyć gromadzenie statystyk dla wszystkich lub pojedynczych tabel problemów, zmieniając parametr konfiguracyjny PostgreSQL (plik postgresql.conf ) online_analyze.table_type = "temporary" na online_analyze.table_type = "all".

Po zmianie tych parametrów należy ocenić możliwy wpływ tych zmian na działanie systemu i wybrać najbardziej akceptowalną opcję dla swoich zadań.

Posłowie.

Do wszystkich powyższych opcji możesz również dodać:

• Musisz użyć zarządzane zamki przy opracowywaniu rozwiązania aplikacyjnego. Jeśli masz typową konfigurację, to jeśli to możliwe, powinna ona zostać przeniesiona do kontrolowanych zamków.
• Zalecenie implementacji buforowania PostgreSQL SSD . Możesz to zrobić za pomocą Flashcache lub Bcache . Omówię organizację systemu buforowania bardziej szczegółowo w innym artykule.
• Dość wygodnie usługa internetowa dla początkowej konfiguracji PostgreSQL. Jego zalecane ja towarzysz Wasilij P. Melnik. Chociaż interfejs jest w języku angielskim, jest prosty i intuicyjny. Myślę, że każdy może sobie z tym poradzić.

Artykuł zaktualizowany 8 października 2016 r. Dodano testy porównawcze