Вирішення проблеми із зависанням 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".

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