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

Статья создана

Yandex Cloud

Перед началом работы
Диагностика неэффективного выполнения запросов
Устранение проблем с неэффективными запросами
Диагностика дефицита ресурсов
Устранение проблем с дефицитом ресурсов
Диагностика наличия блокировок
Устранение проблем с блокировками
Диагностика ошибок подключения
Устранение проблем с подключениями
Диагностика недостатка места в хранилище
Устранение проблем с недостатком места в хранилище

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

неэффективное выполнение запросов в PostgreSQL,
высокая утилизация CPU, дискового I/O и сети,
блокировки,
исчерпание доступных подключений,
исчерпание свободного места в хранилище.

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

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

Выберите базы данных для диагностики.
Разрешите доступ к базам из консоли управления.
Активируйте сбор статистики о сессиях и запросах.
Включите модуль auto_explain для расширенного логирования планов выполнения запросов.
Чтобы в лог производительности попадало больше запросов, в настройках СУБД уменьшите значение параметра 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.

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

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

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

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

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

Совет

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

Подробнее см. в разделе SQL-запросы в консоли управления.
Обновить статистику с помощью команды 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), вызванные попытками одновременного доступа к одному и тому же ресурсу БД (таблице, строке).

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

Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.
Нажмите на имя нужного кластера, затем выберите вкладку Диагностика производительности.
На вкладке Сессии в поле Срез по выберите значение 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.

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

Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.
Нажмите на имя нужного кластера, затем выберите вкладку Хосты.
На вкладке Мониторинг проверьте графики PostgreSQL connections, [count] и Pooler connections, [count].

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

Изучите графики:
- PostgreSQL connections, [count] отражает количество соединений между СУБД и пулером.
  
  Обратите внимание на количество соединений в статусах Waiting и Idle in transaction. Высокие значения говорят о том, что некоторые запросы держат подключения открытыми слишком долго.
- Pooler connections, [count] отражает использование подключений к пулеру.
  
  Обратите внимание на количество соединений в статусе Used clients. Эта характеристика указывает на количество соединений, установленных приложением с кластером.

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

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

Увеличьте значение параметра max_connections в настройках СУБД.
Распределите свободные подключения между пользователями.
Оптимизируйте запросы таким образом, чтобы не было длинных транзакций.

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

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

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

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

Перейдите на страницу каталога и выберите сервис Managed Service for PostgreSQL.
Нажмите на имя нужного кластера, затем выберите вкладку Мониторинг.
Проверьте график Disk capacity in primary, [bytes].

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

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

Рекомендации по устранению проблем приведены в разделах Вывод кластера из режима read-only и Отслеживание перехода в read-only.