Ускорение работы 1с с postgresql и диагностика проблем производительности

Инструмент мониторинга производительности PostgreSQL

Answers

Оптимизация базы данных обычно представляет собой комбинацию из двух вещей

Уменьшить количество запросов к базе данных
Уменьшите объем данных, на которые нужно смотреть, чтобы отвечать на запросы

Сокращение количества запросов обычно выполняется путем кэширования энергонезависимых / менее важных данных (например, «Какие пользователи подключены к сети» или «Какие последние сообщения этого пользователя?») Внутри приложения (если возможно) или во внешнем – более эффективный – хранилище данных (memcached, redis и т. д.). Если у вас есть информация, которая очень тяжелая для записи (например, hit-counters) и не нуждается в ресурсах ACID, вы также можете подумать о переносе ее из базы данных Postgres в более эффективные хранилища данных.

Оптимизация времени выполнения запроса более сложная – это может означать создание специальных индексов (или, в первую очередь , индексов ), изменение (возможно, денормализацию) модели данных или изменение фундаментального подхода, которое приложение применяет, когда дело доходит до работы с базой данных. См. Например, разбивка на страницы сделала сообщение Postgres Markus Winand о том, как переосмыслить концепцию разбивки на страницы, чтобы сделать ее более эффективной с точки зрения базы данных

Измерение запросов медленным способом

Но чтобы понять, какие запросы следует искать сначала, вам нужно знать, как часто они выполняются и как долго они работают в среднем.

Один из подходов к этому – регистрировать все (или «медленные») запросы, включая их время выполнения, а затем анализировать журнал запросов. Хорошим инструментом для этого является pgfouine котором уже упоминалось ранее в этом обсуждении, с тех пор он был заменен pgbadger который написан на более дружественном языке, намного быстрее и более активно поддерживается.

Как pgfouine и pgbadger страдают от того, что они нуждаются в протоколировании запросов, что может привести к заметному повышению производительности в базе данных или вызвать проблемы с дисковым пространством поверх того факта, что анализ журнала с помощью инструмента может занять некоторое время и не даст вам актуальных сведений о том, что происходит в базе данных.

Ускорение его с помощью расширений

Чтобы устранить эти недостатки, теперь есть два расширения, которые отслеживают производительность запросов непосредственно в базе данных – pg_stat_statements (что полезно только в версии 9.2 или новее) и pg_stat_plans .

Оба расширения предлагают одну и ту же базовую функциональность – отслеживание того, как часто выполнялся заданный «нормализованный запрос» (строка запроса минус все литералы выражения) и сколько времени потребовалось в общей сложности.

В связи с тем, что это выполняется во время выполнения запроса, это делается очень эффективным образом, измеримые накладные расходы составляют менее 5% в синтетических тестах.

Понимание данных

Список самих запросов очень «сух» с точки зрения информации. В третьем расширении проделана работа по устранению этого факта и более pg_statsinfo представлению данных pg_statsinfo (вместе с pg_stats_reporter ), но это pg_stats_reporter – запустить его и запустить.

Чтобы предложить более удобное решение этой проблемы, я начал работать над коммерческим проектом, который сосредоточен вокруг pg_stat_statements и pg_stat_plans и дополняет информацию, собранную множеством других данных, выведенных из базы данных. Он называется pganalyze и вы можете найти его на https://pganalyze.com/ .

Чтобы предоставить краткий обзор интересных инструментов и проектов в области мониторинга Postgres, я также начал составлять список в Wiki Postgres, который регулярно обновляется.

Источник: http://code.i-harness.com/ru/q/90c0

Настройка PostgreSQL для 1С

Перевод 1С:Предприятие на работу с PostgreSQL обеспечивает несколько важных для бизнеса преимуществ:

Можно продолжать увеличивать и объем базы 1С, и количество одновременно работающих в ней пользователей без снижения производительности системы и роста затрат.

Производительность файловой версии 1С резко падает при достижении следующих показателей:

Начинаются типовые проблемы — длительное проведение документов и формирование отчетов, частые блокировки данных, перегруженность дисков сервера. Эти признаки говорят о необходимости перевода 1С с файловой версии на клиент-серверную.

Для перехода на клиент-серверную версию 1С, дополнительно потребуются как лицензия на 1С-сервер (покупается дополнительно к имеющемуся пакету лицензирования 1С), так и СУБД SQL, адаптированная для работы с 1С. В качестве СУБД для этого подходят:

Из них наиболее популярны:

MS SQL ExpressЕсли база 1С меньше 10 Гб и за ее размером постоянно следить, то MS SQL EXPRESS можно использовать как временный вариант:
MS SQL ServerКомпания с солидным бюджетом на регулярную закупку или аренду ПО, может использовать MS SQL для 1С в качестве надежного и производительного решения:
PostgreSQLСвязка 1С + PostgreSQL применяется в компаниях любого размера, с любым количеством пользователей и размером информационных баз:

При исчерпании возможностей файловой версии 1С оптимальным бизнес-решением станет переход на PostgreSQL:

Чтобы не увеличивать нагрузку на ваши рабочие сервера и быстро подобрать необходимые параметры серверных мощностей, мы предоставляем на 30 дней частное облако («песочницу»). Это позволяет отработать связку 1С-базы, 1С-сервера и PostgreSQL и протестировать работу на 2-3 пользователях. После отработки оптимальной конфигурации можно продолжить работу и сопровождение 1С в частном облаке (гибридная ИТ-инфраструктура) или перенести данные на собственные сервера.

Не каждый ИТ-бюджет выдержит политику лицензирования компании «Microsoft». Многие компании переводят свои базы 1С с MS SQL на бесплатный PostgreSQL, а экономия на покупке лицензий с лихвой окупает затраты на перенос базы.

При переносе 1С на PostgreSQL надо учитывать:

Переносится типовая 1С-конфигурация или доработаннаяТиповые 1С-конфигурации практически без проблем переносятся на PostgreSQL. Если же проводились доработки конфигурации и при этом не соблюдался стандарт запросов SQL92, то 1С на PostgreSQL работать не будет. Потребуется переделка запросов во всех измененных отчетах и процедурах.
На какой операционной системе будет работать PostgreSQL — Windows или LinuxПри очень близких показателях по производительности, версии PostgreSQL под Windows быстрее обновляются и содержат больше полезных патчей. Если же PostgreSQL работает под Linux, работу 1С потребуется заново настраивать при каждом ее обновлении и устанавливать новые сборки PostgreSQL.
Разрядность операционной системы и 1С-сервераПри установке 32-битного 1С-сервера на 64-битную ОС количество используемой памяти ограничено 4 гигобайтами. Рекомендуем использовать 64-битные версии операционных систем и 1С-сервера.

Если вы только собираетесь развертывать 1С в сети вашей компании, то лучше сразу ориентироваться на версию 1С:Предприятия на PostrgeSQL. В ином случае в будущем может потребоваться выполнение проекта по переводу 1С уже с СУБД MS SQL на PostgreSQL, что может повлечь дополнительные затраты.

Параметры 1С-конфигурации:

Программное обеспечение:

Используется схема из двух звеньев, при которой база 1С работает на одном сервере, а 1С-сервер и PostgreSQL — на втором. Такой подход позволяет, при умеренном бюджете, измерять и оптимизировать нагрузку на каждый элемент системы, добиваясь общей максимальной производительности.

Включает в себя:

Требуется:

Перенос данных 1С из текущего формата в формат PostgreSQL, проводят одним из следующих способов:

К сожалению ни один из этих способов не дает 100%-ой гарантии работоспособности 1С. Особенно в том случае если в пользовательских SQL-запросах к базе был использован синтаксис за пределами стандарта SQL92. Поэтому в каждом переносе базы данных 1С участвуют 1С-программисты — для проведения соответствующих доработок и тестирования результатов.

Проект по переводу 1С на PostgreSQL требует знаний и квалификации сразу в нескольких ИТ-областях. Такие проекты выполняет команда из системного инженера, специалиста по SQL-базам и 1С-программиста, специализирующегося на данных конфигурациях 1С.

Мы умеем переводить 1С на PostgreSQL и готовы сделать это быстро и качественно:

Как начать работу?

Источник: https://www.stekspb.ru/autsorsing-it-infrastruktury/canned-solutions/postgresql-dlya-1s/

Диагностика проблем производительности 1С: что конкретно тормозит систему | Курсы по 1С

Что конкретно тормозит

работу 1С?

Курс по аудиту
проблем производительности

Теперь, когда Вы услышите жалобы на медленную работу 1С, вы точно будете знать – что, где и как искать.

Проблема в железе?
Память, процессор, диск?
Сеть?
Где-то кривой код?
Неправильно спроектирован запрос?

Мы покажем, как проверить каждый вариант.

Пройдя этот курс, Вы сможете за минимальное время определить, в чем конкретная причина того, что 1С работает медленно.

В случае с железом всё просто – в курсе есть рекомендации по замене / подбору оборудования.

Согласитесь, лучше потратить деньги на железо и получить ускорение работы, чем потратить и выяснить, что это не помогло.

В случае с кривым кодом и запросами – в курсе разобрано несколько приемов быстрой оптимизации.

Мы выбрали из Полного курса решение наиболее распространенных проблем, чтобы вы могли сразу их решить.

Важно: для диагностики не потребуется покупать никакого дополнительного ПО.
И уж тем более – никаких пиратских версий!

Вся работа идет с помощью бесплатных сервисов или системных утилит.

Вы без паники реагируете на крики “какого фига у нас 1С тормозит, мы не успеваем оформлять продажи!!!” и знаете, что нужно сделать 🙂

Более того, жалобы типа “Ваша 1С тормозит и невозможно работать” Вы сможете превратить в отдельный мини-проект по диагностике работы информационной базе – с конкретным, измеримым результатом.

Будете знать основные способы оптимизации запросов

Будет абсолютно точно понятно, как выявить причину при возникновении блокировок и взаимоблокировок.

Сможете настроить аудит состояния работы информационной базы и начать решать проблемы до начала “пожара”. И это не только “звездочка” у начальства, но и более комфортная работа – когда знаешь, что все под контролем 🙂

В процессе оптимизации необходимо регулярно отслеживать скорость работы как системы в целом, так и отдельных операций.

Оптимизация без регулярных замеров – это работа вслепую.

В данной главе:

рассмотрим способы автоматического замера времени различных операций
узнаем, как отслеживать и оценивать производительность системы
рассмотрим, как узнавать о проблемах со скоростью, не опрашивая пользователей

Методика APDEX
Внедрение подсистемы в конфигурацию
Настройка ключевых операций
Замер времени на клиенте. Замер времени на сервере
Возможные ошибки при встраивании замера
Обработка «Оценка производительности»
Использование подсистемы в типовых конфигурациях
Недостатки подсистемы. Ошибки
Недостатки подсистемы. Исправление ошибок
Регистрация в сервисе. Установка. Настройка

Автоматический замер времени проведения документов
Замер произвольной операции
Регистрация текста запроса
Замер времени открытия формы
Просмотр данных APDEX
Динамика APDEX и отчеты сервиса
Отправка данных в сервис по почте
Недостатки сервиса APDEX
Автоматический расчет целевого времени от обратного

В процессе эксплуатации системы необходимо регулярно следить за тем, насколько сильно загружено оборудование.

В данной главе мы разберем, как можно отслеживать загруженность оборудования для ОС Windows и для Linux и как оценивать собранные данные.

Рассмотрим также, на что обращать внимание при анализе и как сделать сбор данных о загруженности оборудования автоматическим даже в случае перезагрузки сервера.

Видеоуроки:

Мониторинг производительности оборудования
Создание счетчиков системного монитора вручную
Создание счетчиков с помощью шаблонов
Добавление счетчиков с помощью bat-файла
Настройка группы сборщиков данных
Настройка автозапуска счетчиков после перезагрузки
Просмотр и анализ графиков загруженности оборудования
Пример анализа графика загруженности оборудования
Просмотр дисковой активности
Сравнение производительности разных дисков
Сравнение производительности 1С в разных условиях
Рекомендации по оборудованию

В данной главе мы узнаем, как понять, почему система работает медленно.

Разберем как платные, так и бесплатные инструменты анализа производительности.

Подробно рассмотрим настройку и использование данных инструментов и на примерах разберем, как проявляют себя различные типы проблем производительности.

После изучения данной главы Вы научитесь точно определять причины, которые приводят к замедлению.

Сервис анализа неоптимальных запросов
Сбор и анализ данных с помощью сервиса взаимоблокировок
Анализ медленных запросов без контекста
Сервис анализа ожиданий на блокировках
Сбор и анализ данных сервиса блокировок
Сбор и выгрузка данных с помощью сервиса
Анализ управляемых блокировок с помощью сервиса
Анализ блокировок СУБД с помощью сервиса

Сервис анализа событий технологического журнала
Установка и настройка сервиса анализа взаимоблокировок
Выгрузка данных о взаимоблокировках с помощью сервиса
Анализ данных о взаимоблокировках
Подключение сервиса через тонкий клиент
Устранение ошибки SHOWPLAN permission denied

Оптимизация медленных запросов может дать прирост производительности в десятки раз – такого эффекта невозможно добиться с помощью апгрейда железа.

В этом занятии мы рассмотрим:

Примеры неоптимальных запросов
Причины медленной работы запросов
Ключевые приемы по оптимизации запросов

Соединение с подзапросами
Использование временных таблиц, индексация временных таблиц
Соединение с виртуальными таблицами
Подзапрос в условии соединения
Подзапросы в условиях и вложенные подзапросы
Несоответствие индексов и условий. Регистр накопления
Условия, не позволяющие использовать индекс. ИЛИ
Условия, не позволяющие использовать индекс. Вычисление
Условия, не позволяющие использовать индекс. НЕ В
Условия, не позволяющие использовать индекс. Функции
Условия, не позволяющие использовать индекс. Вхождение полей в разные списки
Условия, не позволяющие использовать индекс. Вхождение в список с большим числом элементов
Фильтрация виртуальных таблиц

Запрос в цикле. НайтиПо
Запрос в цикле. Обращение к реквизитам
Запрос в цикле. Вывод ссылки на экран
Запрос в цикле. Коррелированные запросы
Запрос в цикле. Намеренное использование
Большой объем выборки данных
Обращение к полю через несколько точек
Получение ссылки от поля ссылочного типа Объединить и Объединить все
Запросы с RLS
Универсальные запросы
Особенности работы с виртуальной таблицей остатков
Особенности работы с виртуальной таблицей среза
Особенности выполнения пакетных запросов
Особенности объектного чтения данных

С одной стороны, курс не претендует стать заменой Полному курсу по оптимизации и ускорению 1С.

С другой стороны, если только показать, где проблема и не дать инструкции, что с этим делать – это не продуктивно.

Поэтому в курс включены материалы по исправлению наиболее распространенных причин снижения продуктивности систем на 1С:

Оптимизация секции Условия в запросах – соответствие индексов системы и полей условия
Оптимизация запросов к регистрам накопления
Вычисляемые выражения в запросах: деградация производительности
Решение проблем при использовании отрицаний в запросах
Непопадание в индекс при использовании встроенных функций языка запросов
Оптимизация с использованием временных таблиц
Решение проблем производительности при использовании подзапросов
Запросы в цикле – оправданное и неоправданное применение
Оптимизация запросов, содержащих объединения
Запросы в RLS – решение проблем производительности
Оптимизация запросов к виртуальным таблицам регистров сведений

Автор и тренер курса – Андрей Бурмистров.

В сфере разработки на платформе «1С:Предприятие 8» более 10 лет
Работал в компании 1С в 2011-2012 гг. на должности эксперта по технологическим вопросам на проекте ЦКТП (http://v8.1c.ru/expert/cts/cts.htm)
Участвовал в качестве эксперта в проектах по повышению быстродействия и стабильности компаний Enter, Комацу, Иркутскэнерго и многих других
Работал над оптимизацией и стабильностью 1cfresh.com
Корпоративное обучение по повышению производительности и стабильности 1С, в частности, для компаний Связной, DHL, Иркутская нефтяная компания, QIWI
В течение 2014-2015 годов обучил более 1500 специалистов по курсу Оптимизация производительности 1С и подготовка к 1С:Эксперт

Материалы курсы – это видеоуроки и небольшая методичка на 65 страницах. Все материалы скачиваются с сайта после оплаты.

Длительность видеоуроков – около 6 часов. Даже если выделять всего один час в день, вы гарантированно укладываетесь в одну неделю.

Обращаем внимание – по данному курсу НЕТ поддержки в Мастер-группе (индивидуальных ответов на вопросы).

Именно за счет этого курс имеет столь низкую стоимость.

Однако уже около 2000 человек прошли Полный курс по Оптимизации и материалы курса уже скорректированы с учетом их вопросов :).

Стоимость курса – всего 1500 рублей.

При этом 100% стоимости курса засчитывается при последующей покупке старших курсов по Оптимизации (Lite или Prof).

Как работает зачет стоимости, на примере:

Сегодня Вы приобретаете курс “Диагностика проблем производительности 1С: что конкретно тормозит систему” стоимостью 1500 рублей.

Через какое-то время решаете приобрести “Полный курс “Ускорение и оптимизация систем на 1С:Предприятие 8.3” + подготовка на 1С:Эксперт”. Его обычная стоимость = 18900 рублей.

Вы оформляете заказ на Полный курс, при этом указываете, что уже приобретали курс по диагностике

Саппорт видит Ваш заказ и комментарий – и корректирует стоимость Полного курса до 17400 рублей, то есть зачитывает оплату младшего курса.

Все просто 🙂

Наши курсы можно оплатить по частям или в рассрочку, в том числе без процентов. При этом доступ к материалам Вы получаете сразу.

Это возможно при оплате от физических лиц на сумму от 3 000 руб. до 150 000 руб.

Все, что Вам нужно сделать – это выбрать способ оплаты “Оплата через Яндекс.Касса”. Далее на сайте платежной системы выбираете “Заплатить по частям”, указываете срок и размер выплат, заполняете небольшую анкету – и через пару минут получаете решение.

Мы ведем обучение с 2008 года, уверены в качестве наших курсов и даем на этот курс нашу стандартную 60-дневную гарантию.

Это значит, что если Вы начали заниматься по нашему курсу, но вдруг передумали (или, скажем, не имеете возможности), то у Вас есть 60-дневный срок для принятия решения – и если Вы производите возврат, мы возвращаем 100% оплаты.

Текущий уровень возвратов наших курсов: менее 1 процента…

Коллеги, этот курс снят с открытых продаж.

Это означает, что приобрести его могут только клиенты, которые уже покупали ранее другие наши курсы.

Оформить заказ можно только через саппорт – для этого нужно заполнить форму ниже.

Наши курсы предназначены для индивидуального обучения. Групповое обучение по одному комплекту является незаконным распространением.

Если компании требуется обучить нескольких сотрудников, мы обычно предлагаем “дополнительные комплекты”, которые стоят на 40% дешевле.

Для оформления заказа на “дополнительный комплект” обратитесь на [email protected].

Мы принимаем все основные формы платежей.

От физических лиц – оплаты с карт, оплаты электронными деньгами (WebMoney, ЯндексДеньги), оплаты через интернет-банкинг, оплаты через салоны связи и так далее. Возможна также оплата заказа по частям (в рассрочку), в том числе без дополнительных процентов.

Начните оформлять заказ – и на втором шаге Вы сможете выбрать предпочтительный способ оплаты.

От организаций и ИП – безналичная оплата, предоставляются документы на поставку. Вы вводите заказ – и сразу можете распечатать счет на оплату.

Источник: https://xn—-1-bedvffifm4g.xn--p1ai/1c-v8/optimization/audit/

Тормозит 1С. Ускорение работы программы

В силу разных причин пользователи программы 1С время от времени встречаются с проблемами производительности 1С.

Например: долго проводится документ, долго формируется отчет, ошибки транзакции, зависает программа, замедленный отклик на действия пользователя и т.д.

Следуя нашим предписаниям можно добиться значительных успехов в быстродействии программы, предупредить превышение лимита системы. Это не панацея от всех болезней, но как раз большинство причин тормозов 1С кроется именно в этих вопросах.

1. Не проводить регламентные и фоновые задания во время работы пользователей

Первое и главное правило для администраторов системы – вынести выполнение всех фоновых заданий во внерабочее время.

Система должна быть максимально разгружена, чтобы выполнять регламентные задания (индексация, проведение документов, выгрузка данных) и одновременно не мешать работать пользователям.

Ни система не пользователи не будут мешать друг другу если будут работать в разное время.

2. Не производить обмены данными РИБ во время рабочего дня пользователей

Хотя в последнее время компании отказываются от системы обмена данными РИБ в пользу online режима и терминального доступа, не лишним будет помнить, что во время выгрузки-загрузки данных обмена невозможно проведение документов и полноценная работа в программе. По-возможности и эту процедуру, если она есть, необходимо с помощью фоновых заданий выполнять ночью.

3. Вовремя повышать производительность ПК, сопоставлять его мощности реальным потребностям

Не забывайте что одновременная работа 30 и 100 пользователей в системе дают разную нагрузку. Соответственно если планируется количественный рост пользователей служба ИТ своевременно должна рассмотреть вопрос с руководством компании о расширении парка машин, закупки дополнительной памяти или серверов.

4. ПО на котором работает 1С

Программа 1С такова, что работает по разному на операционных системах. Не известно точно почему, но это так.

К примеру, серверная версия базы данных 1С на ОС Linux в связке с SQL Postgre работает значительно медленнее, чем такая же база данных 1С но на ОС Windows в связке с MS SQL.

Точные причины данного факта не известны, но видимо где-то глубоко в платформе 1С существуют проблемы совместимости с операционными системами и СУБД не семейства Microsoft. Также стоит развернуть систему на 64-разрядном сервере, если планируются значительные нагрузки на базу данных.

5. Индексация БД

Внутренняя процедура программы 1С, которая «причесывает» систему изнутри. Поставьте на выполнение фоновым регламентным заданием в ночное время суток и будьте спокойны.

6. Отключение оперативного партионного учета

Дело в том, что при оперативном проведении документов происходит запись движений в регистры, в том числе в регистры партионного учета.

Запись регистров партионного учета при проведении документов можно отключить в настройках программы.

Раз в месяц необходимо будет запускать обработку проведения документов по партиям, например в то время, когда нагрузка на базу данных наименьшая или когда работает наименьшее число пользователей.

7. Оперативная память

Используйте следующую формулу:

ОЗУ = (БД 1+БД 2+БД N) / 100 * 70

Около 70 % от совокупного физического объема баз данных. Базы 1С любят хорошо питаться оперативной памятью. Не забывайте об этом.

8. По возможности оптимизировать самописные отчеты и обработки с несовершенными и устаревшими кодами

В процессе жизни компании существуют потребности в написании отчетов и обработок, а также доработок для управления бизнес-процессами и извлечения специфической информации. Как раз эти все доработки и могут глючить, замедлять работу, т.к.

а) некие кулибины могли когда-то навернуть тяжелый неправильный код трудный к выполнению программой и требующий значительных усилий для выполнения б) код на котором написана обработка или отчет мог морально устареть и требует пересмотра, перепрограммирования.

Пользуйтесь правилом – Чем меньше мы что-то меняем в программе, тем лучше.

9. Чистка кэша

Обычная перезагрузка сервера иногда решает проблемы с устаревшим кэшем 1С. Просто попробуйте. Также может помочь выгрузка – загрузка информационной базы через конфигуратор.

И самое последнее чистка кэша конкретного пользователя – удаление папок в системном каталоге 1С вида: kexifzghjuhfv8j33hbdgk0. Но удаление кэшированых папок пользователя последнее дело, т.к.

кроме удаления мусора чистка кэша имеет неприятные последствия в виде удаления сохраненных настроек отчетов, интерфейса меню пользователя.

10. Сокращения физического объема баз данных

Больше база – больше ресурсов. Закономерно. Воспользуйтесь стандартными средствами 1С для свертки базы. Подумайте, вдруг вы можете отказаться от данных пятилетней давности ради увеличения производительности. А если вам понадобятся все же данные прошлой пятилетки всегда можно воспользоваться копией базы.

11. Правильная организация архитектуры

В целом архитектура корпоративной информационной системы должна быть правильной. Что мы подразумеваем под правильной системой? Сопоставимость возлагающихся на систему задач с имеющимся оборудованием и программным обеспечением.

Планируйте систему совместно с: системным администратором (потому что он знает парк машин), программистом 1С (потому что он знает потребности 1С в ресурсах) и руководителем компании (потому что он знает о будущем росте или сокращении компании).

Источник: http://erp-project.com.ua/index.php/ru/poleznye-materialy/2013-05-19-11-50-06/sovety-podderzhka/520-tormozit-1s-uskorenie-raboty-programmy

Настройка производительности PostgreSQL для ускорения выполнения запросов

Наша цель состоит в том, чтобы сделать выполнение фиктивного запроса в базе данных PostgreSQL быстрее, используя только встроенные инструменты, доступные в базе данных.

Операционная система и версии программного обеспечения

Операционная система: Red Hat Enterprise Linux 7.5
Программное обеспечение: сервер PostgreSQL 9.2

Требования

Установка и запуск сервера PostgreSQL. Доступ к инструменту командной строки psql

Введение

PostgreSQL — это надежная база данных с открытым исходным кодом, доступная во многих современных хранилищах дистрибутива.

Простота использования, возможность использования расширений и стабильность, которые он обеспечивает, все добавляют к его популярности. Предоставляя базовую функциональность, например, отвечая на запросы SQL, последовательно сохраняйте вставленные данные, обрабатывайте транзакции и т. д.

Большинство зрелых решений для баз данных предоставляют инструменты и ноу-хау по настройке базы данных, определению возможных узких мест и решению проблем производительности связанный с тем, что система, работающая от данного решения, растет.

PostgreSQL не является исключением, и в этом руководстве мы будем использовать встроенный инструмент, чтобы сделать медленный запрос завершенным быстрее.

Это далека от базы данных реального мира, но можно прибегнуть к использованию встроенных инструментов. Мы будем использовать сервер PostgreSQL версии 9.2 в Red Hat Linux 7.5, но инструменты, показанные в этом руководстве, представлены в более старых версиях базы данных и операционной системы.

Проблема, которая будет решена

Рассмотрим эту простую таблицу (имена столбцов не требуют пояснений):

С такими записями как:

В этом примере мы являемся компанией и развернули приложение под названием HBapp, которое отправляет письмо «Happy Birthday» сотруднику в день его рождения.

Приложение запрашивает базу данных каждое утро, чтобы найти получателей в течение дня (до работы, мы не хотим убивать нашу базу данных HR из доброты).

Приложение ищет следующий запрос для поиска получателей:

foobardb=# select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; emp_id | first_name | last_name ——–+————+———– 1 | Emily | James

Все работает нормально, пользователи получают свою почту.

Многие другие приложения используют базу данных и таблицу сотрудников внутри, например, учет и BI.

Компания растет, и поэтому растет штат сотрудников.

Со временем приложение работает слишком долго, а выполнение перекрывается с началом рабочего времени, что приводит к медленному времени отклика базы данных в критически важных приложениях.

Мы должны что-то сделать, чтобы этот запрос выполнялся быстрее, или приложение будет неработоспособным, и с ним в Nice Company будет меньше внимания.

В этом примере мы не будем использовать какие-либо дополнительные инструменты для решения проблемы, только один из них предоставляется базовой установкой. Посмотрим, как разработчик базы данных выполнит запрос с объяснением.

Мы не тестируем на производстве; мы создаем базу данных для тестирования, создаем таблицу и вставляем в нее двух сотрудников, упомянутых выше.

Мы используем те же самые значения для запроса в этом учебном пособии, поэтому при любом запуске только одна запись будет соответствовать запросу: Emily James.

Затем мы запускаем запрос с предыдущим объяснением, чтобы увидеть, как он выполняется с минимальными данными в таблице:

foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN —————————————————————————————————– Seq Scan on employees (cost=0.00..15.40 rows=1 width=96) (actual time=0.023..0.025 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 1 Total runtime: 0.076 ms (4 rows)

Это очень быстро.

Возможно, так быстро, как только компания сначала развернула HBapp.

Давайте подражать состоянию текущего производства foobardb, загрузив в базу данных как можно больше (фальшивых) сотрудников в нашей базе данных (обратите внимание: нам потребуется тот же размер хранилища в тестовой базе данных, что и в производстве).

Мы просто используем bash для заполнения тестовой базы данных (если у нас есть 500 000 сотрудников в производстве):

$ for j in {1..500000} ; do echo “insert into employees (first_name, last_name, birth_year, birth_month, birth_dayofmonth) values ('user$j','Test',1900,01,01);”; done | psql -d foobardb

Сейчас у нас 500002 сотрудников:

foobardb=# select count(*) from employees; count ——– 500002 (1 row)

Давайте снова запустим запрос explain:

foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ———————————————————————————————————- Seq Scan on employees (cost=0.00..11667.63 rows=1 width=22) (actual time=0.012..150.998 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 500001 Total runtime: 151.059 ms

У нас все еще есть только одно совпадение, но запрос значительно медленнее.

Мы должны заметить первый узел планировщика: Seq Scan, который означает последовательное сканирование — база данных считывает всю таблицу, а нам нужна только одна запись, например, grep в bash.

Фактически, это может быть на самом деле медленнее, чем grep.

Если мы экспортируем таблицу в файл csv с именем /tmp/exp500k.csv:

foobardb=# copy employees to '/tmp/exp500k.csv' delimiter ',' CSV HEADER; COPY 500002

И grepнем информацию, которая нам нужна (мы ищем 20-й день третьего месяца, последние два значения в файле csv в каждой строке):

$ time grep “,3,20” /tmp/exp500k.csv 1,Emily,James,1983,3,20 real 0m0.067s user 0m0.018s sys 0m0.010s

Решение является причиной индексации

. Ни один сотрудник не может иметь более одной даты рождения, которая состоит из ровно одного birth_year, birth_month и birth_dayofmonth — поэтому эти три поля предоставляют уникальное значение для этого конкретного пользователя.

И пользователь идентифицирует его / ее emp_id (в компании с таким же именем может быть более одного сотрудника).

Если мы объявим ограничение для этих четырех полей, будет создан неявный индекс:

foobardb=# alter table employees add constraint birth_uniq unique (emp_id, birth_year, birth_month, birth_dayofmonth); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index “birth_uniq” for table “employees”

Итак, мы получили индекс для четырех полей, посмотрим, как работает наш запрос:

foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ———————————————————————————————————— Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=103.131..151.084 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 500001 Total runtime: 151.103 ms (4 rows)

Это идентично последнему, и мы можем видеть, что план одинаков, индекс не используется.

Давайте создадим еще один индекс с помощью уникального ограничения на emp_id, birth_month и birth_dayofmonth (в конце концов, мы не запрашиваем для birth_year в HBapp):

foobardb=# alter table employees add constraint birth_uniq_m_dom unique (emp_id, birth_month, birth_dayofmonth); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index “birth_uniq_m_dom” for table “employees”

Давайте посмотрим на результат нашей настройки:

foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ———————————————————————————————————– Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=97.187..139.858 rows=1 loops=1) Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric)) Rows Removed by Filter: 500001 Total runtime: 139.879 ms (4 rows)

Ничего. Разница выше связана с использованием кешей, но план одинаков.

Пойдем дальше. Затем мы создадим еще один индекс для emp_id и birth_month:

foobardb=# alter table employees add constraint birth_uniq_m unique (emp_id, birth_month); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index “birth_uniq_m” for table “employees”

И запустите запрос еще раз:

foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN —————————————————————————————————————————— Index Scan using birth_uniq_m on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.089..95.605 rows=1 loops=1) Index Cond: (birth_month = 3::numeric) Filter: (birth_dayofmonth = 20::numeric) Total runtime: 95.630 ms (4 rows)

Успех! Запрос на 40% быстрее, и мы можем видеть, что план изменился: база данных больше не сканирует всю таблицу, но использует индекс на birth_month и emp_id.

Мы создали все миксы из четырех полей, осталось только одно. Стоит попробовать:

foobardb=# alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index “birth_uniq_dom” for table “employees”

Последний индекс создается на полях emp_id и birth_dayofmonth.

И результат:

foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20; QUERY PLAN ——————————————————————————————————————————– Index Scan using birth_uniq_dom on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.025..72.394 rows=1 loops=1) Index Cond: (birth_dayofmonth = 20::numeric) Filter: (birth_month = 3::numeric) Total runtime: 72.421 ms (4 rows)

Теперь наш запрос примерно на 49% быстрее, используя последний (и только последний) индекс.

Наша таблица и связанные с ней индексы выглядят следующим образом:

Нам не нужны промежуточные индексы, в плане четко указано, что они не будут их использовать, поэтому мы их дропаем:

foobardb=# alter table employees drop constraint birth_uniq; ALTER TABLE foobardb=# alter table employees drop constraint birth_uniq_m; ALTER TABLE foobardb=# alter table employees drop constraint birth_uniq_m_dom; ALTER TABLE

В итоге наша таблица получает только один дополнительный индекс, который является низкой ценой для двойной скорости HBapp:

И мы можем представить нашу настройку для производства, добавив индекс, который мы видели наиболее полезным:

alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);

Вывод

Излишне говорить, что это всего лишь фиктивный пример.

Маловероятно, что вы будете хранить дату рождения вашего сотрудника в трех отдельных полях, в то время как вы можете использовать поле типа даты, что позволяет выполнять операции, связанные с датой, намного проще, чем сравнивать значения месяца и дня как целые числа. Также обратите внимание, что приведенные выше объяснения запросов не подходят для чрезмерного тестирования.

В сценарии реального мира вам необходимо проверить влияние нового объекта базы данных на любое другое приложение, использующее базу данных, а также компоненты вашей системы, которые взаимодействуют с HBapp.

Например, в этом случае, если мы сможем обрабатывать таблицу получателей в 50% от исходного времени ответа, мы можем фактически создать 200% писем на другом конце приложения (скажем, HBapp работает последовательно для все 500 дочерней компании Nice Company), что может привести к пиковой нагрузке где-то еще — возможно, почтовые серверы получат много писем «С днем рождения» для ретрансляции, прежде чем они отправят ежедневные отчеты руководству, что приведет к задержкам доставки. Также немного далека от реальности, что кто-то настраивает базу данных, создаст индексы со слепой пробкой и ошибкой — или, по крайней мере, будем надеяться, что это так происходит в компании, использующей много людей.

Обратите внимание, однако, что мы получили 50% -ное повышение производительности по запросу, только используя встроенную функцию объяснения PostgreSQL, чтобы идентифицировать один индекс, который может быть полезен в данной ситуации. Мы также показали, что любая реляционная база данных не лучше, чем четкий текстовый поиск, если мы не используем их, поскольку они предназначены для использования.

Источник: http://itisgood.ru/2018/07/25/nastrojka-proizvoditelnosti-postgresql-dlja-uskorenija-vypolnenija-zaprosov/

Оптимизация PostgreSQL. Настройка ресурсов

PostgreSQL не читает данные напрямую с диска и не пишет их сразу на диск.

Данные загружаются в общий буфер сервера, находящийся в разделяемой памяти, серверные процессы читают и пишут блоки в этом буфере, а затем уже изменения сбрасываются на диск.

Если процессу нужен доступ к таблице, то он сначала ищет нужные блоки в общем буфере. Если блоки присутствуют, то он может продолжать работу, если нет — делается системный вызов для их загрузки.

Если объём буфера недостаточен для хранения часто используемых рабочих данных, то они будут постоянно писаться и читаться из кэша ОС или с диска, что крайне отрицательно скажется на производительности.

Чтобы знать точное число shared_buffers, нужно учесть количество оперативной памяти компьютера, размер базы данных, число соединений и сложность запросов, так что лучше воспользуемся несколькими простыми правилами настройки.

Здесь не следует забывать об остальных приложениях – выделив слишком много памяти для базы данных, можно получить серьезное ухудшение производительности. И то, что PostgreSQL активно использует также и дисковый кэш.

В качестве начальных значений можете попробовать следующие значения:

Средний объём данных и 256-512 МБ доступной памяти: 16-32 МБ
Большой объём данных и 1-4 ГБ доступной памяти: 64-256 МБ

temp_buffers

Буфер под временные объекты, в основном для временных таблиц.

Можно установить порядка 16 МБ

max_prepared_transactions

Количество одновременно подготавливаемых транзакций.

Для работы 1С этот параметр значения не имеет, PREPARE TRANSACTION там не используются. Да и в других приложениях его лучше не трогать.

Можно оставить по дефолту – 5

work_mem

Ранее известное как sort_mem, было переименовано, так как сейчас определяет максимальное количество оперативной памяти, которое может выделить одна операция сортировки, агрегации и др. Это не разделяемая память – work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос).

Разумное значение параметра определяется следующим образом: количество доступной оперативной памяти (после того, как из общего объема вычли память, требуемую для других приложений, и shared_buffers) делится на максимальное число одновременных запросов умноженное на среднее число операций в запросе, которые требуют памяти.

Если объём памяти недостаточен для сортировки некоторого результата, то серверный процесс будет использовать временные файлы. Если же объём памяти слишком велик, то это может привести к своппингу.

Единица измерения параметра — 1 кБ. Значение по умолчанию — 1024. В качестве начального значения для параметра можете взять 2–4% доступной памяти.

Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ.

Приложения для поддержки принятия решений, аналитических систем с сотнями строк в каждом запросе и десятками миллионов столбцов в таблицах фактов часто требуют work_mem порядка 500 МБ.

Для баз данных, которые используются и так, и так, этот параметр можно устанавливать для каждого запроса индивидуально, используя настройки сессии.

Например, при памяти 1–4 ГБ рекомендуется устанавливать 32–128 MB. Для 1С это рекомендованное значение.

maintenance_work_mem

Память использующаяся для операций VACUUM, CREATE INDEX, ALTER TABLE и FOREGIN KEY.

Предыдущее название в – vacuum_mem. Этот параметр задаёт объём памяти, используемый командами VACUUM, ANALYZE, CREATE INDEX, и добавления внешних ключей. Чтобы операции выполнялись максимально быстро, нужно устанавливать этот параметр тем выше, чем больше размер таблиц в вашей базе данных.

Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ. Следует устанавливать большее значение, чем для work_mem. Слишком большие значения приведут к использованию свопа.

При памяти 1-4Gb рекомендуется устанавливать 128-512MB. Это значение рекомендовано для 1С, но все-таки лучше попытаться определить размер самой большой таблицы.

max_stack_depth

Специальный стек для сервера, в идеале он должен совпадать с размером стека, выставленном в ядре ОС. Установка большего значения, чем в ядре, может привести к ошибкам.

Рекомендуется устанавливать 2-4MB

max_fsm_relations и max_fsm_pages

Особенностями версионных движков БД (к которым относится и используемый в PostgreSQL) является следующее:

Транзакции, изменяющие данные в таблице, не блокируют транзакции, читающие из неё данные, и наоборот (это хорошо);
При изменении данных в таблице (командами UPDATE или DELETE) накапливается мусор1 (а это плохо).

В каждой СУБД сборка мусора реализована особым образом, в PostgreSQL для этой цели применяется команда VACUUM.

До версии 7.2 команда VACUUM полностью блокировала таблицу. Начиная с версии 7.2, команда VACUUM накладывает более слабую блокировку, позволяющую параллельно выполнять команды SELECT, INSERT, UPDATE и DELETE над обрабатываемой таблицей. Старый вариант команды называется теперь VACUUM FULL.

Новый вариант команды не пытается удалить все старые версии записей и, соответственно, уменьшить размер файла, содержащего таблицу, а лишь помечает занимаемое ими место как свободное. Для информации о свободном месте есть следующие настройки:

max_fsm_relations Максимальное количество таблиц, для которых будет отслеживаться свободное место в общей карте свободного пространства. Эти данные собираются VACUUM. Параметр max_fsm_relations должен быть не меньше общего количества таблиц во всех базах данной установки (лучше с запасом);

max_fsm_pages Данный параметр определяет размер реестра, в котором хранится информация о частично освобождённых страницах данных, готовых к заполнению новыми данными.

Значение этого параметра нужно установить чуть больше, чем полное число страниц, которые могут быть затронуты операциями обновления или удаления между выполнением VACUUM.

Чтобы определить это число, можно запустить VACUUM VERBOSE ANALYZE и выяснить общее число страниц, используемых базой данных. max_fsm_pages обычно требует немного памяти, так что на этом параметре лучше не экономить.

Если эти параметры установлены верно и информация обо всех изменениях помещается в FSM, то команды VACUUM будет достаточно для сборки мусора, если нет – понадобится VACUUM FULL, во время работы которой нормальное использование БД сильно затруднено.

ВНИМАНИЕ! Начиная с 8.4 версии fsm параметры были убраны, поскольку Free Space Map сохраняется на жесткий диск, а не в память.

max_files_per_process

Максимальное количество файлов, открываемых процессом и его подпроцессами в один момент времени.

Уменьшите данный параметр, если в процессе работы наблюдается сообщение “Too many open files”.

vacuum_cost_delay

Если у вас большие таблицы, и производится много одновременных операций записи, вам может пригодиться функция, которая уменьшает затраты на I/O для VACUUM, растягивая его по времени. Чтобы включить эту функциональность, нужно поднять значение vacuum_cost_delay выше 0.

Используйте разумную задержку от 50 до 200 мс.

Для более тонкой настройки повышайте vacuum_cost_page_hit и понижайте vacuum_cost_page_limit. Это ослабит влияние VACUUM, увеличив время его выполнения.

В тестах с параллельными транзакциями Ян Вик (Jan Wieck) получил, что при значениях delay — 200, page_hit — 6 и limit —100 влияние VACUUM уменьшилось более чем на 80%, но его длительность увеличилась втрое;

Источник: https://www.oslogic.ru/knowledge/633/optimizatsiya-postgresql-nastrojka-resursov/