Ajuste autónomo en Azure Database for PostgreSQL - Servidor flexible

El ajuste autónomo es una característica de Azure Database for PostgreSQL servidor flexible que analiza las consultas registradas desde la carga de trabajo y proporciona recomendaciones para mejorar el rendimiento de esas consultas.

Se trata de una oferta integrada en Azure Database for PostgreSQL servidor flexible que se basa en la funcionalidad del almacén de consultas. El ajuste autónomo analiza la carga de trabajo registrada en el almacén de consultas y genera recomendaciones sobre índices o tablas para mejorar el rendimiento de la carga de trabajo analizada. Puede generar recomendaciones para crear nuevos índices, eliminar índices duplicados o sin usar, analizar tablas que no tienen estadísticas o estadísticas obsoletas o tablas infladas al vacío.

• Identifique qué índices son beneficiosos para crear porque podrían mejorar significativamente las consultas analizadas durante una sesión de optimización autónoma.
• Identifique los índices que son duplicados exactos y que se pueden eliminar.
• Identifique los índices que no se usan en un período configurable que podrían ser candidatos para eliminar.
• Identifique los índices marcados como no válidos que se deben volver a indexar para convertirlos en válidos.
• Identifique las tablas que carecen de estadísticas actuales que se deben analizar.
• Identifique las tablas que están hinchadas que se deben vaciar.

Descripción general del algoritmo de ajuste autónomo

Cuando configura el parámetro index_tuning.mode en report, el sistema inicia automáticamente sesiones de ajuste con la frecuencia que se configura en el parámetro index_tuning.analysis_interval, expresada en minutos.

En la primera fase, la sesión de optimización busca la lista de bases de datos en las que las recomendaciones podrían afectar significativamente al rendimiento general del sistema. Para ello, recopila todas las consultas registradas por el Almacén de consultas cuyas ejecuciones se capturaron dentro del intervalo de búsqueda en el que se centra esta sesión de ajuste. El intervalo de búsqueda abarca actualmente hasta los últimos index_tuning.analysis_interval minutos, desde la hora de inicio de la sesión de ajuste.

Para todas las consultas iniciadas por el usuario con ejecuciones registradas en el Almacén de consultas y cuyas estadísticas en runtime no se restablecen, el sistema las clasifica en función del tiempo de ejecución total agregado. Centra su atención en las consultas más destacadas, en función de su duración.

Las siguientes consultas se excluyen de esa lista:

• Consultas iniciadas por el sistema. (es decir, las consultas ejecutadas por el rol azuresu)
• Consultas ejecutadas en el contexto de cualquier base de datos del sistema (azure_sys, template0, template1 y azure_maintenance).

El algoritmo itera las bases de datos de destino, buscando posibles índices que podrían mejorar el rendimiento de las cargas de trabajo analizadas. También busca índices que puede eliminar porque son duplicados o no se usan durante un período de tiempo configurable. También identifica tablas que carecen de estadísticas actuales o tablas infladas.

Recomendaciones de CREATE INDEX

Para cada base de datos identificada como candidata para analizar, el proceso considera todas las consultas SELECT, UPDATE, INSERT y DELETE ejecutadas durante el intervalo de búsqueda y en el contexto de esa base de datos específica.

El proceso clasifica el conjunto resultante de consultas en función de su tiempo de ejecución total agregado y analiza la parte superior index_tuning.max_queries_per_database de las posibles recomendaciones de índice.

Las posibles recomendaciones tienen como objetivo mejorar el rendimiento de estos tipos de consultas:

• Consultas con filtros (es decir, consultas con predicados en la cláusula WHERE).
• Las consultas que combinan varias relaciones, independientemente de si siguen la sintaxis en la que las combinaciones se expresan con la cláusula JOIN o si los predicados de combinación se expresan en la cláusula WHERE.
• Consultas que combinan filtros y predicados de combinación.
• Consultas con agrupación (consultas con una cláusula GROUP BY).
• Consultas que combinan filtros y agrupación.
• Consultas con ordenación (consultas con una cláusula ORDER BY).
• Consultas que combinan filtros y ordenación.

Si una consulta hace referencia a una columna de una tabla y esa tabla no tiene estadísticas, el proceso no genera ninguna recomendación de índice para mejorar su ejecución. Sin embargo, genera una recomendación para analizar la tabla.

index_tuning.max_indexes_per_table especifica el número de índices que se pueden recomendar, excepto los índices que ya podrían existir en la tabla para cualquier tabla única a la que haga referencia cualquier número de consultas durante una sesión de ajuste.

index_tuning.max_index_count especifica el número de recomendaciones de índice generadas para todas las tablas de cualquier base de datos analizada durante una sesión de ajuste.

Para que se emita una recomendación de índice, el motor de ajuste debe calcular que este mejora al menos una consulta de la carga de trabajo analizada por un factor especificado con index_tuning.min_improvement_factor.

Del mismo modo, el proceso comprueba todas las recomendaciones de índice para asegurarse de que no introducen regresión en ninguna consulta única de esa carga de trabajo de un factor especificado con index_tuning.max_regression_factor.

Todos los parámetros mencionados en los párrafos anteriores, sus valores predeterminados y intervalos válidos se describen en las opciones de configuración.

El script generado junto con la recomendación de crear un índice sigue este patrón:

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

Incluye la cláusula CONCURRENTLY. Para obtener más información sobre los efectos de esta cláusula, consulte la documentación oficial de PostgreSQL para CREATE INDEX.

El ajuste autónomo genera automáticamente los nombres de los índices recomendados, que normalmente constan de los nombres de las distintas columnas de clave separadas por "_" (caracteres de subrayado) y con un sufijo constante "_idx". Si la longitud total del nombre supera los límites de PostgreSQL o si entra en conflicto con las relaciones existentes, el nombre será ligeramente diferente. Podría truncarse y se podría anexar un número al final del nombre.

Calcular el impacto de una recomendación CREATE INDEX

El impacto de crear una recomendación de índice se mide en IndexSize (megabytes) y QueryCostImprovement (porcentaje).

IndexSize es un valor único que representa el tamaño estimado del índice, teniendo en cuenta la cardinalidad actual de la tabla y el tamaño de las columnas a las que hace referencia el índice recomendado.

QueryCostImprovement consta de una matriz de valores, donde cada elemento representa la mejora del costo del plan para cada consulta cuyo costo del plan se estima que mejorará si existiera este índice. Cada elemento muestra el identificador de la consulta (consultado) y el porcentaje por el que el costo del plan mejoraría si se implementara la recomendación (dimensional).

Recomendaciones DROP INDEX y REINDEX

Para cada base de datos identificada como candidata, el proceso inicia una nueva sesión. Una vez completada la fase de recomendaciones CREATE INDEX , recomienda quitar o volver a indexar los índices existentes, en función de los siguientes criterios:

• Excluir si se considera duplicado de otros.
• Excluir si no se usa durante un período de tiempo configurable.
• Vuelva a indexar los índices marcados como no válidos.

Quitar índices duplicados

Las recomendaciones para quitar índices duplicados comienzan identificando qué índices tienen duplicados.

Los duplicados se clasifican en función de las distintas funciones que se pueden atribuir al índice y en función de sus tamaños estimados.

Por último, el proceso recomienda quitar todos los duplicados con una clasificación inferior a su líder de referencia y describe por qué cada duplicado se clasificó como estaba.

Para que se consideren duplicados dos índices, deben:

• Crearse en función de la misma tabla.
• Ser un índice del mismo tipo exacto.
• Haga coincidir sus columnas clave y, en el caso de claves de índice multicolumna, haga coincidir también el orden en que se referencian.
• Coincidir en el árbol de expresión de su predicado. Esta condición solo se aplica a índices parciales.
• Coincidir en el árbol de expresión de todas las referencias de columna no simples. Esta condición solo se aplica a los índices creados en expresiones.
• Coincidir en la intercalación de cada columna a la que se hace referencia en la clave.

Quitar índices sin usar

Las recomendaciones para quitar índices sin usar identifican esos índices que:

• No se usaron durante al menos index_tuning.unused_min_period días.
• Muestran una cantidad mínima (media diaria) de DML index_tuning.unused_dml_per_table en la tabla donde se crea el índice.
• Muestran una cantidad mínima (media diaria) de lecturas index_tuning.unused_reads_per_table en la tabla donde se crea el índice.

Volver a indexar índices no válidos

Las recomendaciones para volver a indexar los índices existentes identifican esos índices marcados como no válidos. Para obtener más información sobre por qué y cuándo los índices están marcados como no válidos, consulte la documentación oficial de REINDEX en PostgreSQL.

Calcular el impacto de una recomendación DROP INDEX

El impacto de una recomendación DROP INDEX se mide en dos dimensiones: Ventaja (porcentaje) e IndexSize (megabytes).

La ventaja es un valor único que puede omitir por ahora.

IndexSize es un valor único que representa el tamaño estimado del índice, teniendo en cuenta la cardinalidad actual de la tabla y el tamaño de las columnas a las que hace referencia el índice recomendado.

Recomendaciones de tabla

Para cada base de datos identificada como candidata para analizar, el proceso inicia una sesión que pretende generar recomendaciones de nivel de tabla. Estas recomendaciones le invitan a ejecutar ANALYZE o VACUUM en las tablas a las que acceden las consultas inspeccionadas. El motor de optimización considera que ejecutar estos comandos podría mejorar el rendimiento de la carga de trabajo.

Recomendaciones de tabla ANALYZE

Las recomendaciones para analizar una tabla identifican esas tablas que:

• Se mencionan en una consulta, tienen alguna columna de esa tabla utilizada en uno de sus predicados (WHERE, JOIN, ORDER BY, GROUP BY), y además cumplen una de las dos condiciones siguientes:
  • Nunca se analizan.
  • Se analizaron en algún momento, pero ahora carecen de estadísticas (normalmente porque el servidor se bloqueó antes de que las estadísticas se conservaran en el disco).

Recomendaciones de tabla VACUUM

Las recomendaciones para el vaciado de una tabla identifican las tablas que están sobredimensionadas. El proceso genera estas recomendaciones solo cuando autovacuum_enabled no se establece en off en el nivel de servidor al analizar la carga de trabajo.

Configuración del ajuste autónomo

Puede habilitar, deshabilitar y configurar el ajuste autónomo a través de un conjunto de parámetros que controlan su comportamiento.

Al habilitar el ajuste autónomo, se reactiva con una frecuencia configurada en el parámetro (que tiene como valor predeterminado 720 minutos o 12 horas) y comienza a analizar la carga de trabajo registrada por el index_tuning.analysis_interval almacén de consultas durante ese período.

Si cambia el valor de index_tuning.analysis_interval, el nuevo valor solo surte efecto después de que se complete la siguiente ejecución programada. Por ejemplo, si habilita el ajuste autónomo un día a las 10:00 a. m., porque el valor predeterminado de index_tuning.analysis_interval es de 720 minutos, la primera ejecución se programa para empezar a las 10:00 p. m. ese mismo día. Los cambios realizados en el valor de index_tuning.analysis_interval entre las 10:00 y las 10:00 p. m. no afectan a esa programación inicial. Solo cuando se completa la ejecución programada, lee el valor actual establecido para index_tuning.analysis_interval y programa la siguiente ejecución según ese valor.

Use las siguientes opciones para configurar parámetros de ajuste autónomo:

Si usa los comandos az postgres flexible-server autonomous-tuning show-settings de la CLI y az postgres flexible-server autonomous-tuning set-settings para mostrar o modificar cualquiera de los valores de ajuste autónomos, los valores aceptados como argumentos para el --name parámetro son los que se muestran en la columna Parámetro de la tabla anterior, pero sin incluir el prefijo index_tuning..

Información generada por el ajuste autónomo

Usar recomendaciones de ajuste autónomo describe detalladamente cómo obtener y usar las recomendaciones generadas por el ajuste autónomo.

Limitaciones y compatibilidad

En la lista siguiente se describen las limitaciones y el ámbito de compatibilidad para el ajuste autónomo.

Eliminación automática de recomendaciones

El sistema elimina automáticamente las recomendaciones 35 días después de la última vez que las generó. Para que este mecanismo de eliminación automática funcione, debe habilitar el ajuste autónomo.

Dependencia de la extensión hypopg

Para generar recomendaciones CREATE INDEX, la optimización autónoma usa la extensión hypopg.

Si la extensión existe cuando comienza una sesión de optimización, el proceso lo usa en el esquema donde se creó. Cuando finaliza la sesión de ajuste, el proceso no elimina la extensión. Una excepción a esta regla es si la extensión se creó en el pg_catalog esquema. Si es así, el ajuste autónomo quita la extensión.

Si la extensión no existía desde el principio o el proceso la elimina porque se creó en el esquema pg_catalog, la optimización autónoma la crea en un esquema denominado ms_temp_recommendations709253. Cuando la sesión de ajuste finaliza correctamente, el proceso elimina la extensión y el esquema.

Los usuarios que sean miembros del rol azure_pg_admin pueden eliminar la extensión hypopg en cualquier momento, incluso si la ha creado la función de ajuste autónomo. Sin embargo, quitarla mientras se ejecuta una sesión de optimización autónoma podría provocar que se produzca un error en la sesión y no genere ninguna recomendación.

Niveles de proceso y SKU admitidos

Servidor flexible de Azure Database for PostgreSQL admite el ajuste autónomo en todos los niveles disponibles actualmente: Burstable, De uso general y Optimizado para memoria. También admite el ajuste autónomo en cualquier SKU de proceso compatible actualmente con al menos 4 vCores.

Versiones admitidas de PostgreSQL

Azure Database for PostgreSQL Flexible Server es compatible con la optimización autónoma en las versiones principales12 o superiores.

Uso de search_path

La optimización automática usa el valor de la columna search_path de query_store.qs_view. Cuando analiza cada consulta, usa el mismo search_path valor que se estableció cuando la consulta se ejecutó originalmente para analizar posibles recomendaciones.

Consultas parametrizadas

Las consultas con parámetros creadas con PREPARE o mediante el uso del protocolo de consultas extendidas se procesan sintácticamente y se analizan para generar recomendaciones de índices.

Para el análisis de consultas con parámetros, la sincronización autónoma requiere que pg_qs.parameters_capture_mode esté establecido en capture_first_sample cuando el almacén de consultas captura la ejecución de la consulta. También requiere que el almacén de consultas capture correctamente los parámetros cuando se ejecuta la consulta. En otras palabras, para la consulta que se está analizando, la parameters_capture_status columna de query_store.qs_view debe establecerse succeededen .

Modo de solo lectura y réplicas de lectura

Dado que el ajuste autónomo se basa en los datos que el almacén de consultas conserva de forma local en la azure_sys base de datos, y no se admiten las réplicas de lectura ni los casos en que un servidor está en modo de solo lectura, la característica no es compatible con las réplicas de lectura ni con los servidores que están en modo de solo lectura.

Cualquier recomendación que veas en una réplica de lectura se ha generado en la réplica principal tras analizar exclusivamente la carga de trabajo que se ejecutó en dicha réplica principal.

Reducción vertical del proceso

Si se habilita el ajuste automático en un servidor y, a continuación, se reduce la capacidad de proceso de ese servidor a menos del número mínimo de vCores necesarios, la función sigue habilitada. Dado que la función no se admite en servidores con menos de 4 vCores, no se ejecuta para analizar la carga de trabajo y generar recomendaciones, incluso si index_tuning.mode se estableció en ON al reducir la capacidad de proceso. Aunque el servidor no cumple los requisitos mínimos, no se puede acceder a todos los index_tuning.* parámetros. Cada vez que escalas el servidor a una capacidad que cumpla los requisitos mínimos, index_tuning.mode se configura con el valor que se estableció antes de reducirlo a un rendimiento que no cumple los requisitos.

Alta disponibilidad y réplicas de lectura

Si configura alta disponibilidad o réplicas de lectura en su servidor, tenga en cuenta las implicaciones que conlleva generar cargas de trabajo de escritura intensiva en el servidor principal al implementar los índices recomendados. Tenga especial cuidado al crear índices con un tamaño estimado grande.

Motivos por los que el ajuste autónomo podría no generar recomendaciones de creación de índices para determinadas consultas

El ajuste autónomo no genera CREATE INDEX recomendaciones para los siguientes tipos de consultas:

• Consultas que encuentran un error cuando el motor de ajuste autónomo intenta obtener su salida EXPLAIN durante la fase de análisis.
• Consultas que hacen referencia a tablas sin estadísticas sobre su contenido en el catálogo del pg_statistic sistema. Ejecute ANALYZE en esas tablas para que el motor de optimización pueda considerar estas consultas en el futuro.
• Consultas con texto de consulta truncado en el almacén de consultas. Este truncamiento se produce cuando la longitud del texto de consulta supera el valor configurado en pg_qs.max_query_text_length.
• Consultas que hacen referencia a objetos que se han quitado o cambiado el nombre antes de que se produzca el análisis. Estas consultas todavía pueden ser válidas sintácticamente, pero no son semánticamente válidas.
• Consultas que acceden a tablas temporales o a índices de tablas temporales.
• Consultas que acceden a vistas o vistas materializadas.
• Consultas que acceden a tablas particionadas.
• Consultas identificadas como instrucciones de utilidad. Las instrucciones de utilidad o los comandos de utilidad son, básicamente, cualquier instrucción que no se considere SELECT, INSERT, UPDATE, DELETEo MERGE, y ciertos comandos que contienen una de estas instrucciones.
• Consultas que no se encuentran entre las index_tuning.max_queries_per_database más lentas, para la base de datos y el periodo analizados.
• Las consultas que se ejecutan en el contexto de una base de datos específica, cuando ninguna de esas consultas se identifica como la más lenta en el nivel de servidor.

Contenido relacionado

• Almacén de consultas.
• Configurar el ajuste autónomo.
• Use recomendaciones de ajuste autónomo.