Прискорення роботи 1С з postgresql і діагностика проблем продуктивності

  1. Вступ
  2. Паралельне виконання запитів на декількох ядрах в postgresql
  3. Логування sql запитів в postgresql
  4. Аналіз запитів postgresql за допомогою pgFouine
  5. висновок
  6. Онлайн курс "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?
Допомогла стаття?