Конфигурационные параметры
max_wal_size
Максимальный размер, до которого может вырастать WAL во время автоматических контрольных точек. Это мягкий предел; размер WAL может превышать max_wal_size при особых обстоятельствах, например при большой нагрузке, сбое в archive_command/archive_library или при большом значении wal_keep_size. Если это значение задаётся без единиц измерения, оно считается заданным в мегабайтах. Значение по умолчанию — 1 ГБ.
min_wal_size
Пока WAL занимает на диске меньше этого объёма, старые файлы WAL в контрольных точках всегда перерабатываются, а не удаляются. Это позволяет зарезервировать достаточно места для WAL, чтобы справиться с резкими скачками использования WAL, например, при выполнении больших пакетных заданий
effective_cache_size
Определяет представление планировщика об эффективном размере дискового кеша, доступном для одного запроса. Это представление влияет на оценку стоимости использования индекса; чем выше это значение, тем больше вероятность, что будет применяться сканирование по индексу, чем ниже, тем более вероятно, что будет выбрано последовательное сканирование. При установке этого параметра следует учитывать и объём разделяемых буферов Postgres Pro, и процент дискового кеша ядра, который будут занимать файлы данных Postgres Pro, хотя некоторые данные могут оказаться и там, и там. Кроме того, следует принять во внимание ожидаемое число параллельных запросов к разным таблицам, так как общий размер будет разделяться между ними. Этот параметр не влияет на размер разделяемой памяти, выделяемой Postgres Pro, и не задаёт размер резервируемого в ядре дискового кеша; он используется только в качестве ориентировочной оценки. При этом система не учитывает, что данные могут оставаться в дисковом кеше от запроса к запросу. Если это значение задаётся без единиц измерения, оно считается заданным в блоках (размер которых равен BLCKSZ байт, обычно это 8 КБ). Значение по умолчанию — 4 гигабайта (4GB).
work_mem
Задаёт базовый максимальный объём памяти, который будет использоваться во внутренних операциях при обработке запросов (например, для сортировки или хеш-таблиц), прежде чем будут задействованы временные файлы на диске. Если это значение задаётся без единиц измерения, оно считается заданным в килобайтах. Значение по умолчанию — четыре мегабайта (4MB). Заметьте, что в сложных запросах одновременно могут выполняться несколько операций сортировки и хеширования, и при этом примерно этот объём памяти может использоваться в каждой операции, прежде чем данные начнут вытесняться во временные файлы. Кроме того, такие операции могут выполняться одновременно в разных сеансах. Таким образом, общий объём памяти может многократно превосходить значение work_mem; это следует учитывать, выбирая подходящее значение. Операции сортировки используются для ORDER BY, DISTINCT и соединений слиянием. Хеш-таблицы используются при соединениях и агрегировании по хешу, мемоизации узлов, а также обработке подзапросов IN с применением хеша.
Операции вычисления хеша обычно более требовательны к памяти, чем равнозначные им операции сортировки. Поэтому ограничение памяти для хеш-таблиц определяется произведением work_mem и hash_mem_multiplier и может превышать обычный базовый объём work_mem.
temp_buffers
Задаёт максимальный объём памяти, выделяемой для временных буферов в каждом сеансе. Эти существующие только в рамках сеанса буферы используются исключительно для работы с временными таблицами. Если это значение задаётся без единиц измерения, оно считается заданным в блоках (размер которых равен BLCKSZ байт, обычно это 8 КБ). Значение по умолчанию — 8 мегабайт (8MB). (Если BLCKSZ отличен от 8 КБ, значение по умолчанию корректируется пропорционально.) Этот параметр можно изменить в отдельном сеансе, но только до первого обращения к временным таблицам; после этого изменения его значения не будут влиять на текущий сеанс.
Сеанс выделяет временные буферы по мере необходимости до достижения предела, заданного параметром temp_buffers. Если сеанс не задействует временные буферы, то для него хранятся только дескрипторы буферов, которые занимают около 64 байт (в количестве temp_buffers). Однако если буфер действительно используется, он будет дополнительно занимать 8192 байта (или BLCKSZ байт, в общем случае).
random_page_cost
Задаёт приблизительную стоимость чтения одной произвольной страницы с диска. Значение по умолчанию равно 4.0. Это значение можно переопределить для таблиц и индексов в определённом табличном пространстве, установив одноимённый параметр табличного пространства (см. ALTER TABLESPACE).
При уменьшении этого значения по отношению к seq_page_cost система начинает предпочитать сканирование по индексу; при увеличении такое сканирование становится более дорогостоящим. Оба эти значения также можно увеличить или уменьшить одновременно, чтобы изменить стоимость операций ввода/вывода по отношению к стоимости процессорных операций, которая определяется следующими параметрами.
Произвольный доступ к механическому дисковому хранилищу обычно гораздо дороже последовательного доступа, более чем в четыре раза. Однако по умолчанию выбран небольшой коэффициент (4.0), в предположении, что большой объём данных при произвольном доступе, например, при чтении индекса, окажется в кеше. Таким образом, можно считать, что значение по умолчанию моделирует ситуацию, когда произвольный доступ в 40 раз медленнее последовательного, но 90% операций произвольного чтения удовлетворяются из кеша.
Если вы считаете, что для вашей рабочей нагрузки процент попаданий не достигает 90%, вы можете увеличить параметр random_page_cost, чтобы он больше соответствовал реальной стоимости произвольного чтения. И напротив, если ваши данные могут полностью поместиться в кеше, например, когда размер базы меньше общего объёма памяти сервера, может иметь смысл уменьшить random_page_cost. С хранилищем, у которого стоимость произвольного чтения не намного выше последовательного, как например, у твердотельных накопителей, так же лучше выбрать меньшее значение random_page_cost, например 1.1.
effective_io_concurrency
Задаёт допустимое число параллельных операций ввода/вывода, которое говорит Postgres Pro о том, сколько операций ввода/вывода могут быть выполнены одновременно. Чем больше это число, тем больше операций ввода/вывода будет пытаться выполнить параллельно Postgres Pro в отдельном сеансе. Допустимые значения лежат в интервале от 1 до 1000, а нулевое значение отключает асинхронные запросы ввода/вывода. В настоящее время этот параметр влияет только на сканирование по битовой карте.
Для магнитных носителей хорошим начальным значением этого параметра будет число отдельных дисков, составляющих массив RAID 0 или RAID 1, в котором размещена база данных. (Для RAID 5 следует исключить один диск (как диск с чётностью).) Однако если база данных часто обрабатывает множество запросов в различных сеансах, и при небольших значениях дисковый массив может быть полностью загружен. Если продолжать увеличивать это значение при полной загрузке дисков, это приведёт только к увеличению нагрузки на процессор. Диски SSD и другие виды хранилища в памяти часто могут обрабатывать множество параллельных запросов, так что оптимальным числом может быть несколько сотен.
Асинхронный ввод/вывод зависит от эффективности функции posix_fadvise, которая отсутствует в некоторых операционных системах. В случае её отсутствия попытка задать для этого параметра любое ненулевое значение приведёт к ошибке. В некоторых системах (например, в Solaris), эта функция присутствует, но на самом деле ничего не делает.
Значение по умолчанию равно 1 в системах, где это поддерживается, и 0 в остальных. Это значение можно переопределить для таблиц в определённом табличном пространстве, установив одноимённый параметр табличного пространства (см. ALTER TABLESPACE).
commit_delay
Параметр commit_delay добавляет паузу перед собственно выполнением сохранения WAL. Эта задержка может увеличить быстродействие при фиксировании множества транзакций, позволяя зафиксировать большее число транзакций за одну операцию сохранения WAL, если система нагружена достаточно сильно и за заданное время успевают зафиксироваться другие транзакции. Однако этот параметр также увеличивает задержку максимум до commit_delay при каждом сохранении WAL. Эта задержка окажется бесполезной, если никакие другие транзакции не будут зафиксированы за это время, поэтому она добавляется, только если в момент запроса сохранения WAL активны как минимум commit_siblings других транзакций. Кроме того, эти задержки не добавляются при выключенном fsync. Если это значение задаётся без единиц измерения, оно считается заданным в микросекундах. По умолчанию значение commit_delay равно нулю (задержка отсутствует).
Вопрос нейросети
Какое наиболее эффективное значение для конфигурационного параметра XXX для СУБД CPU=8 и RAM=8GB при нагрузке генерируемой pgbench и экспоненциальном росте нагрузки от 5 до 100 одновременных соединений и значении параметра shared_buffers=2GB ?
max_wal_size
Для конфигурации PostgreSQL с 8 CPU и 8 GB RAM, нагрузкой pgbench и экспоненциальным ростом соединений от 5 до 100, при заданном shared_buffers=2GB, оптимальное значение max_wal_size = 4 GB обосновывается следующими ключевыми факторами:
1. Расчёт на основе доступной памяти
- Рекомендация PostgreSQL: max_wal_size должен составлять ~50% от доступной RAM для интенсивных записей.
- Расчёт:
8 GB RAM×0.5=4 GB8 GB RAM×0.5=4 GB. - Обоснование:
При shared_buffers=2GB (25% RAM) оставшиеся 6 GB делятся между кэшем ОС и WAL. Значение 4 GB гарантирует, что WAL не исчерпает память и не вызовет своппинг.
min_wal_size
Для конфигурации PostgreSQL 8 CPU, 8 GB RAM, с shared_buffers=2GB под нагрузкой pgbench (5→100 соединений), оптимальное значение min_wal_size = 1 GB обосновывается следующими ключевыми факторами:
1. Расчёт на основе max_wal_size (рекомендуемое значение 4 GB)
- Расчёт:
4 GB×0.25=1 GB4 GB×0.25=1 GB. - Исключение:
При экспоненциальном росте нагрузки допустимо увеличение до 30% (1.2 GB), но 1 GB сохраняет баланс.
effective_cache_size
Для конфигурации 8 CPU, 8 GB RAM с shared_buffers=2GB под нагрузкой pgbench (экспоненциальный рост 5→100 соединений) оптимальное значение effective_cache_size = 6 GB. Детальное обоснование:
1. Расчёт и рекомендации
- Формула:
effective_cache_size = (Total RAM - shared_buffers - OS резерв) × 0.8 - Расчёт для 8 GB RAM:
8 GB RAM
- 2 GB (shared_buffers)
- 0.5 GB (резерв под ОС и процессы)
= 5.5 GB доступно для кэша
- Итог: 5.5 GB × 0.8 ≈ 6 GB (округляем для простоты).
2. Влияние на pgbench при 100 соединениях
- Для индексных сканов (ключевых в TPC-B):
Планировщик выбирает индексный скан, если:
index_size < effective_cache_size × random_page_cost
При random_page_cost=1.1 (SSD) и effective_cache_size=6 GB индексные сканы используются агрессивно.
work_mem
Для конфигурации PostgreSQL 8 CPU, 8 GB RAM, с shared_buffers=2GB под нагрузкой pgbench (экспоненциальный рост 5→100 соединений), оптимальное значение work_mem = 16–32 MB обосновывается следующими факторами:
1. Расчёт на основе доступной памяти
- Доступная RAM:
8 GB RAM - 2 GB (shared_buffers) - 0.5 GB (резерв ОС) ≈ 5.5 GB. - Потенциальное потребление при 100 соединениях:
100 соединений × work_mem × hash_mem_multiplier (учёт хеш-операций) 16.
При work_mem=32 MB: 100 × 32 MB × 2 (hash_mem_multiplier) = 6.4 GB — близко к пределу.
При work_mem=16 MB: 100 × 16 MB × 2 = 3.2 GB — безопасно 36.
2. Характер нагрузки pgbench
- TPC-B (OLTP): Короткие транзакции с простыми операциями (SELECT/UPDATE). Сложные сортировки или хеш-соединения редки 48.
- Риск переполнения: При work_mem < 16 MB возможны операции с диском (external merge Disk в EXPLAIN ANALYZE), что замедляет выполнение .
temp_buffers
Для конфигурации PostgreSQL 8 CPU, 8 GB RAM с shared_buffers=2GB под нагрузкой pgbench (экспоненциальный рост 5→100 соединений) оптимальное значение temp_buffers = 32–64 MB обосновывается следующими факторами:
🔍 1. Расчёт на основе доступной памяти
- Доступная RAM:
8 GB RAM - 2 GB (shared_buffers) - 0.5 GB (резерв ОС) ≈ 5.5 GB. - Потребление на соединение:
temp_buffers выделяется на каждую сессию, поэтому суммарное потребление может достигать:
100×64 MB=6.4 GB100×64 MB=6.4 GB.
Риск: При 64 MB возможен дефицит памяти в пике.
random_page_cost
Для конфигурации PostgreSQL 8 CPU, 8 GB RAM, с shared_buffers=2GB под нагрузкой pgbench (экспоненциальный рост 5→100 соединений), оптимальное значение random_page_cost = 1.1 обосновывается следующими ключевыми факторами:
1. Рекомендации для SSD/NVMe
- Современные SSD:
Значение по умолчанию (4.0) завышено и не отражает реальную производительность SSD/NVMe. - Для pgbench (OLTP):
1.1 — баланс между стимулированием индексных сканов и учётом реальных задержек .
effective_io_concurrency
Для конфигурации PostgreSQL 8 CPU, 8 GB RAM с shared_buffers=2GB под нагрузкой pgbench (экспоненциальный рост 5→100 соединений) оптимальное значение effective_io_concurrency = 200 обосновывается следующими ключевыми факторами:
⚙️ 1. Рекомендации для SSD/NVMe
- Для SATA SSD: Значение 200 соответствует общепринятым рекомендациям для SSD-накопителей.
commit_delay
Для конфигурации PostgreSQL 8 CPU, 8 GB RAM с shared_buffers=2GB под нагрузкой pgbench (5→100 соединений) оптимальное значение commit_delay = 0 (по умолчанию) является наиболее эффективным. Обоснование:
⚠️ Почему НЕ рекомендуется изменять commit_delay?
- Природа pgbench нагрузки:
TPC-B-like транзакции выполняются за 1-5 мс. Любая задержка коммита (commit_delay > 0) увеличивает latency на 20-100% .
При 100 соединениях группировка коммитов не даёт выигрыша: короткие транзакции редко совпадают во времени .
⚡ Когда использовать commit_delay > 0?
Исключительные сценарии (не подходят для pgbench):
- Пакетная вставка данных: INSERT ... SELECT с миллионами строк.
- Асинхронная репликация: При риске отставания реплик.
- HDD-диски: Для группировки операций записи (но pgbench требует SSD).
Итог
Рекомендуемые изменения конфигурационных параметров для проведения эксперимента:
#tune-1
shared_buffers = '2GB'
max_wal_size = '4GB'
min_wal_size = '1GB'
effective_cache_size = '6GB'
work_mem = '24MB'
temp_buffers = '48MB'
random_page_cost = '1.1'
effective_io_concurrency = '200'
commit_delay = '0'