Postgresql оптимизация: Настройка параметров PostgreSQL для оптимизации производительности / Хабр

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

SQL – декларативный язык программирования. Вы объявляете базе данных о своих намерениях, а способ получения результата она выбирает сама. Это решения об использовании индексов, порядке объединения таблиц или проверки условий. PostgreSQL в стандартном виде не предусматривает вмешательства извне в этот процесс, но показывает последователь выполнения любого запроса.

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

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

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

Получение плана запроса

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

Для получения плана запроса используют команду EXPLAIN. Добавьте параметры ANALYZE и BUFFERS, и, помимо действительной отработки запроса, получите время выполнения, статистическую информацию, количество попаданий, блоков и прочих данных буфера. Эта команда подходит только для локальной отладки вручную, потому что при большой нагрузке системы данные быстро меняются, и план теряет актуальность во время выполнения.

Для крупных приложений применяют модуль auto_explain. Главная прелесть auto_explain в том, что вы задаёте «медленное» время выполнения запроса. Он анализирует и записывает план в лог сервера только тогда, когда оператор отрабатывает дольше указанного числа.

Смотрите, как выглядит получение плана:

После заголовка вы видите тело запроса, а со слова Aggregate начинается, собственно, план.

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

Визуализация плана запроса

Что поможет визуализировать необходимую информацию?

Сайт на базе текстового представления плана отрисовывает таблицу с такими данными:

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

Из минусов этого ресурса отметим:

• копирование и вставка планов из лога;
• отсутствие данных буфера;
• медленный выпуск новых версий;
• некорректный анализ общих табличных выражений (CTE) и InitPlan.

Обратите внимание, что результирующее время плана совпадает со временем отдельного узла CTE Scan:

Это ошибка, ведь Seq Scan отработал 1,389 мс, и для получения времени CTE Scan выполняется вычитание.

Усовершенствование представления плана

Чтобы качественнее анализировать планы запросов, компания «Тензор» реализовала собственный сервис для внутреннего использования, но позднее появилась публичная версия. Разработчики сделали парсер планов, добавили правильный анализ CTE-ресурсов и буфера, а также наглядную визуализацию.

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

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

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

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

При разборе распределения ресурсов CTE возникли сложности. Для иллюстрации рассмотрим пример:

Создали таблицу, прочитали оттуда две записи: первую и со смещением на 100. План запроса выглядит так:

Посмотрите на количество потреблённых данных. На последовательное сканирование ушло 3 страницы, первый CTE scan занял 1, а второй – 2. Покажется, будто потребление составило 6 страниц, но в действительности это те же 3 страницы, что и в Seq scan.

Получается, распределение ресурсов в плане вовсе не дерево – ациклический направленный граф. Для понимания взгляните на схему выполнения предыдущего запроса, где видно, что ресурсы расходятся на 2 потребителя:

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

Чаще вы встречаетесь с подобными случаями:

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

• отсутствие вынесенных отдельно узлов CTE, SubPlan: они отображаются в информационном порядке над первыми под ними узлами;
• красная точка – обозначение фильтрации, которая иногда делает ситуацию критической и требует проверки;
• число внутри красной фигуры над узлом – количество циклов. Если число четырёхзначное, обратите внимание на эту операцию.

В конце концов, проблем с планами запросов становится меньше, но если у вас сотни серверов, появляется ещё одна проблема – тонны логов.

Консолидация логов

Для объединения информации PostgreSQL предоставляет дефолтный инструмент pg_stat_statements. При использовании этого модуля возникают такие неудобства:

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

С учётом копипасты в масштабах выгоднее написать собственный коллектор, который по SHH обращается к серверу PostgreSQL, запускает там tail и получает зеркальный трафик логов. Причём эта информация доступна онлайн. Для сохранения ресурсов коллектор поддерживает соединение и периодически проверяет pg_stat_activity и pg_locks.

При мониторинге сотен серверов приходится ускорять запись в базу. Когда объёмы данных большие, нет ничего быстрее COPY ... FROM STDIN. Если у вас 50 тысяч запросов в секунду на 100 серверов, то получите 100–150 гигабайт логов в сутки. Поэтому сразу становится вопрос секционирования по дням, очень быстрого «потокового» COPY и частичного отказа от триггеров. Приходится убирать внешние ключи, потому что каждый раз на проверку тратятся ресурсы. Агрегация и хеширование переносятся на коллектор, а для каждой сущности в базе выделяется отдельный COPY-поток, который закрывается через заданный интервал времени для завершения транзакции.

Тестирование показало, что данные лучше отправлять сразу после создания, то есть без буферизации. Поскольку перешли на вставку через COPY, в словарных таблицах понадобился триггер BEFORE INSERT для избежания дубликатов, который с версии 9.5+ заменяется на INSERT ... ON CONFLICT DO NOTHING.

Благодаря COPY-потоку удалось снизить нагрузку с 4 тысяч операций в секунду до 1 тысячи. При дальнейшем увеличении нагрузки до 6 тысяч потоковая запись идёт со скоростью 100 мегабайт в секунду. Сколько разработчик будет разбираться с проблемой? За три месяца уж справится с любым сложным запросом, при этом размер архива оставит 10 терабайт.

Структуризация собранной информации

Что делать с миллионами сохранённых планов? Без структурирования ничего не понятно, поэтому нужна такая информация о запросе:

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

Для определения «хозяина» запроса используйте SET APPLICATION NAME = '<host>:<method>'. Длину названия сервера или метода иногда приходится подрезать из-за ограничения 63 байта в поле name. С помощью настройки log_line_prefix вы получите необходимую информацию. При log_line_prefix = '%m [%p:%v] [%d] %r %a' заголовок лога выглядит так:

Отсюда легко отобразить пользователю, когда и на каком сервере базы выполнялся запрос, PID процесса и другие данные, которые вы выбрали при конфигурации log_line_prefix.

Помимо именования, запросы разделяют по серверам и дням, а также рассматривают в пределе шаблона (сокращённого плана), приложения или метода и узла. Шаблоны уменьшают количество анализируемых объектов в несколько раз, например, из 1121 получаем 80. С помощью timeline вы находите общие паттерны поведения и сопоставляете с действиями.

При анализе планов важно обратить внимание на следующие показатели:

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

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

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

Заключение

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

Когда речь идёт о сотнях серверов, важно консолидировать логи. Теперь вы знаете, насколько нетривиальная эта задача и какие принципы применять.

А как вы оптимизируете производительность запросов?

Автоматическая оптимизация настроек MySQL, PostgreSQL / Блог компании Southbridge / Хабр

Оптимизация настроек всегда дело тонкое и выставить именно те параметры, которые дадут максимальную производительность, зачастую можно только уже в процессе работы приложения, когда уже есть статистика нагрузки и видны узкие места.
Но очень полезно сделать и первичную оптимизацию при запуске СУБД. В этом посте рассмотрены пути автоматической оптимизации MySQL и PostgreSQL утилитами mysqltuner и pgtune.

MySQL

Для оптимизации mysql существует простая и удобная в использовании утилита mysqltuner.

Раздобыть ее в безвозмездное пользование можно на github, а именно тут. Или загрузить одной командой:

wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl

Пользоваться просто: загружаем mysqltuner.pl на сервер с mysql, ставим права на запуск для файла (или запускаем так: perl mysqltuner.pl), на запрос логина / пароля даем учетку с привилегированными правами и смотрим рекомендации.

Рекомендации заносим в конфиг, перезапускаем mysql-server. Или применяем «налету» через консоль mysql, если проект уже запущен и перезапуск нежелателен.

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

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

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

Для дефрагментации запускаем OPTIMIZE TABLE из консоли mysql, но удобнее сделать для всех таблиц разом через интерфейс типа phpMyAdmin.

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

Полезная информация описана моим коллегой в этой статье.

Для большей производительности полезно использовать Percona Server на замену стандартному MySQL Server.

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

Про Percona Server уже достаточно много статей на Хабре, но в будущем поделюсь и своим опытом использования этой сборки.

PostgreSQL

Для тюнинга настроек PostgreSQL так же существует полезная утилита под названием pgtune.

В отличие от mysqltuner, утилита не дает рекомендаций, а сразу создает конфигурационный файл postgresql.conf с параметрами, оптимальными для системы, на которой запущен PostgreSQL.

Схема использования следующая:

pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune


где $PGDATA — путь к директории с конфигом сервера postgresql.conf. На выходе получаем файл postgresql.conf.pgtune, в котором выставлены подобранные утилитой параметры. Эти параметры утилита записывает в конец файла после блока

#------------------------------------------------------------------------------
# pgtune wizard run on YYYY-MM-DD
# Based on XXXXXXX KB RAM in the server
#------------------------------------------------------------------------------

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

-M или —memory — полный размер ОЗУ на сервере, на основе которого выделяются ресурсы памяти для PostgreSQL;

-T или —type — Указывает тип базы данных: DW, OLTP, Web, Mixed, Desktop;

-с или —connections — Максимально возможное количество подключений к базе; Если значение не указано, определяется на основе типа базы;

-D или —debug — Включает режим отладки в PostgreSQL

-S или —settings — Устанавливает к директории, в которойрасположен конфигурационный файл.

После завершения работы утилиты редактируем сгенерированный файл postgresql.conf.pgtune при необходимости (например, выставить нестандартный порт или настроить логирование ), заменяем им конфигурационный файл postgresql.conf и перезапускаем PostgreSQL-server.

Буду рад любым вопросам / замечаниям / дополнениям!

Оптимизация производительности PostgreSQL для работы с 1С:Предприятие

03:07 18.03.2020
Оптимизация производительности PostgreSQL для работы с 1С:Предприятие

PostgreSQL приобретает все большую популярность как СУБД для использования в связке с 1С:Предприятие. При этом одним из самых частых нареканий является низкая производительность этого решения. Во многом это связано с тем, что настройки PostgreSQL по умолчанию не являются оптимальными, а обеспечивают запуск и работу СУБД на минимальной конфигурации. Поэтому имеет смысл потратить некоторое количество времени на оптимизацию производительности сервера, тем более что это не очень сложно.

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

Для тестирования мы использовали систему:

• CPU — Core i5-4670 — 3.4/3.8 ГГц
• RAM — 32 ГБ DDR3
• Системный диск — SSD WD Green 120 ГБ
• Диск для данных — 2 х SSD Samsung 860 EVO 250 ГБ — RAID1
• СУБД — PostgresPro 11.6
• Платформа — 8.3.16.1148
• ОС — Debian 10 x64

Прежде всего выполним тестирование с параметрами по умолчанию:

Полученный результат — 22,32 по Гилеву высоким не назовешь, для субъективного контроля мы использовали конфигурацию Розница 2.2 с базой реального торгового предприятия объемом в 8 ГБ, в целом работу можно было признать удовлетворительной, но местами наблюдалась некоторая «задумчивость», особенно при открытии динамических списков.

Перейдем к оптимизации. Все изменения следует вносить в файл postgesql.conf, который располагается в Linuх для сборки от 1С по пути /etc/postgres/1x/main, а для сборки от PostgresPro в /var/lib/pgpro/1c-1x/data. В Windows данный файл располагается в каталоге данных, по умолчанию это C:\Program Files\PostgreSQL 1C\1х\data. Все параметры указаны в порядке их следования в конфигурационном файла.

Одним из основных параметров, используемых при расчетах, является объем оперативной памяти. При этом следует использовать то значение, которое вы готовы выделить серверу СУБД, за вычетом ОЗУ используемой ОС и другими службами, скажем, сервером 1С. В нашем случае будет использоваться значение в 24 ГБ.

Затем рассчитаем значения отдельных параметров с помощью калькулятора, для чего укажем ОС и версию Postgres, тип накопителя, количество доступной памяти и количество ядер процессора. В поле DB Type указываем Data Warehouses, количество соединений можем проигнорировать, так как вычисляемый результат будет значительно расходиться с рекомендациями 1С.

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

max_connections = 500..1000

Максимальное число соединений, 1С рекомендует указанные выше значения, мы установили 1000.

shared_buffers = RAM/4

Объем памяти для совместного кеша страниц, разделяется между всеми процессами Postgres, рекомендуемое значение — четверть доступного объема памяти, в нашем случае 6 ГБ.

temp_buffers = 256MB

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

work_mem = RAM/32..64

Указывает объем памяти, который может быть использован для запроса прежде, чем будут задействованы временные файлы на диске. Применяется для каждого соединения и каждой операции, поэтому итоговый объем используемой памяти может существенно превосходить указанное значение. Это один из тех параметров, вычисляемое значение которого калькулятором существенно отличается от рекомендаций 1С. Для объема памяти в 24 ГБ рекомендуемыми значениями будут 375 — 750 МБ, мы выбрали 512 МБ.

maintenance_work_mem = RAM/16..32 или work_mem * 4

Объем памяти для обслуживающих задач (автовакуум, реиндексация и т.д.), указываем рекомендованный калькулятором объем, в нашем случае 2 ГБ.

max_files_per_process = 1000

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

bgwriter_delay = 20ms
bgwriter_lru_maxpages = 400
bgwriter_lru_multiplier = 4.0

Параметры процесса фоновой записи, который отвечает за синхронизацию страниц в shared_buffers с диском.

effective_io_concurrency = 2 для RAID, 200 для SSD, 500..1000 для NVMe

Допустимое число одновременных операций ввода/вывода. Для жестких дисков указывается по количеству шпинделей, для массивов RAID5/6 следует исключить диски четности. Для SATA SSD это значение рекомендуется указывать равным 200, а для быстрых NVMe дисков его можно увеличить до 500-1000. При этом следует понимать, что высокие значения в сочетании с медленными дисками сделают обратный эффект, поэтому подходите к этой настройке грамотно.

Важно! Параметр effective_io_concurrency настраивается только в среде Linux, в Windows системах его значение должно быть равно нулю.

max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

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

fsync = on

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

synchronous_commit = off

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

wal_buffers = 16MB

Задает размер буферов журнала предзаписи (WAL, он же журнал транзакций), если оставить эту настройку без изменений, то сервер будет автоматически устанавливать это значение в 1/32 от shared_buffers, но не менее 64 КБ и не более размера одного сегмента WAL в 16 МБ.

commit_delay = 1000
commit_siblings = 5

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

min_wal_size = 512MB..4G 
max_wal_size = 2..4 * min_wal_size

Минимальный и максимальный размер файлов журнала предзаписи. Указываем значения из калькулятора, в нашем случае это 4 ГБ и 16 ГБ.

checkpoint_completion_target = 0.5..0.9

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

seq_page_cost = 1.0

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

random_page_cost = 1.5..2.0 для RAID, 1.1..1.3 для SSD

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

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

Для производительных SSD можно использовать значения:

seq_page_cost = 0.5
random_page_cost = 0.5

А для NVme:

seq_page_cost = 0.1
random_page_cost = 0.1

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

effective_cache_size = RAM - shared_buffers

Определяет эффективный размер кеша, который может использоваться при одном запросе. Этот параметр не влияет на размер выделяемой памяти, не резервирует ее, а служит для ориентировочной оценки доступного размера кеша планировщиком запросов. Чем он выше, тем большая вероятность использования сканирования по индексу, а не последовательного сканирования. При расчете следует использовать выделенный серверу объем RAM, а не полный объем ОЗУ. В нашем случае это 18 ГБ.

autovacuum = on

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

autovacuum_max_workers = NCores/4..2 но не меньше 4

Количество рабочих процессов автовакуума, рассчитывается по числу процессорных ядер, не менее 4, в нашем случае 4.

autovacuum_naptime = 20s

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

row_security = off

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

max_locks_per_transaction = 256 

Максимальное количество блокировок в одной транзакции, рекомендация от 1С.

escape_string_warning = off
standard_conforming_strings = off

Данные опции специфичны для 1С и регулируют использование символа \ для экранирования.

Сохраним файл конфигурации и перезапустим PostgreSQL, в Linux это можно выполнить командами:

pg-setup service stop
pg-setup service start

В Windows штатными средствами операционной системы, либо скриптами из поставки сборки PostgreSQL:

После чего снова выполним тестирование производительности, на этот раз мы получили следующий результат:

Как видим, достаточно несложные действия по оптимизации добавили серверу около 30% производительности, субъективные ощущения от работы с конфигурацией Розница также повысились, исчезло ощущение «задумчивости», повысилась отзывчивость системы.

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

read more at Записки IT специалиста

Ускорение и оптимизация настроек PostgreSQL для 1С » Администрирование » FAQ » HelpF.pro

По умолчанию PostgreSQL настроен таким образом, чтобы расходовать минимальное количество ресурсов для работы с небольшими базами до 4 Gb на не очень производительных серверах. То есть, если дело касается систем посерьезней, то вы столкнетесь с большими потерями производительности базы данных лишь потому, что дефолтные настройки могут в корне не соответствовать производительности вашего северного оборудования. Настройки выделения ресурсов оперативной памяти RAM для работы PostgreSQL хранятся в файле postgresql.conf.

Доступен как из папки, куда установлен PostgreSQL / Data, так и из pgAdmin:

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

shared_buffers

Это размер памяти, разделяемой между процессами PostgreSQL, отвечающими за выполнения активных операций. Максимально-допустимое значение этого параметра – 25% всего количества RAM

Например, при 1-2 Gb RAM на сервере, достаточно указать в этом параметре значение 64-128 Mb (8192-16384).

temp_buffers

Это размер буфера под временные объекты (временные таблицы). Среднее значение 2-4% всего количества RAM

Например, при 1-2 Gb RAM на сервере, достаточно указать в этом параметре значение 32-64 Mb.

work_mem

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

При 1-2 Gb RAM на сервере, рекомендуемое значение 32-64 Mb.

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

Еще два важных параметра это maintenance_work_mem (для операций VACUUM, CREATE INDEX и других) и max_stack_depth

Примеры оптимальных настроек:

Hardware:

• CPU: E3-1240 v3 @ 3.40GHz
• RAM: 32Gb 1600Mhz
• Диски: Plextor M6Pro

postgresql.conf:

• shared_buffers = 8GB
• work_mem = 128MB
• maintenance_work_mem = 2GB
• fsync = on
• synchronous_commit = off
• wal_sync_method = fdatasync
• checkpoint_segments = 64
• seq_page_cost = 1.0
• random_page_cost = 6.0
• cpu_tuple_cost = 0.01
• cpu_index_tuple_cost = 0.0005
• cpu_operator_cost = 0.0025
• effective_cache_size = 24GB

Вариант настроек от pgtune:

Полезные запросы:

Блокировки БД по пользователям

Код SQL

 select a.usename, count(l.pid) from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not(mode = ‘AccessShareLock’) group by a.usename;   

Вывести все таблицы, размером больше 10 Мб

Код SQL

 SELECT tableName, pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) as size

from pg_tables

where tableName not like ‘sql_%’ and pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) like ‘%MB%’;   

Определение размеров таблиц в базе данных PostgreSQL

Код SQL

 SELECT tableName, pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) as size

from pg_tables

where tableName not like ‘sql_%’

order by size;   

Пользователи блокирующие конкретную таблицу

Код SQL

 select a.usename, t.relname, a.current_query, mode from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid inner join pg_stat_all_tables t on t.relid=l.relation where t.relname = ‘tablename’;   

Код SQL

 select relation::regclass, mode, a.usename, granted, pid from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not mode = ‘AccessShareLock’ and relation is not null;   

Запросы с эксклюзивными блокировками

Код SQL

 select a.usename, a.current_query, mode from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where mode ilike ‘%exclusive%’;   

Количество блокировок по пользователям

Код SQL

 select a.usename, count(l.pid) from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not(mode = ‘AccessShareLock’) group by a.usename;   

Количество подключений по пользователям

Код SQL

 select count(usename), usename from pg_stat_activity group by usename order by count(usename) desc;   

Насильственная оптимизация запросов PostgreSQL / Хабр

Что делать, когда имеется приложение с закрытым исходным кодом, которое обращается к БД не самым оптимальным образом? Как потюнить запросы, не меняя приложение, а возможно и саму БД?

Если вы не задавались такими вопросами — вы очень успешный и строгий DBA.

Ну а если задавались — позвольте поделиться страданиями и опытом.

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

Этот раздел можно смело пролистать, если не интересна история вопроса.

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

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

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

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

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

Пока не прошло пару лет и данных хорошенько не поднакопилось.

Тут «внезапно» оказалось, что запросы, которые делает инструментарий используемой системы, не ограничивает выборку по дате (а точнее ограничивает ее не по тому полю, по которому идет разбиение на партиции). Т.е. если мы что-то ищем — поиск идет по всем партициям. Также начали тормозить и операции UPDATE — в условиях там использовался только ID-шник.

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

Конечно, первое что приходит в голову — обратиться к разработчику.

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

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

Нам нужен прокси

Быстрое гугление не нашло четкого ответа на вопрос, как можно переписать поступивший запрос на стороне PostgreSQL или какого-то стороннего софта.

Поэтому (ну и just for fun тоже, конечно) была написана довольно простая софтина, которая принимает подключения от клиентов и проксирует их в PostgreSQL. При этом поступающие SQL-запросы читаются, и, если нужно, заменяются.

Делюсь ссылкой на github

Пока никаких бинарных пакетов не делал, руки не дошли. Но сборка довольно простая. Написано все на C++/Qt, т.к. уже давно на этом пишу…

Конфиг довольно простой:

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

listen_address=0.0.0.0
listen_port=5433

Заставляем нерадивый софт подключаться по указанному адресу вместо прямого подключения к PostgreSQL-серверу.

Прописываем куда пробрасывать соединения (в данном примере прокси размещен на той же машине что и PostgreSQL-сервер):

dst_address=127.0.0.1
dst_port=5432

Задаем регулярное выражение для отлова нужного запроса:

query = SELECT \* FROM tablename WHERE (.+)

Говорим что нужно его переписать:

action = rewrite

Говорим как переписать:

rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1)

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

Можно было бы написать и так:

rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1)

Но тогда запрос не будет оптимальным из-за наличия функции now() — поиск все равно будет осуществляться по всем партициям. Для того чтобы искать только в нужных, необходимо указать значение-константу. Поэтому наш прокси подставляет вместо конструкции $(now-1M) таймстемп уже со сдвигом на один месяц.

Результат (из лога):

ORIGINAL query: SELECT * FROM tablename WHERE id=1;
MODIFIED query (rule 1): SELECT * FROM tablename WHERE (col3 >= '2018-11-12 11:25:23.0+00') AND id=1;

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

Также можно легко вывести все интересующие запросы в лог:

query = .+
action = log

В репозитории есть конфиг с примерами и более детальным описанием.

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

INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31')

Должно быть вот так:

INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date)

К сожалению, пока что наш прокси так писать не умеет :/ но сделать это не сложно. Возможно, в будущем можно будет с его помощью переписывать первый запрос на второй.

Да, важный момент — пока что SSL не поддерживается, так что все соединения от клиентов к прокси будут без шифрования.

Буду рад комментариям и замечаниям.

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

Можно добавить работу и с другими базами.

Тюнинг базы Postgres

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

И так, Вы поставили postgres, у Вас есть приложение (возможно в планах) с определенными требованиями к количеству/размеру таблиц, отношению записи/чтения и т.д. Стандартные настройки, как всегда подходят только для стандартных проектов, а стандартных проектов нет. Поэтому сервер придется настраивать под Ваши требования для обеспечения его оптимальной работы.

Для начала несколько слов о настройках Postgres. Конфигурационный файл обычно лежит по адресу:

/etc/postgresql/8.3/main/postgresql.conf

Естественно это пример для версии 8.3. Любые конфигурационные параметры принадлежат к различным уровням, список которых приведен ниже:

• Postmaster — требует перезапуска сервера
• Sighup — требует только сигнала HUP (работающий сервер перезагрузит конфигурационный файл без прекращения работы)
• User — значение может быть установлено в рамках сессии и актуально только внутри этой сессии
• Internal — устанавливает только во время компиляции, т.о. не изменяемо! (Только для справки)
• Backend — значение должно быть установлено до начала сессии
• Superuser — может быть изменено во время работы сервера, но только из под пользователя с правами superuser

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

На что стоит обратить внимание в настройках, и какие значения стоит поменять:

listen_addresses

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

listen_addresses = '*'

Теперь postgres сможет принимать соединения от удаленных служб по протоколу TCP.

max_connections

Этот параметр определяет максимальное количество одновременных соединений, которые будет обслуживать сервер. В принципе, это число должно определяться исходя из требований к системе. Этот параметр в большей степени влияет на использование ресурсов. Если Вы только стартуете, устанавливайте это значение небольшим (16…32), постепенно увеличивая его (по мере необходимости — такой мерой будет получение ошибок от postgres «too many clients»).

Учтите! На поддержку каждого активного клиента, postgres тратит немалое количество ресурсов, и если Вам необходимо добиться производительности в несколько тысяч активных соединений, то стоит использовать менеджеры соединений, например: Pgpool.

shared_buffers

Этот параметр определяет, сколько памяти будет выделяться postgres для кеширования данных. В стандартной поставке значение этого параметра мизерное — для обеспечения совместимости. В практических условиях это значение следует установить в 15..25% от всей доступной оперативной памяти.

effective_cache_size

Этот параметр помогает планировщику postgres определить количество доступной памяти для дискового кеширования. На основе того, доступна память или нет, планировщик будет делать выбор между использованием индексов и использованием сканирования таблицы. Это значение следует устанавливать в 50%…75% всей доступной оперативной памяти, в зависимости от того, сколько памяти доступно для системного кеша. Еще раз — этот параметр не влияет на выделяемые ресурсы — это оценочная информация для планировщика.

checkpoint_segments

На эту настройку стоит обратить внимание, если у Вас происходит немалое количество записей в БД (для высоконагруженных систем это нормальная ситуация). Postgres записывает данные в базу данных порциями (WALL сегменты) — каждая размером в 16Mb. После записи определенного количества таких порций (определяется параметром checkpoint_segments) происходит чекпойнт. Чекпойнт — это набор операций, которые выполняет postgres для гарантии того, что все изменения были записаны в файлы данных (следовательно при сбое, восстановление происходит по последнему чекпойнту). Выполнение чекпоинтов каждые 16Мб может быть весьма ресурсоемким, поэтому это значение следует увеличить хотя бы до 10.

Для случаев с большим количеством записей, стоит увеличивать это значение в рамках от 32 до 256.

work_mem

Важный параметр для запросов, использующих всевозможные сложные выборки и сортировки. Увеличение его позволяет выполнять эти операции в оперативной памяти, что гораздо более эффективно, чем на диске (еще бы). Будьте внимательны! Этот параметр указывает, сколько памяти выделять на каждую подобную операцию! Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10Мб для этого параметра скушает 100Мб оперативной памяти. Этот параметр стоит увеличивать, если у Вас большое количество памяти в распоряжении. Для старта следует выставить его в 1Мб.

maintainance_work_mem

Этот параметр определяет количество памяти для различных статистических и управляющих процессов (например вакуумизация). Разработчики рекомендуют выделять 128…256Мб под эти нужды.

wal_buffers

Этот параметр стоит увеличивать в системах с большим количеством записей. Значение в 1Мб рекомендуют разработчики postgres даже для очень больших систем.

synchronous_commit

Обратите особое внимание на этот параметр! Он включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера.

Допустим, в Вашей системе не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы. Но жизненно важно обеспечить в несколько раз большую производительность по количеству транзакций в секунду. В этом случае устанавливайте этот параметр в off (отключение синхронной записи).

Самое важное

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

PostgreSQL : Документация: 11: 19.7. Планирование запросов : Компания Postgres Professional

Настройка параметров ядра Linux для оптимизации PostgreSQL

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

SHMMAX / SHMALL

SHMMAX — это параметр ядра, используемый для определения максимального размера одного сегмента разделяемой памяти, который может выделить процесс Linux. До версии 9.2 PostgreSQL использует System V (SysV), для которой требуется настройка SHMMAX. После 9.2 PostgreSQL перешел на разделяемую память POSIX. Таким образом, теперь требуется меньше байтов разделяемой памяти System V.

До версии 9.3 SHMMAX был наиболее важным параметром ядра. Значение SHMMAX выражается в байтах.

Аналогично, SHMALL — это еще один параметр ядра, используемый для определения общесистемного общего объема страниц совместно используемой памяти.Чтобы просмотреть текущие значения SHMMAX, SHMALL или SHMMIN, используйте команду ipcs .

$ ipcs -lm

—— Пределы общей памяти ———
максимальное количество сегментов = 4096
максимальный размер сегмента (кбайт) = 1073741824
максимальный общий объем разделяемой памяти (кбайт) = 17179869184
минимальный размер сегмента (байты) = 1

$ ipcs -M
Статус IPC по состоянию на четверг, 16 августа, 22:20:35 PKT 2018
shminfo:
shmmax: 16777216 (максимальный размер сегмента разделяемой памяти)
shmmin: 1 (минимальный размер сегмента разделяемой памяти)
shmmni: 32 (максимальное количество идентификаторов разделяемой памяти)
shmseg: 8 (максимальное количество сегментов разделяемой памяти на процесс)
shmall: 1024 (максимальный объем разделяемой памяти в страницах)

PostgreSQL использует System V IPC для выделения разделяемой памяти.Этот параметр является одним из самых важных параметров ядра. Когда вы получаете следующие сообщения об ошибках, это означает, что у вас более старая версия PostgreSQL и очень низкое значение SHMMAX. Ожидается, что пользователи изменят и увеличат значение в соответствии с общей памятью, которую они собираются использовать.

Возможные ошибки неправильной конфигурации

Если SHMMAX настроен неправильно, вы можете получить сообщение об ошибке при попытке инициализировать кластер PostgreSQL с помощью команды initdb .

ДЕТАЛИ. Произошел сбой системного вызова shmget (ключ = 1, размер = 2072576, 03600).ПОДСКАЗКА: Эта ошибка обычно означает, что запрос PostgreSQL на сегмент разделяемой памяти превысил параметр SHMMAX вашего ядра. & Nbsp;
Вы можете уменьшить размер запроса или перенастроить ядро ​​на больший SHMMAX. Чтобы уменьшить размер запроса (в настоящее время 2072576 байт),
уменьшить использование разделяемой памяти PostgreSQL, возможно, за счет уменьшения shared_buffers или max_connections.

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

Точно так же вы можете получить ошибку при запуске сервера PostgreSQL с помощью команды pg_ctl .

ДЕТАЛИ: сбойный системный вызов: shmget (ключ = 5432001, размер = 14385152, 03600).

СОВЕТ: эта ошибка обычно означает, что запрос PostgreSQL на сегмент разделяемой памяти превысил параметр SHMMAX вашего ядра.
Вы можете уменьшить размер запроса или перенастроить ядро ​​на больший SHMMAX .; Чтобы уменьшить размер запроса (в настоящее время 14385152 байта),
уменьшить использование разделяемой памяти PostgreSQL, возможно, за счет уменьшения shared_buffers или max_connections.Если размер запроса уже мал, возможно, он меньше, чем параметр SHMMIN вашего ядра,
в этом случае требуется увеличение размера запроса или изменение конфигурации SHMMIN.

В документации PostgreSQL содержится дополнительная информация о конфигурации разделяемой памяти.

Имейте в виду разные определения

Определение параметров SHMMAX / SHMALL немного отличается в Linux и MacOS X. Это определения:

• Linux: kernel.shmmax, kernel.shmall
• MacOS X : Kern.sysv.shmmax, kern.sysv.shmall

Для временного изменения значения можно использовать команду sysctl . Чтобы установить значение навсегда, добавьте запись в /etc/sysctl.conf . Подробности приведены ниже.

# Получить значение SHMMAX
sudo sysctl kern.sysv.shmmax
kern.sysv.shmmax: 4096

# Получите значение SHMALL
sudo sysctl kern.sysv.shmall
kern.sysv.shmall: 4096

# Установите значение SHMMAX
sudo sysctl -w kern.sysv.shmmax = 16777216
kern.sysv.shmmax: 4096 -> 16777216

# Установить значение SHMALL
sudo sysctl -w kern.sysv.shmall = 16777216
kern.sysv.shmall: 4096 -> 16777216

# Получить значение SHMMAX
sudo sysctl ядро.shmmax
kernel.shmmax: 4096

# Получите значение SHMALL
sudo sysctl kernel.shmall
kernel.shmall: 4096

# Установите значение SHMMAX
sudo sysctl -w kernel.shmmax = 16777216
kernel.shmmax: 4096 -> 16777216

# Установить значение SHMALL
sudo sysctl -w kernel.shmall = 16777216
kernel.shmall: 4096 -> 16777216

Помните : чтобы сделать изменение постоянным, добавьте эти значения в /etc/sysctl.conf

Huge Pages

Linux по умолчанию использует страницы памяти размером 4 КБ, BSD имеет Super Pages , а Windows Large Pages .Страница — это фрагмент ОЗУ, который выделяется процессу. В зависимости от требований к памяти процессу может принадлежать более одной страницы. Чем больше памяти требуется процессу, тем больше страниц ему выделяется. ОС поддерживает таблицу распределения страниц по процессам. Чем меньше размер страницы, чем больше таблица, тем больше времени требуется для поиска страницы в этой таблице страниц. Следовательно, большие страницы позволяют использовать большой объем памяти с меньшими накладными расходами; меньше просмотров страниц, меньше ошибок страниц, более быстрые операции чтения / записи через большие буферы.Это приводит к повышению производительности.

PostgreSQL поддерживает большие страницы только в Linux. По умолчанию Linux использует страницы памяти размером 4 КБ, поэтому в случаях, когда операций с памятью слишком много, необходимо установить страницы большего размера. Прирост производительности наблюдается при использовании огромных страниц размером от 2 МБ до 1 ГБ. Размер огромной страницы можно установить во время загрузки. Вы можете легко проверить настройки огромной страницы и ее использование на вашем Linux-компьютере с помощью cat / proc / meminfo | grep -i huge command.

Примечание: это только для Linux, для других ОС эта операция игнорируется $ cat / proc / meminfo | grep -i огромный
AnonHugePages: 0 КБ
ShmemHugeСтраниц: 0 КБ
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 кБ

Результат работы скрипта выглядит как это:

Pid: 12737
VmPeak: 180932 КБ
Огромный размер: 2048 КБ
Установить огромные страницы: 88

Рекомендуемые огромные страницы — 88, поэтому вы должны установить значение 88.

sysctl -w vm.nr_hugepages = 88

Теперь проверьте огромные страницы, вы не увидите, что огромные страницы используются (HugePages_Free = HugePages_Total).

$ cat / proc / meminfo | grep -i огромный
AnonHugePages: 0 КБ
ShmemHugeСтраниц: 0 КБ
HugePages_Total: 88
HugePages_Free: 88
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB