БД — большой кэш

06.04.2019

Я обещал написать о том, что в проектах с более менее серьезной нагрузкой БД либо помещается в память, либо не работает. Ситуация в современном мире меняется в связи с появлением SSD дисков, но пока что они стоят достаточно дорого, по сравнению со старыми добрыми вращающимися дисками. Чтобы «потрогать» это руками, проделаем несложный тест.

Я завел новую машину с Ubuntu 12.04, на облачном хостинге Amazon размера t1.micro. После этого необходимо поставить 2 пакета: postgresql-contrib-9.1 и ruby.

PostgreSQL contrib содержит, помимо всего прочего, программу pgbench, специально предназначенную для нагрузочного тестирования PostgreSQL.

В postgresql.conf параметр shared_buffers был выставлен в 64MB.

File . open ("res.txt" , "w" ) do | f | (1 .. 50 ). each do | s | # -i означает, что мы создаем чистую БД # -s - это scale factor, определяет размер БД `/usr/lib/postgresql/9.1/bin/pgbench -i -s #{ s } ` # -S - тесты для запросов только на чтение # -T 20 - тест длится 20 секунд # -c 40 - 40 клиентов # -j 4 - 4 потока res = `/usr/lib/postgresql/9.1/bin/pgbench -c 40 -j 4 -S -T 20` puts res f . write res end end

На машинках t1.micro free -m выдает следующее: total used free shared buffers cached Mem: 590 583 6 0 1 428

Именно этим обусловлен такой выбор границ scale factor. Про scale factor = 50 размер БД приблизительно 750Мб, то есть больше количества доступной памяти, а граница shared_buffers пересекается при значениях scale factor 4-5.

Давайте же посмотрим на график.

Данные получились не очень красивыми, но в целом - понятными. Виден провал в районе scale factor 5, а следующий провал в районе scale factor 45. Собственно, две явные ступеньки - выпадание из shared buffers и из дискового кэша.

Задаёт объём памяти, который будет использовать сервер баз данных для буферов в разделяемой памяти. По умолчанию это обычно 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 просто игнорируется.

Когда 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 раз, поэтому не стоит устанавливать значение по умолчанию слишком большим. Возможно, будет лучше управлять объёмом памяти для автоочистки отдельно, изменяя . replacement_sort_tuples (integer)

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

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

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

Задаёт максимальный объём памяти, который будет использовать каждый рабочий процесс автоочистки. По умолчанию равен -1, что означает, что этот объём определяется значением . Этот параметр не влияет на поведение команды 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. Диск

Примерная стоимость очистки буфера, оказавшегося в общем кеше. Это подразумевает блокировку пула буферов, поиск в хеш-таблице и сканирование содержимого страницы. По умолчанию этот параметр равен одному. 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. Фоновая запись

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

Задаёт максимальное число буферов, которое сможет записать процесс фоновой записи за раунд активности. При нулевом значении фоновая запись отключается. (Учтите, что на контрольные точки, которые управляются отдельным вспомогательным процессом, это не влияет.) По умолчанию значение этого параметра - 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 в конце контрольной точки или когда ОС сбрасывает данные на диск большими порциями в фоне. Часто это значительно уменьшает задержки транзакций, но бывают ситуации, особенно когда объём рабочей нагрузки больше , но меньше страничного кеша ОС, когда производительность может упасть. Этот параметр действует не на всех платформах. Он может принимать значение от 0 (при этом управление отложенной записью отключается) до 2 Мбайт (2MB). Значение по умолчанию - 512kB в Linux и 0 в других ОС. (Если BLCKSZ отличен от 8 Кбайт, значение по умолчанию и максимум корректируются пропорционально.) Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера.

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

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

Effective_io_concurrency (integer)

Задаёт допустимое число параллельных операций ввода/вывода, которое говорит 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 . Параллельные рабочие процессы берутся из пула процессов, контролируемого параметром . Учтите, что запрошенное количество рабочих процессов может быть недоступно во время выполнения. В этом случае план будет выполняться с меньшим числом процессов, что может быть неэффективно. Значение 0 (заданное по умолчанию) отключает параллельное выполнение запросов.

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

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

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

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

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

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

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

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

По умолчанию PostgreSQL настроен таким образом, чтобы расходовать минимальное количество ресурсов для работы с небольшими базами до 4 Gb на не очень производительных серверах. То есть, если дело касается систем посерьезней, то вы столкнетесь с большими потерями производительности базы данных лишь потому, что дефолтные настройки могут в корне не соответствовать производительности вашего северного оборудования. Настройки выделения ресурсов оперативной памяти RAM для работы PostgreSQL хранятся в файле postgresql.conf .

Доступен как из папки, куда установлен PostgreSQL / Data, так и из pgAdmin:

В общем на начальном этапе при возникновении трудностей и замедления работы БД, заметной для глаз пользователей достаточно увеличить три параметра:

shared_buffers

Это размер памяти, разделяемой между процессами PostgreSQL, отвечающими за выполнения активных операций. Максимально-допустимое значение этого параметра – 25% всего количества RAM

Например, при 1-2 Gb RAM на сервере, достаточно указать в этом параметре значение 64-128 Mb (8192-16384).

temp_buffers

Это размер буфера под временные объекты (временные таблицы). Среднее значение 2-4% всего количества RAM

Например, при 1-2 Gb RAM на сервере, достаточно указать в этом параметре значение 32-64 Mb.

work_mem

Это размер памяти, используемый для сортировки и кэширования таблиц.

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

Еще два важных параметра это maintenance_work_mem (для операций VACUUM, CREATE INDEX и других) и max_stack_depth

Примеры оптимальных настроек:

CPU: E3-1240 v3 @ 3.40GHz
RAM: 32Gb 1600Mhz
Диски: Plextor M6Pro

postgresql.conf:

shared_buffers = 8GB
work_mem = 128MB
maintenance_work_mem = 2GB
fsync = on
synchronous_commit = off
wal_sync_method = fdatasync
checkpoint_segments = 64
seq_page_cost = 1.0
random_page_cost = 6.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.0005
cpu_operator_cost = 0.0025
effective_cache_size = 24GB

Полезные запросы:

Блокировки БД по пользователям

Вывести все таблицы, размером больше 10 Мб

from pg_tables
where tableName not like ‘sql_%’ and pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) like ‘%MB%’;

Определение размеров таблиц в базе данных PostgreSQL

Код SQL SELECT tableName, pg_size_pretty(pg_total_relation_size(CAST(tablename as text))) as size
from pg_tables
where tableName not like ‘sql_%’
order by size;

Пользователи блокирующие конкретную таблицу

Код SQL select a.usename, t.relname, a.current_query, mode from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid inner join pg_stat_all_tables t on t.relid=l.relation where t.relname = ‘tablename’; Код SQL select relation::regclass, mode, a.usename, granted, pid from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not mode = ‘AccessShareLock’ and relation is not null;

Запросы с эксклюзивными блокировками

Код SQL select a.usename, a.current_query, mode from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where mode ilike ‘%exclusive%’;

Количество блокировок по пользователям

Код SQL select a.usename, count(l.pid) from pg_locks l inner join pg_stat_activity a on a.procpid = l.pid where not(mode = ‘AccessShareLock’) group by a.usename;

Количество подключений по пользователям

Код SQL select count(usename), usename from pg_stat_activity group by usename order by count(usename) desc;

Данная статья не претендует на полноту изложения всех возможностей конфигурирования PostgreSQL, и в сравнительном тестировании я не охватываю всех режимов работы базы данных. Заинтересовавшимся советую изучить книгу по ссылке

Вступление

Я много работал с PostgreSQL и считаю его прекрасной СУБД. У меня многогигабайтная рабочая база (не 1С) обрабатывает моментально огромные массивы данных. PostgreSQL прекрасно использует индексы, хорошо справляется с параллельной нагрузкой, функционал хранимых процедур на высоте, есть хорошие средства администрирования и повышения производительности "из коробки", а сообщество создало полезные утилиты. Но я с удивлением узнал что у многих администраторов 1С мнение о PostgreSQL не на высоте, что он тормоз и едва обгоняет файловый вариант базы, и только MSSQL может спасти положение.

Поизучав вопрос, я нашел множество статей по установке PostgreSQL по шагам для чайников, как по Linux, так и под Windows. Но подавляющее большинство статей описывают установку до "установилось - создадим базу", и совершенно не затрагивают вопрос конфигурирования. В оставшихся конфигурирование упоминается лишь на уровне "прописать такие значения", практически не объясняя зачем.

И если подход "установка в одну кнопку" применим к MSSQL и вообще многим продуктам под Windows, то к PostgreSQL он, к сожалению, не относится. Настройки по умолчанию очень сильно ограничивают его в использовании памяти, чтобы можно было его установить хоть на калькулятор и он там не мешал работе остального ПО. PostgreSQL обязательно нужно конфигурировать под конкретную систему, и только тогда он сможет показать себя на высоте. В особо тяжелых случаях можно тюнинговать настройки PostgreSQL, базы и файловой системы друг под друга, но это касается в большей степени Linux-систем, где больше возможностей по настройке всего и вся.

Следует напомнить, что для 1С не подойдет сборка PostgreSQL от разработчиков СУБД, только собранная из пропатченных 1С исходных текстов. Готовые совместимые сборки предлагает 1С (через диски ИТС и кабинет для имеющих подписку на поддержку) и EterSoft

Тестирование проводилось в среде Windows, но все рекомендации по настройке не являются специфичными для платформы и применимы к любой ОС.

Тестирование и сравнение

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

Для тестирования я использовал следующую конфигурацию:
Host-машина: Win7, Core i5-760 2.8MHz, 4 ядра, 12Гб ОЗУ, VMWare 10
Виртуальная: Win7 x64, 2 ядра, 4Гб ОЗУ, отдельный физический жесткий диск для размещения БД (не SSD)
MSSQL Express 2014
PostgreSQL EtherSoft 9.2.1
1C 8.3.5 1383

Использовалась БД, dt-выгрузка 780Мб.
После восстановления базы:
размер файла 1CD в файловом варианте - 10Гб,
размер базы PostgreSQL - 8Гб,
размер базы MSSQL - 6.7Гб.

Для теста использовал запрос на выборку договоров контрагентов (21к) с выборкой дополнительных реквизитов из различных регистров, для каждого договора фактически делалась отдельная выборка из регистров. Конфигурацию взял что была под рукой - сильно доработанная на базе Бухгалтерии 3.0.

При тестировании выполнял запрос одним и двумя клиентами по несколько раз до получения стабильных результатов. Первые прогоны игнорировал.

Тестирование одним клиентом:

Выборка на хосте из файлового варианта с размещением базы на SSD - 31с
Выборка из файлового варианта в виртуальной машине (с жесткого диска) - 46с
Выборка из MSSQL-базы - первый проход - 25с или 9с (видимо в зависимости от актуальности кэша СУБД) (потребление памяти процессом СУБД составило примерно 1.3Гб)
Выборка из PostgreSQL с настройками по умолчанию - 43с (потребление памяти не превышало 80Мб на подключение)
Выборка из оптимизированного PostgreSQL - 21с (потребление памяти составило 120Мб на подключение)

Тестирование двумя клиентами:

Выборка на хосте из файлового варианта с размещением базы на SSD - по 34с
Выборка из файлового варианта в виртуальной машине (с жесткого диска) - по 56с
Выборка из MSSQL-базы - по 50с или 20с (видимо в зависимости от актуальности кэша СУБД)
Выборка из PostgreSQL с настройками по умолчанию - по 60с
Выборка из оптимизированного PostgreSQL - по 40с

Замечания к тестированию:

После добавления третьего ядра PostgreSQL и MSSQL-варианты стали работать в тесте "два клиента" практически с производительностью теста "один клиент", т.е. удачно распараллелились. Что мешало им параллелить работу на двух ядрах для меня осталось загадкой.
MSSQL памяти захватил сразу много, PostgreSQL требовал во всех режимах существенно меньше, и сразу после завершения выполнения запроса почти всю высвобождал.
MSSQL работает единым процессом. PostgreSQL запускает по отдельному процессу на подключение+служебные процессы. Это позволяет даже 32-разрядному варианту эффективно использовать память при обработке запросов от нескольких клиентов.
Увеличение памяти для PostgreSQL в настройках свыше указанных ниже значений не привело к заметному росту производительности.
Первые тесты во всех случаях проходили дольше чем в последующих замерах, специально замеры не производил, но MSSQL субъективно стартовал быстрее.

Конфигурирование PostgreSQL

Есть прекрасная книга на русском языке о конфигурировании и оптимизировании PostgreSQL: Каждому слоноводу имеет смысл поставить себе в закладки эту ссылку. В книге описывается множество техниг оптимизации СУБД, создание отказоустойчивых и распределенных систем. Но сейчас мы рассмотрим то что пригодится всем - конфигурирование использования памяти. PostgreSQL не будет использовать памяти больше чем разрешено настройками, а с настройками по умолчанию PostgreSQL использует минимум памяти. При этом не стоит указывать памяти больше чем доступно к использованию - система начнет использовать файл подкачки со всеми вытекающими печальными последствиями для производительности сервера. Ряд советов по настройке PostgreSQL приведены на диске ИТС.

В Windows конфигурационные файлы PostgreSQL находятся в каталоге установки в каталоге Data:

postgresql.conf - основной файл с настройками СУБД
pg_hba.conf - файл с настройками доступа для клиентов. В частности, тут можно указать каким пользователям с каких IP-адресов можно подключаться к определенным БД, и требуется ли проверять пароль пользователя, и если требуется - каким методом.
pg_ident.conf - файл с преобразованием имен пользователей из системных во внутренние (вряд ли он потребуется большинству пользователей)

Файлы текстовые, можно править блокнотом. Строки, начинающиеся с # считаются комментариями и игнорируются.

Параметры, относящиеся к объму памяти могут дополняться суффиксами kB, MB, GB - килобайты, мегабайты, гигабайты, например, 128MB. Параметры, описывающие интервалы времени, могут дополняться суффиксами ms,s,min,h,d - миллисекунды, секунды, минуты, часы, дни, например, 5min

Если вы забыли пароль к постгрессу - не беда, можно прописать в pg_hba.conf строку:

Host all all 127.0.0.1/32 trust

И подключаться любым пользователем (например, postgres ) к СУБД на локальной машине по адресу 127.0.0.1 без проверки пароля.

Оптимизация использования памяти

Настройки использования памяти располагаются в postgresql.conf

Оптимальные значения параметров зависят от объема свободной оперативной памяти, размера базы и отдельных элементов базы (таблицы и индексы), сложности запросов (в принципе, стоит полагаться что запросы будут достаточно сложными - множественные соединения в запросах это типовой сценарий) и количества одновременных активных клиентов. Кстати, PostgreSQL хранит таблицы и индексы БД в отдельных файлах (<каталог установки PG>\data\base\<идентификатор БД>\), и размеры объектов можно оценить. Так же можно используя входящую в поставку утилиту pgAdmin подключиться к базе, раскрыть "Схемы"-"public", и сформировать отчет по статистике для элемента "Таблицы".

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

При настройке сервера для тестирования я полагался на следующие расчеты:
Всего 4Гб ОЗУ. Потребители - ОС Windows, сервер 1С, PostgreSQL и дисковый кэш системы. Я исходил из того что для СУБД можно выделить до 2.5Гб ОЗУ

Значения могут указываться с суффиксами kB, MB, GB (значения в килобайта, мегабайтах или гигабайтах). После изменения значений требуется перезапустить службу PostgreSQL.

shared_buffers - Общий буфер сервера

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

Если объём буфера недостаточен для хранения часто используемых рабочих данных, то они будут постоянно писаться и читаться из кэша ОС или с диска, что крайне отрицательно скажется на производительности.

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

В тесте использовалось

shared_buffers = 512MB

work_mem - память для сортировки, агрегации данных и т.д.

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

Есть рекомендация при расчетах взять объем доступной памяти за вычетом shared_buffers , и поделить на количество одновременно исполняемых запросов. В случае сложных запросов делитель стоит увеличить, т.е. уменьшить результат. Для рассматриваемого случая из расчета 5 активных пользователей (2.5Гб-0.5Гб (shared_buffers))/5=400Мб. В случае если СУБД сочтет запросы достаточно сложными, или появятся дополнительные пользователи, потребуется значение уменьшить.

Для простых запросов достаточно небольших значений - до пары мегабайт, но для сложных запросов (а это типовой сценарий для 1С) потребуется больше. Рекомендация - для памяти 1-4Гб можно использовать значения 32-128Мб. В тесте использовал

work_mem = 128MB

maintenance_work_mem - память для команд сбора мусора, статистики, создания индексов.

Рекомендуется устанавливать значение 50-75% от размера самой большой таблицы или индекса, но чтобы памяти хватило для работы системы и приложений. Рекомендуется устанавливать значения больше чем work_mem. В тесте использовал
maintenance_work_mem = 192MB

temp_buffers - буфер под временные объекты, в основном для временных таблиц.

Можно установить порядка 16 МБ. В тесте использовал
temp_buffers = 32MB

effective_cache_size - примерный объем дискового кэша файловой системы.

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

Autovacuum - "сборка мусора"

PostgreSQL как типичный представитель "версионных" СУБД (в противоположность блокирующим) самостоятельно не блокирует при изменении данных таблицы и записи от читающих транзакций (в случае 1С этим занимается сам сервер 1С). Вместо этого создаётся копия изменяемой записи, которая становится видна последующим транзакциям, действующие же продолжают видеть данные, актуальные на начало своей транзакции. Как следствие, в таблицах накапливаются устаревшие данные - предыдущие версии измененных записей. Для того чтобы СУБД могла высвободившееся место использовать, необходимо произвести "сборку мусора" - определить какие из записей больше не используются. Это можно сделать явно SQL-командой VACUUM , либо дождаться когда таблицу обработает автоматический сборщик мусора - AUTOVACUUM . Так же до определенной версии сборка мусора была связана со сбором статистики (планировщик использует данные о количестве записей в таблицах и распределении значений индексированных полей для построения оптимального плана запроса). С одной стороны, сбор мусора делать необходимо, чтобы таблицы не разрастались и эффективно использовали дисковое пространство. С другой внезапно начавшаяся уборка мусора дает дополнительную нагрузку на диск и таблицы, что приводит к увеличению времени выполнения запросов. Аналогичный эффект создает автоматический сбор статистики (явно его можно запустить командой ANALYZE или совместно со сборкой мусора VACUUM ANALYZE ). И хотя от версии к версии PostgreSQL совершенствует эти механизмы, чтобы минимизировать негативное влияние на производительность (например, в ранних версиях сборка мусора полностью блокировала доступ к таблице, с версии 9.0 работа VACUUM ускорена), тут есть что настроить.

Полностью отключить autovacuum можно параметром:

autovacuum = off

Так же для работы Autovacuum требуется параметр track_counts = on, в противном случае он работать не будет.

По умолчанию оба параметра включены. На самом деле autovacuum полностью отключить нельзя - даже при autovacuum = off иногда (после большого количества транзакций) autovacuum будет запускаться.

Замечание: VACUUM обычно не уменьшает размер файла таблицы, только помечает свободные, доступные для повторного использования области. Если же требуется физически высвободить лишнее место и максимально уменьшить занимаемое пространство на диске, потребуется команда VACUUM FULL . Этот вариант блокирует доступ к таблице на время работы, и обычно не требуется его использовать. Подробнее об использовании команды VACUUM можно прочитать в документации (на английском).

Если Autovacuum полностью не отключать, настроить его влияние на выполнение запросов можно следующими параметрами:

autovacuum_max_workers - максимальное количество параллельно запущенных процессов уборки.

autovacuum_naptime - минимальный интервал, реже которого autovacuum не будет запускаться. По умолчанию 1 минута. Можно увеличить, тогда при частых изменениях данных анализ будет выполняться реже.

autovacuum_vacuum_threshold, - количество измененных или удаленных записей в таблице, необходимых для запуска процесса сборки мусора VACUUM или сбора статистики ANALYZE . По умолчанию по 50.

autovacuum_vacuum_scale_factor , autovacuum_analyze_scale_factor - коэфициент от размера таблицы в записях, добавляемый к autovacuum_vacuum_threshold и autovacuum_analyze_threshold соответственно. Значения по умолчанию 0.2 (т.е. 20% от количества записей) и 0.1 (10%) соответственно.

Рассмотрим пример с таблицей на 10000 записей. Тогда при настройках по умолчанию после 50+10000*0.1=1050 измененных или удаленных записей будет запущен сбор статистики ANALYZE , а после 2050 изменений - сборка мусора VACUUM .

Если увеличить threshold и scale_factor, обслуживающие процессы будут выполняться реже, но небольшие таблицы могут существенно разрастаться. Если БД состоит преимущественно из небольших таблиц, общее увеличение занимаемого дискового пространства может быть существенным, таким образом увеличивать эти значения можно, но с умом.

Таким образом может иметь смысл увеличить интервал autovacuum_naptime, и несколько увеличить threshold и scale_factor. В нагруженных базах может быть альтернативой существенно поднять scale_factor (значение 1 позволит "разбухать" таблицам вдвое) и поставить в планировщик ежесуточное выполнение VACUUM ANALYZE в период минимальной загруженности БД.

default_statistics_target - назначает объем статистики, собираемый командой ANALYZE . Значение по умолчанию 100. Большие значения увеличивают время выполнения команды ANALYZE, но позволяют планировщику строить более эффективные планы выполнения запросов. Встречаются рекомендации по увеличению до 300.

Можно управлять производительностью AUTOVACUUM , делая его более длительным но менее нагружающим систему.

vacuum_cost_page_hit - размер "штрафа" за обработку блока, находящегося в shared_buffers. Связан с необходимостью блокировать доступ к буферу. Значение по умолчанию 1

vacuum_cost_page_miss - размер "штрафа" за обработку блока на диске. Связан с блокировкой буфера, поиском данных в буфере, чтении данных с диска. Значение по умолчанию 10

vacuum_cost_page_dirty - размер "штрафа" за модификацию блока. Связан с необходимостью сбросить модифицированные данные на диск. Значение по умолчанию 20

vacuum_cost_limit - максимальный размер "штрафов", после которых процесс сборки может быть "заморожен" на время vacuum_cost_delay. По умолчанию 200

vacuum_cost_delay - время "заморозки" процесса сборки мусора по достижению vacuum_cost_limit. Значение по умолчанию 0ms

autovacuum_vacuum_cost_delay - время "заморозки" процесса сборки мусора для autovacuum. По умолчанию 20ms. Если установить -1, будет использоваться значение vacuum_cost_delay

autovacuum_vacuum_cost_limit - максимальный размер "штрафа" для autovacuum. Значение по умолчанию -1 - используется значение vacuum_cost_limit

По сообщениям использование vacuum_cost_page_hit = 6 , vacuum_cost_limit = 100 , autovacuum_vacuum_cost_delay = 200ms уменьшает влияние AUTOVACUUM до 80%, но увеличивает время его выполнения втрое.

Настройка записи на диск

При завершении транзакции PostgreSQL начала пишет данные в специальный журнал транзакций WAL (Write-ahead log), а затем уже в базу после того, как данные журнала гарантированно записаны на диск. По умолчанию используется механизм fsync , когда PostgreSQL принудительно сбрасывает данные (журнала) из дискового кэша ОС на диск, и только после успешной записи (журнала) клиенту сообщается об успешном завершении транзакции. Использование журнала транзакций позволяет завершить транзакцию или восстановить базу если во время записи данных произойдет сбой.

В нагруженных системах с большими объемами записи может иметь смысл вынести журнал транзакций на отдельный физический диск (но не на другой раздел этого же диска!). Для этого нужно остановить СУБД, перенести каталог pg_xlog в другое место, а на старом месте создать символическую ссылку, например, утилитой junction. Так же ссылки умеет создавать Far Manager (Alt-F6). При этом надо убедиться что новое место имеет права доступа для пользователя, от которого запускается PostgreSQL (обычно postgres).

При большом количестве операций изменения данных может потребоваться увеличить значение checkpoint_segments, регулирующее объем данных, который может ожидать переноса из журнала в саму базу. По умолчанию используется значение 3. При этом следует учитывать что под журнал выделяется место, расчитываемое по формуле (checkpoint_segments * 2 + 1) * 16 МБ, что при значении 32 уже потребует более 1Гб места на диске.

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

fsync = off
full_page_writes = off

Делать это можно только в случае если вы на 100% доверяете оборудованию и ИБП (источнику бесперебойного питания). Иначе в случае аварийного завершения системы есть риск получить разрушенную БД. И в любом варианте не помешает так же RAID-контроллер с батарейкой для питания памяти недозаписанных данных.

Определенной альтернативой может быть использование параметра

synchronous_commit = off

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

Если не отключать fsync совсем, можно указать метод синхронизации в параметре. Статья с диска ИТС ссылается на утилиту pg_test_fsync, но в моей сборке PostgreSQL её не оказалось. По утверждению 1С, в их случае в Windows оптимально себя показал метод open_datasync (судя по всему, именно этот метод и используется по умолчанию).

В случае если используется множество мелких пишущих транзакций (в случае 1С этом может быть массовое обновление справочника вне транзакции), может помочь сочетание параметров commit_delay (время задержки завершения транзакции в микросекундах, по умолчанию 0) и commit_siblings (по умолчанию 5). При включении опций завершение транзакции может быть отложено на время commit_delay, если в данный момент исполняется не менее commit_siblings транзакций. В этом случае результат всех завершившихся транзакций будет записан совместно для оптимизации записи на диск.

Прочие параметры, влияющие на производительность

wal_buffers - объем памяти в shared_buffers для ведения транзакционных логов. Рекомендация - при 1-4Гб доступной памяти использовать значения 256КБ-1МБ. Документация утверждает что использование значения "-1" автоматически подбирает значение в зависимости от значения shared_buffers.

random_page_cost - "стоимость" случайного чтения, используется при поиске данных по индексам. По умолчанию 4.0. За единицу берется время последовательного доступа к данным. Для быстрых дисковых массивов, особенно SSD, имеет смысл понижать значение, в этом случае PostgreSQL будет более активно использовать индексы.

В книге по ссылке есть некоторые другие параметры, которые можно настраивать. Так же настоятельно рекомендуется ознакомиться с документацией на PostgreSQL по назначению конкретных параметров.

Параметры из раздела QUERY TUNING, особенно касающиеся запрета планировщику использовать конкретные методы поиска, рекомендуется изменять только в том случае если есть полное понимание что делаете. Очень легко оптимизировать один вид запросов и обрушить производительность всех остальных. Эффективность изменения большинства параметров в этом разделе зависит от данных в БД, запросов к этим данным (т.е. от используемой версии 1С в т.ч.) и версии СУБД.

Заключение

PostgreSQL - мощная СУБД в умелых руках, но требующая тщательной настройки. Его вполне можно использовать совместно с 1С и получить приличное быстродействие, а бесплатность его будет очень приятным бонусом.

Критика и дополнения к этой статье приветствуются.

Полезные ссылки

http://postgresql.leopard.in.ua/ - сайт книги "Работа с PostgreSQL настройка и масштабирование ", наиболее полное и понятное руководство на мой взгляд по конфигурированию и администрированию PostgreSQL

http://etersoft.ru/products/postgre - здесь можно скачать 1С-совместимую сборку PostgreSQL под Windows и различные дистрибутивы и версии Linux. Для тех у кого нет подписки на ИТС или требуется версия под версию Linux, которая не представлена на v8.1c.ru.

http://www.postgresql.org/docs/9.2/static/ - официальная документация на PostgreSQL (на английском)

Статьи с диска ИТС по настройке PostgreSQL

История правок статьи

29.01.2015 - опубликована первоначальная версия
31.01.2015 - статья дополнена разделом по AUTOVACUUM, добавлена ссылка на оригинальную документацию.

В дальнейшем я намерен провести тестирование работы СУБД в режиме добавления и изменения данные.

Появилась у нас в конторе мегапрограмма по оперативному учету. Программеры ее уже год как ваяют, практически все работники конторы в ней заняты, без нее в облэнерго сейчас жизни нет. Не работает программа - контора в определенном смысле парализована.
Сейчас в системе около 700 пользователей, при этом активных бывает до 100 рыл.
Естественно вопрос производительности сейчас первостепенный (если считать с нуля, то задача номер 0 отказоустойчивость будет решена в ближайшее время). Моя задача как админа выжать все возможное из базы данных. Это PostgreSQL версии 8.2, с которого сейчас осуществляется тестовая миграция на 9.0.
Здесь будет приведен файл параметров postgresql.conf с расширенными комментариями по каждому пункту.
Установка каждого параметра выбиралась исходя из существующего опыта эксплуатации, исследования открытых источников, как минимум 3-х, официальной документации, а также тестов производительности приложения на сервере в максимально идентичными настройками БД 8.2. и 9.0.

# Файл параметров PostgreSQL postgresql.conf
# Попытка заставить базу EnergyNET летать соколом.
# Указаные настройки подразумевают, что на сервере, где
# крутится PostgreSQL никого кроме него нет, не считая
# операционной системы.
#########################################################

# shared_buffers
#
# Смысл параметра: PostgreSQL не читает данные напрямую
# с диска и не пишет их сразу на диск. Данные загружаются
# в общий буфер сервера, находящийся в разделяемой памяти,
# серверные процессы читают и пишут блоки в этом буфере,
# а затем уже изменения сбрасываются на диск.
# Если процессу нужен доступ к таблице, то он сначала
# ищет нужные блоки в общем буфере. Если блоки
# присутствуют, то он может продолжать работу, если
# нет — делается системный вызов для их загрузки.
# Загружаться блоки могут как из файлового кэша ОС, так и
# с диска, и эта операция может оказаться весьма «дорогой».
# Если объём буфера недостаточен для хранения часто
# используемых рабочих данных, то они будут постоянно
# писаться и читаться из кэша ОС или с диска, что крайне
# отрицательно скажется на производительности.
# Принцип таков, что значение параметра устанавливаем
# в размере 25-40% от оперативной памяти. От версии
# PostgreSQL не зависит.
#
shared_buffers = 500MB # 2GB ОЗУ
#shared_buffers = 3000MB # 12GB ОЗУ

# temp_buffers
#
# Смысл параметра: Максимальное количество памяти, выделяемой
# каждой сессии для работы с временными таблицами.
# Необходимо помнить, что как только сессия заняла размер,
# указанный в temp_buffers, эта память не освобождается,
# пока сессия не завершится. На системах с большим
# количеством одновременно работающих пользователей
# некорректное (завышенное) значение этого параметра
# запросто может привести к бешеному свопу, и как
# следствие, краху операционной системы. А если при
# этом еще и fsync = off, то готовьтесь восстанавливать
# базу из резервных копий.
# У нас временные таблицы не используются, так что
# можно оставить значение по умолчанию. То есть
# вообще удалить из postgresql.conf
#
temp_buffers = 8MB

# max_prepared_transactions
#
# Смысл параметра: устанавливает максимальное количество
# PREPARED TRANSACTION которые могут устанавливаться в
# состояние prepared единовременно.
# У нас 98% всех транзакций - PREPARED.
# Минимально необходимое значение параметра = max_connections
#
max_prepared_transactions = 600

# work_mem
#
# Смысл параметра: Задаёт объём памяти, который используют
# внутренние операции сортировки и хэш-таблицы перед тем
# как перейти на использование временных файлов на диске.
# Если объём памяти недостаточен для сортироки некоторого
# результата, то серверный процесс будет использовать
# временные файлы. Если же объём памяти слишком велик,
# то это может привести к своппингу.
# В сложном запросе параллельно может быть запущено несколько
# операций сортировки и хэширования, и каждая может занять
# столько памяти, сколько задано в этом параметре, прежде
# чем начнет использовать временные файлы. Кроме того, эти
# операции могут одновременно выполняться в нескольких сессиях.
# Таким образом, используемая память может оказаться в несколько
# раз больше, чем work_mem. Операции сортировки используются
# для ORDER BY, DISTINCT и операций соединения методом
# слияния (merge joins). Хэш-таблицы используются при
# операциях соединения методом хэширования (hash joins),
# операциях аггрегирования, основанных на хэшировании и
# обработке IN-подзапросов на основе хэширования.
# Своими словами: если некая операция из перечисленных выше требует
# для выполнения больше памяти, чем указано в параметре work_mem,
# для этой операции будет создан временный файл в каталоге pgsql_tmp
# соответствующей БД. Так что оценить, какой объем необходимо задать
# данному параметру можно просто влянув в этот каталог в пиковые
# часы нагрузки. Если суммарный объем созданных там файлов поместится
# в оперативную память и останется хотя-бы 30% резерва, с учетом
# остальных работающих на сервере задач, то можно задавать значение, равное
# размеру примерно одинаковых по объему файлов. Тут конечно тоже без
# фанатизма, ибо если временный файл больше 100 метров, то нужно
# трижды подумать...
#
work_mem = 32MB

# autovacuum
#
# Смысл простой - выполнение процессов VACUUM в автоматическом
# режиме во время работы сервера.
# У нас VACUUM и ANALYZE будут выполняться ночью
# через CRON, так что включать автовакуум не будем.
# Ну незачем нам еще и в рабочее время систему
# нагружать некритичными вещами.
#
autovacuum = off

# effective_io_concurrency
#
# Смысл параметра: Задаёт число операций чтения/записи на
# диск, которые по мнению PostgreSQL могут выполняться
# одновременно. Увеличение этого значения может повысить
# число операций ввода/вывода, которое любая сессия
# PostgreSQL может попытаться выполнить параллельно.
# Допустимые значения лежат в диапазоне от 1 до 1000,
# либо 0, если вы хотите запретить выполнение асинхронных
# операций ввода/вывода.
# Для оценки необходимого значения этого параметра можно
# начать с числа отдельных устройств, включая RAID 0
# или RAID 1, используемых базой данных. Для RAID 5
# чётность устройств не учитывается. Однако, если база
# данных часто занята множеством запросов, получаемых от
# различных одновременных сессий, даже низкие значения
# могут держать занятым дисковый массив. Значение выше
# необходимого для поддержания занятости дисков приведёт
# только к дополнительной загрузке процессора.
# По простому, ставим значение параметра в число,
# равное количеству шпинделей в дисках, на
# которых расположена база данных. Потом пробуем уменьшить
# или увеличить, и оцениваем результат при каждом изменении
# параметра. Вообще параметр новый, малоопробованый,
# рекомендаций мало. Так что будем тестировать.
#
effective_io_concurrency = 4 # 4 диска в RAID10

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

# full_page_writes
#
# Смысл параметра: Если этот параметр включен, сервер
# PostgreSQL пишет весь контент каждой страницы диска в WAL
# во время первого изменения этой страницы после контрольной
# точки. Это необходимо, поскольку если в процессе записывания
# страницы произойдет системный сбой, на диске может оказаться
# страница, в которой смешаны старые и новые данные. Изменения
# на уровне строк данных, которые обычно хранятся в WAL, может
# быть не достаточно для восстановления страницы после
# системного сбоя. Хранение образа всей страницы гарантирует,
# что страница будет восстановлена правильно, но это будет
# стоить увеличения количества данных, которые нужно будет
# занести в WAL. (Так как чтение WAL всегда начинается с
# контрольной точки, удобно делать это при первом изменении
# каждой страницы после контрольной точки. Таким образом,
# одним из способов уменьшения стоимости записи страниц
# является увеличение интервала между контрольными точками.)
# Отключение этого параметра ускоряет работу, но может привести
# к повреждению базы данных в случае системного сбоя или
# отключения питания. Риски схожи с отключением fsync, хотя в
# данном случае они меньше.
#
full_page_writes=on

# synchronous_commit
#
# Смысл параметра: Определяет, должна ли транзакция ждать,
# пока записи WAL будут перенесены на диск, прежде чем команда
# вернёт клиенту сообщение об успешном выполнении. По
# умолчанию и с точки зрения безопасности этот параметр должен
# быть включен. Если параметр выключен, возможен промежуток
# времени между сообщением об успешном выполнении транзакции
# и моментом, когда транзакция на самом деле защищена от сбоя
# на сервере. В отличие от fsync, отключение этого параметра
# не может привести к риску противоречия в базе данных: сбой
# может привести к потере последних прошедших транзакций, но
# состояние базы данных при этом будет таким, как будто эти
# транзакции просто были прерваны. Таким образом, отключение
# synchronous_commit может оказаться полезным, если
# производительность важнее, чем точность в проведении
# транзакций.
#
synchronous_commit = off

effective_cache_size: указывает планировщику на размер самого большого объекта в базе данных, который теоретически может быть закеширован. На выделенном сервере имеет смысл выставлять effective_cache_size в 2/3 от всей оперативной памяти; на сервере с другими приложениями сначала нужно вычесть из всего объема RAM размер дискового кэша ОС и память, занятую остальными процессами.