Описание:

Сегодня мы с вами посмотрим на глявные параметры конфигурации PostgreSQL. Наверняка вы уже пробовали установить PostgreSQL на свой ноут? Или на самую дешевую (или даже бесплатную) виртуалочку, типа 1-vCPU/1Gb-RAM/20Gb-HDD? Сработало? — Без проблем! И даже заработало, можно посоздавать базы и таблицы, побаловаться с запросами и прочее.

Это все потому, что настройки по умолчанию (это те, которые можно найти в файле /etc/postgresql//main/postgresql.conf) сделаны так, чтобы можно было запуститься на самой «дохленькой» конфигурации. И использовать их для серьезных нагрузок не рекомендуется и даже вредно. Давайте рассмотрим, что можно покрутить в настройках для получения максимальной производительности сервера баз данных.

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

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

Наиболее важные параметры

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

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

Общий буфер сервера: shared_buffers — этот параметр задает размер буферного кеша, который используется для того, чтобы работа с наиболее часто используемыми данными происходила в оперативной памяти и не требовала избыточных обращений к диску.

По умолчанию значение параметра составляет 128MB. Это весьма мало, и для полноценной работы значение параметра следует увеличить. Настройку можно начинать с 25 % от общего объема ОЗУ сервера. Для тонкой настройки можно использовать следующий подход: установите заведомо большее значение для shared_buffers и потестируйте базу при обычной нагрузке. Проверяйте использование разделяемой памяти при помощи ipcs или других утилит. Рекомендуемое значение параметра будет примерно в 1,2-2 раза больше, чем максимум использованной памяти. Но так как PostgreSQL использует и кеш операционной системы, выделять для shared_buffers более 40% ОЗУ вряд ли будет полезно. При увеличении shared_buffers обычно требуется соответственно увеличить max_wal_size, чтобы растянуть процесс записи большого объема новых или измененных данных на более продолжительное время. Обратите внимание, что память под буфер выделятся при запуске сервера и ее объем при работе не изменяется.

Значение параметра effective_cache_size не влияет на выделение памяти, но подсказывает PostgreSQL, на какой общий размер кеша рассчитывать, включая кеш операционной системы. Чем выше это значение, тем большее предпочтение отдается индексам. Начать можно с 50–75 % от объема ОЗУ.

Параметр work_mem определяет объем памяти, выделяемый для выполнения таких операций, как сортировка или построение хеш-таблиц при выполнении соединения. Признаком того, что памяти недостаточно, является активное использование временных файлов и, как следствие, уменьшение производительности. Значение по умолчанию — 4 МБ, в большинстве случаев его стоит увеличить как минимум в несколько раз, но так, чтобы не выйти при этом за общий размер оперативной памяти сервера.

Параметр maintenance_work_mem определяет размер памяти, выделяемой служебным процессам. Его увеличение может ускорить построение индексов, работу процесса очистки (vacuum). Обычно устанавливается значение, в несколько раз превышающее значения work_mem. Учтите, что, когда выполняется автоочистка, этот объем может быть выделен autovacuum_max_workers, поэтому не стоит устанавливать значение по умолчанию слишком большим. Возможно, будет лучше управлять объемом памяти для автоочистки отдельно, изменяя autovacuum_work_mem.

Например, при ОЗУ 32 ГБ можно начать с настройки:

shared_buffers = '8GB' effective_cache_size = '24GB' work_mem = '128MB' maintenance_work_mem = '512MB'

Отношение значений двух параметров random_page_cost и seq_page_cost должно соответствовать отношению скоростей произвольного и последовательного доступа к диску. По умолчанию предполагается, что произвольный доступ в 4 раза медленнее последовательного в расчете на обычные HDD-диски. Но для дисковых массивов и SSD-дисков значение random_page_cost надо уменьшить (но никогда не изменяйте значение seq_page_cost, равное 1).

Например, для дисков SSD будет адекватна настройка:

random_page_cost = 1.2

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

• уменьшить значение autovacuum_vacuum_scale_factor до 0.01, чтобы очистка выполнялась чаще и меньшими порциями;
• увеличить значение autovacuum_vacuum_cost_limit (либо уменьшить autovacuum_vacuum_cost_delay) в 10 раз, чтобы очистка выполнялась быстрее (для версий до 12).

Не менее важной является настройка процессов, связанных с обслуживанием буферного кеша и журнала предзаписи, но и она зависит от конкретного приложения. Начните с установки checkpoint_completion_target = 1 (чтобы сгладить нагрузку), увеличения checkpoint_timeout с 5 минут до 30 (чтобы уменьшить накладные расходы на выполнение контрольных точек) и пропорционального увеличения max_wal_size (с той же целью).

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

В этом случае нет необходимости сбрасывать на диск изменения данных при каждом успешном завершении транзакции: в случае сбоя БД может быть восстановлена по записям в журнале. Таким образом, данные из буферов сбрасываются на диск при проходе контрольной точки: либо при заполнении нескольких (параметр checkpoint_segments, по умолчанию 3) сегментов журнала транзакций, либо через определенный интервал времени (параметр checkpoint_timeout, измеряется в секундах, по умолчанию 300).

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

Наиболее радикальное из возможных решений по увеличению производительности — выставить значение No параметру fsync. При этом записи в журнале транзакций не будут принудительно сбрасываться на диск, что даст большой прирост скорости записи. Учтите: вы жертвуете надежностью, в случае сбоя целостность базы будет нарушена, и ее придется восстанавливать из резервной копии!

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

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

Вредные советы

• Выключение автоочистки (autovacuum = off)

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

• Выключение синхронизации с диском (fsync = off)

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

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

• Не обновляется с 2009 года (расчеты стали уже немного не актуальны).
• Нужно скачивать или устанавливать с пакетов (не проблема, во многих Linux-дистрибутивах она есть).

Поэтому добрые люди создали онлайн-версию PgTune. Основные плюсы:

• Обновлен подсчет конфига.
• Не нужно ничего ставить или качать.
• Работает в offline режиме и из-за этого может использоваться как mobile app.

Эталонные тесты (pgbench)

Сконфигурировав сервер базы данных, можно прогнать эталонные тесты, например с помощью программы pgbench, и определить количество транзакций в секунду (transactions per second – TPS).

pgbench – простая программа, которая выполняет заранее подготовленный набор SQL-команд и вычисляет среднюю скорость транзакции (количество транзакций в секунду). Она является реализацией стандарта TPC-B Совета по производительности транзакционной обработки (Transaction Processing Performance Council – TPC). Программу pgbench можно также настроить с помощью скриптов. В общем случае клиент эталонного тестирования следует запускать в одиночестве, чтобы не отвлекать время и память тестируемого сервера на другие задачи. Кроме того, pgbench рекомендуется прогонять несколько раз с различными нагрузками и конфигурационными параметрами. Наконец, помимо pgbench, существуют открытые реализации и других эталонных стандартов, например TPC-C и TPC-H. Порядок вызова pgbench такой:

pgbench [options] dbname. Флаг -i служит для создания в базе данных тестовых таблиц, а флаг -s определяет коэффициент масштабирования базы, или количество строк в каждой таблице.

Рассмотрим установку pgbench на примере CentOS 7.

sudo yum install postgresql13-contrib -y
cp /usr/pgsql-13/bin/pgbench /usr/bin

Вывод pgbench с коэффициентом масштабирования по умолчанию на виртуальной машине с одним процессором выглядит примерно так:

$pgbench -i test_database
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.71 s, remaining 0.00 s). vacuum...

О других флагах команды можно прочитать на страницах руководства (pg-bench --help).

Полезные ссылки:

• PostgreSQL: настройка производительности
• Рецепты производительности
• ipcs

Правила выполнения задания:

1. Время создания окружения занимает до 5 минут.
2. После нажатия кнопки «Начать выполнение» для вас будет создано окружение для выполнения задания, состоящее из одной или более виртуальнах машин с Ubuntu Linux. К ним будут выданы IP, логин и пароль для доступа по SSH, а также при необходимости — дополнительных ресурсов.
3. Также, если необходимо, вам могут быть выданы переменные, которые в задании указаны в фигурных скобках, — их надо будет подставить при выполнении задания (например, ${base_domain}).
4. После выполнения всех пунктов задания нажмите кнопку «Проверить выполнение», и в течение 3-5 минут скрипт проверит выполнение всех условий и выставит вам оценку.
5. В случае, если вы что-то забыли, можно исправить ошибку и отправить на проверку повторно (нажав кнопку «Проверить выполнение»).
6. После получения удовлетворительной оценки нажмите кнопку «Завершить задание», чтобы созданное окружение уничтожилось и вы могли приступить к следующему заданию.
7. Если у вас закончилось время (истек таймер) — окружение будет автоматически уничтожено и вам придется начать выполнение заново.
8. Также, если вы успешно сдали задание, но у вас остались вопросы — вы всегда сможете задать их куратору после проверки (используя кнопку «Задать вопрос куратору») или в чате практикума в любое удобное для вас время. Обращаем внимание — кураторы проверяют вопросы в течение 24 часов.

Задание:

1. Установить postgresql 13 через пакетный менеджер.
2. Добавьте в PostgreSQL пользователя root c правами SUPERUSER.
3. Установить pgbench.
4. Создайте бд pgbench.
5. Создайте таблицы для работы pgbench с помощью:

pgbench --initialize --scale=100 pgbench

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

psql -U postgres << EOF
ALTER SYSTEM RESET ALL;
ALTER SYSTEM SET fsync to off;
EOF

psql -U postgres << EOF
ALTER SYSTEM RESET ALL;
ALTER SYSTEM SET synchronous_commit to off;
ALTER SYSTEM SET commit_delay to 100000;
EOF

EXPLAIN ANALYZE SELECT n FROM generate_series(1,5) as foo(n) order by n;