1. Wprowadzenie
2. Równoległe wykonywanie zapytań na wielu jądrach w postgresql
3. Rejestrowanie zapytań sql w postgresql
4. Analiza żądań postgresql za pomocą pgFouine
5. Wniosek
6. Kurs online „Data Engineer”

Jakiś czas temu uruchomiłem pracę przedsiębiorstwa 1C z bazą danych postgresql. Podczas testowania natrafiłem na problem powolnego przetwarzania niektórych zapytań. Chcę podzielić się użytecznymi informacjami, które pozwolą uporządkować takie sytuacje i spróbować przyspieszyć pracę i pozbyć się wąskich gardeł w bazie danych.

Kurs online Data Engineer jest przeznaczony dla programistów, administratorów DBMS i każdego, kto chce doskonalić swoje umiejętności, uczyć się nowych narzędzi i angażować w ciekawe zadania z zakresu pracy z dużymi danymi. Kurs nie jest dla początkujących - musisz przejść.

Wprowadzenie

Serwer postgresql jest skonfigurowany tak jak w poprzednim artykule - Zainstaluj i skonfiguruj postgresql na debian 8, aby działał z 1C . Podano tam główne punkty przyspieszenia pracy bazy. Znacznie zwiększają wydajność w porównaniu z ustawieniami domyślnymi. W większości przypadków wystarczy. Jeśli nie, to nie jesteś już typowym przypadkiem i musisz zrozumieć bardziej szczegółowo.

Problem, który napotkałem, polega na szczególnej pracy postgresql i braku optymalizacji 1C do pracy z tą bazą danych. Baza danych postgresql, w przeciwieństwie do mssql, nie wie, jak zrównoważyć wykonanie pojedynczego zapytania na kilku rdzeniach procesora. Nawet jeśli masz bardzo wydajny serwer z dużą liczbą rdzeni, możesz dostać się do sytuacji, w której duże zapytanie spowolni bardzo, ładując tylko jeden rdzeń. Pozostała moc procesora będzie w tym czasie bezczynna. Zwiększenie zasobów serwera nie pomoże przyspieszyć bazy danych. Ona zawsze potknie się na tę prośbę.

Równoległe wykonywanie zapytań na wielu jądrach w postgresql

Użyłem postgresql w wersji 9.6. Jeśli wierzysz w wiadomości - http://www.opennet.ru/opennews/art.shtml?num=43313 dodaje obsługę równoległości zapytań. Zacząłem próbować ćwiczyć tę równoległość. Informacje w Internecie, ku mojemu żalowi, nie tak bardzo. Wydaje się, że jest to popularny problem, gdzie widziałem wiele pytań na ten temat. Na przykład tutaj omawiają temat używania kilku rdzeni procesorów do spełnienia żądania - http://www.sql.ru/forum/1002408/zadeystvovanie-neskolkih-processorov .

Najpopularniejszymi zaleceniami są zmiany żądań i logiki aplikacji z bazy danych, aby nie wpaść w sytuację, w której występuje jedno duże żądanie, którego nie można podzielić i przetwarzać równolegle na kilku rdzeniach. Przykładem takiego podejścia jest Habré - https://habrahabr.ru/post/76309/ . Nie mam odpowiedniej wiedzy o sql, a tym bardziej 1C, aby coś zmienić na poziomie aplikacji. Zaczął zajmować się funkcjami postgresql.

Istnieje kilka parametrów odpowiedzialnych za równoległe przetwarzanie zapytań:

max_worker_processes = 16 max_parallel_workers_per_gather = 8 min_parallel_relation_size = 0 parallel_tuple_cost = 0.05 parallel_setup_cost = 1000

Muszą być wybrane dla ich liczby rdzeni. W tym przypadku ustawienia są prezentowane dla 16 systemów jądrowych. Następnie musisz zastosować skrypt oparty na 1C, który pozwoli optymalizatorowi postgres na wykorzystanie przetwarzania równoległego tych zapytań 1C, w których uczestniczą pola tekstowe (większość zapytań) poprzez zmianę definicji funkcji. Tekst skryptu jest bardzo długi, więc nie przytaczam go tutaj, aby nie obciążać artykułu. Pobieramy go ze strony - postgre.sql .

Żądanie musi zostać złożone w bazie danych używanej przez 1C. Aby to zrobić, możesz użyć programu pgAdmin lub bezpośrednio połączyć się z bazą danych za pośrednictwem konsoli serwera. Opiszę szczegółowo drugą opcję.

Połącz się z serwerem za pomocą postgresql przez ssh. Przechodzimy pod postgres użytkownika:

# su postgres

Przejdź do katalogu domowego użytkownika:

# cd

Utwórz plik z żądaniem, które wykonamy. W takim przypadku można natychmiast skopiować wcześniej pobrany plik lub utworzyć go ręcznie i skopiować do niego tekst żądania.

# dotknij postgre.sql

Jeśli skopiujesz gotowy plik, upewnij się, że użytkownik postgres ma dostęp do tego pliku.

Połącz się z serwerem bazy danych:

# psql -U postgres

Połącz się z żądaną bazą danych:

podłącz bazę1c

Wykonaj zapytanie sql z pliku:

postgre.sql

Wszystko można sprawdzić. Musieliśmy zwiększyć szybkość żądań 1C w bazie danych postgresql, umożliwiając równoległe przetwarzanie niektórych żądań. W moim przypadku nie przyniosło to żadnych korzyści w przypadku problematycznych zapytań. Sama baza jako całość działała dobrze, ale natknęła się na pewne pytania. Rozumiemy dalej.

Rejestrowanie zapytań sql w postgresql

Aby dowiedzieć się, co nam przeszkadza, musimy sami przyjrzeć się żądaniom. Aby to zrobić, musimy włączyć rejestrowanie zapytań do bazy danych. Żądania będą bardzo duże, nie potrzebujemy wszystkiego. Ograniczamy rejestrowanie tylko tych żądań, które są wykonywane dłużej niż 3 sekundy. Aby to zrobić, narysuj następujące parametry w konfiguracji bazy danych:

log_destination = 'syslog' syslog_facility = 'LOCAL0' syslog_ident = 'postgres' log_min_duration_statement = 3000 # 3000 ms = 3 sekundy log_duration = off log_statement = 'none'

I dodajemy opis kanału dla logów LOCAL0 do konfiguracji rsyslog w pliku /etc/rsyslog.conf do samego końca:

LOCAL0. * - / var / log / postgresql / sql.log

Jeśli pozostawisz ustawienia rsyslog w tym formularzu, dziennik żądań zostanie zapisany nie tylko w pliku /var/log/postgresql/sql.log , ale także w wiadomościach i syslog . Nie lubię spamować w logach systemowych, więc wyłącz dziennik logowania SQL. Dodajemy wartość LOCAL0.none do opisu tych plików dziennika. Powinno to wyglądać tak:

*. *; auth, authpriv.none; LOCAL0.none - / var / log / syslog *. = Info; *. = Uwaga; *. = Ostrzegaj, authpriv.none; cron, daemon.none; mail, news.none; LOCAL0.none - / var / log / messages

Uruchom ponownie postgresql i rsyslog:

# systemctl restart postgresql # systemctl restart rsyslog

Idź do bazy 1C i zadzwoń do swojej prośby, która zwalnia. Jeśli jego wykonanie zajmie więcej niż 3 sekundy, zobaczysz tekst zapytania w pliku dziennika. Możesz użyć bazy nieco dłużej, aby skompilować listę zapytań do analizy. Żąda 1C tak dużego, że nawet kopiuje je z dziennika i radzi sobie z trudnym zadaniem. W tym celu użyjemy specjalnego programu.

Włączenie rejestrowania zapytań spowalnia system. Zalecam diagnozowanie po godzinach lub w bazie testowej i na serwerze, jeśli to możliwe. Aby zastosować ustawienia bazy danych, musisz ją ponownie uruchomić. Może to powodować problemy, jeśli ktoś pracuje z innymi bazami danych serwera. Zanotuj to.

Analiza żądań postgresql za pomocą pgFouine

Zainstaluj pgFouine na debian:

# apt-get install pgfouine

To jest stary program, ale dla naszych celów to się skończy. Jest bardzo łatwy w użyciu. Nie wchodziłem w szczegóły ustawień i nie sprawdzałem możliwych parametrów. Wystarczyło mi to zrobić:

# pgfouine -file /var/log/postgresql/sql.log> /root/report.html

Bierzemy plik report.html na nasz komputer i otwieramy go w przeglądarce. Zrobiłem coś takiego:

Prośba jest imponująca :) Nic dziwnego, że zwalnia! Powiedzieć, że byłem zaskoczony, to nic nie mówić. Patrząc na te żądania, zdałem sobie sprawę, że nie ma optymalizacji w 1C do pracy z postgresql. Mimo że jestem bardzo słabo zaznajomiony z sql, jestem zaznajomiony powierzchownie ze składnią i sam skomponowałem tylko bardzo proste zapytania. Ale nawet ja widzę, że problem hamulców polega na tym, że ta prośba jest po prostu brzydka. Parser żądań wybrał znaki kosza. W moim przypadku są to znaki # 011, są one obecne w dzienniku sql.log. Nie wiem skąd pochodzą, ale aby uzyskać czystą prośbę, muszą zostać usunięte. Skopiowałem tekst zapytania do edytora tekstu i zastąpiłem znaki # 011 spacją. Rezultatem było zapytanie poprawne składniowo. W moim przypadku wygląda to tak:

WYBIERZ PRZYPADEK KIEDY (T1._Folder = FAŁSZ) WTEDY PRZYPADEK KIEDY T1._Marked = PRAWDA THEN 13 ELSE 12 END ELSE ((-1 + PRZYPADEK KIEDY T1._Marked = TRUE THEN 1 ELSE 0 END) + PRZYPADEK KIEDY (T1._Fld607 = FAŁSZ) TO 1 ELSE 3 END) END, T1._IDRRef, 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 ':: bytea, T1._ParentIDRRef, T1._Description, PRZYPADEK KIEDY T2.Fld4011_TYPE JEST NULL TO PRZYPADEK, GDY T1._Fld591RRef NIE JEST NULL TO ”010' :: bytea END ELSE T2.Fld4011_TYPE KONIEC, PRZYPADEK KIEDY T2.Fld4011_TYPE JEST NULL TO PRZYPADEK KIEDY T1._Fld591RRef .Fld4011_TYPE JEST NULL TO T1._Fld591RRef ELSE T2.Fld4011_RRRef END, T1._Fld595RRef, T1._Fld601RRef, T1._Fld606RRef, T1._Fld607, T1._Fld608, T1._Fld4737RR aktor strony i zastosuję tfr. (CAST ((T2.Fld4009_ * 1) JAKO NUMERYCZNE (22, 8)) / 1 JAKO NUMERYCZNE (22, 8))) JAKO NUMERYCZNE (15, 2)) JAKO NUMERYCZNE (15, 2)), 0), PRZYPADEK WHEN (T2.Fld4011_TYPE = '010' :: bytea AND T2.Fld4011_RTRef = '000 0 00 000 ':: bytea) TO (CAST (CAST (COALESCE (CAST (T6.Fld4265 Balance_ AS NUMERIC (27, 3)), 0) JAKO NUMERYCZNE (35, 8)) / CASE WHEN T2.Fld4011_TYPE =' 010 ':: bytea AND T2.Fld4011_RTRef =' 000 000 000 ':: bytea TO T10._Fld483 ELSE CAST (NULL AS NUMERIC) KONIEC JAKO NUMERYCZNE (35, 8))) ELSE COALESCE (CAST (T6.Fld4265Balance_ AS NUMERIC (27, 3)), 0) END, CASE WHEN (COALESCE (CAST (T8.Fld4212Balance_ AS NUMERIC (27, 3)), 0) = 0) THEN 1 ELSE 0 END, CASE WHEN ( T2.Fld4011_TYPE = '010' :: bytea AND T2.Fld4011_RTRef = '000 000 000' :: bytea] TO (CAST (CAST ((COALESCE (CAST (T6.Fld4265Balance_ AS NUMERIC (27 , 3)), 0) - COALESCE (CAST (T8.Fld4212 Balance_ AS NUMERIC (27, 3)), 0)) JAKO NUMERYCZNE (36, 8)) / CASE WHEN T2.Fld4011_TYPE = '010' :: bytea AND T2.Fld4011_RTRef = '000 000 000' :: bytea TO T10._Fld483 ELSE CAST (NULL AS NUMERIC) KONIEC JAKO NUMERYCZNE (36, 8))) ELSE (COALESCE (CAST (T6.Fld4265Balance_ AS NUMERYCZNE (27, 3)), 0) - WALUT (CAST (T8.Fld4212 Równowaga_ JAKO NUMERYCZNE (27, 3)), 0)) END, T1._Marked, PRZYPADEK KIEDY (T1._Folder = FAŁSZ) THE N PRAWDA wypadku FALSE koniec od _Reference44 T1 LEWA sprzężenia zewnętrznego (SELECT T5._Fld4007RRef CO Fld4007RRef, T5._Fld4011_TYPE AS Fld4011_TYPE, T5._Fld4011_RTRef AS Fld4011_RTRef, T5._Fld4011_RRRef AS Fld4011_RRRef, T5._Fld4009 AS Fld4009_ z (SELECT T4._Fld4006RRef CO Fld4006RRef , T4._Fld4007RRef AS, Fld4007RRef, T4._Fld4008RRef, AS Fld4008RRef, MAX (T4._Period) : 00 ':: TIMESTAMP AND (((T4._Fld4010 = TRUE AND (T4._Fld4006RRef =' 204 232 225 2473l 375 305J 023bNdY & s ':: bytea)) ORAZ (T4._Fld4008RRef = '000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 ':: bytea)))) GROUP BY Fld4008RRef = T5._Fld4008RRef AND T3.MAXPERIOD_ = T5._Period GDZIE (T5._Fld5554 = 0)) T2 ON (T1._IDRRef = T2.Fld4007RRef) LEWE ZEWNĘTRZNE ŁĄCZENIE (SELE CT T7._Fld4260RRef AS Fld4260RRef, SUM (T7._Fld4265) AS Fld4265Balance_ Z _AccumRgT4266 T7 GDZIE ((T7._Fld5554 = 0)) ORAZ (T7._Period = '3999-11-01 00:00:00' :: TIMESTAMP I ((T7._Fld4259RRef = '224 206 245 237 200 356j 370Kp 252IFC 324a' :: bytea)) AND (T7._Fld4265 <> 0) AND (T7._Fld4265 <> 0)) GROUP BY T7._Fld4260RRef HAVING (SUM (T7._Fld4265)) <> 0) T6 ON (T1._IDRRef = T6.Fld4260RRef) LEFT OUTER JOIN (SELECT T9._Fld4208RRef AS Fld4208RRef, S (T9._Fld4212) AS Fld4212Balance_ FROM _AccumRgT4232 T9 WHERE ((T9._Fld5554 = 0)) AND (T9._Period = '3999-11-01 00:00:00' :: TIMESTAMP AND ((((T9._Fld4205RRef = 224 206 245 237 200 356j 370Kp 252IFC 324a 'bytea ORAZ (T9._Fld4206_TYPE =' 010 ':: bytea AND T9). _Fld4206_RTRef = '000 000 000B' :: bytea)) ORAZ (T9._Fld4211RRef <> '000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 000 ':: bytea))) ORAZ (T9._Fld4212 <> 0) ORAZ (T9._Fld4212 <> 0)) GROUP BY T9._Fld4208RRef HAVING (SUM (T9._Fld4212)) <> 0) T8 ON (T1. _IDRRef = T8.Fld4208RRef) LEFT OUTER JOIN _Reference32 T10 ON (T2.Fld4011_TYPE = '010' :: bytea AND T2.Fld4011_RTRef = '000 000 000' :: bytete I T2.Fld4011_RRRef = Re ._IDRRef) AND (T10 ._Fld5554 = 0) WHERE ((T1._Fld5554 = 0)) ORAZ ((T1._Fld604RRef IN ('256 ") 314 021 {V} G 321 = 343U 243 367 344 „:: bytea”, 236 273 035 371, t 035kC {024b 273W 037 206 ”:: bytea)) ORAZ (T1._Folder) = PRAWDA I (T1._Fld14883 = FAŁSZ) ORAZ (T1._ParentIDRRef IN (WYBIERZ T11._REFFIELDRRef JAKO REFFIELDRRef OD tt9 T11))) ZAMÓWIENIE PRZEZ (T1._Opis), (T1._IDRRef ) LIMIT 25;

Następnie możesz zająć się swoimi prośbami, w zależności od wiedzy i możliwości. Nie wiedziałem, co robić dalej, aby rozwiązać mój problem. Próbowałem zbudować mapę zapytań za pomocą ANALIZY EXPLAIN , ale nie powiodło się. Zapytanie używa pewnego rodzaju tabeli tymczasowej, więc po prostu skopiuj i powtórz to nie zadziałało. Wystąpił błąd, że pewna tabela nie istnieje.

W tej chwili na forum z profilem otrzymałem poradę dotyczącą mojego problemu. Powiedziano mi, że sytuacja jest znana i dość typowa dla 1C. Konieczne jest poprawienie go po stronie samego 1C, zmiana kodu żądania próbki z wirtualnych tabel na zapytania z tabel tymczasowych, a następnie połączenie ich z główną tabelą. To zadanie dla programisty. W ogóle nie rozumiem 1C.

Wniosek

W tej chwili mój problem nie został rozwiązany, ale stało się jasne, w jakim kierunku się poruszać i co robić. Zasadniczo, początkowo, gdy zaangażowałem się w to zadanie, założyłem, że problem był po stronie 1C ze względu na złożone zapytanie i brak optymalizacji pracy 1C z postgresql. Zrozumiałem to, ponieważ z mssql takich hamulców nigdy nie widziałem na podstawach tego rozmiaru. W tym przypadku wolumen podstawowy wynosi tylko 10 GB, nie jest zbyt duży. 15 sekund na odłożenie prośby na takiej bazie danych może być tylko wtedy, gdy ta prośba jest straszna. W rzeczywistości wszystko poszło dobrze.

W procesie analizy sytuacja zyskała pewne doświadczenie, które próbował naprawić w tym artykule. Myślę, że będzie to przydatne w przyszłości, zarówno dla mnie, jak i dla innych użytkowników. W Internecie nie znalazłem dobrych artykułów na temat analizy wydajności postgres. Musiałem zebrać wszystkie okruchy w różnych artykułach, ale więcej na forach. Biorąc pod uwagę koszt licencji mssql, zastąpienie jej postgresql wygląda bardzo rozsądnie, więc temat jest istotny.

Będę wdzięczny za wszelkie uwagi i sugestie zawarte w komentarzach. Temat dla mnie jest nowy, ale użyteczny. Chciałbym zrozumieć postgres pracy.

Kurs online „Data Engineer”

Kurs online Data Engineer jest przeznaczony dla programistów, administratorów DBMS i każdego, kto chce doskonalić swoje umiejętności, uczyć się nowych narzędzi i angażować w ciekawe zadania z zakresu pracy z dużymi danymi. Kurs nie jest dla początkujących - musisz zdać test wstępny. Absolwenci kursu będą mogli:

• Wdrażaj, dostosowuj i optymalizuj narzędzia do przetwarzania danych
• dostosowywać zestawy danych do dalszej pracy i analiz;
• tworzyć usługi, które wykorzystują wyniki przetwarzania dużych ilości danych;
• odpowiedzialny za architekturę danych w firmie.

Sprawdź się podczas testu wstępnego i zobacz program, aby uzyskać więcej szczegółów.

Czy artykuł pomógł? Istnieje możliwość dziękuję autor