Данная статья посвящена способам повышения производительности PostgreSQL и EDB Postgres Advanced Server (EPAS) с 10 по 13 версии. Мы начнём с аппаратного обеспечения и будем двигаться вверх по стеку, оставив напоследок SQL-запросы. 

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

Проектирование

При проектировании «голого» сервера для PostgreSQL нужно учитывать несколько моментов: процессор, оперативную память и диск.

CPU

Выбор подходящего процессора может оказаться решающим в производительности PostgreSQL. При работе с большими данными важна скорость, но размер кэша L3 тоже влияет на показатели производительности. 

Что такое кэш L3? Процессоры имеют по крайней мере два кэша: L1 — кэш первого уровня, и L2 — кэш второго уровня. L1 считается самым маленьким и быстрым кэшем; L2 медленнее, но больше по размеру. В современных процессоров L1 и L2 присутствуют на самих ядрах, причём каждое ядро получает собственный кэш. L3 работает медленнее, чем L1 и L2, но является общим для всех доступных ядер. 

Наличие кэша L3 повышает производительность процессора при работе с большим набором данных. Также это полезно для параллельных запросов PostgreSQL.

RAM

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

Disk

Если приложение связано с вводом-выводом (интенсивное чтение и /или запись), выбор более быстрого набора дисков значительно повысит производительность. Доступно множество решений, включая NMVe и SSD-накопители.

Первое правило — отделить диск WAL от диска данных. WAL может оказаться узким местом в базах данных с интенсивной записью, а его хранение на отдельном и быстром диске решит проблему. Используйте RAID 1. Если база данных много пишет — RAID 10.

Отдельные табличные пространства и диски для индексов также позволяют повысить производительность, особенно если PostgreSQL работает на SATA. Но для SSD и NVMe это обычно не требуется. 

Network card

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

«PostgreSQL База»

Виртуальные машины

Есть несколько способов, как повысить производительность PostgreSQL в виртуальных машинах. Первый — закрепить виртуальную машину на определённых процессорах и дисках. Это устранит или ограничит узкие места в производительности, которые могут возникнуть из-за других виртуальных машин, работающих на хосте. Второй — предварительно выделить диски перед установкой. Это предотвратит выделение хостом дискового пространства во время операций с базой данных. Если вы не можете этого сделать, измените два параметра в postgresql.conf:

• Отключите параметр wal_recycle в postgresql.conf. По умолчанию при переработке PostgreSQL переименовывает файлы WAL. Однако в файловых системах с копированием при записи (COW) создание новых файлов WAL быстрее.

• Отключите параметр wal_init_zero в postgresql.conf. По умолчанию пространство WAL выделяется перед вставкой записей WAL. Это замедляет операции WAL в файловых системах COW. Отключение параметра приведёт к отключению этой функции, что позволит виртуальным машинам работать лучше. Если установлено значение off, при создании файла записывается только последний байт, чтобы он имел ожидаемый размер.

Настройка системы

Настройка операционной системы также даёт дополнительные возможности для повышения производительности PostgreSQL. В рамках этой статьи разбираем настройку PostgreSQL для семейства Red Hat Enterprise Linux (RHEL).

Демон tuned

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

Примечание: показанные команды предназначены для RHEL 8. Для RHEL 7 нужно использовать команду yum везде, где в примерах указан dnf.

Обычно демон tuned устанавливается по умолчанию. Если нет, установите его: 

dnf -y install tuned

И включите:

systemctl enable --now tuned

tuned позволяет системным администраторам легко и динамично менять настройки ядра. Больше не нужно вносить изменения в /etc/sysctl — всё делается с помощью tuned.

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

tuned-adm list

Установщик RHEL выбирает значение в зависимости от среды. По умолчанию используется «throughput-performance», целью которого — увеличение пропускной способности. Вы можете запустить следующую команду, чтобы посмотреть, что порекомендует tuned daemon после оценки вашей системы:

tuned-adm recommend

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

tuned-adm active
Current active profile: virtual-guest

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

Сделать это довольно просто. Назовём профиль «edbpostgres» и запустим команды от имени root:

#  This directory name will also be the
#  name of the profile:
mkdir /etc/tuned/edbpostgres
# Create the profile file:
echo "
[main]
summary=Tuned profile for EDB PostgreSQL Instances
[bootloader]
cmdline=transparent_hugepage=never
[cpu]
governor=performance
energy_perf_bias=performance
min_perf_pct=100
[sysctl]
vm.swappiness = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 250
vm.dirty_ratio = 10
vm.dirty_background_ratio = 3
vm.overcommit_memory=0
net.ipv4.tcp_timestamps=0

[vm]
transparent_hugepages=never
" > /etc/tuned/edbpostgres/tuned.conf

Строки, включающие [ and ], называются «настроенными плагинами» и используются для взаимодействия с данной частью системы.

Теперь рассмотрим параметры и значения:

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

• плагин [cpu] включает в себя настройки регулятора процессора и настройки мощности процессора.

• плагин [sysctl] включает значения, которые взаимодействуют с procfs.

• плагины [vm] и [bootloader] включают/отключают huge pages (плагин bootloader помогает взаимодействовать с параметрами командной строки GRUB).

С помощью изменений мы стремимся достичь следующего:

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

• ядро поможет Postgres очищать грязные страницы, уменьшая нагрузку на bgwriter и checkpointer;

• демон pdflush будет запускаться чаще;

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

Для внедрения изменений выполните эту команду:

tuned-adm profile edbpostgres

Полностью отключите huge pages:

grub2-mkconfig -o /boot/grub2/grub.cfg

И перезагрузите систему:

systemctl start reboot.target

Оптимизация файловой системы

Ещё один аспект настройки — диски. PostgreSQL не полагается на метку времени доступа atime для файлов данных, поэтому их отключение сэкономит циклы процессора.

Откройте /etc/fstab и добавьте noatime рядом со значением для диска, на котором хранятся данные PostgreSQL и файлы WAL:

/dev/mapper/pgdata-01-data /pgdata xfs 	defaults,noatime 1 1

Активируйте его:

mount -o remount,noatime,nodiratime /pgdata

Huge pages

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

Включение huge pages в Linux повысит производительность PostgreSQL, так как он будет выделять большие блоки памяти вместе. Есть два аспекта настройки — конфигурация операционной системы и конфигурация PostgreSQL.

Начнём с того, сколько huge pages необходимо вашей системе для PostgreSQL. Когда запускается инстанс PostgreSQL, postmaster создает файл с именем postmaster.pid file в $PGDATA. Вы можете найти pid основного процесса там:

$ head -n 1 $PGDATA/postmaster.pid
1991

Теперь найдите VmPeak для этого инстанса:

$ grep -i vmpeak /proc/1991/status
VmPeak:     8823028 kB

Совет: если используете более одного инстанса PostgreSQL на одном сервере, рассчитайте сумму всех значений VmPeak на следующем шаге.

Подтвердим размер страницы:

$ grep -i hugepagesize /proc/meminfo
Hugepagesize:     2048 kB

Подсчитаем количество huge pages, которые потребуются инстансу(ам):

8823028 / 2048 = 4308.12

Оптимальное количество huge pages чуть больше этого — совсем чуть-чуть. Если вы увеличите значение слишком сильно, процессы, которым нужны small pages и место в ОС, не запустятся. Это может привести к сбою загрузки операционной системы или сбою запуска других инстансов PostgreSQL на том же сервере.

Отредактируем созданный файл tuning.conf и добавим строку в раздел [sysctl]:

vm.nr_hugepages=4500

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

tuned-adm profile edbpostgres

Теперь можем установить:

huge_pages=on

в postgresql.conf и перезапустить PostgreSQL.

Также нужно убедиться, что после перезагрузки настроенная служба запустится раньше, чем служба PostgreSQL. Отредактируем юнит-файл:

systemctl edit postgresql-13.service

И добавим две строки:

[Unit]
After=tuned.service

Запустим, чтобы изменения вступили в силу:

systemctl daemon-reload

PostgreSQL: конфигурация и аутентификация

max_connections

Оптимальное число для max_connections примерно в 4 раза превышает количество ядер CPU. Формула часто даёт небольшое число, которое не оставляет места для ошибки. Рекомендуемое число — GREATEST(4 x CPU cores, 100). Помимо этого следует использовать средство объединения подключений, например pgbouncer.

Важно избегать установки слишком большого значения max_connections, так как это увеличит размер структур данных в Postgres, что может привести к потере циклов CPU. И наоборот: необходимо убедиться, что выделено достаточно ресурсов для поддержки требуемой рабочей нагрузки.

Resource Usage

shared_buffers

Этот параметр имеет наибольшую дисперсию из всех. Некоторые рабочие нагрузки лучше всего работают с маленькими значениями (например, 1 ГБ или 2 ГБ) даже с внушительными объёмами базы данных. Другие рабочие нагрузки требуют больших значений. Оптимально — LEAST(RAM/2, 10GB).

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

work_mem

Рекомендуемое значение для  work_mem — ((Total RAM - shared_buffers)/(16 x CPU cores)). Логика формулы заключается в том, что если у вас так много запросов, что вы рискуете исчерпать память, вы автоматически привязаны к процессору. 

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

maintenance_work_mem

Определяет максимальный объём памяти, который используется для операций обслуживания (VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY) и операций загрузки данных. Может увеличивать объём операций ввода-вывода на серверах баз данных, поэтому выделение большего объёма памяти приводит к более быстрому завершению операций. Значение 1 ГБ — хорошее начало, поскольку это команды, которые явно выполняются администратором баз данных.

effective_io_concurrency

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

Упреждающая журнализация

wal_compression

Когда параметр включен, сервер PostgreSQL сжимает полностраничный образ, записываемый в WAL, при включённом параметре full_page_writes или во время базового резервного копирования. Установите для wal_compression значение «on», так как большинство серверов баз данных, скорее всего, будут ограничены вводом-выводом, а не процессором.

wal_log_hints

Параметр необходим для использования pg_rewind. Включите его.  

wal_buffers

Определяет объём пространства, доступного серверным частям для записи данных WAL в память, чтобы затем WALWriter мог записать их в журнал WAL на диске в фоновом режиме. Сегменты WAL по умолчанию имеют размер 16 МБ каждый, поэтому буферизация сегмента обходится требует немного памяти. Отмечается, что большие размеры буфера потенциально оказывают положительное влияние на производительность при тестировании. Установите для этого параметра значение 64 МБ.

checkpoint_timeout

Более длительные тайм-ауты уменьшают общий объём WAL, но увеличивают время восстановления после сбоя. Рекомендуемое значение — не менее 15 минут, но, в конечном счёте, бизнес-требования определяют, каким оно должно быть.

checkpoint_completion_target

Определяет количество времени, за которое PostgreSQL стремится выполнить контрольную точку. Контрольная точка не обязательно должна приводить к скачку ввода-вывода. Вместо этого она нацелена на распределение операций записи по части значения checkpoint_timeout. Рекомендуемое значение — 0.9.

max_wal_size

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

archive_mode

Для изменения этого параметра требуется перезагрузка, поэтому для него следует установить значение «on», если только вы не уверены, что никогда не будете использовать архивирование WAL.

archive_command

Если включен archive_mode, требуется команда archive_command. Пока архивирование не будет готово к настройке, предлагается значение по умолчанию 'to be configured'.

Примитив ':' сообщает Postgres, что сегмент WAL может быть переработан или удалён. 

to be configured — это набор аргументов, которые будут проигнорированы.

Коротко о главном

Эти инструкции — хорошая отправная точка для большинства рабочих нагрузок OLTP. Мониторинг и корректировка перечисленных параметров нужны для получения максимальной производительности PostgreSQL для вашей рабочей нагрузки. В следующей части мы поговорим о настройке запросов и параметров подключения, конфигурации Autovacuum. А также обсудим необходимость разделения.

«PostgreSQL База»