Настройка PostgreSQL под Linux

Время от времени приходится слышать мнение от некоторых системных администраторов, а также некоторых 1С-разработчиков, что установка, настройка и поддержка PostgreSQL под Linux очень сложна. Что гораздо дешевле покупать лицензии Windows и Microsoft SQL Server, чем нанимать высококвалифицированных администраторов, которые будут администрировать все эти open-source системы.

На наших бизнес-приложениях, использующих в качестве СУБД PostgreSQL, работают 70% крупнейших розничных сетей в Беларуси. Во всех из них одновременно работают от 500 до 1500 пользователей. Размер баз данных на данный момент составляет от 2 до 4ТБ. И все они работают практически со стандартными настройками PostgreSQL на одиночных серверах без какой-либо кластеризации. При этом даже в самых загруженных серверах есть еще значительный резерв по ресурсам для дальнейшего увеличения нагрузки без потребности в кластеризации.

Да, конечно же, многое зависит от запросов к СУБД, и несколькими кривыми запросами можно положить весь сервер. Однако, точно также можно положить и Oracle, и MSSQL. Да, платформа lsFusion, на которой написаны наши приложения, делает много различных оптимизаций запросов конкретно под PostgreSQL. Но вручную SQL-запросы можно оптимизировать еще лучше.

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

Установка PostgreSQL

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

Сейчас у большинства наших клиентов мы используем в качестве ОС CentOS 7. Просто так исторически сложилось, хотя у некоторых используются и Debian, и Ubuntu, и там тоже все работает нормально. Единственная проблема у нас возникала у двух клиентов только с CentOS 8. Там почему-то производительность была процентов на 30 ниже чем с CentOS 7 за счет возникающего высокого system time при активной работе со временными таблицами. Анализ perf и исходников PostgreSQL не дал быстрых результатов, а из-за ограничения времени и давления со стороны клиентов пришлось просто откатиться на CentOS 7, после чего проблема ушла.

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

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

После того, как ОС установлена, и к ней получен SSH-доступ, делается установка как описано на официальном сайте PostgreSQL. В частности, на Redhat-based Linux, которой и является CentOS, в консоле нужно запустить следующие команды :

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
sudo yum install -y postgresql14-server postgresql14-contrib

Эти команды добавляют в общий список yum-репозиторий, и устанавливают программные файлы PostgreSQL.

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

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

localectl set-locale LANG=ru_RU.utf8
timedatectl set-timezone Europe/Moscow

По умолчанию, база данных будет установлена по пути /var/lib/pgsql/14/data. Но если под базу данных был выделен отдельный диск, то можно перед инициализацией кластера задать путь, куда будет установлена база данных следующим образом :

systemctl edit postgresql-14.service

В появившемся окне редактора указать следующие параметры :

[Service]
Environment=PGDATA=/data/14

Если диск с базой был смонтирован по пути /data, то лучше помещать кластер в подкаталог /data/14, чтобы потом было легче делать pg_upgrade базы данных до 15й и последующих версий.

Наконец, создаем сам кластер БД при помощи следующей команды :

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb

Помимо создания самой базы данных эта команда создает в ОС службу postgresql-14. Ее дальше нужно добавить в автозагрузку при помощи команды :

sudo systemctl enable postgresql-14

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

sudo systemctl start postgresql-14
sudo systemctl stop postgresql-14 

Настройка PostgreSQL

Все основные настройки PostgreSQL находятся в двух файлах : postgresql.conf и pg_hba.conf. В первом хранятся настройки самой базы данных, а в во втором - настройки доступа к ней. Изменение параметров осуществляется путем редактирования этих файлов в любом текстовом редакторе. Лично я чаще всего пользуюсь встроенным редактором Midnight Commander.

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

su postgres
psql

А затем уже внутри psql запускается :

SELECT pg_reload_conf();

Основные настройки

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

Первым делом возможно надо увеличить параметр max_connections. По умолчанию, он равен 100, что может быть мало при большом количестве пользователей. Однако слишком большое значение тоже не стоит указывать (так как есть дополнительные расходы). Поскольку lsFusion создает под каждого пользователя по своему выделенному соединению, то мы обычно выставляем :

max_connections = <кол-во пользователей> * 2

PostgreSQL не работает с данными на диске напрямую. Когда ему нужно что-то считать или записать, то он загружает соответствующие страницы с диска в блок памяти, который называется shared buffers. Эта общая память, которая используется одновременно всеми подключениями. Чем выше объем этих буферов, тем меньше будет нагрузка на диск. Для тонкой настройки можно анализировать в динамике, как именно идет ротация этих буферов, но на практике мы обычно выставляем от 30 до 50% всей доступной памяти на сервере :

shared_buffers = 128GB

Помимо этого параметра, обычно мы сразу настраиваем еще три :

temp_buffers = 32MB
work_mem = 32MB

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

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

maintenance_work_mem = 2GB

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

Дополнительные настройки

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

wal_level = minimal
synchronous_commit = off

Если не планируется настройка асинхронной реплики, то обычно понижаем уровень wal_level. Synchronous_commit также отключаем, так как мы не пишем банковские системы. Если сервер упадет (что бывает крайне редко), то нет принципиальной разницы успело пользователю прийти сообщение об успешном сохранении или нет. Зато все сохранения будут работать чуть-чуть быстрее.

checkpoint_timeout = 20min
max_wal_size = 16GB

Под сильной нагрузкой СУБД некоторых наших клиентов успевают записывать по 1ГБ wal'ов в минуту. При значении max_wal_size равном 1ГБ получается, что чекпоинты будут происходить раз в минуту, что не есть хорошо (особенно при включенном full_page_writes). Поэтому обычно повышаем значение, чтобы чекпоинты происходили раз в 20 минут. Соответственно, немного уменьшается нагрузка на диск. Да, будет дольше восстановлении при падении, но это бывает крайне редко.

seq_page_cost = 0.1                     # measured on an arbitrary scale
random_page_cost = 0.1                  # same scale as above

cpu_tuple_cost = 0.05                   # same scale as above
cpu_index_tuple_cost = 0.05             # same scale as above
cpu_operator_cost = 0.01                # same scale as above

Обычно мы значительно понижаем (по сравнению со стандартными) стоимости диска, и в свою очередь увеличиваем стоимости процессорных операций. Так делается потому, что изначально настройки PostgreSQL делались под медленные HDD-диски. У нас же всегда используются SSD-диски в RAID-массивах, где стоимость чтения значительно ниже, а произвольное запись/чтение от последовательной не сильно отличается.

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

Здесь следует отметить, что изменения в параметрах PostgreSQL не всегда приводят к ожидаемому результату в планах запросов. У нас была ситуация, когда простое увеличение параметра work_mem привело к тому, что запрос вместо 20 минут начал выполняться 2 часа. В плане выполнения начал использоваться hash join с предварительным seq scan всей таблицы, которую приходилось читать с диска. Тут кроется одна из основных проблем планирования запросов в PostgreSQL. Планы не учитывают какие данные находятся сейчас в shared buffers, а какие нет. И часто гораздо выгоднее сделать пробег по тем данным, которые в кэше (пусть их и значительно больше), чем читать с диска меньший объем.

Внешний доступ

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

listen_addresses = '*'

После этого потребуется рестарт всей службы PostgreSQL. После этого, нужно добавить в pg_hba.conf IP, с которых принимать подключения (а именно адрес сервера приложений) :

host    all             all             192.168.1.22/32         trust

Вместо trust нужно использовать scram-sha-256, если доступ требуется по паролю.

Дополнительные настройки Linux

Помимо описанных ранее настроек PostgreSQL в серверах с большим количеством памяти мы часто изменяем еще несколько настроек самого CentOS.

Во-первых, в /etc/sysctl.conf устанавливаются следующие параметры :

vm.min_free_kbytes = 4194304
vm.swappiness = 1

Первый параметр устанавливает минимальное количество свободной памяти, которую будет стараться держать ОС. Это нужно, чтобы избавиться фрагментации памяти и высокого system time в определенных случаях (вот тут описана проблема). Swappiness выставляем в 1, так как своп будет очень сильно вредить, а 0, вроде как, не рекомендуется (хотя особой разницы в поведении между 0 и 1 я не замечал).

Далее, в /etc/fstab при подключении диска с базой данных прописываем опции noatime,nodiratime. Мелочь, но хуже не будет. Например :

/dev/sdb /data xfs defaults,noatime,nodiratime 0 0

Также на большом объеме памяти обычно настраиваем использование huge pages. Для этого сначала отключаем THP, а затем добавляем фиксированное количество страниц, которое соответствует размеру shared buffers. В файл /etc/sysctl.conf добавляем :

vm.nr_hugepages = (<shared_buffers> / 2MB) + 3% # например, для 128ГБ = 67502

Ну и наконец, так как мы используем высокопроизводительные SSD диски, то обычно выключаем планировщик ввода/вывода, включая noop или none режим. Есть много способов это сделать, но обычно мы просто настраиваем службу :

[Unit]
Description=Change scheduler

[Service]
Type=simple
ExecStart=/bin/sh -c "echo 'noop' > /sys/block/sdb/queue/scheduler" 

[Install]
WantedBy=multi-user.target

Конфигурация сервера

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

Например, один из наших клиентов использует сервер с двумя процессорами Intel Gold с 24 ядрами в каждом (что дает 96 виртуальных ядер) и 256ГБ памяти. В сервер напрямую через PCI express воткнуты 4 NVME диска по 3ТБ каждый, которые собраны в RAID-10 объемом около 5.8ТБ. Сейчас база данных там занимает около 3ТБ, с которой работают около 1000 одновременных пользователей. Рыночная стоимость такого сервера на данный момент составляет около 12K$ (и еще 12К$ стоят диски такого размера).

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

В пиковые моменты времени скорость чтения на таком сервере достигает 1.5ГБайт/секунду без существенного увеличения времени waiting :

С таким запасом производительности сервера будет достаточно при увеличении количества пользователей в 2-3 раза, прежде чем нужно будет начинать использовать кластеризацию.

Заключение

Мы активно используем PostgreSQL в нагруженных приложениях уже более 5 лет. Мы поддерживаем базы данных у нескольких десятков клиентов (как мелких на пару десятков пользователей, так и достаточно крупных). За все это время было много различных нештатных ситуаций, связанных с аварийными отключениями виртуальных машин и серверов. И ни разу у нас не было потерь данных. PostgreSQL всегда запускался, воспроизводил wal с последнего checkpoint и прекрасно продолжал работу. Один раз системный администратор клиента случайно удалил диск, на котором хранился целый tablespace от базы данных. При этом PostgreSQL продолжил работу просто без этих таблиц. Но даже тогда после определенных танцев с бубнами удалось восстановить базу данных в нормальное состояние, а данные пропавших таблиц достать из копии.

PostgreSQL постоянно развивается. Приблизительно каждый код выходит новый релиз с новыми возможностями. Последним значимым для нас была 13я версия (все крупные клиенты мы уже давно перевели на нее). В ней, в частности, значительно улучшили работу индексов с повторяющимися значениями. В результате размер наших баз данных сократился на 10-15 процентов.

Резюмируя, хочу отметить, что PostgreSQL прекрасно подходит для бизнес-приложений, легка в настройке, и предоставляет отличную альтернативу коммерческим СУБД.