Паскарэнне працы 1С з postgresql і дыягностыка праблем прадукцыйнасці
- ўвядзенне
- Паралельнае выкананне запытаў на некалькіх ядрах ў postgresql
- Лагаванне sql запытаў у postgresql
- Аналіз запытаў postgresql з дапамогай pgFouine
- заключэнне
- Онлайн курс "Data Engineer"
Некаторы час таму я наладжваў працу 1С прадпрыемства з базай дадзеных postgresql. Падчас тэставання сутыкнуўся з праблемай павольнай працы некаторых запытаў. Хачу падзяліцца карыснай інфармацыяй, якая дазволіць разабрацца ў такіх сітуацыях і паспрабаваць паскорыць працу і пазбавіцца ад вузкіх месцаў у базе.
Онлайн-курс Data Engineer - для распрацоўшчыкаў, адміністратараў СКБД і ўсіх, хто імкнецца павысіць прафесійны ўзровень, асвоіць новыя інструменты і займацца цікавымі задачамі ў сферы працы з вялікімі дадзенымі. Курс не для пачаткоўцаў - трэба прайсці.
ўвядзенне
Сервер postgresql настроены па папярэдняй артыкуле - Ўстаноўка і настройка postgresql на debian 8 для працы з 1С . Асноўныя моманты па паскарэнню работы базы там прыведзены. Яны істотна павялічваюць прадукцыйнасць у параўнанні з наладамі па-змаўчанні. У большасці выпадкаў гэтага бывае дастаткова. Калі не - то ў вас ужо не тыповы выпадак і трэба разбірацца больш дэталёва.
Праблема, з якой сутыкнуўся я, крыецца ў асаблівасці працы postgresql і адсутнасці аптымізацыі 1С для працы з гэтай БД. База дадзеных postgresql, у адрозненне ад mssql, не ўмее распараллеливать выкананне аднаго запыту не некалькі ядраў працэсара. Нават калі ў вас вельмі высокапрадукцыйны сервер з вялікім лікам ядраў, вы можаце патрапіць у сітуацыю, калі нейкі цяжкі запыт будзе вельмі моцна тармазіць, нагружаючы толькі адно ядро. Астатнія магутнасці працэсара будуць прастойваць пры гэтым. Павелічэнне рэсурсаў сервера ніяк не дапаможа вам паскорыць працу базы. Яна будзе заўсёды спатыкацца на гэтым запыце.
Паралельнае выкананне запытаў на некалькіх ядрах ў postgresql
Я выкарыстаў версію postgresql 9.6. Калі верыць навіны - http://www.opennet.ru/opennews/art.shtml?num=43313 у ёй дададзеная падтрымка распаралельвання запытаў. Я стаў спрабаваць на практыцы гэта распаралельванне. Інфармацыі ў інтэрнэце, да майго жаль, не так шмат. Накшталт праблема папулярная, шмат дзе бачыў пытанняў на гэтую тэму. Напрыклад, вось тут абмяркоўваюць тэму выкарыстання некалькіх ядраў працэсара для выканання запыту - http://www.sql.ru/forum/1002408/zadeystvovanie-neskolkih-processorov .
Найбольш папулярныя рэкамендацыі, гэта змяніць запыты і логіку працы прыкладання з БД, каб не трапляць у сітуацыю, калі ўзнікае адзін вялікі запыт, які немагчыма разбіць і апрацаваць паралельна на некалькіх ядрах. Прыклад такога падыходу ёсць на Хабрэ - https://habrahabr.ru/post/76309/ . У мяне няма ні належных ведаў sql, ні тым больш 1С, каб на ўзроўні прыкладання нешта мяняць. Стаў разбірацца з магчымасцямі postgresql.
Ёсць некалькі параметраў, якія як раз адказваюць за паралельную апрацоўку запытаў:
max_worker_processes = 16 max_parallel_workers_per_gather = 8 min_parallel_relation_size = 0 parallel_tuple_cost = 0.05 parallel_setup_cost = 1000
Іх неабходна падбіраць пад сваё колькасць ядраў. У дадзеным выпадку налады прадстаўлены для 16-ці ядзернай сістэмы. Далей неабходна прымяніць скрыпт на базе 1С, які дазволіць аптымізатар постгреса выкарыстоўваць паралельную апрацоўку тых запытаў 1С дзе ўдзельнічаюць тэкставыя палі (большасць запытаў), шляхам змены азначэнняў функцый. Тэкст скрыпту вельмі доўгі, таму не прыводжу яго тут, каб не нагружаць артыкул. Качаем яго з сайта - postgre.sql .
Запыт неабходна выканаць у базе, якую выкарыстоўвае 1С. Для гэтага можна скарыстацца альбо праграмай pgAdmin, альбо наўпрост падлучыцца да базы, праз кансоль сервера. Апішу другі варыянт у падрабязнасцях.
Падключаемся да сервера з postgresql па ssh. Заходзім пад юзарам postgres:
# Su postgres
Пераходзім у хатні каталог карыстальніка:
# cd
Ствараем файл з запытам, які будзем выконваць. У дадзеным выпадку можаце адразу скапіяваць файл, які спампавалі раней, альбо стварыце ўручную і скапіруйце ў яго тэкст запыту.
# Touch postgre.sql
Калі будзеце капіяваць гатовы файл, пераканайцеся, што ў карыстальніка postgres ёсць доступ да гэтага файла.
Падключаемся да сервера БД:
# Psql -U postgres
Падключаемся да патрэбнай базе даных:
\ Connect base1c
Выконваем sql запыт з файла:
\ I postgre.sql
Усе, можна ісці правяраць. Мы павінны былі павялічыць хуткадзейнасць 1С запытаў у базе postgresql, дазволіўшы выкарыстоўваць паралельную апрацоўку некаторых запытаў. У маім выпадку гэта не дало ніякага прыросту па праблемных запытам. Сама база збольшага працавала нармальна, але спатыкалася на пэўных запытах. Разбіраемся далей.
Лагаванне sql запытаў у postgresql
Для таго, каб разабрацца, што ж канкрэтна ў нас тармозіць, трэба паглядзець на самі запыты. Для гэтага нам трэба ўключыць лагаванне запытаў да базе даных. Запытаў будзе вельмі шмат, нам не патрэбныя ўсе запар. Зробім абмежаванне на лагаванне толькі тых запытаў, якія выконваюцца даўжэй за 3 секунды. Для гэтага малюем наступныя параметры ў конфігу БД:
log_destination = 'syslog' syslog_facility = 'LOCAL0' syslog_ident = 'postgres' log_min_duration_statement = 3000 # 3000 мс = 3 секунды log_duration = off log_statement = 'none'
І дадаем апісанне канала для логаваў LOCAL0 ў конфіг rsyslog ў файле /etc/rsyslog.conf, у самы канец:
LOCAL0. * - / var / log / postgresql / sql.log
Калі пакінуць налады rsyslog ў такім выглядзе, то лог запытаў будзе пісацца не толькі ў файл /var/log/postgresql/sql.log, але і ў messages, і ў syslog. Я не люблю спамілі ў сістэмныя логі, таму адключым запіс sql логаваў туды. Дадаем у апісанне гэтых лог файлаў значэнне LOCAL0.none. Павінна атрымацца прыкладна так:
*. *; Auth, authpriv.none; LOCAL0.none - / var / log / syslog *. = Info; *. = Notice; *. = Warn; \ auth, authpriv.none; \ cron, daemon.none; \ mail, news.none; \ LOCAL0.none - / var / log / messages
Перазапускаем postgresql і rsyslog:
# Systemctl restart postgresql # systemctl restart rsyslog
Ідзем у базу 1С і выкліканы свой запыт, які тармозіць. Калі яго выкананне займае больш, чым 3 секунды, вы ўбачыце тэкст запыту ў лог файле. Можаце даўжэй пакарыстацца базай, каб сабраць спіс запытаў для аналізу. Запыты 1С настолькі грувасткія, што нават проста скапіяваць іх з лога і апрацаваць няпростая задача. Скарыстаемся для гэтага спецыяльнай праграмай.
Ўключэнне лагаванне запытаў запавольвае працу сістэмы. Я рэкамендую займацца дыягностыкай альбо ў непрацоўны час, альбо на тэставай базе і сэрвэры, калі ёсць такая магчымасць. Для ўжывання налад базы дадзеных, неабходна перазапускаць яе. Гэта можа даставіць праблем, калі з іншымі базамі сервера хтосьці працуе. Прыміце гэта да ведама.
Аналіз запытаў postgresql з дапамогай pgFouine
Усталёўваем pgFouine ў debian:
# Apt-get install pgfouine
Гэта старая праграма, але для нашых мэтаў сыдзе. Карыстацца ёй вельмі проста. Я не уразаўся ў падрабязнасці налады і не глядзеў магчымыя параметры. Мне было дастаткова зрабіць вось так:
# Pgfouine -file /var/log/postgresql/sql.log> /root/report.html
Забіраем файл report.html да сабе на кампутар і адкрываем ў браўзэры. У мяне атрымалася прыкладна так:
Запыт ўражвае :) Не дзіўна, што ён тармозіць! Сказаць, што я быў здзіўлены, гэта нічога не сказаць. Гледзячы на гэтыя запыты, я разумеў, што ніякай аптымізацыі ў 1С для працы з postgresql няма. Хоць я вельмі дрэнна разбіраюся ў sql, знакам павярхоўна з сінтаксісам, і сам складаў толькі вельмі простыя запыты. Але нават я бачу, што праблема тармазоў у тым, што гэты запыт проста пачварна велізарны. Парсер запыту нахапаў ў код смеццевых знакаў. У маім выпадку гэта знакі # 011, яны прысутнічаю ў логу sql.log. Я не ведаю, адкуль яны там бяруцца, але каб атрымаць чысты запыт, іх трэба прыбраць. Я скапіяваў тэкст запыту ў тэкставы рэдактар і зрабіў замену сімвалаў # 011 на прабел. У выніку атрымаўся сінтаксічна карэктны запыт. У маім выпадку ён выглядае такім чынам:
SELECT CASE WHEN (T1._Folder = FALSE) THEN CASE WHEN T1._Marked = TRUE THEN 13 ELSE 12 END ELSE ((-1 + CASE WHEN T1._Marked = TRUE THEN 1 ELSE 0 END) + CASE WHEN (T1._Fld607 = FALSE) THEN 1 ELSE 3 END) END, T1._IDRRef, '\\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 ':: bytea, T1._ParentIDRRef, T1._Description, CASE WHEN T2.Fld4011_TYPE IS NULL THEN CASE WHEN T1._Fld591RRef IS NOT NULL THEN' \\ 010 ':: bytea END ELSE T2.Fld4011_TYPE END, CASE WHEN T2.Fld4011_TYPE IS NULL THEN CASE WHEN T1._Fld591RRef IS NOT NULL THEN '\\ 000 \\ 000 \\ 000%' :: bytea END ELSE T2.Fld4011_RTRef END, CASE WHEN T2 .Fld4011_TYPE IS NULL THEN T1._Fld591RRef ELSE T2.Fld4011_RRRef END, T1._Fld595RRef, T1._Fld601RRef, T1._Fld606RRef, T1._Fld607, T1._Fld608, T1._Fld4737RRef, T1._Fld610, COALESCE (CAST (CAST ((CAST (CAST ((T2.Fld4009_ * 1) AS NUMERIC (22, 8)) / 1 AS NUMERIC (22, 8))) AS NUMERIC (15, 2)) AS NUMERIC (15, 2)), 0), CASE WHEN (T2.Fld4011_TYPE = '\\ 010' :: bytea AND T2.Fld4011_RTRef = '\\ 000 \\ 0 00 \\ 000 ':: bytea) THEN (CAST (CAST (COALESCE (CAST (T6.Fld4265Balance_ AS NUMERIC (27, 3)), 0) AS NUMERIC (35, 8)) / CASE WHEN T2.Fld4011_TYPE =' \ \ 010 ':: bytea AND T2.Fld4011_RTRef =' \\ 000 \\ 000 \\ 000 ':: bytea THEN T10._Fld483 ELSE CAST (NULL AS NUMERIC) END AS NUMERIC (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) THEN (CAST (CAST ((COALESCE (CAST (T6.Fld4265Balance_ AS NUMERIC (27 , 3)), 0) - COALESCE (CAST (T8.Fld4212Balance_ AS NUMERIC (27, 3)), 0)) AS NUMERIC (36, 8)) / CASE WHEN T2.Fld4011_TYPE = '\\ 010' :: bytea AND T2.Fld4011_RTRef = '\\ 000 \\ 000 \\ 000' :: bytea THEN T10._Fld483 ELSE CAST (NULL AS NUMERIC) END AS NUMERIC (36, 8))) ELSE (COALESCE (CAST (T6.Fld4265Balance_ AS NUMERIC (27, 3)), 0) - COALESCE (CAST (T8.Fld4212Balance_ AS NUMERIC (27, 3)), 0)) END, T1._Marked, CASE WHEN (T1._Folder = FALSE) THE N TRUE ELSE FALSE END FROM _Reference44 T1 LEFT OUTER JOIN (SELECT T5._Fld4007RRef AS Fld4007RRef, T5._Fld4011_TYPE AS Fld4011_TYPE, T5._Fld4011_RTRef AS Fld4011_RTRef, T5._Fld4011_RRRef AS Fld4011_RRRef, T5._Fld4009 AS Fld4009_ FROM (SELECT T4._Fld4006RRef AS Fld4006RRef , T4._Fld4007RRef AS Fld4007RRef, T4._Fld4008RRef AS Fld4008RRef, MAX (T4._Period) AS MAXPERIOD_ FROM _InfoRg4005 T4 WHERE ((T4._Fld5554 = 0)) AND (T4._Period <= '2017/01/27 00:00 : 00 ':: TIMESTAMP AND (((T4._Fld4010 = TRUE AND (T4._Fld4006RRef =' \\ 204 \\ 232 \\ 225 \\ 2473l \\ 375 \\ 305J \\ 023bNdY & s ':: bytea)) AND (T4._Fld4008RRef = '\\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 ':: bytea)))) GROUP BY T4._Fld4006RRef, T4._Fld4007RRef, T4._Fld4008RRef) T3 INNER JOIN _InfoRg4005 T5 ON T3.Fld4006RRef = T5._Fld4006RRef AND T3.Fld4007RRef = T5._Fld4007RRef AND T3. Fld4008RRef = T5._Fld4008RRef AND T3.MAXPERIOD_ = T5._Period WHERE (T5._Fld5554 = 0)) T2 ON (T1._IDRRef = T2.Fld4007RRef) LEFT OUTER JOIN (SELE CT T7._Fld4260RRef AS Fld4260RRef, SUM (T7._Fld4265) AS Fld4265Balance_ FROM _AccumRgT4266 T7 WHERE ((T7._Fld5554 = 0)) AND (T7._Period = '3999-11-01 00:00:00' :: TIMESTAMP AND ((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, SUM (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) AND (T9._Fld4206_TYPE = '\\ 010' :: bytea AND T9. _Fld4206_RTRef = '\\ 000 \\ 000 \\ 000B' :: bytea)) AND (T9._Fld4211RRef <> '\\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 \\ 000 ':: bytea))) AND (T9._Fld4212 <> 0) AND (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' :: bytea AND T2.Fld4011_RRRef = T10 ._IDRRef) AND (T10._Fld5554 = 0) WHERE ((T1._Fld5554 = 0)) AND ((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)) AND (T1._Folder) = TRUE AND (T1._Fld14883 = FALSE) AND (T1._ParentIDRRef IN (SELECT T11._REFFIELDRRef AS REFFIELDRRef FROM tt9 T11))) ORDER BY (T1._Description), (T1._IDRRef ) LIMIT 25;
Далей вы можаце разбірацца са сваімі запытамі, у залежнасці ад вашых ведаў і магчымасцяў. Я не ведаў, што рабіць далей, для вырашэння сваёй праблемы. Паспрабаваў пабудаваць карту запыту з дапамогай EXPLAIN ANALYZE, але не атрымалася. Запыт выкарыстоўвае нейкія часовыя табліцы, так што проста скапіяваць і паўтарыць яго не атрымлівалася. Выходзіла памылка, што нейкі табліцы не існуе.
У сапраўдны момант я атрымаў савет на профільным форуме па маёй праблеме. Мне сказалі, што сітуацыя вядомая і досыць тыповая для 1С. Выпраўляць яе трэба на боку самому 1С, змяняючы код запыту выбаркі з віртуальных табліц на запыты з часовых табліц, злучаючы іх потым з асноўнай. Гэта ўжо задача для праграміста. Я ў самым 1С не разбіраюся наогул.
заключэнне
На бягучы момант мая праблема не вырашана, але стала зразумела, у якім кірунку рухацца і што рабіць. У прынцыпе, я першапачаткова, калі стаў займацца гэтай задачай, меркаваў, што праблема менавіта на баку 1С з-за складанага запыту і адсутнасці аптымізацыі працы 1С менавіта з postgresql. Я гэта зразумеў, таму што з mssql такіх тармазоў ніколі назіраў на базах такога памеру. У дадзеным выпадку аб'ём базы ўсяго 10 гб, яна не вельмі вялікая. 15 секунд рыдлёўкі запыт на такой базе можна толькі, калі гэты запыт жудасны. На справе ўсё так яно і было.
У працэсе разбору сітуацыі набыў пэўны вопыт, які паспрабаваў зафіксаваць у гэтым артыкуле. Думаю, ён спатрэбіцца ў будучыні, як мне, так і іншым карыстальнікам. У інтэрнэце не знайшоў добрых артыкулаў па аналізу прадукцыйнасці постгрес. Прыйшлося ўсё збіраць па драбках ў розных артыкулах, але больш на форумах. З улікам кошту ліцэнзіі mssql, замена яе на postgresql выглядае вельмі абгрунтаванай, так што тэма актуальная.
Буду рады любым заўвагам і парадаў у каментарах. Тэма для мяне новая, але карысная. Хацелася б разабрацца ў працы постгрес.
Онлайн курс "Data Engineer"
Онлайн-курс Data Engineer - для распрацоўшчыкаў, адміністратараў СКБД і ўсіх, хто імкнецца павысіць прафесійны ўзровень, асвоіць новыя інструменты і займацца цікавымі задачамі ў сферы працы з вялікімі дадзенымі. Курс не для пачаткоўцаў - трэба прайсці ўступны тэст. Выпускнікі курса змогуць:
- разгортваць, наладжваць і аптымізаваць інструменты апрацоўкі даных;
- адаптаваць датасеты для далейшай працы і аналітыкі;
- ствараць сэрвісы, якія выкарыстоўваюць вынікі апрацоўкі вялікіх аб'ёмаў дадзеных;
- адказваць за архітэктуру дадзеных у кампаніі.
Праверце сябе на ўступным цесцю і глядзіце праграму дэталёва па.
Дапамагла артыкул? ёсць магчымасць аддзячыць аўтара
Shtml?Дапамагла артыкул?