Оптимизация производительности PostgreSQL

Ниже перечислены основные параметры, на которые следует обратить внимание при оптимизации производительности PostgreSQL.

Объём совместно используемой памяти, выделяемой PostgreSQL для кэширования данных, определяется числом страниц ( shared_buffers ) по 8 килобайт каждая. Следует учитывать, что операционная система сама кеширует данные, поэтому нет необходимости отводить под кэш всю наличную оперативную память. Размер shared_buffers зависит от многих факторов, для начала можно принять следующие значения:

• 8–16 Мб – Обычный настольный компьютер с 512 Мб и небольшой базой данных
• 80–160 Мб – Небольшой > сервер, предназначенный для обслуживания базы данных с объёмом оперативной памяти 1 Гб и базой данных около 10 Гб.
• 400 Мб – Сервер с несколькими процессорами, с объёмом памяти в 8 Гб и базой данных занимающей свыше 100 Гб обслуживающий несколько сотен активных соединений одновременно .

Под каждый запрос выделяется ограниченный объём памяти для работы. Этот объём используется для сортировки, объединения и других подобных операций. При превышении этого объёма сервер начинает использовать временные файлы на диске, что может существенно снизить производительность. Оценить необходимое значение для work_mem можно разделив объём доступной памяти (физическая память минус объём занятый под другие программы и под совместно используемые страницы shared_buffers ) на максимальное число одновременно используемых активных соединений.

Эта память используется для выполнения операций по сбору статистики (ANALYZE), сборке мусора (VACUUM), создания индексов (CREATE INDEX) и добавления внешних ключей. Размер выделяемой под эти операции памяти должен быть сравним с физическим размером самого большого индекса на диске.

PostgreSQL в своих планах опирается на кэширование файлов, осуществляемое операционной системой. Этот параметр соответствует максимальному размеру объекта, который может поместиться в системный кэш. Это значение используется только для оценки. effective_cache_size можно установить в 1/2 — 2/3 от объёма имеющейся в наличии оперативной памяти, если вся она отдана в распоряжение PostgreSQL.

Следующие параметры могут существенно увеличить производительность работы PostgreSQL. Однако их рекомендуется использовать только если имеются надежные ИБП и программное обеспечение, завершающее работу системы при низком заряде батарей.

Данный параметр отвечает за сброс данных из кэша на диск при завершении транзакций. Если установить его значение fsync =off то данные не будут записываться на дисковые накопители сразу после завершения операций. Это может существенно повысить скорость операций insert и update, но есть риск повредить базу, если произойдет сбой (неожиданное отключение питания, сбой ОС, сбой дисковой подсистемы).

Включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера.

Если вашей системе не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы, но необходимо обеспечить в несколько раз большую производительность по количеству транзакций в секунду. В этом случае можно установить этот параметр в off (отключение синхронной записи).

PostgreSQL. shared_buffers. Эксперименты 1

Я провел множество экспериментов с shared_buffers, и мои эксперименты продолжаются. В этот раз решил описать эксперимент, в котором я смотрю что будет, если уменьшить размер кэша PostgreSQL. К чему это приведет и как это посмотреть – ниже.

Когда не хватает выделенной shared_buffers:

Пытался придумать хороший пример на моей не нагруженной системе. В итоге, у меня получился громоздкий пример. Но, пока я придумывал, как его реализовать, кучу всего нового узнал:

Подготовка:

Нужно установить расширение pg_prewarm, которое используется для прогрева кэша PostgreSQL. В состав данного расширения входит функция pg_prewarm, в параметр которой нужно передать объект, который будет принудительно загружен в кэш (либо PostgreSQL, либо ОС):

Создаем таблицу t с одним полем id типа numeric:

Меняем способ хранения «длинных» значений в колонке данной таблицы на plain – не будет использоваться никакой способ сжатия и не будут использоваться TOAST-таблицы:

• ALTER TABLE t ALTER COLUMN id SET STORAGE PLAIN;

Включаем замер операций вводы/вывода (параметр track_io_timing) и перечитаем конфигурацию, чтобы замер времени заработал:

• ALTER SYSTEM SET track_io_timing = on;
• SELECT pg_reload_conf();

Далее забиваем таблицу большими данными:

• INSERT INTO t SELECT 120^12345::numeric from generate_series(1,65000);

В данном инсерте я вставляю 65 000 строк в таблицу t с единственным значением, которое очень большое (120 в степени 12345).

Проверяем размер таблицы (я использую psql):

Размер моей таблицы 508 МБ. Под этот размер установлю значение shared_buffers в значение 512 МБ. При установке нового значения shared_buffers не забудьте перезапустить сервер PostgreSQL. В момент перезагрузки очистится совместная память PostgreSQL, что нам и нужно.

Для супер-чистоты эксперимента я перезапустил виртуалку, запустил PostgreSQL.

Эксперимент:

1. Считываем таблицу с диска

• EXPLAIN (ANALYZE, BUFFERS) select * from t;

Данным запросом просим PostgreSQL выполнить запрос и показать фактическое время его выполнения, чтения буферов, количество строк:

В выводе EXPLAIN видно план и факт выполнения.

Seq Scan – сокращение от sequential, означает последовательное чтение всей таблицы. У меня нет индексов, да и значения вроде как все нужны, поэтому тут всё нормально.

По какой-то причине, планировщик посчитал, что у меня в таблице будет почти 9 миллионов строк (я эксперименты проводил, таблицу чистил, а статистику не обновлял). Фактически было выбрано 65 тысяч строк.

В секции buffers можно увидеть строку shared read 65000. Подобрал такой размер таблицы, чтобы одна строка была примерно равна 8 КБ, то есть, одной странице в общей памяти. shared read показывает, сколько чтений было осуществлено НЕ ИЗ shared_buffers PostgreSQL – с диска, или из буфера ОС. В данном случае PostgreSQL прочитал 65000 строк-страниц с диска.

I/O Timings – на чтение этих 65000 страниц PostgreSQL потратил примерно 2.5 секунды. Если в секцию actual time посмотреть, последний параметр (после двоеточия, на второй строке) стоит 3000 миллисекунд – 3 секунды. За три секунды сервер получил все 65000 строк, из этих 3 секунд он 2.5 секунды только читал данных с диска.

Всего на отработку запроса ушло чуть более 3 секунды (3063 миллисекунды) – последняя строка.

Если несколько раз повторно выполнить данный запрос, скорость получения данных будет немного увеличиваться:

Появился элемент shared hit – это чтения из shared_buffers. У меня по 32 страницы туда попадает, пока с этим не разбирался. Второй раз выборка данных НЕ ИЗ shared_buffers происходит значительно быстрее, чем в первые разы. Я считаю, это связано с тем, что данные попали в буферный кэш операционной системы. Shared read, напоминаю, показывает чтения только из shared_buffers.

1. Считываем таблицу изshared_buffers

С помощью расширения pg_prewarm загоняем всю таблицу t в shared_buffers. В общий буфер PostgreSQL было занесено 65000 страниц, как раз все строки нашей таблицы t.

Снова выполняем предыдущий запрос на анализ чтения данных из таблицы:

• EXPLAIN (ANALYZE, BUFFERS) select * from t;

В данном случае время на чтение данных не тратится (нет раздела I/O Timings), есть только shared hit = 65000. Это означает, что, при исполнении данного запросы все строки были получены из shared_buffers и очень-очень быстро.

Если повторно выполнить запрос – результат будет примерно тем же самым:

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

В данном случае у нас shared_buffers был равен 512 МБ, и в него полностью помещалась наша тестовая таблица.

1. Уменьшаемshared_buffersдо 128кБ

В postgresql.conf меняем значение параметра до 128 кБ, перезапускаем сервер PostgreSQL, чтобы освободить буфер сервера, перезапускаем ОС для очистки совести.

Снова выполняем запрос:

• EXPLAIN (ANALYZE, BUFFERS) select * from t;

Результат, собственно, такой же, как и был в первый раз. Всё читаем с диска, тратим время на это, почти три секунды.

Сейчас не особенно показательно, так как всё ожидаемо. Далее снова загрузим таблицу в shared_buffers PostgreSQL:

Нам сообщается, что загружено 65000 страниц. Я думаю, просто по очереди блоки закидывались в общую память PostgreSQL, предыдущие выкидывались оттуда.

А теперь снова запустим запрос:

• EXPLAIN (ANALYZE, BUFFERS) select * from t;

Хоть нам мы и ожидаем, что таблица будет в кэше PostgreSQL, но по факту (при наличии крошечного shared_buffers) у нас в него попадает только 14 страниц: 14*8=112 КБ данных. А все остальные страницы читаются с диска, на это тратится время.

Выполним запрос еще несколько раз:

Скорость получения данных не из shared_buffers увеличилась. Но, повторюсь, я считаю, что это произошло из-за того, что у меня не нагруженная система и таблица загрузилась в буферный кэш операционной системы.

Но можно увидеть, что shared hit как был 14, так и остался. Больше страниц в shared_buffers PostgreSQL загрузить не удается, так как у нас этот параметр установлен в небольшое значение.

Вот таким «нехитрым» и «небольшим» экспериментом можно увидеть, как влияет правильно подобранный параметр shared_buffers на производительность запросов.

У меня на этом не всё про буферный PostgreSQL.

PostgreSQL shared_buffers on Windows

I’m runnung 64-bit PostgreSQL 9.1 on Windows Server. I’m trying to improve its performanace especially for handling heavy writing. I used to increase shared_buffer to %25 of RAM, and scine I got 32GB RAM I decided to set shared_buffers to 8GB. While I’m searching for more info I came across this post: http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html

It says: but not more than about 8GB on Linux or 512MB on Windows, and sometimes less.

Now I’m confused. What’s the point of increasing RAM if it won’t help improving PostgreSQL performance?!

The other values will be as follows:

1 Answer 1

For a write-heavy Windows server, the most important setting is to adjust checkpoint_segments. Your value is fairly high already, but you may want to experiment with values up to 256.

From the postgresql performance tuning guide (found here):

PostgreSQL writes new transactions to the database in files called WAL segments that are 16MB in size. Every time checkpoint_segments worth of these files have been written, by default 3, a checkpoint occurs. Checkpoints can be resource intensive, and on a modern system doing one every 48MB will be a serious performance bottleneck. Setting checkpoint_segments to a much larger value improves that. Unless you’re running on a very small configuration, you’ll almost certainly be better setting this to at least 10, which also allows usefully increasing the completion target.

For more write-heavy systems, values from 32 (checkpoint every 512MB) to 256 (every 4GB) are popular nowadays. Very large settings use a lot more disk and will cause your database to take longer to recover, so make sure you’re comfortable with both those things before large increases. Normally the large settings (>64/1GB) are only used for bulk loading. Note that whatever you choose for the segments, you’ll still get a checkpoint at least every 5 minutes unless you also increase checkpoint_timeout (which isn’t necessary on most systems).

Postgresql shared buffers windows

Задаёт объём памяти, который будет использовать сервер баз данных для буферов в разделяемой памяти. По умолчанию это обычно 128 мегабайт ( 128MB ), но может быть и меньше, если конфигурация вашего ядра накладывает дополнительные ограничения (это определяется в процессе initdb ). Это значение не должно быть меньше 128 килобайт. (Этот минимум зависит от величины BLCKSZ .) Однако для хорошей производительности обычно требуются гораздо большие значения. Задать этот параметр можно только при запуске сервера.

Если вы используете выделенный сервер с объёмом ОЗУ 1 ГБ и более, разумным начальным значением shared_buffers будет 25% от объёма памяти. Существуют варианты нагрузки, при которых эффективны будут и ещё большие значения shared_buffers , но так как Postgres Pro использует и кеш операционной системы, выделять для shared_buffers более 40% ОЗУ вряд ли будет полезно. При увеличении shared_buffers обычно требуется соответственно увеличить max_wal_size , чтобы растянуть процесс записи большого объёма новых или изменённых данных на более продолжительное время.

В серверах с объёмом ОЗУ меньше 1ГБ следует использовать меньший процент ОЗУ, чтобы оставить достаточно памяти для операционной системы. Кроме того, большие значения shared_buffers не так эффективны в Windows. Возможно, вы получите лучшие результаты, если оставите это значение относительно небольшим и будете больше полагаться на кеш операционной системы. Оптимальные значения shared_buffers для Windows обычно лежат в интервале от 64 до 512 мегабайт. huge_pages ( enum )

Включает/отключает использование огромных страниц памяти. Допустимые значения: try (попытаться, по умолчанию), on (вкл.) и off (выкл.).

В настоящее время это поддерживается только в Linux. В других системах значение try просто игнорируется.

В результате использования огромных страниц уменьшаются таблицы страниц и сокращается время, которое тратит процессор на управление памятью. За подробностями обратитесь к Подразделу 17.4.5.

Когда huge_pages имеет значение try , сервер попытается использовать огромные страницы, но может переключиться на обычные, если это не удастся. Со значением on , если использовать огромные страницы не получится, сервер не будет запущен. Со значением off огромные страницы использоваться не будут. temp_buffers ( integer )

Задаёт максимальное число временных буферов для каждого сеанса, По умолчанию объём временных буферов составляет восемь мегабайт (1024 буфера). Этот параметр можно изменить в отдельном сеансе, но только до первого обращения к временным таблицам; после этого изменить его значение для текущего сеанса не удастся.

Сеанс выделяет временные буферы по мере необходимости до достижения предела, заданного параметром temp_buffers . Если сеанс не задействует временные буферы, то для него хранятся только дескрипторы буферов, которые занимают около 64 байт (в количестве temp_buffers ). Однако если буфер действительно используется, он будет дополнительно занимать 8192 байта (или BLCKSZ байт, в общем случае). max_prepared_transactions ( integer )

Задаёт максимальное число транзакций, которые могут одновременно находиться в « подготовленном » состоянии (см. PREPARE TRANSACTION ). При нулевом значении (по умолчанию) механизм подготовленных транзакций отключается. Задать этот параметр можно только при запуске сервера.

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

Для ведомого сервера значение этого параметра должно быть больше или равно значению на ведущем. В противном случае на ведомом сервере не будут разрешены запросы. work_mem ( integer )

Задаёт объём памяти, который будет использоваться для внутренних операций сортировки и хеш-таблиц, прежде чем будут задействованы временные файлы на диске. Значение по умолчанию — четыре мегабайта ( 4MB ). Заметьте, что в сложных запросах одновременно могут выполняться несколько операций сортировки или хеширования, и при этом указанный объём памяти может использоваться в каждой операции, прежде чем данные начнут вытесняться во временные файлы. Кроме того, такие операции могут выполняться одновременно в разных сеансах. Таким образом, общий объём памяти может многократно превосходить значение work_mem ; это следует учитывать, выбирая подходящее значение. Операции сортировки используются для ORDER BY , DISTINCT и соединений слиянием. Хеш-таблицы используются при соединениях и агрегировании по хешу, а также обработке подзапросов IN с применением хеша. maintenance_work_mem ( integer )

Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM , CREATE INDEX и ALTER TABLE ADD FOREIGN KEY . По умолчанию его значение — 64 мегабайта ( 64MB ). Так как в один момент времени в сеансе может выполняться только одна такая операция и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem . Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии.

Учтите, что когда выполняется автоочистка, этот объём может быть выделен autovacuum_max_workers раз, поэтому не стоит устанавливать значение по умолчанию слишком большим. Возможно, будет лучше управлять объёмом памяти для автоочистки отдельно, изменяя autovacuum_work_mem. replacement_sort_tuples ( integer )

Когда количество сортируемых кортежей меньше заданного числа, для получения первого потока сортируемых данных будет применяться не алгоритм quicksort, а выбор с замещением. Это может быть полезно в системах с ограниченным объёмом памяти, когда кортежи, поступающие на вход большой операции сортировки, характеризуются хорошей корреляцией физического и логического порядка. Заметьте, что это не относится к кортежам с обратной корреляцией. Вполне возможно, что алгоритм выбора с замещением сформирует один длинный поток и слияние не потребуется, тогда как со стратегией по умолчанию может быть получено много потоков, которые затем потребуется слить для получения окончательного отсортированного результата. Это позволяет ускорить операции сортировки.

Значение по умолчанию — 150000 кортежей. Заметьте, что увеличивать это значение обычно не очень полезно, и может быть даже контрпродуктивно, так как эффективность приоритетной очереди зависит от доступного объёма кеша процессора, тогда как со стандартной стратегией потоки сортируются кеш-независимым алгоритмом. Благодаря этому стандартная стратегия позволяет автоматически и прозрачно использовать доступный кеш процессора более эффективным образом.

Если в maintenance_work_mem задано значение по умолчанию, внешние сортировки в служебных командах (например, сортировки, выполняемые командами CREATE INDEX для построения-индекса B-дерева) обычно никогда не используют алгоритм выбора с замещением (так как все кортежи помещаются в память), кроме случаев, когда входные кортежи достаточно велики. autovacuum_work_mem ( integer )

Задаёт максимальный объём памяти, который будет использовать каждый рабочий процесс автоочистки. По умолчанию равен -1, что означает, что этот объём определяется значением maintenance_work_mem. Этот параметр не влияет на поведение команды VACUUM , выполняемой в других контекстах. max_stack_depth ( integer )

Задаёт максимальную безопасную глубину стека для исполнителя. В идеале это значение должно равняться предельному размеру стека, ограниченному ядром (который устанавливается командой ulimit -s или аналогичной), за вычетом запаса примерно в мегабайт. Этот запас необходим, потому что сервер проверяет глубину стека не в каждой процедуре, а только в потенциально рекурсивных процедурах, например, при вычислении выражений. Значение по умолчанию — два мегабайта ( 2MB ) — выбрано с большим запасом, так что риск переполнения стека минимален. Но с другой стороны, его может быть недостаточно для выполнения сложных функций. Изменить этот параметр могут только суперпользователи.

Если max_stack_depth будет превышать фактический предел ядра, то функция с неограниченной рекурсией сможет вызвать крах отдельного процесса сервера. В системах, где Postgres Pro может определить предел, установленный ядром, он не позволит установить для этого параметра небезопасное значение. Однако эту информацию выдают не все системы, поэтому выбирать это значение следует с осторожностью. dynamic_shared_memory_type ( enum )

Выбирает механизм динамической разделяемой памяти, который будет использоваться сервером. Допустимые варианты: posix (для выделения разделяемой памяти POSIX функцией shm_open ), sysv (для выделения разделяемой памяти System V функцией shmget ), windows (для выделения разделяемой памяти в Windows), mmap (для эмуляции разделяемой памяти через отображение в память файлов, хранящихся в каталоге данных) и none (для отключения этой функциональности). Не все варианты поддерживаются на разных платформах; первый из поддерживаемых данной платформой вариантов становится для неё вариантом по умолчанию. Применять mmap , который нигде не выбирается по умолчанию, вообще не рекомендуется, так как операционная система может периодически записывать на диск изменённые страницы, что создаст дополнительную нагрузку; однако это может быть полезно для отладки, когда каталог pg_dynshmem находится на RAM-диске или когда другие механизмы разделяемой памяти недоступны.

18.4.2. Диск

Задаёт максимальный объём дискового пространства, который сможет использовать один процесс для временных файлов, например, при сортировке и хешировании, или для сохранения удерживаемого курсора. Транзакция, которая попытается превысить этот предел, будет отменена. Этот параметр задаётся в килобайтах, а значение -1 (по умолчанию) означает, что предел отсутствует. Изменить этот параметр могут только суперпользователи.

Этот параметр ограничивает общий объём, который могут занимать в момент времени все временные файлы, задействованные в данном процессе Postgres Pro . Следует отметить, что при этом учитывается только место, занимаемое явно создаваемыми временными таблицами; на временные файлы, которые создаются неявно при выполнении запроса, это ограничение не распространяется.

18.4.3. Использование ресурсов ядра

Задаёт максимальное число файлов, которые могут быть одновременно открыты каждым серверным подпроцессом. Значение по умолчанию — 1000 файлов. Если ядро реализует безопасное ограничение по процессам, об этом параметре можно не беспокоиться. Но на некоторых платформах (а именно, в большинстве систем BSD) ядро позволяет отдельному процессу открыть больше файлов, чем могут открыть несколько процессов одновременно. Если вы столкнётесь с ошибками « Too many open files » (Слишком много открытых файлов), попробуйте уменьшить это число. Задать этот параметр можно только при запуске сервера.

18.4.4. Задержка очистки по стоимости

Во время выполнения команд VACUUM и ANALYZE система ведёт внутренний счётчик, в котором суммирует оцениваемую стоимость различных выполняемых операций ввода/вывода. Когда накопленная стоимость превышает предел ( vacuum_cost_limit ), процесс, выполняющий эту операцию, засыпает на некоторое время ( vacuum_cost_delay ). Затем счётчик сбрасывается и процесс продолжается.

Данный подход реализован для того, чтобы администраторы могли снизить влияние этих команд на параллельную работу с базой, за счёт уменьшения нагрузки на подсистему ввода-вывода. Очень часто не имеет значения, насколько быстро выполнятся команды обслуживания (например, VACUUM и ANALYZE ), но очень важно, чтобы они как можно меньше влияли на выполнение других операций с базой данных. Администраторы имеют возможность управлять этим, настраивая задержку очистки по стоимости.

По умолчанию этот режим отключён для выполняемых вручную команд VACUUM . Чтобы включить его, нужно установить в vacuum_cost_delay ненулевое значение.

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

При настройке интенсивности очистки для vacuum_cost_delay обычно выбираются довольно небольшие значения, например 10 или 20 миллисекунд. Чтобы точнее ограничить потребление ресурсов при очистке, лучше всего изменять другие параметры стоимости очистки. vacuum_cost_page_hit ( integer )

Примерная стоимость очистки буфера, оказавшегося в общем кеше. Это подразумевает блокировку пула буферов, поиск в хеш-таблице и сканирование содержимого страницы. По умолчанию этот параметр равен одному. vacuum_cost_page_miss ( integer )

Примерная стоимость очистки буфера, который нужно прочитать с диска. Это подразумевает блокировку пула буферов, поиск в хеш-таблице, чтение требуемого блока с диска и сканирование его содержимого. По умолчанию этот параметр равен 10. vacuum_cost_page_dirty ( integer )

Примерная стоимость очистки, при которой изменяется блок, не модифицированный ранее. В неё включается дополнительная стоимость ввода/вывода, связанная с записью изменённого блока на диск. По умолчанию этот параметр равен 20. vacuum_cost_limit ( integer )

Общая стоимость, при накоплении которой процесс очистки будет засыпать. По умолчанию этот параметр равен 200.

Примечание

Некоторые операции устанавливают критические блокировки и поэтому должны завершаться как можно быстрее. Во время таких операций задержка очистки по стоимости не осуществляется, так что накопленная за это время стоимость может намного превышать установленный предел. Во избежание ненужных длительных задержек в таких случаях, фактическая задержка вычисляется по формуле vacuum_cost_delay * accumulated_balance / vacuum_cost_limit и ограничивается максимумом, равным vacuum_cost_delay * 4.

18.4.5. Фоновая запись

В числе специальных процессов сервера есть процесс фоновой записи, задача которого — осуществлять запись « грязных » (новых или изменённых) общих буферов на диск. Когда количество чистых общих буферов считается недостаточным, данный процесс записывает грязные буферы в файловую систему и помечает их как чистые. Это снижает вероятность того, что серверные процессы, выполняющие запросы пользователей, не смогут найти чистые буферы и им придётся сбрасывать грязные буферы самостоятельно. Однако процесс фоновой записи увеличивает общую нагрузку на подсистему ввода/вывода, так как он может записывать неоднократно изменяемую страницу несколько раз, тогда как её можно было бы записать всего один раз в контрольной точке. Параметры, рассматриваемые в данном подразделе, позволяют настроить поведение фоновой записи для конкретных нужд.

Задаёт задержку между раундами активности процесса фоновой записи. Во время раунда этот процесс осуществляет запись некоторого количества загрязнённых буферов (это настраивается следующими параметрами). Затем он засыпает на время bgwriter_delay (задаваемое в миллисекундах), и всё повторяется снова. Однако если в пуле не остаётся загрязнённых буферов, он может быть неактивен более длительное время. По умолчанию этот параметр равен 200 миллисекундам ( 200ms ). Заметьте, что во многих системах разрешение таймера составляет 10 мс, поэтому если задать в bgwriter_delay значение, не кратное 10, фактически будет получен тот же результат, что и со следующим за ним кратным 10. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. bgwriter_lru_maxpages ( integer )

Задаёт максимальное число буферов, которое сможет записать процесс фоновой записи за раунд активности. При нулевом значении фоновая запись отключается. (Учтите, что на контрольные точки, которые управляются отдельным вспомогательным процессом, это не влияет.) По умолчанию значение этого параметра — 100 буферов. Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. bgwriter_lru_multiplier ( floating point )

Число загрязнённых буферов, записываемых в очередном раунде, зависит от того, сколько новых буферов требовалось серверным процессам в предыдущих раундах. Средняя недавняя потребность умножается на bgwriter_lru_multiplier и предполагается, что именно столько буферов потребуется на следующем раунде. Процесс фоновой записи будет записывать на диск и освобождать буферы, пока число свободных буферов не достигнет целевого значения. (При этом число буферов, записываемых за раунд, ограничивается сверху параметром bgwriter_lru_maxpages .) Таким образом, со множителем, равным 1.0, записывается ровно столько буферов, сколько требуется по предположению ( « точно по плану » ). Увеличение этого множителя даёт некоторую страховку от резких скачков потребностей, тогда как уменьшение отражает намерение оставить некоторый объём записи для серверных процессов. По умолчанию он равен 2.0. Этот параметр можно установить только в файле postgresql.conf или в командной строке при запуске сервера. bgwriter_flush_after ( integer )

Когда процессом фоновой записи записывается больше чем bgwriter_flush_after байт, сервер даёт указание ОС произвести запись этих данных в нижележащее хранилище. Это ограничивает объём «грязных» данных в страничном кеше ядра и уменьшает вероятность затормаживания при выполнении fsync в конце контрольной точки или когда ОС сбрасывает данные на диск большими порциями в фоне. Часто это значительно уменьшает задержки транзакций, но бывают ситуации (особенно когда объём рабочей нагрузки больше shared_buffers, но меньше страничного кеша ОС), когда производительность может упасть. Этот параметр действует не на всех платформах. Он может принимать значение от 0 (при этом управление отложенной записью отключается) до 2 мегабайт ( 2MB ). Значение по умолчанию — 512kB в Linux и 0 в других ОС. (Если BLCKSZ отличен от 8 КБ, значение по умолчанию и максимум корректируются пропорционально.) Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

С маленькими значениями bgwriter_lru_maxpages и bgwriter_lru_multiplier уменьшается активность ввода/вывода со стороны процесса фоновой записи, но увеличивается вероятность того, что запись придётся производить непосредственно серверным процессам, что замедлит выполнение запросов.

18.4.6. Асинхронное поведение

Задаёт допустимое число параллельных операций ввода/вывода, которое говорит Postgres Pro о том, сколько операций ввода/вывода могут быть выполнены одновременно. Чем больше это число, тем больше операций ввода/вывода будет пытаться выполнить параллельно Postgres Pro в отдельном сеансе. Допустимые значения лежат в интервале от 1 до 1000, а нулевое значение отключает асинхронные запросы ввода/вывода. В настоящее время этот параметр влияет только на сканирование по битовой карте.

Для магнитных носителей хорошим начальным значением этого параметра будет число отдельных дисков, составляющих массив RAID 0 или RAID 1, в котором размещена база данных. (Для RAID 5 следует исключить один диск (как диск с чётностью).) Однако, если база данных часто обрабатывает множество запросов в различных сеансах, и при небольших значениях дисковый массив может быть полностью загружен. Если продолжать увеличивать это значение при полной загрузке дисков, это приведёт только к увеличению нагрузки на процессор. Диски SSD и другие виды хранилища в памяти часто могут обрабатывать множество параллельных запросов, так что оптимальным числом может быть несколько сотен.

Асинхронный ввод/вывод зависит от эффективности функции posix_fadvise , которая отсутствует в некоторых операционных системах. В случае её отсутствия попытка задать для этого параметра любое ненулевое значение приведёт к ошибке. В некоторых системах (например, в Solaris), эта функция присутствует, но на самом деле ничего не делает.

Значение по умолчанию равно 1 в системах, где это поддерживается, и 0 в остальных. Это значение можно переопределить для таблиц в определённом табличном пространстве, установив одноимённый параметр табличного пространства (см. ALTER TABLESPACE ). max_worker_processes ( integer )

Задаёт максимальное число фоновых процессов, которое можно запустить в текущей системе. Этот параметр можно задать только при запуске сервера. Значение по умолчанию — 8.

Для ведомого сервера значение этого параметра должно быть больше или равно значению на ведущем. В противном случае на ведомом сервере не будут разрешены запросы. max_parallel_workers_per_gather ( integer )

Задаёт максимальное число рабочих процессов, которые могут запускаться одним узлом Gather . Параллельные рабочие процессы берутся из пула процессов, контролируемого параметром max_worker_processes. Учтите, что запрошенное количество рабочих процессов может быть недоступно во время выполнения. В этом случае план будет выполняться с меньшим числом процессов, что может быть неэффективно. Значение 0 (заданное по умолчанию) отключает параллельное выполнение запросов.

Учтите, что параллельные запросы могут потреблять значительно больше ресурсов, чем не параллельные, так как каждый рабочий процесс является отдельным процессом и оказывает на систему примерно такое же влияние, как дополнительный пользовательский сеанс. Это следует учитывать, выбирая значение этого параметра, а также настраивая другие параметры, управляющие использованием ресурсов, например work_mem. Ограничения ресурсов, такие как work_mem , применяются к каждому рабочему процессу отдельно, что означает, что общая нагрузка для всех процессов может оказаться гораздо больше, чем при обычном использовании одного процесса. Например, параллельный запрос, задействующий 4 рабочих процесса, может использовать в 5 раз больше времени процессора, объёма памяти, ввода/вывода и т. д., по сравнению с запросом, не задействующим рабочие процессы вовсе.

За дополнительными сведениями о параллельных запросах обратитесь к Главе 15. backend_flush_after ( integer )

Когда одним обслуживающим процессом записывается больше backend_flush_after байт, сервер даёт указание ОС произвести запись этих данных в нижележащее хранилище. Это ограничивает объём «грязных» данных в страничном кеше ядра и уменьшает вероятность затормаживания при выполнении fsync в конце контрольной точки или когда ОС сбрасывает данные на диск большими порциями в фоне. Часто это значительно сокращает задержки транзакций, но бывают ситуации (особенно когда объём рабочей нагрузки больше shared_buffers, но меньше страничного кеша ОС), когда производительность может упасть. Этот параметр действует не на всех платформах. Он может принимать значение от 0 (при этом управление отложенной записью отключается) до 2 мегабайт ( 2MB ). По умолчанию он имеет значение 0 , то есть это поведение отключено. (Если BLCKSZ отличен от 8 КБ, максимальное значение корректируется пропорционально.) old_snapshot_threshold ( integer )

Задаёт минимальное время, которое можно пользоваться снимком без риска получить ошибку снимок слишком стар . Этот параметр можно задать только при запуске сервера.

По истечении этого времени старые данные могут быть вычищены. Это предотвращает замусоривание данными снимков, которые остаются задействованными долгое время. Во избежание получения некорректных результатов из-за очистки данных, которые должны были бы наблюдаться в снимке, клиенту будет выдана ошибка, если возраст снимка превысит заданный предел и из этого снимка будет запрошена страница, изменённая со времени его создания.

Значение -1 (по умолчанию) отключает это поведение. Полезные значения для производственной среды могут лежать в интервале от нескольких часов до нескольких дней. Заданное значение округляется до минут, а минимальные значения (как например, 0 или 1min ) допускаются только потому, что они могут быть полезны при тестировании. Хотя допустимым будет и значение 60d (60 дней), учтите, что при многих видах нагрузки критичное замусоривание базы или зацикливание идентификаторов транзакций может происходить в намного меньших временных отрезках.

Когда это ограничение действует, освобождённое пространство в конце отношения не может быть отдано операционной системе, так как при этом будет удалена информация, необходимая для выявления условия снимок слишком стар . Всё пространство, выделенное отношению, останется связанным с ним до тех пор, пока не будет освобождено явно (например, с помощью команды VACUUM FULL ).

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