Оптимизация работы PostgreSQL

Данная статья не претендует на полноту изложения всех возможностей конфигурирования 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с

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

1. После добавления третьего ядра PostgreSQL и MSSQL-варианты стали работать в тесте "два клиента" практически с производительностью теста "один клиент", т.е. удачно распараллелились. Что мешало им параллелить работу на двух ядрах для меня осталось загадкой.
2. MSSQL памяти захватил сразу много, PostgreSQL требовал во всех режимах существенно меньше, и сразу после завершения выполнения запроса почти всю высвобождал.
3. MSSQL работает единым процессом. PostgreSQL запускает по отдельному процессу на подключение+служебные процессы. Это позволяет даже 32-разрядному варианту эффективно использовать память при обработке запросов от нескольких клиентов.
4. Увеличение памяти для PostgreSQL в настройках свыше указанных ниже значений не привело к заметному росту производительности.
5. Первые тесты во всех случаях проходили дольше чем в последующих замерах, специально замеры не производил, но 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, добавлена ссылка на оригинальную документацию.

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

Я обещал написать о том, что в проектах с более менее серьезной нагрузкой БД либо помещается в память, либо не работает. Ситуация в современном мире меняется в связи с появлением 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 и из дискового кэша.

По умолчанию 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;

16 фев 2015

Оптимизация работы PostgreSQL

by Admin

Настройка конфигурации

Настройка ресурсов

shared_buffers

Размер разделяемой между процессами PostgreSQL памяти, которая нужна для выполнения активных операций. Не следует указывать слишком большой объём, так как PostgreSQL использует также дисковый кэш.
Значения:
- Средний объём данных и 256-512 МБ доступной памяти: 16-32 МБ
- Большой объём данных и 1-4 ГБ доступной памяти: 64-256 МБ

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

max_prepared_transactions
Количество одновременно подготавливаемых транзакций.
Для работы 1С этот параметр значения не имеет, PREPARE TRANSACTION там не используются.
Можно оставить по дефолту - 5

work_mem
Специальная память, используется для сортировки и кэширования таблиц, для одного запроса.
При задании этого параметра следует учитывать количество конкурентных запросов, выполняемых в один момент времени.
При памяти 1-4Gb рекомендуется устанавливать 32-128MB

maintenance_work_mem
Память использующаяся для операций VACUUM, CREATE INDEX, ALTER TABLE и FOREGIN KEY.
Следует устанавливать большее значение, чем для work_mem. Слишком большие значения приведут к использованию свопа.
При памяти 1-4Gb рекомендуется устанавливать 128-512MB

max_stack_depth
Специальный стек для сервера, в идеале он должен совпадать с размером стека, выставленном в ядре ОС. Установка большего значения, чем в ядре, может привести к ошибкам.
Рекомендуется устанавливать 2-4MB

max_fsm_relations
Максимальное количество таблиц, для которых будет отслеживаться свободное место в общей карте свободного пространства. Эти данные собираются VACUUM.
Выставьте параметр в соответствии с количеством таблиц в вашей базе с запасом. (Применимо для версий до 8.4)

max_fsm_pages
Количество блоков, для которых будет хранится информация о свободном месте. Информация хранится в разделяемой памяти, для каждой записи требуется по 6 байт.
Использование этого параметра позволяет избежать использования VACUUM FULL для базы, достаточно будет VACUUM. (Применимо для версий до 8.4)
Этот параметр должен быть не меньше чем 16*max_fsm_relations
Данный параметр задается автоматически при создании базы утилитой initdb
Можно задать его и вручную: в качестве начального приближения можно взять половину от среднего количества записей, изменяемых (UPDATE или DELETE) между запусками команды VACUUM.
Оценить это значение (база должна проработать уже какое-то время) можно выполнив:

vacuum full analyze;
NOTICE: number of page slots needed (196272) exceeds max_fsm_pages (153600)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 196272.

max_files_per_process
Максимальное количество файлов, открываемых процессом и его подпроцессами в один момент времени.
Уменьшите данный параметр, если в процессе работы наблюдается сообщение "Too many open files ".

Запись транзакционных логов на диск

commit_delay и commit_siblings
Значение commit_delay выражается в микросекундах (0 по умолчанию). Значение commit_siblings выражается в штуках (5 по умолчанию).
commit_delay определяют задержку между попаданием записи в буфер журнала транзакций и сбросом её на диск. Если при успешном завершении транзакции активно не менее commit_siblings транзакций, то запись будет задержана на время commit_delay. Если за это время завершится другая транзакция, то их изменения будут сброшены на диск вместе, при помощи одного системного вызова. Эти параметры позволят ускорить работу, если параллельно выполняется много «мелких» транзакций.

fsync
Данный параметр отвечает за сброс данных из кэша на диск при завершении транзакций.
Если установить его значение

fsync=off

то данные не будут записываться на дисковые накопители сразу после завершения операций. Это может существенно повысить скорость операций insert и update, но есть риск повредить базу, если произойдет сбой (неожиданное отключение питания, сбой ОС, сбой дисковой подсистемы).
Используйте эту возможность только если у вас имеются надежные ИБП и программное обеспечение, завершающее работу системы при низком заряде батарей.
Не следует отключать fsync при работе PostgreSQL на Windows платформе, из-за нестабильности системы.

wal_sync_method
Метод, который используется для принудительной записи данных на диск.
Если fsync=off, то этот параметр не используется.

Возможные значения:
open_datasync - запись данных методом open() с параметром O_DSYNC
fdatasync - вызов метода fdatasync() после каждого commit
fsync_writethrough - вызывать fsync() после каждого commit игнорирую паралельные процессы
fsync - вызов fsync() после каждого commit
open_sync - запись данных методом open() с параметром O_SYNC

Не все методы доступны на определенных платформах. По умолчанию устанавливается первый, который доступен в системе.

full_page_writes
Установите данный параметр в off, если fsync=off.
Иначе
Когда этот параметр on, PostgreSQL записывает содержимое каждой страницы в журнал транзакций во время первой модификации таблицы после контрольной точки. Это необходимо потому что страницы могут записаться лишь частично если в ходе процесса ОС "упала". Это приволит к тому, что на диске оказаываются новые данные смешанные со старыми. Строкового уровня записи в журнал транзакций может быть не достаточно, что бы полность восстановить данные после "падения". full_page_writes гарантирует корректное восстановление, ценой увелечения записываемых данных в журнал транзакций.(Потому что журнал транзакций все время начинается с контрольной точки. Единственный способ снижения объема записи заключается в увеличении checkpoint interval).

wal_buffers
Количество памяти используемое в SHARED MEMORY для ведения транзакционных логов.
При доступной памяти 1-4GB рекомендуется устанавливать 256-1024kb

Оптимизация запросов

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

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

На серверах с быстрыми дисковыми массивами имеет смысл уменьшать изначальную настройку до 3.0, 2.5 или даже до 2.0. Если же активная часть вашей базы данных много больше размеров оперативной памяти, попробуйте поднять значение параметра. Можно подойти к выбору оптимального значения и со стороны производительности запросов. Если планировщик запросов чаще, чем необходимо, предпочитает последовательные просмотры (sequential scans) просмотрам с использованием индекса (index scans), понижайте значение. И наоборот, если планировщик выбирает просмотр по медленному индексу, когда не должен этого делать, настройку имеет смысл увеличить. После изменения тщательно тестируйте результаты на максимально широком наборе запросов. Никогда не опускайте значение random_page_cost ниже 2.0; если вам кажется, что random_page_cost нужно еще понижать, разумнее в этом случае менять настройки статистики планировщика.

cpu_tuple_cost
Устанавливает у планировщика оценку "стоимоти" затрат на обработку каждой строки во время выполнения запроса. По умолчанию 0,01.

cpu_index_tuple_cost
Устанавливает у планировщика оценку "стоимоти" затрат на обработку каждого индекса во время операции сканирования индекса. По умолчанию 0,005

cpu_operator_cost
Устанавливает у планировщика оценку "стоимоти" затрат на выполнение каждого оператора или функции во время выполнения запроса. По умолчанию 0.0025.

effective_cache_size
Передает данные планировщику запросов об объёме памяти, которая используется ОС для кэширования файлов, для одного запроса.
Этот параметр в ОС можно посмотреть в настройках:
Для Windows: в Диспетчере задач, Закладка Быстродействие, Физическая память-Системный кэш.
Для Linux: наберите команду free, необходимое значение в столбце cached (в kB)

Данное значение необходимо разделить на количество конкурентных запросов в один момент времени (среднее количество подключений к базе + запас).

default_statistics_target
Устанавливает глубину статистики по таблицам. БОльшие значения могут повысить время выполнения команды ANALYZE, но улучшат построение плана запроса.
Рекомендуется устанавливать порядка 100.

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

Сбор статистики

stats_command_string
Передавать ли сборщику статистики информацию о текущей выполняемой команде и времени начала её выполнения.
Устанавливать on

stats_start_collector
Включать ли сбор статистики.
Устанавливать on

stats_row_level, stats_block_level
Собирать ли информацию об активности на уровне записей и блоков соответственно.
Устанавливать
stats_row_level=on
stats_block_level=off

stats_reset_on_server_start
Обнулять ли статистику при перезапуске сервера
Устанавливать off

Автовакуум

VACUUM - сборка "мусора". VACUUM восстанавливает место занятые "мертвыми" данными. При выполнении обычных операций с данными, PostgreSQL не удаляет данные физически из таблиц, это происходит с операцией VACUUM.

autovacuum
Включать ли автовакуум (автоматического запуска VACUUM),
устанавливать on

autovacuum_naptime
Пауза между запусками Автовакуума.
Зависит от того, как часто обновляются данные в ваших таблицах. Может соствлять порядка 5min, по умолчанию 1min

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

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

Блокировки

max_locks_per_transaction
Количество блокировок за одну транзакцию:
установить порядка 250

deadlock_timeout
Время жизни взаимных блокировок.
Установить порядка 2 секунд.

Возможная ошибка
После исправления файла конфигурации PostgreSQL может не запуститься, выдав ошибку:

FATAL: could not create shared memory segment:...
Failed system call was shmget(key=5432001, size=140075008, 03600).
This error usually means that PostgreSQL"s request for a shared memory segment exceeded your kernel"s SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 140075008 bytes), reduce PostgreSQL"s shared_buffers parameter (currently 16384) and/or its max_connections parameter (currently 10).
If the request size is already small, it"s possible that it is less than your kernel"s SHMMIN parameter, in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration.

Для исправления необходимо увеличить параметр SHMMAX в системе. Сделать это можно следующим образом (для ОС Linux):

Выполните команду:

echo 150829120 > /proc/sys/kernel/shmmax

указав свое новое значение для параметра (какое именно - можно посмотреть в логе ошибки PostgreSQL). Данная операция устанавливает параметр на лету, но после перезагрузки системы изменения будут потеряны. Чтобы этого не происходило, внесите в файл /etc/sysctl.conf следующую строку:

kernel.shmmax = 150829120

указав своё значение.

Пример конфигурации для сервера с 2G оперативной памяти

Средняя настройка

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

RAM -- размер памяти

* shared_buffers = 1/8 RAM или больше (но не более 1/4);
* work_mem в 1/20 RAM;
* maintenance_work_mem в 1/4;
* max_fsm_relations в планируемое кол-во таблиц в базах * 1.5;
* max_fsm_pages в max_fsm_relations * 2000;
* fsync = true;
* wal_sync_method = fdatasync;
* commit_delay = от 10 до 100 ;
* commit_siblings = от 5 до 10;
* effective_cache_size = 0.9 от значения cached, которое показывает free;
* random_page_cost = 2 для быстрых cpu, 4 для медленных;
* cpu_tuple_cost = 0.001 для быстрых cpu, 0.01 для медленных;
* cpu_index_tuple_cost = 0.0005 для быстрых cpu, 0.005 для медленных;
* autovacuum = on
* autovacuum_vacuum_threshold = 1800
* autovacuum_analyze_threshold = 900

Обслуживание базы

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

VACUUM выполняется автоматически, в соответствии с настройками в файле конфигурации.

VACUUM FULL выполнять не обязательно при правильно настроенном автовакууме.

Диски и файловые системы

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

Остановите сервер.
Перенесите каталоги pg_clog и pg_xlog, находящийся в каталоге с базами данных, на другой диск.
Создайте на старом месте символическую ссылку.
Запустите сервер.