27.01.2012

Решение проблемы с зависанием PostgreSQL

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

Варианты решения проблемы:

• Увеличить количество записей, просматриваемых при сборе статистики по таблицам. Большие значения могут повысить время выполения команды ANALYZE, но улучшат построение плана запроса:
  • Файл postgresql.conf - default_statistics_target = 1000 -10000.
• Отключение оптимизатору возможности использования NESTED LOOP при выборе плана выполнения запроса в конфигурации PostgreSQL:
  • Файл postgresql.conf - enable_nestloop=off.
  • Отрицательным эффектом этого способа является возможное замедление некоторых запросов, поскольку при их выполении будут использоваться другие, более затратные, методы соединения (HASH JOIN).
• Отключение оптимизатору возможности изменения порядка соединений таблиц в запросе:
  • Файл postgresql.conf - join_collapse_limit=1.
  • Следует использовать этот метод, если вы уверены в правильности порядка соединений таблиц в проблемном запросе.
• Изменение параметров настройки оптимизатора:
  • Файл postgresql.conf:
    • seq_page_cost = 0.1 
    • random_page_cost = 0.4
    • cpu_operator_cost = 0.00025
• Использование версии PostgreSQL 9.1.2-1.1.C, в которой реализован независимый от AUTOVACUUM сбор статистики, на основе информации об изменении данных в таблице. По умолчанию включен сбор статистики только для временных таблиц и во многих ситуациях этого достаточно. При возникновении проблем с производительностью выполнения регламентных операций можно включить сбор статистики для всех  или отдельных проблемных таблиц изменив значение параметра конфигурации PostgreSQL(файл postgresql.conf) online_analyze.table_type = "temporary"  на online_analyze.table_type = "all".

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