Мониторинг производительности с помощью хранилища запросов

Область применения: отдельный сервер Базы данных Azure для PostgreSQL

Компонент "Хранилище запросов" в базе данных Azure для PostgreSQL позволяет отслеживать производительность запросов с течением времени. Хранилище запросов упрощает устранение неполадок, позволяя быстро выявлять самые медленные и ресурсоемкие запросы. Хранилище запросов автоматически ведет журнал запросов и статистики выполнения и сохраняет их для просмотра. Этот компонент разделяет данные по периодам, давая представление о закономерностях использования баз данных. Данные для всех пользователей, баз данных и запросов хранятся в базе данных с именем azure_sys в экземпляре базы данных Azure для PostgreSQL.

Важно!

Не изменяйте базу данных azure_sys или ее схемы. Это приведет к нарушению работы хранилища запросов и связанных функций оценки производительности.

Включение хранилища запросов

Хранилище запросов является дополнительной функцией, поэтому оно не активно на сервере по умолчанию. Хранилище включается или отключается глобально для всех (но не для отдельных) баз данных на данном сервере.

Включение хранилища запросов с помощью портала Azure

  1. Войдите на портал Azure и выберите сервер службы "База данных Azure для PostgreSQL".
  2. В разделе меню Параметры выберите Параметры сервера.
  3. Найдите параметр pg_qs.query_capture_mode.
  4. Установите значение TOP и нажмите Сохранить.

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

  1. Найдите параметр pgms_wait_sampling.query_capture_mode.
  2. Установите значение ALL и нажмите Сохранить.

Кроме того, вы можете установить эти параметры с помощью Azure CLI.

az postgres server configuration set --name pg_qs.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value TOP
az postgres server configuration set --name pgms_wait_sampling.query_capture_mode --resource-group myresourcegroup --server mydemoserver --value ALL

Подождите около 20 минут, пока первый набор данных сохранится в базе данных azure_sys.

Данные в хранилище запросов

Хранилище запросов включает два хранилища:

  • хранилище статистики времени выполнения для хранения статистических сведений о выполнении запросов;
  • хранилище статистики ожидания для хранения статистических сведений об ожидании.

Распространенные сценарии использования хранилища запросов включают следующие:

  • определение числа выполнений запроса за данный период времени;
  • сравнение среднего времени выполнения запроса за периоды времени для выявления больших расхождений;
  • Определение самых медленно выполняющихся запросов за прошедшие X часов.
  • Определение основных N запросов, ожидающих ресурсы.
  • понимание характера ожидания для конкретного запроса.

С целью экономии места к статистическим данным о выполнении запросов в хранилище статистики времени выполнения применяется агрегирование за фиксированный настраиваемый период. Сведения в этих хранилищах отображаются путем запроса представлений хранилища запросов.

Доступ к информации из хранилища запросов

Данные Query Store хранятся в базе данных azure_sys на вашем сервере Postgres.

Следующий запрос возвращает сведения о запросах в хранилище запросов:

SELECT * FROM query_store.qs_view; 

Это запрос статистики ожидания:

SELECT * FROM query_store.pgms_wait_sampling_view;

Поиск запросов ожидания

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

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

Наблюдение Действие
Ожидания с высоким уровнем блокировки Проверьте текст затронутых запросов и выявите целевые сущности. Найдите в хранилище запросов другие запросы, изменяющие ту же сущность, которые часто выполняются и (или) имеют большую длительность. Найдя такие запросы, рекомендуется изменить логику приложения, чтобы улучшить параллелизм, или использовать менее строгий уровень изоляции.
Ожидания с большим числом операций ввода-вывода буфера Найдите в хранилище запросов запросы с большим числом физических операций чтения. Если они соответствуют запросам с высокими значениями ожидания ввода-вывода, попробуйте ввести индекс для базовой сущности, чтобы задать поиск вместо сканирования. Это позволит свести к минимуму затраты на операции ввода-вывода запросов. Ознакомьтесь с рекомендациями по повышению производительности серверов на портале: возможно, для этого сервера есть рекомендации по индексам, которые позволят оптимизировать запросы.
Ожидания с высокой загрузкой памяти Найдите в хранилище запросов те запросы, которые используют больше всего памяти. Вероятнее всего, эти запросы препятствуют дальнейшей обработке затронутых запросов. Ознакомьтесь с рекомендациями по повышению производительности для сервера на портале: возможно, есть рекомендации по индексам, которые позволят оптимизировать запросы.

Варианты настройки

При включении хранилища запросов оно сохраняет данные с 15-минутным периодом агрегирования: не более 500 уникальных запросов на период.

Ниже приведены настраиваемые параметры для хранилища запросов.

Параметр Описание Default Range
pg_qs.query_capture_mode Задает, какие инструкции отслеживаются. нет none, top, all
pg_qs.max_query_text_length Задает максимальную длину запроса, которую можно сохранить. Более длинные запросы будут усечены. 6000 100–10 000
pg_qs.retention_period_in_days Задает период хранения. 7 1–30
pg_qs.track_utility Задает, будут ли отслеживаться команды служебных программ. on on, off

Следующие параметры применяются исключительно к статистике ожидания.

Параметр Описание Default Range
pgms_wait_sampling.query_capture_mode Задает, какие инструкции отслеживаются для статистики ожидания. нет none, all
Pgms_wait_sampling.history_period Задайте частоту (в миллисекундах) выборки событий ожидания. 100 1–600 000

Примечание

pg_qs.query_capture_mode заменяет pgms_wait_sampling.query_capture_mode. Если параметр pg_qs.query_capture_mode имеет значение NONE, pgms_wait_sampling.query_capture_mode не оказывает влияния.

Используйте портал Azure или Azure CLI, чтобы получить значение для параметра или задать другое значение.

Представления и функции

Просмотр и управление хранилищем запросов осуществляется с помощью следующих представлений и функций. Любой пользователь с общедоступной ролью PostgreSQL может использовать эти представления для просмотра данных в хранилище запросов. Эти представления доступны только в базе данных azure_sys.

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

query_store.qs_view

Это представление возвращает текстовые данные запроса в хранилище запросов. Для каждого отдельного query_text используется отдельная строка. Данные недоступны через раздел интеллектуального управления производительностью на портале, API или CLI, но их можно найти, подключившись к azure_sys и запросив 'query_store.query_texts_view'.

имя; Тип Справочные материалы Описание
runtime_stats_entry_id BIGINT Идентификатор из таблицы runtime_stats_entries
user_id oid pg_authid.oid Идентификатор объекта пользователя, который выполнил инструкцию
db_id oid pg_database.oid Идентификатор объекта базы данных, в которой была выполнена инструкция
query_id BIGINT Внутренний хэш-код, вычисляемый на основе дерева синтаксического анализа инструкции
query_sql_text Varchar(10000) Текст репрезентативной инструкции. Разные запросы с одной структурой объединяются в кластеры: этот текст — текст для первого запроса в кластере.
plan_id BIGINT Идентификатор плана, соответствующего этому запросу (пока недоступно)
start_time TIMESTAMP Запросы проходят агрегирование по временным группам — интервал группы составляет 15 минут по умолчанию. Это время начала, соответствующее временной группе для этой записи.
end_time TIMESTAMP Время окончания, соответствующее временной группе для этой записи.
calls BIGINT Число выполнений запроса
total_time double precision Общее время выполнения запроса (в миллисекундах)
min_time double precision Минимальное время выполнения запроса (в миллисекундах)
min_time double precision Максимальное время выполнения запроса (в миллисекундах)
mean_time double precision Среднее время выполнения запроса (в миллисекундах)
stddev_time double precision Стандартное отклонение времени выполнения запроса (в миллисекундах)
rows BIGINT Общее число строк, полученных или затронутых инструкцией
shared_blks_hit BIGINT Общее число обращений инструкции к кэшу общего блока
shared_blks_read BIGINT Общее число операций чтения общего блока, выполненных инструкцией
shared_blks_dirtied BIGINT Общее число общих блоков, измененных инструкцией
shared_blks_written BIGINT Общее число общих блоков, записанных инструкцией
local_blks_hit BIGINT Общее число обращений инструкции к кэшу локального блока
local_blks_read BIGINT Общее число операций чтения локального блока, выполненных инструкцией
local_blks_dirtied BIGINT Общее число локальных блоков, измененных инструкцией
local_blks_written BIGINT Общее число локальных блоков, записанных инструкцией
temp_blks_read BIGINT Общее число временных блоков, считанных инструкцией
temp_blks_written BIGINT Общее число временных блоков, записанных инструкцией
blk_read_time double precision Общее время (в миллисекундах), затраченное инструкцией на чтение блоков (если track_io_timing включен, в противном случае нуль)
blk_write_time double precision Общее время (в миллисекундах), затраченное инструкцией на запись блоков (если track_io_timing включен, в противном случае нуль)

query_store.query_texts_view

Это представление возвращает текстовые данные запроса в хранилище запросов. Для каждого отдельного query_text используется отдельная строка.

имя; Тип Описание
query_text_id BIGINT Идентификатор для таблицы query_texts
query_sql_text Varchar(10000) Текст репрезентативной инструкции. Разные запросы с одной структурой объединяются в кластеры: этот текст — текст для первого запроса в кластере.

query_store.pgms_wait_sampling_view

Это представление возвращает текстовые данные запроса в хранилище запросов. Для каждого отдельного query_text используется отдельная строка. Данные недоступны через раздел интеллектуального управления производительностью на портале, API или CLI, но их можно найти, подключившись к azure_sys и запросив 'query_store.query_texts_view'.

имя; Тип Справочные материалы Описание
user_id oid pg_authid.oid Идентификатор объекта пользователя, который выполнил инструкцию
db_id oid pg_database.oid Идентификатор объекта базы данных, в которой была выполнена инструкция
query_id BIGINT Внутренний хэш-код, вычисляемый на основе дерева синтаксического анализа инструкции
event_type text Тип события, которого ожидает серверный компонент
event text Имя события ожидания, если серверный компонент сейчас находится в состоянии ожидания
calls Целочисленный тип Число одинаковых записанных событий

Функции

Query_store.qs_reset() returns void

qs_reset удаляет все статистические данные, собранные хранилищем запросов на данный момент. Эту функцию может выполнить только роль администратора сервера.

Query_store.staging_data_reset() returns void

staging_data_reset удаляет все статистические данные, собранные хранилищем запросов в памяти (то есть данные в памяти, которые еще не были очищены в базе данных). Эту функцию может выполнить только роль администратора сервера.

Azure Monitor

База данных Azure для PostgreSQL интегрирована с диагностическими настройками Azure Monitor. Параметры диагностики позволяют отправлять журналы Postgres в формате JSON в журналы Azure Monitor для аналитики и предупреждений, концентраторы событий для потоковой передачи и хранилище Azure для архивирования.

Важно!

Данная функция диагностики доступна только в ценовых категориях «Общее назначение» и «Оптимизация памяти».

Настройка параметров диагностики

Вы можете включить параметры диагностики для своего сервера Postgres с помощью портала Azure, интерфейса командной строки, REST API и PowerShell. Категории журналов, которые необходимо настроить: QueryStoreRuntimeStatistics и QueryStoreWaitStatistics.

Чтобы включить журналы ресурсов с помощью портала Azure:

  1. На портале перейдите в раздел «Параметры диагностики» в меню навигации вашего сервера Postgres.
  2. Выберите «Добавить диагностическую настройку».
  3. Присвойте имя параметру.
  4. Выберите предпочтительную конечную точку (учетная запись хранения, концентратор событий, аналитика журналов).
  5. Выберите типы журналов QueryStoreRuntimeStatistics и QueryStoreWaitStatistics.
  6. Сохраните вашу настройку.

Чтобы включить данный параметр с помощью PowerShell, CLI или REST API, см. статью о диагностических настройках.

Формат журнала JSON

В нижеприведенных таблицах описаны поля для двух типов журналов. Порядок появления выбранных полей зависит от выбранной конечной точки вывода.

QueryStoreRuntimeStatistics

Поле Описание
TimeGenerated [UTC] Метка времени, когда журнал был записан в формате UTC
ResourceId URI ресурса сервера Azure Postgres
Категория QueryStoreRuntimeStatistics
OperationName QueryStoreRuntimeStatisticsEvent
LogicalServerName_s Имя сервера Postgres
runtime_stats_entry_id_s Идентификатор из таблицы runtime_stats_entries
user_id_s Идентификатор объекта пользователя, который выполнил инструкцию
db_id_s Идентификатор объекта базы данных, в которой была выполнена инструкция
query_id_s Внутренний хэш-код, вычисляемый на основе дерева синтаксического анализа инструкции
end_time_s Время окончания, соответствующее временному интервалу для этой записи
calls_s Число выполнений запроса
total_time_s Общее время выполнения запроса (в миллисекундах)
min_time_s Минимальное время выполнения запроса (в миллисекундах)
max_time_s Максимальное время выполнения запроса (в миллисекундах)
mean_time_s Среднее время выполнения запроса (в миллисекундах)
ResourceGroup Группа ресурсов
SubscriptionId идентификатор подписки;
ResourceProvider Microsoft.DBForPostgreSQL
Ресурс Имя сервера Postgres
ResourceType Servers

QueryStoreWaitStatistics

Поле Описание
TimeGenerated [UTC] Метка времени, когда журнал был записан в формате UTC
ResourceId URI ресурса сервера Azure Postgres
Категория QueryStoreWaitStatistics
OperationName QueryStoreWaitEvent
user_id_s Идентификатор объекта пользователя, который выполнил инструкцию
db_id_s Идентификатор объекта базы данных, в которой была выполнена инструкция
query_id_s Внутренний хэш-код запроса
calls_s Число одинаковых записанных событий
event_type_s Тип события, которого ожидает серверный компонент
event_s Имя события ожидания, если серверная часть в настоящее время ожидает
start_time_t Время начала события
end_time_s Время окончания события
LogicalServerName_s Имя сервера Postgres
ResourceGroup Группа ресурсов
SubscriptionId идентификатор подписки;
ResourceProvider Microsoft.DBForPostgreSQL
Ресурс Имя сервера Postgres
ResourceType Servers

Ограничения и известные проблемы

  • Если на сервере PostgreSQL настроен параметр default_transaction_read_only, хранилище запросов не может записывать данные.
  • Операции хранилища запросов могут быть прерваны при обнаружении длинных запросов в Юникоде (>= 6000 байт).
  • Реплики чтения реплицируют данные хранилища запросов с первичного сервера. Это означает, что хранилище запросов реплики чтения не предоставляет статистику о запросах, выполняемых в реплике чтения.

Дальнейшие действия