Вступ до pg_stat_statements: встановлення та налаштування розширення

Розширення pg_stat_statements у PostgreSQL — це інструмент для збору статистики запитів. Воно дозволяє побачити, які запити виконуються найчастіше, які з них займають найбільше часу, і наскільки ефективно використовуються ресурси бази даних. Замість того, щоб аналізувати кожен запит вручну через EXPLAIN, ми можемо отримати загальну картину продуктивності бази даних.

Переваги використання pg_stat_statements:

Моніторинг у реальному часі: ти можеш бачити, які запити зараз навантажують базу даних.

Аналіз продуктивності всієї системи: інформація доступна для всіх запитів у базі даних, а не тільки для тих, які ти вирішив аналізувати вручну.

Пошук повільних запитів: легко визначити, які запити займають найбільше часу.

Виявлення повторюваних запитів: дозволяє оптимізувати кешування і додати індекси для популярних запитів.

Встановлення та налаштування pg_stat_statements

Тепер, коли ти розумієш, навіщо потрібен pg_stat_statements, давай розберемося, як його встановити та налаштувати крок за кроком.

1. Перевіряємо готовність PostgreSQL. Переконайся, що твій PostgreSQL підтримує розширення pg_stat_statements. Це розширення входить у стандартну поставку починаючи з PostgreSQL 9.2. Щоб перевірити наявність розширення, виконай:

SELECT extname FROM pg_extension;

Якщо pg_stat_statements не входить до списку, це може означати, що його не встановив твій адміністратор.

Ось як має виглядати встановлене та активоване розширення:

2. Додавання розширення.

Щоб активувати pg_stat_statements, його треба додати до списку попередньо завантажуваних бібліотек PostgreSQL. Це робиться у конфігураційному файлі postgresql.conf.

Кроки:

1. Знайди файл postgresql.conf. Зазвичай він знаходиться у директорії даних PostgreSQL.
2. Відкрий його для редагування.
3. Додай або зміни рядок:

   shared_preload_libraries = 'pg_stat_statements'

Чому це потрібно? Тому що pg_stat_statements вимагає попереднього завантаження, бо відслідковує запити на рівні системи.

4. Збережи зміни і перезапусти сервер PostgreSQL, щоб активувати зміни. Нижче команда для Linux:

   sudo systemctl restart postgresql
   

Якщо ти розробляєш або тестуєш локально, простий перезапуск сервера теж підійде.

3. Створення розширення у базі даних. Після того як сервер PostgreSQL був перезапущений, ми можемо створити розширення pg_stat_statements у конкретній базі даних. Підключись до потрібної бази даних через psql або інший інструмент і виконай:

CREATE EXTENSION pg_stat_statements;

Якщо все пройшло успішно, команда завершиться без помилок. Тепер pg_stat_statements активовано для твоєї бази даних.

4. Налаштування параметрів pg_stat_statements.

Після встановлення розширення корисно налаштувати параметри його роботи для коректного збору статистики. Основні параметри можна задати у файлі postgresql.conf.

Основні параметри

• pg_stat_statements.track
• Визначає, які запити будуть відслідковуватись.
• Значення:
  • all — відслідковувати всі запити (рекомендується для відладки та аналізу).
  • top — відслідковувати тільки запити верхнього рівня.
  • none — вимкнути відслідковування.
• Приклад налаштування:

pg_stat_statements.track = 'all'

• pg_stat_statements.max

  • Вказує максимальну кількість запитів, які будуть зберігатися у статистиці.
  • За замовчуванням: 5000.
  • Якщо у твоїй системі багато запитів, це значення варто збільшити, наприклад:

    pg_stat_statements.max = 10000
    

• pg_stat_statements.save

  • Визначає, чи зберігати статистику між перезапусками сервера.
  • Значення: on або off.
  • Рекомендуємо залишити on:

    pg_stat_statements.save = on
    

Після зміни параметрів знову перезапусти сервер PostgreSQL.

Перевірка роботи pg_stat_statements

Тепер, коли розширення встановлено і налаштовано, давай перевіримо його роботу. Щоб побачити зібрану статистику запитів, виконай наступний запит:

SELECT
    queryid,        -- Унікальний ідентифікатор запиту
    query,          -- Текст запиту
    calls,          -- Кількість викликів запиту
    total_time,     -- Загальний час виконання (у мілісекундах)
    rows            -- Кількість рядків, повернутих запитом
FROM pg_stat_statements
ORDER BY total_time DESC;

Що означають стовпці?

• queryid: унікальний ідентифікатор запиту, корисний для пошуку однакових запитів з різними параметрами.
• query: текст SQL-запиту, який виконувався.
• calls: скільки разів запит був викликаний.
• total_time: загальний час (сума часу всіх викликів запиту).
• rows: загальна кількість рядків, повернутих запитом.

Наприклад, якщо ти бачиш, що запит з calls = 100 і total_time = 50000 (50 секунд) займає основну частину виконання у системі, це явний сигнал, що його треба оптимізувати.

Типові сценарії використання pg_stat_statements

1. Пошук найповільніших запитів. Щоб знайти запити, які займають найбільше часу, відсортуй результати по total_time:

SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

2. Виявлення найактивніших запитів. Щоб знайти запити, які виконуються найчастіше, використовуй сортування по calls:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

3. Аналіз використання індексів. Якщо ти бачиш багато запитів, що працюють повільно, перевір використання індексів. Наприклад, у запитах з фільтрацією (WHERE) відсутність індексу часто є причиною низької продуктивності.

Очищення даних pg_stat_statements

Іноді тобі може знадобитися скинути накопичену статистику, щоб почати аналіз з нуля. Це можна зробити за допомогою команди:

SELECT pg_stat_statements_reset();

Після скидання вся статистика буде очищена, і збір даних почнеться заново.

Практичні поради

Обмежуй обсяг зібраної статистики: якщо ти працюєш у високонавантаженій системі з мільйонами запитів, залиш pg_stat_statements.max на розумному рівні, щоб уникнути зайвих навантажень.

Регулярно очищуй статистику: це корисно робити перед початком аналізу продуктивності, щоб не змішувати старі й нові дані.

Уважно стався до повільних запитів: навіть якщо вони рідко виконуються, одноразовий повільний запит може сильно навантажити твою базу даних.

Тепер ти знаєш, як встановити, налаштувати і використовувати розширення pg_stat_statements для аналізу продуктивності запитів. У наступній лекції ми заглибимося в те, як знаходити повільні запити з його допомогою і оптимізувати їх виконання.