Введение в 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 для анализа производительности запросов. В следующей лекции мы углубимся в то, как находить медленные запросы с его помощью и оптимизировать их выполнение.

2

Задача

SQL SELF, 42 уровень, 1 лекция

Недоступна

Получение информации из `pg_stat_statements`

Получение информации из `pg_stat_statements`

Открыть