Оптимизация PostgreSQL на Ubuntu: настройки, которые считает pgtune
Оптимизация PostgreSQL после установки — это в основном рассказать серверу, сколько у него памяти и какой диск: настройки по умолчанию рассчитаны на то, чтобы он запустился на любой машине. Калькуляторы вроде pgtune считают это по памяти, ядрам и числу соединений. Ниже — те же настройки с объяснением, что делает каждая, чтобы числа вы выбирали сами. Инструкция для PostgreSQL 16 на Ubuntu 24.04. В 22.04 стоит версия 14: в путях пишите 14 вместо 16, а свою версию и кластер покажет pg_lsclusters.
Где лежат настройки для оптимизации PostgreSQL
Главный файл — /etc/postgresql/16/main/postgresql.conf. В Ubuntu он заканчивается строкой include_dir = 'conf.d', поэтому свой файл в /etc/postgresql/16/main/conf.d/ читается последним и побеждает. ALTER SYSTEM пишет в /var/lib/postgresql/16/main/postgresql.auto.conf, а он сильнее обоих. Держите каждую настройку в одном месте, чтобы потом не гадать, какое значение действует.
sudo nano /etc/postgresql/16/main/conf.d/tuning.confОдни настройки требуют перезапуска, другим хватает перечитать конфигурацию. PostgreSQL сам говорит, каким что, в столбце context: postmaster — перезапуск, sighup — хватит reload, user можно менять даже внутри сеанса. После reload столбец pending_restart покажет, что ещё ждёт перезапуска:
sudo systemctl reload postgresql
sudo -u postgres psql -c "SELECT name, setting, unit, context, pending_restart FROM pg_settings WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'max_connections');"Если после перезапуска сервер не поднялся, читайте конец его журнала. В ошибке будут настройка и файл:
sudo tail -n 50 /var/log/postgresql/postgresql-16-main.logПамять: shared_buffers и effective_cache_size
shared_buffers — собственный кэш таблиц и индексов PostgreSQL, по умолчанию 128 МБ. В документации PostgreSQL разумным началом на выделенном сервере от 1 ГБ названы 25% памяти, а больше 40% вряд ли сработает лучше: PostgreSQL опирается ещё и на файловый кэш системы. Нужен перезапуск. effective_cache_size ничего не выделяет: это подсказка планировщику, сколько данных, скорее всего, лежит в кэше PostgreSQL и системы вместе, и чем она больше, тем дешевле ему кажется чтение по индексу. Обычно начинают с 50–75% памяти. Для сервера с 8 ГБ только под PostgreSQL:
shared_buffers = 2GB
effective_cache_size = 6GBsudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"Если на том же сервере работает сайт, считайте от памяти, которую отдаёте базе, а не от всей. Если PostgreSQL не запускается с could not map anonymous shared memory: Cannot allocate memory, серверу не хватает памяти на shared_buffers: уменьшите его.
work_mem и почему он умножается, maintenance_work_mem
work_mem (по умолчанию 4 МБ) — память на одну сортировку или одну хеш-операцию. Один запрос может выполнять несколько таких сразу, хеш-операции могут брать вдвое больше (hash_mem_multiplier равен 2.0), параллельные процессы берут свою, и всё это может происходить в каждом соединении одновременно. Так что общий расход может быть во много раз больше, чем work_mem × max_connections. Грубая отправная точка: (память − shared_buffers) / (max_connections × 3). Для 8 ГБ, 2 ГБ shared buffers и 100 соединений выходит около 20 МБ, так что 16 МБ — осторожное начало.
maintenance_work_mem (по умолчанию 64 МБ) нужен VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. Одновременно их идёт немного, поэтому он может быть гораздо больше: 5–10% памяти, до 1–2 ГБ. Autovacuum может брать его в каждом своём процессе (по умолчанию их 3), если не задан autovacuum_work_mem.
work_mem = 16MB
maintenance_work_mem = 512MB
log_temp_files = 0sudo systemctl reload postgresqllog_temp_files = 0 пишет в журнал строку с размером каждый раз, когда запрос сбрасывает данные во временный файл на диске. Много таких строк от одного запроса — ему не хватает work_mem. Дайте память только ему, а не всему серверу: SET work_mem = '256MB'; в его сеансе перед большим отчётом.
max_connections и PgBouncer
Каждое соединение в PostgreSQL — отдельный процесс со своей памятью. max_connections по умолчанию 100 и меняется только с перезапуском. Когда все заняты, новые клиенты получают FATAL: sorry, too many clients already. Поднять предел до тысяч — значит потратить память и процессор, а нагруженный сервер часто работает быстрее с меньшим числом активных соединений. Обычное решение — пул соединений: много соединений приложения делят несколько соединений с базой. PgBouncer есть в репозитории Ubuntu:
sudo apt install -y pgbouncer
sudo nano /etc/pgbouncer/pgbouncer.ini[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20В /etc/pgbouncer/userlist.txt каждая строка — имя пользователя и секрет его пароля в двойных кавычках. Секрет покажет эта команда:
sudo -u postgres psql -Atc "SELECT rolpassword FROM pg_authid WHERE rolname = 'myapp';"Потом перезапустите PgBouncer:
sudo systemctl restart pgbouncerНаправьте приложение на порт 6432 вместо 5432. В режиме transaction соединение возвращается в пул после каждой транзакции, поэтому возможности сеанса — SET на весь сеанс, advisory-блокировки между транзакциями, LISTEN — не работают. Проверьте, что они не нужны приложению и его драйверу, иначе ставьте pool_mode = session.
SSD: random_page_cost и effective_io_concurrency
random_page_cost (по умолчанию 4.0) — во сколько раз, по мнению планировщика, случайное чтение дороже последовательного. Значение по умолчанию подходит обычным дискам, когда большая часть данных в кэше. На SSD и NVMe случайное чтение почти такое же дешёвое, и часто ставят значение пониже, например 1.1: тогда планировщик чаще выбирает индексы. effective_io_concurrency (по умолчанию 1) — сколько чтений диск может делать параллельно, и для SSD, по документации, лучшее значение может исчисляться сотнями. Обе настройки применяются по reload.
random_page_cost = 1.1
effective_io_concurrency = 200До и после выполните несколько типичных медленных запросов с EXPLAIN (ANALYZE, BUFFERS) и сравните планы и время. Если планы стали хуже, верните прежнее значение.
Контрольные точки и WAL
Контрольная точка (checkpoint) записывает все изменённые страницы в файлы таблиц. Она наступает раз в checkpoint_timeout (по умолчанию 5 минут) или когда WAL дорастает до max_wal_size (по умолчанию 1 ГБ), смотря что раньше. При большом потоке записей контрольные точки идут слишком часто, и PostgreSQL предупреждает в журнале: checkpoints are occurring too frequently (12 seconds apart) с подсказкой Consider increasing the configuration parameter "max_wal_size". Больший max_wal_size и долгий таймаут растягивают запись, но WAL занимает больше места на диске, а восстановление после сбоя идёт дольше. checkpoint_completion_target уже 0.9, его не трогайте.
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15minsudo systemctl reload postgresql
sudo grep checkpoint /var/log/postgresql/postgresql-16-main.log | tail -n 5log_checkpoints включён по умолчанию с PostgreSQL 15, так что каждая контрольная точка оставляет строку со временем и числом записанных буферов. В большинстве должно быть checkpoint starting: time, а не wal.
Какие запросы съедают время: pg_stat_statements
Настройки дают десятки процентов. Пропущенный индекс может сделать запрос в сто раз медленнее. pg_stat_statements собирает статистику по каждому запросу и входит в пакет PostgreSQL в Ubuntu. Ему нужны строка в настройках и перезапуск, потом расширение в вашей базе:
shared_preload_libraries = 'pg_stat_statements'sudo systemctl restart postgresql
sudo -u postgres psql -d myapp -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
sudo -u postgres psql -d myapp -c "SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time) AS mean_ms, left(query, 80) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"Сверху — запросы, которые в сумме отняли у сервера больше всего времени. Выполните каждый с EXPLAIN (ANALYZE, BUFFERS) и ищите Seq Scan по большим таблицам. Если в shared_preload_libraries уже есть другие модули, допишите этот через запятую, а не заменяйте их.
Если вы работаете и на Mac
Сборка мусора в MacWashing находит кэш всех знакомых ей программ, в том числе спрятанный внутри их данных, а ещё журналы, мусор Xcode и остатки удалённых программ. Она показывает, сколько места занимает каждая находка, и очищает всё за один раз. Всё спорное остаётся без галочки, пока вы не решите.
Бесплатная пробная версия · macOS 14 и новее
Вопросы и ответы
Что менять первым при оптимизации PostgreSQL?
shared_buffers (около 25% памяти), effective_cache_size (50–75%), random_page_cost на SSD, а потом искать медленные запросы через pg_stat_statements. Остальное зависит от нагрузки.
Может, просто взять pgtune?
Для начала годится: он считает те же настройки по памяти, ядрам, типу диска и числу соединений. Но знайте, что значит каждое значение, и проверяйте результат запросами выше: pgtune не знает ваших запросов и того, что ещё работает на сервере.
После правки — restart или reload?
Смотрите столбец context в pg_settings. Для postmaster нужен перезапуск: sudo systemctl restart postgresql Для sighup и user хватит перечитать настройки: sudo systemctl reload postgresql Reload не обрывает соединения.