Зависает запрос 1с postgresql
Обновлено: 07.07.2024
SELECT
pg_database.datname AS Database,
pg_stat_statements.query AS Query,
pg_stat_statements.calls AS ExecutionCount,
pg_stat_statements.total_time ExecutionTime,
pg_stat_statements.shared_blks_read + pg_stat_statements.shared_blks_written AS Memory,
pg_stat_statements.local_blks_read + pg_stat_statements.local_blks_written AS IO,
pg_stat_statements.temp_blks_read + pg_stat_statements.temp_blks_written AS Temp
FROM
pg_stat_statements AS pg_stat_statements
INNER JOIN pg_database AS pg_database
ON pg_database.oid = pg_stat_statements.dbid
ORDER BY
ExecutionTime DESC
with s AS (SELECT
CASE WHEN sum(total_time) = 0 THEN 1 ELSE sum(total_time) END AS sum_time,
CASE WHEN sum(blk_read_time+blk_write_time) = 0 THEN 1 ELSE sum(blk_read_time+blk_write_time) END as sum_iotime,
CASE WHEN sum(total_time-blk_read_time-blk_write_time) = 0 THEN 1 ELSE sum(total_time-blk_read_time-blk_write_time)
END as sum_cputime,
CASE WHEN sum(calls) = 0 THEN 1 ELSE sum(calls) END AS sum_calls,
CASE WHEN sum(rows) = 0 THEN 1 ELSE sum(rows) END as sum_rows
FROM pg_stat_statements
)
SELECT
'all_users@all_databases' as "user@database",
100 AS time_percent,
100 AS iotime_percent,
100 AS cputime_percent,
(sum_time/1000)*'1 second'::interval as total_time,
(sum_cputime/sum_calls)::numeric(20, 2) AS avg_cpu_time_ms,
(sum_iotime/sum_calls)::numeric(20, 2) AS avg_io_time_ms,
sum_calls as calls,
100 AS calls_percent,
sum_rows as rows,
100 as row_percent,
'all_queries' as query
FROM s
UNION ALL
SELECT
(select rolname from pg_roles where oid = p.userid) || '@' || (select datname from pg_database where oid
(100*total_time/(SELECT sum_time FROM s))::numeric(20, 2) AS time_percent,
(100*(blk_read_time+blk_write_time)/(SELECT sum_iotime FROM s))::numeric(20, 2) AS iotime_percent,
(100*(total_time-blk_read_time-blk_write_time)/(SELECT sum_cputime FROM s))::numeric(20, 2) AS cputime_percent,
(total_time/1000)*'1 second'::interval as total_time,
((total_time-blk_read_time-blk_write_time)/calls)::numeric(20, 2) AS avg_cpu_time_ms,
((blk_read_time+blk_write_time)/calls)::numeric(20, 2) AS avg_io_time_ms,
calls,
(100*calls/(SELECT sum_calls FROM s))::numeric(20, 2) AS calls_percent,
rows,
(100*rows/(SELECT sum_rows from s))::numeric(20, 2) AS row_percent,
query
FROM pg_stat_statements p
WHERE
(total_time-blk_read_time-blk_write_time)/(SELECT sum_cputime FROM s)>=0.005
UNION ALL
SELECT
'all_users@all_databases' as "user@database",
(100*sum(total_time)/(SELECT sum_time FROM s))::numeric(20, 2) AS time_percent,
(100*sum(blk_read_time+blk_write_time)/(SELECT sum_iotime FROM s))::numeric(20, 2) AS iotime_percent,
(100*sum(total_time-blk_read_time-blk_write_time)/(SELECT sum_cputime FROM s))::numeric(20, 2) AS cputime_percent,
(sum(total_time)/1000)*'1 second'::interval,
(sum(total_time-blk_read_time-blk_write_time)/sum(calls))::numeric(10, 3) AS avg_cpu_time_ms,
(sum(blk_read_time+blk_write_time)/sum(calls))::numeric(10, 3) AS avg_io_time_ms,
sum(calls),
(100*sum(calls)/(SELECT sum_calls FROM s))::numeric(20, 2) AS calls_percent,
sum(rows),
(100*sum(rows)/(SELECT sum_rows from s))::numeric(20, 2) AS row_percent,
'other' AS query
FROM pg_stat_statements p
WHERE
(total_time-blk_read_time-blk_write_time)/(SELECT sum_cputime FROM s)
Запись опубликована автором admin в рубрике postgresql, код, производительность. Добавьте в закладки постоянную ссылку.
postgresql настроен по всем канонам. Поле "Жилец" индексируется.
Разработчики конфигурации уже 2 неделю анализируют базу или говорят, что анализируют(
Помогите, у меня уже варианты закончились.
(1) А вот об этом я не подумал.
На сайте postgre рекомендуют 9.4.14 и 9.6.5.
Щас попробуем, спасибо за наводку.
(0) вообще в "типичных причинах неоптимальной работы запросов" на ИТС как раз написано про соединение с виртуальными таблицами.
Так что как вариант - поместить каждую виртуальную во временную, и лишь потом соединять.
(1) на 10-ую патч уже не нужен, насколько я знаю. там сделано через расширение.
(0) Перепишите запрос на пакет и ипите мозг.
> Разработчики конфигурации уже 2 неделю анализируют базу или говорят, что анализируют
Они-то как раз ипут вам мозг. Это очевидно.
(0) Для начала можно прост избавиться от ошибок в виде сравнения даты с NULL.
Типа КВП_СведенияОПроживающихНаДату.ДатаИзменения < КВП_СведенияОПроживающихНаДатуБезОтбора.ДатаИзменения.
Вообще вот это поле (ниже) - оно что? Каков его смысл? Что там должно быть, если условие в КОГДА не выполняется? Почему нет ИНАЧЕ?
+ к (9) Вообще авторы текста запроса из (0) в курсе что такое правое и левое соединение и чем они отличаются? В тексте явно перепутаны право и лево.(9) Да сам отчет в конкретном случае я переписал. Но проблема в том что в типовом решении они используют такой отбор проживающих везде где нужно получить таблицу проживающих, там не правильная логика еще и самого регистра. И в самом первом обращении к разработчикам я указывал конкретно на этот кривой запрос, а они еще чето анализируют. Просто переписывать пол конфы из-за "крутых" разработчиков.
Кстати запрос вывел информацию спустя 57 596 сек, в файловой базе выводит за 18 сек.
(13) включал выключал - без результата, делал индексацию, чистил мусор.
(9) Спасибо и за эту наводку
Кстати оптимизированный запрос, выводиться за 8 сек)))
А вот так за 6 сек.
что мешает использовать вместо ЕСТЬNULL(СОтбором.ЛицевойСчет.Адрес.Владелец, БезОтбора.ЛицевойСчет.Адрес.Владелец) КАК Здание ЕСТЬNULL(СОтбором.ЛицевойСчетАдресВладелец, БезОтбора.ЛицевойСчетАдресВладелец) КАК Здание .(18) ничего, здесь просто были заменены таблицы регистров на временные, итоговый запрос не менялся
(21) понимаю, я так и переделал, изначально это место сформировалось конструктором при замене таблицы регистра на временную
разработчикам вашим не показывайте, а то они будут к вам относиться как вы к ним
В итоге имеем время вывода 3 сек.
Всем спасибо!)
В итоге больше всего нравиться ответ разработчиков.
Выглядело это примерно так:
07.03-Я- У нас не формируется отчет, проблема в запросе вот в этом месте.
22.03-Я(В шоке)- а что вы делали до этого?
22.03-Р- до этого момента проблема анализировалась линией консультации. Как будет ответ мы вам сообщим.
(26) Купите корпоративную поддержку и к Вашим проблемам будут относиться уважительнее (С)
(24) Еще бы сравнить это оптимизированый код, с файловой и MS SQL.
(29) В этом, думаю, особого смысла нет. Результат сравнения больше будет зависеть не от конкретной СУБД, а от различных прочих условий (общий размер данных, размер данных попадающих в отбор и пр.).
В особенности если верить автору ветки в (12): "там не правильная логика еще и самого регистра". И судя по самому запросу, я склонен с ним согласиться.
(29) в среднем, во всех вариантах, время выполнения 3-5 сек.
(12) если это та конфа о которой я думаю, то там в регистре накопления УПЖКХ_Начисления - есть измерение Количество, я так и не разобрался зачем)
(32) да-да есть такое) использование нигде не встречается, может код в защищенных обработках)
(33) Ну пару раз при написании собственных квитанций, я натыкался на то что количество сворачивалось, а не суммировалось. И каждый раз с криком на весь отдел "Количество же измерение" задача решалась.
А так да, в старых релизах в зашифрованных модулях.
Еще хотел написать, если нужно использовать запрос к виртуальной таблице(пример "Срез последних") с пост условием 100 раз подумайте использовать ли такой механизм в postgre.
Столкнулся со следующей задачей "Получить действующие на дату начисления" регистра сведений, казалось бы что может быть проще.
Пишу запрос
Время выполнения запроса 870 сек. понятное дело что в отчете его особо не используешь.
Любые запросы в отчетах накладывают эксклюзивную блокировку на всю таблицу регистра. Это я определил, выполняя в момент формирования отчета в консоле управлением сервера запрос select * from pg_locks;
пока отчет выполняется там тысячи(!) записей о эксклюзивных блокировках поэтому делаю вывод что вся таблица стоит.
Из-за чего имею постоянный вылет:
Ошибка СУБД:
ERROR: Out of shared memory
HINT: You might need to increase max_locks_per_transaction
Вставки управляемых блокировок перед выполнением запроса ситуацию не меняют хотя в модулях проведения управляемые блокировки отрабатывают корректно. Почему так?
Да увеличение приведенных в ошибке параметров проблемму не решают, оно вроде и помогает времеменно но
при добавлении в базу очередной порции информации всё повторяется вновь.
Случаем режим разделения итогов регистров не включен?
(2) да включен для регистра бухгалтерии, но отчет не по нему, а по регистру накопления!
Дык этим то и отличается PostGre от MS SQL. Что он блин блокировку на всю таблицу накладывает.
(4) включен режим управляемых блокировок он как раз под постгрии делался
(5) нет не типовая
(8)разумеется дописаны я же это писал выше, более того к докам вопросов нет блокировки работают корретно что было подтверждено испытаниями. Единсственная проблема возникала с регистром бухгалтерии но там выкртились установкой разделения итогов опсле чего можно реально одновременно проводить документы с неконфликтными измерениями. Теперь грабли с отчетами валят он 1С изза блокировок хотя по логике запросы на чтения ну никак не должны накладывать эксклюзивные блокировки а они валятся в pg_locks пачками при этом в этот момент доки вообще не проводятся тобишь дело в отчете. Но он прост как пять пальцев запрос по таблице остатков и оборотов могу привести код если нужно. не знаю что там можно оптимизировать
(6) (1) дайте конфиг к серверу может у меня руки кривые вроде все рекомендации по настройке выполнил. сервант не слабый W2K3
(10) запрос в отчете без признака "ДЛЯ ИЗМЕНЕНИЯ" почему он должен чтото блокировать эксклюзивно.
(12) все таки рекомендую ознакомится с базой знаний, например http://kb.1c.ru/articleView.jsp?id=44 либо книгу "Профессиональная разработка в системе 1С:Предприятие"
ну вот, например, кусочек статьи
Если в структуре базы данных отсутствует индекс, удовлетворяющий всем перечисленным условиям, то запрос может отрабатывать неоптимально. В результате этого может увеличиться время выполнения запроса и количество блокировок, которые он устанавливает на уровне СУБД.
Вот что в логах:
Некоторое время назад я настраивал работу 1С предприятия с базой данных postgresql. Во время тестирования столкнулся с проблемой медленной работы некоторых запросов. Хочу поделиться полезной информацией, которая позволит разобраться в таких ситуациях и попытаться ускорить работу и избавиться от узких мест в базе.
Хочешь научиться автоматически разворачивать и поддерживать высоконагруженные проекты? Тогда рекомендую познакомиться с онлайн курсом " Infrastructure as a code." в OTUS. Актуально для системных администраторов и devops инженеров. Подробности по .Данная статья является частью единого цикла статьей про сервер Debian.
Введение
Сервер postgresql настроен по предыдущей статье - Установка и настройка postgresql на debian 8 для работы с 1С. Основные моменты по ускорению работы базы там приведены. Они существенно увеличивают производительность по сравнению с настройками по-умолчанию. В большинстве случаев этого бывает достаточно. Если нет - то у вас уже не типичный случай и надо разбираться более детально.
Проблема, с которой столкнулся я, кроется в особенности работы postgresql и отсутствии оптимизации 1С для работы с этой бд. База данных postgresql, в отличие от mssql, не умеет распараллеливать выполнение одного запроса не несколько ядер процессора. Даже если у вас очень высокопроизводительный сервер с большим числом ядер, вы можете попасть в ситуацию, когда какой-то тяжелый запрос будет очень сильно тормозить, нагружая только одно ядро. Остальные мощности процессора будут простаивать при этом. Увеличение ресурсов сервера никак не поможет вам ускорить работу базы. Она будет всегда спотыкаться на этом запросе.
Параллельное выполнение запросов на нескольких ядрах в postgresql
Есть несколько параметров, которые как раз отвечают за параллельную обработку запросов:
Их необходимо подбирать под свое количество ядер. В данном случае настройки представлены для 16-ти ядерной системы. Далее необходимо применить скрипт на базе 1С, который позволит оптимизатору постгреса использовать параллельную обработку тех запросов 1С где участвуют текстовые поля (большинство запросов), путём изменения определений функций. Текст скрипта очень длинный, поэтому не привожу его здесь, чтобы не нагружать статью. Качаем его с сайта - postgre.sql.
Запрос необходимо выполнить в базе, которую использует 1С. Для этого можно воспользоваться либо программой pgAdmin, либо напрямую подключиться к базе, через консоль сервера. Опишу второй вариант в подробностях.
Подключаемся к серверу с postgresql по ssh. Заходим под юзером postgres:
Переходим в домашний каталог пользователя:
Создаем файл с запросом, который будем выполнять. В данном случае можете сразу скопировать файл, который скачали ранее, либо создайте вручную и скопируйте в него текст запроса.
Если будете копировать готовый файл, убедитесь, что у пользователя postgres есть доступ к этому файлу.
Подключаемся к серверу бд:
Подключаемся к нужной базе данных:
Выполняем sql запрос из файла:
Все, можно идти проверять. Мы должны были увеличить быстродействие 1С запросов в базе postgresql, разрешив использовать параллельную обработку некоторых запросов. В моем случае это не дало никакого прироста по проблемным запросам. Сама база в целом работала нормально, но спотыкалась на определенных запросах. Разбираемся дальше.
Логирование sql запросов в postgresql
Для того, чтобы разобраться, что же конкретно у нас тормозит, надо посмотреть на сами запросы. Для этого нам нужно включить логирование запросов к базе данных. Запросов будет очень много, нам не нужны все подряд. Сделаем ограничение на логирование только тех запросов, которые выполняются дольше, чем 3 секунды. Для этого рисуем следующие параметры в конфиге БД:
И добавляем описание канала для логов LOCAL0 в конфиг rsyslog в файле /etc/rsyslog.conf, в самый конец:
Если оставить настройки rsyslog в таком виде, то лог запросов будет писаться не только в файл /var/log/postgresql/sql.log, но и в messages, и в syslog. Я не люблю спамить в системные логи, поэтому отключим запись sql логов туда. Добавляем в описание этих лог файлов значение LOCAL0.none. Должно получиться примерно так:
Перезапускаем postgresql и rsyslog:
Идем в базу 1С и вызываем свой запрос, который тормозит. Если его выполнение занимает больше, чем 3 секунды, вы увидите текст запроса в лог файле. Можете подольше попользоваться базой, чтобы собрать список запросов для анализа. Запросы 1С настолько громоздкие, что даже просто скопировать их из лога и обработать непростая задача. Воспользуемся для этого специальной программой.
Включение логирования запросов замедляет работу системы. Я рекомендую заниматься диагностикой либо в нерабочее время, либо на тестовой базе и сервере, если есть такая возможность. Для применения настроек базы данных, необходимо перезапускать ее. Это может доставить проблем, если с другими базами сервера кто-то работает. Примите это к сведению.Анализ запросов postgresql с помощью pgFouine
Устанавливаем pgFouine в debian:
Это старая программа, но для наших целей сойдет. Пользоваться ей очень просто. Я не вдавался в подробности настройки и не смотрел возможные параметры. Мне было достаточно сделать вот так:
Забираем файл report.html к себе на компьютер и открываем в браузере. У меня получилось примерно так:
Дальше вы можете разбираться со своими запросами, в зависимости от ваших знаний и возможностей. Я не знал, что делать дальше, для решения своей проблемы. Попытался построить карту запроса с помощью EXPLAIN ANALYZE, но не получилось. Запрос использует какие-то временные таблицы, так что просто скопировать и повторить его не получалось. Выходила ошибка, что какой-то таблицы не существует.
В настоящий момент я получил совет на профильном форуме по моей проблеме. Мне сказали, что ситуация известная и достаточно типичная для 1С. Исправлять ее нужно на стороне самой 1С, изменяя код запроса выборки из виртуальных таблиц на запросы из временных таблиц, соединяя их потом с основной. Это уже задача для программиста. Я в самой 1С не разбираюсь вообще.
Заключение
На текущий момент моя проблема не решена, но стало понятно, в каком направлении двигаться и что делать. В принципе, я изначально, когда стал заниматься этой задачей, предполагал, что проблема именно на стороне 1С из-за сложного запроса и отсутствии оптимизации работы 1С именно с postgresql. Я это понял, потому что с mssql таких тормозов никогда наблюдал на базах такого размера. В данном случае объем базы всего 10 гб, она не очень большая. 15 секунд лопатить запрос на такой базе можно только, если этот запрос ужасен. На деле все так и оказалось.
В процессе разбора ситуации приобрел определенный опыт, который постарался зафиксировать в этой статье. Думаю, он пригодится в будущем, как мне, так и другим пользователям. В интернете не нашел хороших статей по анализу производительности постгрес. Пришлось все собирать по крохам в разных статьях, но больше на форумах. С учетом стоимости лицензии mssql, замена ее на postgresql выглядит весьма обоснованной, так что тема актуальна.
Буду рад любым замечаниям и советам в комментариях. Тема для меня новая, но полезная. Хотелось бы разобраться в работе постгрес.
Напоминаю, что данная статья является частью единого цикла статьей про сервер Debian.
Читайте также: