Оптимизация Время чтения: 2 мин. 390 просмотров Для оптимизации 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 Источники:Тык Тык Тык Метки:PostgreSQL Вперёд – PostgreSQL Установка и настройка