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

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

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

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

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

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

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

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

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

• Запросы, инициированные системой. (то есть запросы, выполняемые ролью 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).
• Запросы, объединяющие фильтры и сортировку.

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

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

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

Чтобы была выдана рекомендация по индексу, модуль настройки должен оценить, что индекс улучшит по крайней мере один запрос в анализируемой нагрузке как минимум в число раз, заданное с помощью 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 DML в таблице, для которой создается индекс.
• Показывать минимальное (среднее за день) количество операций чтения 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, и в соответствии с этим значением будет запланировано следующее выполнение.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Автономная настройка поддерживается на версиях 12 или выше гибких серверов База данных Azure для 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_status должно быть задано значение succeeded.

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

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

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

• Хранилище запросов.
• Настройте автономную настройку.
• Используйте рекомендации по автономной настройке.