Связаться с намиПодключиться

Анализ производительности и оптимизация Managed Service for PostgreSQL

Статья создана
Обновлена 26 августа 2024 г.

Снижение производительности кластера Managed Service for PostgreSQL чаще всего происходит по одной из следующих причин:

Ниже приводятся советы по диагностике и решению этих проблем.

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

Перед началом работы

  1. Выберите базы данных для диагностики.

  2. Включите в кластере опцию Доступ из консоли управления.

  3. Активируйте сбор статистики о сессиях и запросах.

  4. Включите модуль auto_explain для расширенного логирования планов выполнения запросов.

  5. Чтобы в лог производительности попадало больше запросов, в настройках СУБД уменьшите значение параметра log_min_duration_statement.

    Важно

    При значении параметра log_min_duration_statement равном 0 в лог будут попадать все запросы независимо от времени их выполнения. Это может привести к быстрому исчерпанию свободного места в хранилище.

Диагностика неэффективного выполнения запросов

Выявить проблемные запросы можно двумя способами:

  • Сделать выборку из системной таблицы PostgreSQL pg_stat_activity:

    SELECT NOW() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' ORDER BY 1 DESC;

    Будет возвращен список запросов, выполняющихся на сервере. Обратите внимание на запросы с высоким значением duration.

    Подробнее об информации в выдаче см. в документации PostgreSQL.

  • Получить и проанализировать статистику по запросам с помощью встроенного в Managed Service for PostgreSQL инструмента для диагностики.

Устранение проблем с неэффективными запросами

Проблемные запросы можно оптимизировать несколькими способами:

  • Проанализировать план запроса (query plan) с помощью команды EXPLAIN.

    Обратите внимание на запросы, не использующие индексы (большое количество строк в узлах Seq Scan). Такие запросы увеличивают как потребление I/O (чтений с диска будет больше), так и CPU (требуется больше процессорного времени на обработку большого числа строк).

    Создайте или обновите необходимые индексы.

    Совет

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

    Подробнее см. в разделе SQL-запросы в Yandex WebSQL.

  • Автоматически логировать план выполнения запросов с помощью модуля auto_explain.

  • Обновить статистику с помощью команды ANALYZE.

    План выполнения запроса строится на основе статистики, собранной СУБД. Если данные в СУБД обновляются часто, эта статистика быстро устаревает. Используйте запрос ANALYZE, чтобы СУБД выполнила повторный анализ таблицы или всей базы данных:

    ANALYZE <имя_таблицы_или_базы_данных>;

    При необходимости в настройках СУБД увеличьте значение параметра default_statistics_target, затем выполните запрос ANALYZE повторно.

    Подробнее про параметр default_statistics_target см. в настройках PostgreSQL.

  • Создать расширенные объекты статистики.

    PostgreSQL не собирает статистику о корреляции данных между столбцами одной таблицы. Это связано с тем, что число возможных комбинаций столбцов может быть очень большим. Если между некоторыми столбцами есть связь, создайте расширенные объекты статистики. Тогда планировщик сможет оптимизировать запросы на основе информации о корреляции данных в столбцах.

  • Проанализировать статистику планов выполнения запросов в логах PostgreSQL.

    Модуль PostgreSQL auto_explain выводит информацию о планах выполнения запросов в лог PostgreSQL. Вы можете собрать статистику поиском по строкам лога. Подробнее читайте в документации PostgreSQL.

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

Диагностика дефицита ресурсов

Дефицит ресурсов — одна из вероятных причин падения производительности кластера. Дефицит ресурсов виден по графикам мониторинга кластера (CPU, дисковые операции I/O, сетевые соединения). Если график использования ресурса постоянно рос, а потом вышел на плато, нагрузка на ресурс достигла лимита или выходит за границы гарантированного уровня обслуживания.

В большинстве случаев высокая утилизация CPU и дискового I/O связана с неоптимальными индексами или большой нагрузкой на хосты. Изучите данные о сессиях и запросах, собранные инструментом диагностики производительности.

Устранение проблем с дефицитом ресурсов

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

Диагностика наличия блокировок

Причиной низкой производительности кластера могут быть блокировки (locks), вызванные попытками одновременного доступа к одному и тому же ресурсу БД (таблице, строке).

Чтобы выявить блокировки с помощью инструмента диагностики производительности:

  1. Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.

  2. Нажмите на имя нужного кластера, затем выберите вкладку Диагностика производительности.

  3. На вкладке Сессии в поле Срез выберите значение WAIT_EVENT_TYPE.

    • Обратите внимание на график Lock. Он показывает количество запросов, которые в выбранный период находились в состоянии блокировки.
    • Чтобы получить детальную информацию о запросах, выполнявшихся в выбранный период, перейдите на вкладку Запросы.

    Подробнее про отображаемые сведения см. в документации PostgreSQL.

Чтобы диагностировать наличие блокировок средствами PostgreSQL, выполните запрос:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;

Подробнее о выборке запросов с блокировками см. в документации PostgreSQL.

Устранение проблем с блокировками

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

Диагностика ошибок подключения

Количество соединений с базой данных ограничено параметром max_connections и рассчитывается по формуле:

200 × <доля_vCPU_на_хосте> — 15

Здесь <доля_vCPU_на_хосте> — произведение количества vCPU на их гарантированную долю, а 15 — количество зарезервированных служебных соединений. Полученное количество подключений распределяется между ролями базы данных.

Если количество открытых соединений достигает лимита, в логах кластера появляются ошибки:

  • Too many connections for role.
  • Server conn crashed.
  • Invalid server parameter.
  • Query wait timeout.

Чтобы получить подробную информацию об использовании доступных подключений с помощью инструментов мониторинга:

  1. Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.

  2. Нажмите на имя нужного кластера, затем выберите вкладку Мониторинг.

  3. Изучите график Total pooler connections.

    Managed Service for PostgreSQL не допускает подключений напрямую к СУБД, вместо этого происходит подключение к менеджеру подключений.

    • Характеристика Clients отражает количество клиентских подключений к менеджеру подключений.

    • Характеристика Servers отражает количество соединений между СУБД и менеджером подключений.

      Обратите внимание на количество соединений — высокие значения говорят о том, что некоторые запросы держат подключения открытыми слишком долго.

Устранение проблем с подключениями

Чтобы решить проблему с количеством подключений:

Если нагрузка все еще высокая или оптимизировать нечего, остается только поднять класс хостов.

Диагностика недостатка места в хранилище

Если кластер демонстрирует низкую производительность и в логах наблюдается ошибка ERROR: cannot execute INSERT in a read-only transaction, возможно, в хранилище кластера закончилось свободное место, и он перешел в режим read-only.

Чтобы проверить наличие свободного места в хранилище кластера:

  1. Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.

  2. Нажмите на имя нужного кластера, затем выберите вкладку Мониторинг.

  3. Проверьте график Disk capacity in primary, [bytes].

    Обратите внимание на значение параметра Used, показывающего степень заполнения хранилища кластера.

Устранение проблем с недостатком места в хранилище

Рекомендации по устранению проблем приведены в разделе Управление дисковым пространством.

Предыдущая
Создание кластера PostgreSQL для «1С:Предприятия»
Следующая
Миграция БД из Managed Service for PostgreSQL