Оптимизация
Для оптимизации PostgreSQL нужно отредактировать некоторые параметры его работы. Редактировать можно основной конфиг (postgresql.conf), который для 14 версии находится в этом каталоге — /etc/postgresql/14/main/ . Хотя в некоторых случаях конфиг может быть и в другом месте.
Лучше создать новый конфиг в каталоге /etc/postgresql/14/main/conf.d/ . Этот конфиг можете назвать как угодно, но расширение файла должно быть .conf, например postgresql.custom.conf . В этом случае основной конфиг не будет изменяться, но параметры указанные в файле postgresql.custom.conf будут приоритетнее. И в случае чего, вы можете просто очистить postgresql.custom.conf и вернуть настройки по умолчанию.
Потребления памяти
Основной особенностью работы с памятью является то, что вся работа с базами и таблицами ведётся в специальном буфере (shared_buffers). Для этого PostgreSQL при старте выделяет себе shared_buffers. Но таблички загружаются в shared_buffers не просто так, они туда попадают только если они требуются для работы. То есть нужна для работы какая-та табличка, PostgreSQL ищет её в shared_buffers, если не найдет то начинает искать её на диске.
Процессы, которые работают с данными, например выполняют какие-то запросы, потребляют память под названием work_mem. При этом, такие процессы, запрашивая данные из shared_buffers, а обрабатывают их в work_mem.
Но есть в PostgreSQL и служебные процессы, которые для своих операций выделяют себе maintenance_work_mem.
shared_buffers
Память для хранения данных (баз и их табличек).
Во многих статьях пишут, что нужно установить этот параметр в 15% — 40% от всей доступной памяти.
shared_buffers = 1024MB
work_mem
Максимальный лимит памяти, который выделяется для обработки запросов. То есть, каждый запрос сможет получить этот объём памяти.
work_mem = 16MB
Если для запроса не хватит work_mem памяти, то запрос будет использовать временные файлы на диске, поэтому стоит следить за их количеством. Это можно делать с помощью следующего запроса:
select sum(temp_files) as temp_files, pg_size_pretty(sum(temp_bytes)) as temp_size from pg_stat_database;
Если временных файлов нет, значит всё хорошо.
Есть формула по которой можно вычислить максимальное значение для work_mem: (вся память — shared_bufer) / максимальное_количество_соединений.
С помощью такого запроса можно определить количество соединений в данный момент:
SELECT COUNT(*) as connections,
backend_type
FROM pg_stat_activity
where state = 'active' OR state = 'idle'
GROUP BY backend_type
ORDER BY connections DESC;
maintenance_work_mem
В PostgreSQL одни процессы выполняют запросы клиентов, а другие обслуживают базу данных. Например, выполняют VACUUM и ANALYZE. Параметр maintenance_work_mem как раз задаёт максимальный объём памяти для операций обслуживания.
Так как служебные операции обычно не выполняются параллельно, то можно указать больше чем в work_mem.
maintenance_work_mem = 512MB
Эффективность планирования
Когда мы пишем запрос на SQL, мы говорим что хотим получить, а не как это сделать. За то «как это сделать» отвечает планировщик основываясь на статистике. Статистика включает в себя: сколько у нас таблиц, сколько в них строчек, сколько они занимают страниц, как распределены данные в столбцах и тому подобное. Планировщик решает с каких таблиц начать выполнение запроса, какие условия и в каком порядке применять и так далее. Планировщик подготавливает план выполнения.
Также, при планировании запроса, сервер должен определить, какие таблицы нужно сканировать целиком (предпочтительно, если таблица находится на диске), а в каких использовать сканирование по индексу (предпочтительно, если таблица находится в оперативной памяти).
effective_cache_size
Этот параметр влияет на планировщик запросов, а не ограничивает дисковый кэш. Чем выше, тем больше вероятность, что будет применяться сканирование по индексу. Чем ниже, тем более вероятно, что будет выбрано последовательное сканирование.
В общем, если памяти хватает, то задавать это значение нужно побольше. Вы как бы говорите серверу, у меня много памяти, и таблички скорее всего уже загружены в память. Это приведёт к применению сканированию по индексу для каждого запроса.
Считается что нужно выделять от 50% до 75% доступной памяти. Допустим у вас на сервере 30GB, тогда делаем 15GB.
effective_cache_size = 4GB
random_page_cost
Как я уже писал, PostgreSQL при построении плана запроса может выбрать либо последовательное сканирование всей таблицы, либо рандомное сканирование по индексу. Если seek time дисковой подсистемы большое, то дешевле использовать последовательное сканирование. А если маленькое, то лучше использовать сканирование по индексу. Грубо говоря, если у вас диск очень быстрый, то даже с него лучше выполнять сканирование по индексу.
Seek Time — время позиционирования на нужную дорожку. На SSD дисках нет механического позиционирования, поэтому Seek Time маленькое.
Сам сервер Postgres не знает, какая у нас дисковая подсистема. И с помощью этого параметра мы ему об этом сообщаем.
| random_page_cost | Дисковая система |
|---|---|
|
4.0 |
HDD |
|
1.5 - 2.0 |
RAID из HDD
|
|
1.1 - 1.3 |
SSD |
|
0.1 |
NVMe |
random_page_cost = 1.5
Различные ограничения
max_connections
Определяет максимальное число одновременных подключений к серверу.
max_connections = 50
temp_file_limit
Задаёт максимальный объём дискового пространства, который сможет использовать один процесс для временных файлов, например, при сортировке и хешировании, или для сохранения удерживаемого курсора. Транзакция, которая попытается превысить этот предел, будет отменена.
Этот параметр ограничивает общий объём, который могут занимать в момент времени все временные файлы, задействованные в данном процессе PostgreSQL. Следует отметить, что при этом учитывается только место, занимаемое явно создаваемыми временными таблицами; на временные файлы, которые создаются неявно при выполнении запроса, это ограничение не распространяется.
temp_file_limit = 10GB
idle_in_transaction_session_timeout
Время простоя открытой транзакции (в миллисекундах), при превышении которого будет завершена сессия с этой транзакцией.
idle_in_transaction_session_timeout = 10s
lock_timeout
Определяет, сколько ждать освобождения блокировки. Заблокированными могут быть: таблица, индекс, строка или другие объекты базы данных. Если время ожидания для какой-либо операции истекло, то она прерывается.
lock_timeout = 1s
statement_timeout
Максимальная длительность выполнения команды, при превышении которой команда прерывается.
statement_timeout = 60s
Логирование
log_min_duration_statement
Параметр времени, который указывает, как долго должен выполняться запрос, прежде чем он будет отправлен в логи. Чтобы получить все запросы, установите значение 0, но помните, что это может вызвать много операций ввода-вывода.
log_min_duration_statement = 200ms
Итоговый конфиг
shared_buffers = 1024MB
work_mem = 16MB
maintenance_work_mem = 512MB
effective_cache_size = 4GB
random_page_cost = 1.5
max_connections = 50
temp_file_limit = 10GB
idle_in_transaction_session_timeout = 10s
lock_timeout = 1s
statement_timeout = 60s
log_min_duration_statement = 200ms
