Поделиться через

Автономная настройка

Автономная настройка в вашем гибком сервере базы данных Azure для PostgreSQL анализирует запросы, порожденные вашей рабочей нагрузкой, и предоставляет рекомендации по улучшению их производительности.

Это встроенное предложение в гибком экземпляре сервера Базы данных Azure для PostgreSQL, которое строится на основе функциональных возможностей хранилища запросов . Автономная настройка анализирует рабочую нагрузку, отслеживаемую хранилищем запросов, и создает рекомендации по индексу или таблице для повышения производительности проанализированной рабочей нагрузки. Он может предоставлять рекомендации по созданию новых индексов, устранению повторяющихся или неиспользуемых индексов, анализу таблиц, не имеющих статистики или имеющих устаревшую статистику, или выполнять очистку раздутых таблиц.

Общее описание алгоритма автономной настройки

index_tuning.mode При настройке reportпараметра сервера сеансы настройки автоматически запускаются с частотой, настроенной в параметре index_tuning.analysis_intervalсервера, выраженной в минутах.

На первом этапе сеанс настройки выполняет поиск списка баз данных, в которых он считает, что любые рекомендации, которые могут возникнуть, могут значительно повлиять на общую производительность системы. Для этого он собирает все запросы, записанные хранилищем запросов, выполнение которых было записано в интервале подстановки, на этом сеансе настройки основное внимание уделяется. Интервал подстановки в настоящее время охватывает последние index_tuning.analysis_interval минуты с момента начала сеанса настройки.

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

В этом списке исключены следующие запросы:

  • Запросы, инициированные системой. (то есть запросы, выполняемые ролью azuresu )
  • Запросы, выполняемые в контексте любой системной базы данных (azure_sys, , template0template1иazure_maintenance).

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

Рекомендации CREATE INDEX

Для каждой базы данных, определенной в качестве кандидата для анализа, все запросы SELECT, UPDATE, INSERT и DELETE, выполняемые в течение интервала поиска, и в контексте этой конкретной базы данных учитываются.

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

Потенциальные рекомендации предназначены для повышения производительности этих типов запросов:

  • Запросы с фильтрами (то есть запросы с предикатами в предложении WHERE);
  • Запросы, присоединенные к нескольким отношениям, соответствуют ли они синтаксису, в котором соединения выражаются с предложением JOIN, или выражаются ли предикаты соединения в предложении WHERE.
  • Запросы, объединяющие фильтры и предикаты соединения.
  • Запросы с группировкой (запросы с предложением GROUP BY).
  • Запросы, объединяющие фильтры и группировку.
  • Запросы с сортировкой (запросы с предложением ORDER BY).
  • Запросы, объединяющие фильтры и сортировку.

Замечание

Единственным типом индексов, которые в настоящее время рекомендует система, является B-Tree.

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

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

index_tuning.max_index_count указывает количество рекомендаций по индексу, созданных для всех таблиц любой базы данных, проанализированных во время сеанса настройки.

Для создания рекомендации по индексу подсистема настройки должна оценить, что он улучшает по крайней мере один запрос в анализируемой рабочей нагрузке с помощью фактора, указанного в index_tuning.min_improvement_factorпараметре.

Аналогичным образом, все рекомендации по индексу проверяются, чтобы убедиться, что они не вводят регрессию в одном запросе в этой рабочей нагрузке фактора, указанного с index_tuning.max_regression_factor.

Замечание

index_tuning.min_improvement_factor и то, и index_tuning.max_regression_factor другое относится к стоимости планов запросов, а не к их длительности или ресурсам, которые они используют во время выполнения.

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

Скрипт, созданный вместе с рекомендацией по созданию индекса, следует следующему шаблону:

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

Он включает предложение CONCURRENTLY. Дополнительные сведения о влиянии этого предложения см. в официальной документации PostgreSQL для CREATE INDEX.

Автономная настройка автоматически создает имена рекомендуемых индексов, которые обычно состоят из имен различных ключевых столбцов, разделенных символами "_" (подчеркивания) и константой "_idx" суффиксом. Если общая длина имени превышает ограничения PostgreSQL или если она сталкивается с существующими отношениями, имя немного отличается. Оно может быть усечено, а число может быть добавлено в конец имени.

Вычисление влияния рекомендации CREATE INDEX

Влияние создания рекомендации по индексу измеряется на IndexSize (мегабайты) и QueryCostImprovement (процент).

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

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

Рекомендации DROP INDEX и REINDEX

Для каждой базы данных, определяемой как кандидат, он должен инициировать новый сеанс, и после завершения этапа рекомендаций CREATE INDEX рекомендуется удалить или переиндексировать существующие индексы на основе следующих критериев:

  • Удалите, если это считается дубликатом других пользователей.
  • Удалите, если оно не используется для настраиваемого периода времени.
  • Индексы повторного индексирования, помеченные как недопустимые.

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

Рекомендации по удалению повторяющихся индексов: сначала определите, какие индексы имеют дубликаты.

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

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

Для двух индексов, которые следует считать повторяющимися, они должны:

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

Удаление неиспользуемых индексов

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

  • Не используются по крайней мере index_tuning.unused_min_period в течение нескольких дней.
  • Отображение минимального (ежедневного среднего) количества динамических index_tuning.unused_dml_per_table адресов в таблице, в которой создается индекс.
  • Отображение минимального (ежедневного index_tuning.unused_reads_per_table среднего) количества операций чтения в таблице, в которой создается индекс.

Повторное индексирование недопустимых индексов

Рекомендации по повторной индексации существующих индексов определяют те индексы, которые помечены как недопустимые. Дополнительные сведения о том, почему и когда индексы помечены как недопустимые, см . в официальной документации по REINDEX в PostgreSQL.

Вычисление влияния рекомендации DROP INDEX

Влияние рекомендации по снижению индекса измеряется на двух измерениях: преимущество (процент) и IndexSize (мегабайты).

Преимущество — это одно значение, которое можно игнорировать сейчас.

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

Рекомендации по таблицам

Для каждой базы данных, определяемой как кандидат для анализа, он инициирует сеанс, который направлен на создание рекомендаций на уровне таблицы. Эти рекомендации предлагают запустить АНАЛИЗ или ВАКУУМ на таблицах, к которым обращаются проверенные запросы, поскольку модуль настройки считает, что выполнение этих команд может повысить производительность вашей рабочей нагрузки.

Рекомендации по анализу таблиц

Рекомендации по анализу таблицы определяют эти таблицы, которые:

  • Ссылаются в запросе, и некоторые столбцы этой таблицы используются в своих предикатах (WHERE, JOIN, ORDER BY, GROUP BY), а также соответствуют одному из двух следующих условий:
    • Никогда не были проанализированы.
    • Были проанализированы в какой-то момент, но теперь не хватает статистики (как правило, из-за сбоя сервера до сохранения статистики на диске).

Рекомендации по таблицам VACUUM

Рекомендации по вакуумированию таблицы определяют те таблицы, которые раздуты. Эти рекомендации создаются только в том случае, если autovacuum_enabled не задано off значение на уровне сервера при анализе рабочей нагрузки.

Настройка автономной оптимизации

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

Если автономная настройка включена, она просыпается с частотой, настроенной в index_tuning.analysis_interval параметре сервера (по умолчанию — 720 минут или 12 часов) и начинает анализировать рабочую нагрузку, записанную в хранилище запросов в течение этого периода.

Обратите внимание, что если значение изменено index_tuning.analysis_interval, оно наблюдается только после завершения следующего запланированного выполнения. Таким образом, например, если включить автономную настройку в один день в 10:00 УТРА, так как значение index_tuning.analysis_interval по умолчанию составляет 720 минут, первое выполнение должно начинаться в 10:00 вечера в тот же день. Любые изменения, вносимые в значение в промежутке между 10:00 и 22:00, не влияют на начальное расписание. Только после завершения запланированного выполнения он будет считывать текущее значение и index_tuning.analysis_interval запланировать следующее выполнение в соответствии с этим значением.

Для настройки параметров автономной настройки доступны следующие параметры:

Parameter Описание По умолчанию Диапазон Units
index_tuning.analysis_interval Задает частоту, с которой запускается каждый сеанс оптимизации индекса, если задано REPORTзначение index_tuning.mode. 720 60 - 10080 minutes
index_tuning.max_columns_per_index Максимальное количество столбцов, которые могут быть частью ключа индекса для любого рекомендуемого индекса. 2 1 - 10
index_tuning.max_index_count Максимальные индексы, рекомендуемые для каждой базы данных во время одного сеанса оптимизации. 10 1 - 25
index_tuning.max_indexes_per_table Максимальное количество индексов, которые можно использовать для каждой таблицы. 10 1 - 25
index_tuning.max_queries_per_database Количество медленных запросов для каждой базы данных, для которой можно использовать индексы. 25 5 - 100
index_tuning.max_regression_factor Допустимая регрессия, представленная рекомендуемым индексом для любого из запросов, проанализированных во время одного сеанса оптимизации. 0.1 0.05 - 0.2 процент
index_tuning.max_total_size_factor Максимальный общий размер ( в процентах от общего места на диске), что все рекомендуемые индексы для любой конкретной базы данных могут использовать. 0.1 0 - 1 процент
index_tuning.min_improvement_factor Улучшение затрат, которое рекомендуется обеспечить по крайней мере одному из запросов, проанализированных во время одного сеанса оптимизации. 0.2 0 - 20 процент
index_tuning.mode Настраивает оптимизацию индекса как отключенную (OFF) или включенную только рекомендацию. Требует, чтобы хранилище запросов было включено с помощью параметра pg_qs.query_capture_modeTOP или ALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Минимальное количество ежедневных операций DML, влияющих на таблицу, поэтому их неиспользуемые индексы считаются для удаления. 1000 0 - 9999999
index_tuning.unused_min_period Минимальное количество дней, которое индекс не использовался на основе системной статистики, поэтому он считается снижением. 35 30 - 70
index_tuning.unused_reads_per_table Минимальное количество ежедневных операций чтения, влияющих на таблицу, чтобы их неиспользуемые индексы считались для удаления. 1000 0 - 9999999

Если вы используете команды az postgres flexible-server autonomous-tuning show-settings CLI и az postgres flexible-server autonomous-tuning set-settings отображаете или изменяете любые параметры автономной настройки, значения, принятые в качестве аргументов для --name параметра, являются теми, которые отображаются в столбце параметров предыдущей таблицы, но без включения префикса index_tuning..

Сведения, созданные автономной настройкой

Используйте рекомендации по автономной настройке подробно описывает, как получить и использовать рекомендации, созданные автономной настройкой.

Ограничения и возможность поддержки

Ниже приведен список ограничений и области поддержки для автономной настройки.

Автоматическое удаление рекомендаций

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

Зависимость от расширения гипопга

Для автономной настройки, чтобы создавать рекомендации CREATE INDEX, используется расширение hypopg.

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

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

Пользователи, являющиеся членами azure_pg_admin роли, могут удалять расширение hypopg в любое время, даже если оно создано функцией автономной настройки. Однако удаление его во время выполнения сеанса автономной настройки может привести к сбою этого сеанса и отсутствию каких-либо рекомендаций.

Поддерживаемые уровни вычислений и номера SKU

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

Поддерживаемые версии PostgreSQL

Автономная настройка поддерживается на версиях 12 или выше гибких серверов Azure Database for PostgreSQL.

Использование search_path

Автономная настройка использует значение, сохраненное в столбце search_path таблицы query_store.qs_view, чтобы при анализе каждого запроса использовать то же значение search_path, которое было установлено при его первоначальном выполнении, и на основе которого проводятся возможные рекомендации.

Параметризованные запросы

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

Для анализа параметризованных запросов автономная настройка требует, чтобы параметр pg_qs.parameters_capture_mode был задан capture_first_sample , если хранилище запросов фиксирует выполнение запроса. Кроме того, требуется, чтобы параметры правильно захватывались хранилищем запросов при выполнении запроса. Другими словами, для анализируемого запроса query_store.qs_view должен иметь значение столбца parameters_capture_statussucceeded.

Режим только для чтения и реплики чтения

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

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

Уменьшение масштаба вычислений

Если автономная настройка включена на сервере, и вы масштабируете вычислительные ресурсы этого сервера до меньше минимального количества необходимых виртуальных ядер, функция остается включенной. Так как эта функция не поддерживается на серверах с менее чем 4 виртуальными ядрами, она не выполняется для анализа рабочей нагрузки и создания рекомендаций, даже если index_tuning.mode задано значение ON при уменьшении масштаба вычислений. Хотя сервер не соответствует минимальным требованиям, все index_tuning.* параметры сервера недоступны. Каждый раз, когда вы масштабируете сервер обратно до вычислительной мощности, соответствующей минимальным требованиям, index_tuning.mode настраивается на значение, установленное до того, как вы уменьшили вычислительную мощность до уровня, не соответствующего требованиям.

Высокая доступность и реплики чтения

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

Причины, по которым автономная настройка не может создавать рекомендации по созданию индексов для определенных запросов

Ниже приведен список типов запросов, для которых автономная настройка не создает рекомендации CREATE INDEX. Запросы, которые:

  • Возникает ошибка при попытке автономного модуля настройки получить выходные данные EXPLAIN на этапе анализа.
  • Справочные таблицы, не имеющие статистики о их содержимом в системном каталоге pg_statistic. Запустите АНАЛИЗ в этих таблицах, чтобы подсистема настройки могли учитывать эти запросы в будущем.
  • Усеченный текст запроса в хранилище запросов. Это случай, когда длина текста запроса превышает значение, настроенное в pg_qs.max_query_text_length.
  • Ссылочные объекты, которые были удалены или переименованы перед анализом. Эти запросы по-прежнему могут быть синтаксически допустимыми, но не семантически допустимыми.
  • Доступ к временным таблицам или индексам во временных таблицах.
  • Доступ к представлениям или материализованным представлениям.
  • Доступ к секционированных таблицам.
  • Идентифицируются как инструкции служебной программы. Инструкции служебной программы или команды служебной программы— это, в основном, любые инструкции, которые не рассматриваются как SELECT, INSERT, UPDATE, DELETE или MERGE, и некоторые команды, содержащие одну из этих команд.
  • Не являются одними из самых медленных index_tuning.max_queries_per_database для базы данных и периода, проанализированных.
  • Выполнялись в контексте одной конкретной базы данных, когда ни один из этих запросов не был определен как самый медленный на уровне сервера.

Связанный контент

  • Last updated on