Почему postgresql не лучше ms sql

Выявляем и оптимизируем ресурсоемкие запросы 1С:Предприятия

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

Установка RedHat Enterprise Linux 8 (RHEL 8.4). Подключение RHEL8 к домену Active Directory. Запуск терминального клиента.

Операционная система – это один из краеугольных камней в фундаменте организации. От нее напрямую зависит надежность и безопасность корпоративной IT-инфраструктуры. Red Hat Enterprise Linux разработана с учетом всех требований и особенностей коммерческой эксплуатации Linux в производственной среде. Она проста в администрировании и управлении при развертывании приложений в физических, виртуальных и облачных средах. Обеспечивает высокую производительность и доступность приложений, а также обладает достаточной гибкостью, чтобы поддерживать рост организации и внедрение новых решений. Red Hat Enterprise Linux ценят за надежность, безопасность, стабильность, высокую производительность и масштабируемость, которые платформа предоставляет организациям. Клиентские решения Red Hat Enterprise Linux переносят эти инновации на рабочий стол.

Выявляем и оптимизируем ресурсоемкие запросы 1С:Предприятия

Как доработать производительный RLS

Неоднократно в последнее время поступали задачи, когда требовалось доработать новый производительный RLS. В своей статье Ретунский Александр, программист компании АО «Корпоративные ИТ-проекты» (официальный партнер ИнфоСофт) опишет последовательность действий при доработке нового RLS, ключевые моменты и сложности, с которыми столкнулся. В Интернете статей или инструкций, которые подробно и просто описывают – как доработать производительный RLS, не так много и автор делиться своим опытом.
В данной статье не будут описаны различия и плюсы/минусы между стандартным и производительным RLS, в Интернете по этому вопросу есть много информации.

Настройка запросов

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

Начните с узких мест и посмотрите, есть ли какие-то изменения, которые вы можете сделать, чтобы сократить расходы и/или время выполнения.
Заметка о кеше данных и издержках
При внесении изменений и оценке планов выполнения, чтобы увидеть будут ли улучшения, важно знать, что последующие выполнения могут зависеть от кэширования данных, которые дают представление о лучших результатах. Если вы запустите запрос один раз, сделаете исправление и запустите его второй раз, скорее всего, он будет выполняться намного быстрее, даже если план выполнения не будет более благоприятным

Это связано с тем, что PostgreSQL мог кэшировать данные, используемые при первом запуске, и может использовать их при втором запуске. Поэтому вы должны выполнять запросы как минимум 3 раза и усреднять результаты, чтобы сравнить издержки.
Вещи, которые я узнал, могут помочь улучшить планы выполнения:

Индексы
Исключите последовательное сканирование (Seq Scan), добавив индексы (если размер таблицы не мал)
При использовании многоколоночного индекса убедитесь, что вы обращаете внимание на порядок, в котором вы определяете включенные столбцы — Дополнительная информация

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

Условие ГДЕ
Избегайте LIKE
Избегайте вызовов функций в условии WHERE
Избегайте больших условий IN()

JOINы
При объединении таблиц попробуйте использовать простое выражение равенства в предложении ON (т.е

a.id = b.person_id). Это позволяет использовать более эффективные методы объединения (т. Е. Hash Join, а не Nested Loop Join)
Преобразуйте подзапросы в операторы JOIN, когда это возможно, поскольку это обычно позволяет оптимизатору понять цель и, возможно, выбрать лучший план.
Правильно используйте СОЕДИНЕНИЯ: используете ли вы GROUP BY или DISTINCT только потому, что получаете дублирующиеся результаты? Это обычно указывает на неправильное использование JOIN и может привести к более высоким затратам
Если план выполнения использует Hash Join, он может быть очень медленным, если оценки размера таблицы неверны. Поэтому убедитесь, что статистика вашей таблицы точна, пересмотрев стратегию очистки (vacuuming strategy )

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

Общие рекомендации
Делайте больше с меньшими затратами; Процессор быстрее чем операции ввода/вывода (I/O)
Используйте Common Table Expressions и временные таблицы, когда вам нужно выполнить цепочечные запросы.
Избегайте операторов LOOP и предпочитайте операции SET
Избегайте COUNT (*), поскольку PostgresSQL для этого выполняет сканирование таблиц (только для версий

Выявляем и оптимизируем ресурсоемкие запросы 1С:Предприятия

Настройки на Master

В данной статье мы будем настраивать серверы с IP-адресами 192.168.1.10 (первичный или master) и 192.168.1.11 (вторичный или slave).

Переходим на сервер, с которого будем реплицировать данные (мастер) и выполняем следующие действия.

Создаем пользователя в PostgreSQL

Входим в систему под пользователем postgres:

su — postgres

Создаем нового пользователя для репликации:

createuser —replication -P repluser

* система запросит пароль — его нужно придумать и ввести дважды. В данном примере мы создаем пользователя repluser.

Выходим из оболочки пользователя postgres:

exit

Настраиваем postgresql

Смотрим расположение конфигурационного файла postgresql.conf командой:

su — postgres -c «psql -c ‘SHOW config_file;'»

В моем случае система вернула строку:

/etc/postgresql/9.6/main/postgresql.conf

* конфигурационный файл находится по пути /etc/postgresql/9.6/main/postgresql.conf.

Открываем конфигурационный файл postgresql.conf.

vi /etc/postgresql/9.6/main/postgresql.conf

* мы открываем файл, который получили sql-командой SHOW config_file;.

Редактируем следующие параметры:

listen_addresses = ‘localhost, 192.168.1.10’
wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
hot_standby = on
hot_standby_feedback = on

* где

192.168.1.10 — IP-адрес сервера, на котором он будем слушать запросы Postgre;
wal_level указывает, сколько информации записывается в WAL (журнал операций, который используется для репликации);
max_wal_senders — количество планируемых слейвов;
max_replication_slots — максимальное число слотов репликации (данный параметр не нужен для postgresql 9.2 — с ним сервер не запустится);
hot_standby — определяет, можно или нет подключаться к postgresql для выполнения запросов в процессе восстановления;
hot_standby_feedback — определяет, будет или нет сервер slave сообщать мастеру о запросах, которые он выполняет.

Открываем конфигурационный файл pg_hba.conf — он находитсяч в том же каталоге, что и файл postgresql.conf:

vi /etc/postgresql/9.6/main/pg_hba.conf

Добавляем следующие строки:

host replication repluser 127.0.0.1/32 md5
host replication repluser 192.168.1.10/32 md5
host replication repluser 192.168.1.11/32 md5

* данной настройкой мы разрешаем подключение к базе данных replication пользователю repluser с локального сервера (localhost и 192.168.1.10) и сервера 192.168.1.11.

Перезапускаем службу postgresql:

systemctl restart postgresql

* обратите внимание, что название для сервиса в системах Linux может различаться

Результаты тестов

Этот тест показывает общее влияние различных размеров больших страниц. Первый набор тестов был создан с размером страницы по умолчанию в Linux 4 КБ без включения больших страниц

Обратите внимание, что прозрачные огромные страницы также были отключены и оставались отключенными на протяжении всех этих тестов.
Затем второй набор тестов был выполнен размером больших страниц в 2 MБ. Наконец, третий набор тестов выполняется с размером больших страниц 1 ГБ.
Все эти тесты были выполнены в PostgreSQL версии 11

Наборы включают в себя комбинацию разных размеров базы данных и клиентов. На приведенном ниже графике показаны сравнительные результаты производительности для этих тестов с TPS (транзакций в секунду) по оси Y, размером базы данных и количеством клиентов на размер базы данных по оси X.
Из приведенного выше графика видно, что прирост производительности с большими страницами увеличивается с увеличением количества клиентов и размера базы данных, если размер остается в предварительно выделенном буфере в разделяемой памяти (shared buffer).
Этот тест показывает TPS в сравнении с количеством клиентов. В этом случае размер базы данных составляет 48 ГБ. На оси Y у нас есть TPS, а на оси X у нас есть количество подключенных клиентов. Размер базы данных достаточно мал, чтобы поместиться в shared buffer, который установлен на 64 ГБ.
Если для больших страниц установлено значение 1 ГБ, то чем больше клиентов, тем выше сравнительный прирост производительности.
Следующий график такой же, как приведенный выше, за исключением размера базы данных 96 ГБ. Это превышает размер shared buffer, который установлен на 64 ГБ.
Ключевое наблюдение здесь заключается в том, что производительность с большими страницами, равными 1 ГБ, увеличивается по мере увеличения числа клиентов, и в конечном итоге она дает большую производительность, чем большие страницы в 2 МБ или стандартный размер страницы в 4 КБ.
Этот тест показывает TPS в зависимости от размера базы данных. В этом случае количество подключенных клиентов составляет 32. На оси Y у нас TPS, а на оси X — размеры базы данных.
Как и ожидалось, когда база данных выходит за пределы предварительно выделенных больших страниц, производительность значительно снижается.

Бесплатный MS SQL vs бесплатный PostgreSQL

Нельзя сравнивать платный MS SQL – Standard и Enterprise – с бесплатным PostgreSQL, это не совсем корректное сравнение. Нельзя ожидать от бесплатного ПО такой же скорости, как и от платного, причем неслабо платного.

Давайте сравним бесплатную версию MS SQL Express и бесплатные версии PostgreSQL.

Бесплатные версии Postgres – это:

Самосборки, когда вы с сайта postgresql.org скачали исходники, добавили туда патчи для 1С и собрали свою сборку. Она бесплатна, пожалуйста, пользуйтесь.
То же самое можно сделать, скачав сборку, которую собрали специалисты фирмы «1С» с сайта – releases.1с.ru. Эта сборка будет отличаться от вашей самосборки тем, что она после сборки протестирована фирмой 1С.
Есть еще третий вариант – сайт 1с.postgres.ru. Это сборка от компании Postgres Professional – эта команда работает со своими серверами сборки, и они делают бесплатную версию для 1С. где в ванильную сборку добавляют несколько своих патчей, которые считают нужным. На то какие патчи они добавят, а какие – нет, мы с вами повлиять не можем. Но на моей практике сборка с 1с.postgres.ru работает намного стабильнее. Кто имеет доступ на партнерские форумы 1С, там есть несколько обращений, когда у людей сбоили самосборки или сборки с releases, при этом ставим сборки с 1с.postgres.ru с той же версией Postgres, и все отлично работает. Поэтому я бы советовал ставить отсюда.

Теперь давайте сравним бесплатные версии MS SQL Express и Postgres. Какие ограничения они накладывают:

На количество ядер.
- MS SQL Express в последней поддерживаемой 1С 17 версии, позволяет использовать только 4 ядра.
- бесплатный Postgres – безлимитное количество ядер.
То же самое по памяти.
- MS SQL Express может использовать 1,5 Гб памяти и все.
- Postgres – безлимитно.
Объем базы:
- MS SQL Express – 10 Гб,
- у Postgres – безлимитно.
Отказоустойчивость:
- в MS SQL Express не поддерживается вообще.
- В Postgres доступны логические и физические репликации любого уровня каскадирования. Вы можете одновременно делать с мастера десятки реплик. Или сделать реплику с мастера, а с реплики – еще одну (иногда это имеет смысл). Бесплатный Postgres позволит снимать бэкапы с реплики, не трогая этими задачами мастер.
- Есть несколько важных моментов:
  - бэкап у MS SQL консистентен на момент окончания бэкапа, а бэкап Postgres’a (dump) консистентен на момент начала бэкапа.
  - Если вы сливаете этот dumpс мастера Postgres (с главного сервера), то в момент этого бэкапа вы не сможете произвести структурные изменения в 1С, так как они содержат в себе команды drop для таблиц либо удаления столбцов. Postgres не позволит изменить структуру данных в части удаления, пока он снимает dump. Этот момент нужно учитывать при проектировании системы.
Регламентные операции плюс бэкапы
- На MS SQL Express регламенты можно делать только скриптами и cron. Нет там планировщика (агента SQL Server), его там не существует. Вы не сможете внутри SQL создать никаких расписаний или операций. Надо будет батнички нарисовать, засунуть это либо в cron, либо в планировщик Windows.
- То же самое на Postgres, все регламентные операции, бэкапы и восстановления из бэкапов – это скрипты, cron или планировщик Windows, смотря в какой системе вы это делаете.
Ещё момент – бесплатные версии обоих продуктов не входят в список импортозамещающих продуктов. Компаниям госсектора эти базы данных запрещены в использовании вообще, не только в 1С.
Также на бесплатном Postgres и бесплатном MS SQL нет ФСТЭКа. Никто не получает сертификаты ФСТЭК на эти продукты. Забегая вперед, скажу, что и в платном MS SQL ФСТЭКа нет.

С бесплатными версиями все более менее понятно, поехали в платные версии.

Установка PostgreSQL в Ubuntu

PostgreSQL доступен в репозиториях Ubuntu по умолчанию. Просто используйте команду apt для установки PostgreSQL. Наиболее распространенными установленными пакетами являются PostgreSQL server, client, pgadmin, а также библиотеки.

Для установки конкретной версии вы можете использовать PostgreSQL Apt repository.

Чтобы установка PostgreSQL Ubuntu прошла гладко, выполните следующие действия.

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

Установите пакет Postgres, а затем пакет — contrib, он добавит дополнительные утилиты и функциональные возможности.

После завершения установки PostgreSQL запустите службу PostgreSQL. Это можно сделать командой которая написана ниже.

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

Запуск и включение службы postgres

После установки вы можете проверить состояние PostgreSQL, выполнив следующую команду.

Статус службы Postgres

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

Теперь введите команду psql,

Выполнение команд в оболочке Postgres

Для проверки версии PostgreSQL выполните следующую команду:

или

Создание роли в PostgreSQL

Для авторизации и аутентификации Postgres использует понятие “роли”. Которое в некоторых отношениях похоже на учетные записи Unix. Во время установки Postgres настраивается таким образом, чтобы использовать аутентификацию ident. Идентификация Ident связывает роль Postgres с соответствующими системными учетными записями Linux / Unix. Только суперпользователи и юзеры с ролью «CREATEROLE” отвечают за создание новых ролей.

Чтобы создать новую роль например с именем john, введите следующую команду в оболочке PostgreSQL.

Создание базы данных в PostgreSQL

Для создания новой базы данных требуется программа PostgreSQL database shell (psql). Войдите в оболочку psql и введите следующую команду, чтобы создать пользователя и установить пароль. Здесь я собираюсь создать имя пользователя jacky с паролем mystrongpassword. Вы можете создать свой собственный.

Создайте базу данных (например, gallary), используя следующую команду,

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

Чтобы выйти из оболочки PostgreSQL требуется ввести команду q и нажать enter.

Создание пользователя и базы данных

Включение удаленного доступа к PostgreSQL

По умолчанию PostgreSQL слушает на интерфейсе 127.0.0.1. Если вы хотите получить доступ к базе данных из другого места. Тогда вам требуется настроить PostgreSQL для прослушки различных сетевых интерфейсов. Чтобы настроить PostgreSQL для различных интерфейсов, откройте конфигурационный файл PostgreSQL с помощью nano или другим текстовым редактором. Я буду использовать редактор Vim.

В конфигурационном файле найдите запись listen_addresses, находится она в «Connection Settings». Раскомментируйте строку и измените localhost на ‘*’. Это даст команду PostgreSQL прослушивать все сетевые интерфейсы для входящих подключений.

Включение удаленного подключения в postgres

Сохраните свою конфигурацию и перезапустите сервер PostgreSQL, чтобы изменения отразились в БД.

Теперь можно проверить прослушивает ли PostgreSQL другой интерфейс. Для этого выполните следующую команду.

Состояние прослушивания порта Postgres

Если вы включили брандмауэр UFW на сервере, тогда вам необходимо открыть порт 5432 для входящих TCP — соединений. Чтоб сделать это выполните команду.

Также проверьте правило брандмауэра UFW, делается это командой.

Конфигурация Postgres ufw

Заключение

В этом руководстве вы узнали, как установить сервер PostgreSQL на Ubuntu 20.04. Кроме того, вы также узнали, как привязать PostgreSQL к различным сетевым интерфейсам, чтобы принимать соединения от удаленного клиента.

Отладка и устранение проблем в PostgreSQL Streaming Replication +37

15.06.18 06:51

•
mi5ha6in
•
#414111
•
Хабрахабр

•

2000

Блог компании Конференции Олега Бунина (Онтико), Высокая производительность, SQL, Анализ и проектирование систем, PostgreSQL

Потоковая репликация, которая появилась в 2010 году, стала одной из прорывных фич PostgreSQL и в настоящее время практически ни одна инсталляция не обходится без использования потоковой репликации. Она надежна, легка в настройке, нетребовательна к ресурсам. Однако при всех своих положительных качествах, при её эксплуатации могут возникать различные проблемы и неприятные ситуации.Алексей Лесовский (@lesovsky) на Highload++ 2017 рассказал, как с помощью встроенных и сторонних инструментов, диагностировать различные типы проблем и как устранять их. Под катом расшифровка этого доклада, построенного по спиральному принципу: сначала мы перечислим все возможные средства диагностики, потом перейдем к перечислению типовых проблем и их диагностике, далее посмотрим, какие экстренные меры можно принять, и наконец как радикально справиться с задачей.

О спикере

Опыт оптимизации и контроля производительности в БД с 3000 пользователей Промо

Данная статья написана по материалам доклада, прочитанного на Конференции Инфостарта IE 2014 29-31 октября 2014 года.

Меня зовут Сергей, являюсь руководителем отдела оптимизации и производительности систем в компании «Деловые линии».
Цель этого доклада – поделиться информацией о нашем опыте работы с большой базой на платформе 1С, с чем пришлось столкнуться, как удалось обеспечить работоспособность.
Уверен, что вам будет интересно, так как подобной информацией мало кто делится, да и про само существование таких систем их владельцы стараются не рассказывать, максимум про это «краем глаза» упоминают участвовавшие в проекте вендоры.
**update от 04.03.2016 по вопросам из комментариев

Установка MS SQL и PostgreSQL

Давайте сравним установку PostgreSQL и MS SQL Server на Windows.

Чтобы поставить MS SQL-сервер, надо скачать 2,5 Гб с интернета.
При этом дистрибутив Postgres для Windows – 70 Мб.

В процессе инсталляции для MS SQL Server, нам надо сделать 12 кликов и пройти два окна настройки параметров, где мы сразу можем настроить: количество и расположение файлов для tempDB, а также расположение базы данных и лога транзакций. Время инсталляции SQL-сервера с учетом скачивания дистрибутивов – 4 часа.
«Виндовый» Postgres настраивается в 5 кликов (там всего одно окно настройки), почти все параметры проходят автонастройку, опираясь на параметры вашего компьютера. Я сейчас говорю про дистрибутив от компании Postgres Pro (и платный, и бесплатный настраиваются одинаково). Время инсталляции – 5 минут. Если надо разнести файлы базы, WAL и временных файлов то тогда ещё три строчки в конфигурационном файле.

Дальше давайте теперь сравним установку на Linux. В данном случае будут рассматривать CentOS.

Установка MS SQL-сервера на CentOS представляет собой 5 страниц инструкций с сайта Microsoft. Время инсталляции – час. В этот час будет скачиваться неслабый дистрибутив, и потом его нужно будет установить и настроить в командной строке. Время настройки – 15 минут.
- Для самого MS SQL Server в CentOS нужно будет настроить хотя бы два параметра: это параллелизм и количество потребляемой оперативной памяти.
- Нужно будет настроить хотя бы три параметра самой базы: как у вас база будет прирастать, какой у вас будет изначальный объем файлов базы данных и журналов транзакций, установить уровень журналирования.
- Плюс к этому нужно будет настроить минимум две регламентных операций ухаживания за базой – реиндексация и апдейт статистики.
Устанавливая на CentOS Postgres вам придет письмо с кодом в 12 строчек. Копируете, вставляете, и через 5 минут у вас есть запущенный и готовый Postgres. Для настройки Postgres в Windows и в CentOS вам нужно провести всего четыре настройки параметров сервера СУБД и задать две регламентные операции в cron. На это уйдет максимум 15 минут.

Все эти цифры по поводу настройки и установки пока не в пользу MS SQL.

Я свято уверен, что после настройки и установки в СУБД потом постоянно что-то крутить в настройках СУБД не нужно. Анализ проблем лучше делать в технологическом журнале 1С. Техжурнал 1С покажет более полную комплексную информацию, которая будет содержать не только «тормоза» СУБД, но и «тормоза» самого движка платформы, что часто занимает гораздо более длительное время в исполнении запросов чем время, которое тратит на этот запрос СУБД. И только когда вы нашли проблему, код 1С кажется идеальным а СУБД ведёт себя странно, вот тогда уже нужно идти и смотреть планы запросов, делать выводы и может быть менять настройки, хотя лучше всё таки поменять код 1С, так как улучшив одну операцию изменением настроек СУБД можно очень легко “положить” всю систему.