View a markdown version of this page

Solución de problemas básicos y comunes relacionados con el rendimiento de PostgreSQL en Aurora PostgreSQL - Amazon Aurora
Lista de comprobación de diagnóstico rápidoSobrecarga de tablas e índicesAgotamiento de los recursos de consultas paralelasAlta presión de conexión y autenticaciónUso de los eventos de espera de Información de rendimiento para solucionar problemasAjuste de la opción de autovacuumInformación relacionada

Solución de problemas básicos y comunes relacionados con el rendimiento de PostgreSQL en Aurora PostgreSQL

En esta guía se explican los cuatro problemas más comunes de rendimiento que afectan a las bases de datos de Aurora PostgreSQL: sobrecarga de tablas e índices, agotamiento de los recursos de consultas paralelas, alta presión de conexión y autenticación y ajuste de autovacuum. Utilice esta guía como una lista de comprobación de diagnóstico de primera transmisión cuando experimente una degradación del rendimiento, antes de iniciar una investigación más exhaustiva.

En cada sección se describen los síntomas que se pueden observar, se proporcionan consultas de diagnóstico para confirmar la causa raíz y se recomiendan medidas correctivas específicas.

Descripción de regresiones de rendimiento “nada ha cambiado”

Las cargas de trabajo de PostgreSQL suelen ejecutarse sin problemas durante semanas o meses y, después, experimentan una degradación repentina del rendimiento, aunque el código de la aplicación y los patrones de consulta no cambien. Esto se debe a que el entorno de la base de datos nunca es realmente estático: varios factores invisibles cambian con el tiempo y pueden provocar cambios en los planes o limitar los recursos:

  • La acumulación de sobrecarga es un cambio en la carga de trabajo. El control de simultaneidad multiversión (MVCC) de PostgreSQL conserva las versiones de filas antiguas hasta que la opción da autovacuum las recupere. Cuando las tuplas inactivas se acumulan de forma más rápida que la opción de autovacuum puede procesarlas, las tablas y los índices se agrandan físicamente. El planificador de consultas puede entonces cambiar de escaneos de índices eficaces a escaneos secuenciales, ya que las estimaciones de costos cambian a medida que aumenta el tamaño de la tabla. SQL no ha cambiado, pero sí los datos que ve el planificador.

  • Los nuevos valores de los parámetros representan un cambio en la carga de trabajo. Una consulta parametrizada que funciona correctamente para un rango de valores puede tener un rendimiento deficiente cuando la aplicación comienza a utilizar un rango diferente. PostgreSQL puede reutilizar un plan de ejecución genérico que no tenga en cuenta el sesgo de los datos en el nuevo rango, o es posible que las estadísticas del planificador no reflejen con precisión la distribución de esos valores. Cuando también hay una sobrecarga, el impacto se agrava: un plan subóptimo ahora escanea muchos más datos inactivos.

  • Las estadísticas pueden quedar obsoletas incluso cuando se ejecuta la opción de autovacuum. La opción de autovacuum desencadena ANALYZE en función del número de filas insertadas o actualizadas, no en función de si la distribución de los datos ha cambiado de forma considerable. Si la aplicación pasa a realizar consultas sobre un rango de valores o un periodo de tiempo diferente, las estimaciones de costos del planificador pueden ser inexactas, aunque la opción de autovacuum se haya ejecutado recientemente.

  • El crecimiento general de la base de datos es un cambio en la carga de trabajo. A medida que las tablas van creciendo con el paso del tiempo, aumenta el volumen de páginas de datos que las consultas deben escanear. Las consultas que han tenido un buen rendimiento en tablas de menor tamaño pueden desarrollar latencia a medida que aumenta el tamaño de la tabla, incluso si los índices y la lógica de la consulta permanecen sin cambios. Supervise VolumeBytesUsed para realizar un seguimiento de las tendencias de crecimiento de almacenamiento.

Al investigar las regresiones de rendimiento en las que “nada ha cambiado”, considere la acumulación de sobrecarga, los nuevos rangos de valores de los parámetros, el crecimiento general de la base de datos y las estadísticas obsoletas como las causas más probables. Siga los pasos de diagnóstico de esta guía para confirmar el factor que se aplica.

Para obtener más información, consulte los siguientes temas:

Lista de comprobación de diagnóstico rápido

Siga los pasos de clasificación ordenados que se indican a continuación cuando investigue por primera vez un problema relacionado con el rendimiento:

  1. Compruebe lo siguiente pg_stat_activity. Observe el número de conexiones, las sesiones inactivas durante las transacciones y las consultas de ejecución prolongada. Para obtener más información, consulte Conceptos esenciales para el ajuste de Aurora PostgreSQL.

  2. Compruebe si hay sobrecarga. Busque un valor alto de n_dead_tup en pg_stat_user_tables y plantéese utilizar pgstattuple para realizar una medición precisa. Para obtener más información, consulte Diagnóstico de sobrecarga de tablas e índices.

  3. Compruebe lo siguiente pg_stat_user_tables. Busque valores altos de n_dead_tup y marcas de tiempo de last_autovacuum obsoletas. Para obtener más información, consulte Uso de autovacuum de PostgreSQL en Aurora PostgreSQL.

  4. Revise EXPLAIN ANALYZE en las consultas lentas. Busque planos paralelos y escaneos secuenciales en tablas grandes. Para obtener más información, consulte Prácticas recomendadas para consultas paralelas en Aurora PostgreSQL.

  5. Compruebe las métricas de Información de rendimiento y CloudWatch. Revise el uso de la CPU, el número de conexiones, las IOPS y la memoria que se puede liberar. Para obtener más información, consulte Monitorización de Amazon Aurora. Para obtener más información sobre los eventos de espera y las medidas correctivas, consulte Eventos de espera de Aurora PostgreSQL.

  6. Revise el grupo de parámetros de la base de datos. Compruebe max_parallel_workers_per_gather y los ajustes de autovacuum. Para obtener más información, consulte Ajuste de los parámetros de PostgreSQL en Amazon RDS y Amazon Aurora.

Sobrecarga de tablas e índices

La sobrecarga de tablas e índices se produce cuando las tuplas inactivas se acumulan en las tablas de forma más rápida que la opción de autovacuum puede recuperarlas. Con el tiempo, esto provoca una degradación gradual del rendimiento de las consultas, un aumento del uso del almacenamiento y planes de consulta subóptimos.

Síntomas

  • Degradación gradual del rendimiento de las consultas durante semanas o meses.

  • El uso del almacenamiento aumenta a pesar de la estabilidad del volumen de datos.

  • El planificador de consultas elige los escaneos secuenciales en lugar de los escaneos de índices debido a que las estadísticas están obsoletas.

  • Valor alto de dead_tuple_count en las estadísticas de tablas.

Diagnóstico

Puede calcular la sobrecarga en todas las tablas mediante consultas al catálogo del sistema. Este método no requiere ninguna extensión:

SELECT schemaname, relname,
       n_dead_tup,
       n_live_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Para solucionar el problema de sobrecarga, puede usar la extensión pg_repack para reorganizar las tablas y los índices con un bloqueo mínimo. Para obtener más información, consulte Eliminación de la sobrecarga de las tablas con pg_repack y Eliminación de la sobrecarga de Amazon Aurora y RDS para PostgreSQL con pg_repack.

importante

En lugar de depender del mantenimiento manual, asegúrese de que la opción de autovacuum se encuentre habilitada y ajustada de forma correcta a la carga de trabajo. Consulte Ajuste de la opción de autovacuum para obtener recomendaciones de ajuste.

Agotamiento de los recursos de consultas paralelas

PostgreSQL puede ejecutar consultas paralelas para mejorar el rendimiento en las agregaciones y en los escaneos secuenciales grandes. Sin embargo, cada trabajo paralelo es un proceso de backend completo que se tiene en cuenta para max_worker_processes (y el sublímite max_parallel_workers) y asigna su propio work_mem. Una sola consulta con cuatro trabajos paralelos puede consumir cientos de megabytes de memoria y un uso considerable de CPU. En condiciones de alta simultaneidad, un paralelismo excesivo puede agotar la CPU y la memoria rápidamente.

Entre los síntomas más comunes se incluyen picos repentinos de CPU, un uso elevado de memoria por consulta y un aumento del valor DatabaseConnections en CloudWatch sin necesidad de realizar cambios en la aplicación. También puede observar eventos de espera, como, por ejemplo, IPC:BgWorkerStartup, IPC:ExecuteGather y IPC:ParallelFinish. Para obtener más información sobre estos eventos de espera, consulte Eventos de espera de IPC:parallel.

Para la mayoría de las cargas de trabajo de producción de alta simultaneidad y OLTP, deshabilite el paralelismo automático. Para ello, configure max_parallel_workers_per_gather = 0 en el grupo de parámetros de la base de datos. A continuación, puede habilitar el paralelismo de forma selectiva para sesiones de análisis o informes específicas. Para ello, configure el parámetro por sesión o por rol.

Para obtener instrucciones detalladas sobre el diagnóstico y el control del comportamiento de las consultas paralelas, consulte Prácticas recomendadas para consultas paralelas en Aurora PostgreSQL.

Alta presión de conexión y autenticación

La pérdida de conexión (abrir y cerrar con frecuencia las conexiones de bases de datos sin agruparlas) genera una sobrecarga de autenticación y puede agotar las ranuras de conexión disponibles. Las conexiones inactivas que permanecen abiertas también consumen ranuras sin realizar un trabajo útil.

Síntomas

  • Aumento del valor total_auth_attempts en la supervisión de Información de rendimiento. Para obtener más información, consulte Contadores no nativos para Aurora PostgreSQL.

  • Tiempos de establecimiento de conexiones lentas

  • Errores FATAL: too many connections for role o remaining connection slots are reserved

  • Picos de CPU relacionados con la pérdida de conexiones

Diagnóstico

Ejecute la siguiente consulta para comprobar el estado de la conexión actual:

SELECT
  setting::int AS max_connections,
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_settings
WHERE name = 'max_connections';

Un número elevado de conexiones idle o idle in transaction con respecto a max_connections indica que las conexiones no se están liberando correctamente. Las conexiones inactivas durante las transacciones son especialmente problemáticas, ya que mantienen bloqueos e impiden que la opción de autovacuum recupere las tuplas inactivas.

Corrección

  • Implemente la agrupación de conexiones. Utilice PGBouncer o Amazon RDS Proxy Amazon para reducir el número de conexiones directas a la base de datos. La agrupación de conexiones reutiliza las conexiones existentes en lugar de crear nuevas para cada solicitud.

  • Configurar idle_in_transaction_session_timeout. Este parámetro finaliza automáticamente las sesiones que permanecen inactivas en una transacción más allá de la duración especificada. Esto evita que las transacciones inactivas durante un tiempo prolongado mantengan el bloqueo y bloqueen la opción de autovacuum.

  • Revise la gestión de las conexiones de la aplicación. Asegúrese de que la aplicación cierre las conexiones con prontitud y no mantenga las transacciones abiertas más tiempo del necesario.

nota

Los trabajos de consultas paralelas consumen CPU y memoria. Si observa el agotamiento de los recursos junto con la actividad de consultas paralelas, consulte Agotamiento de los recursos de consultas paralelas para obtener orientación sobre cómo controlar el uso de trabajo paralelo.

Uso de los eventos de espera de Información de rendimiento para solucionar problemas

Información de rendimiento captura los eventos de espera que muestran dónde pasa el tiempo la base de datos. Al investigar los problemas de rendimiento, los eventos de espera le ayudan a identificar si el cuello de botella se debe a la CPU, la E/S, el bloqueo, la red o la comunicación entre procesos. Entre las categorías más comunes de eventos de espera que aparecen durante los problemas descritos en esta guía se incluyen las siguientes:

  • CPU: la sesión está activa en la CPU o esperando a que llegue a la CPU. Los eventos de espera de uso elevado de la CPU suelen guardar relación con un paralelismo excesivo o con planes de consulta ineficaces que escanean tablas sobrecargadas.

  • IPC (comunicación entre procesos): eventos de espera, como, por ejemplo, IPC:BgWorkerStartup, IPC:ExecuteGather y IPC:ParallelFinish, indican una sobrecarga de coordinación de consultas paralelas.

  • IO: eventos de espera, como, por ejemplo, IO:DataFileRead, indican que las consultas están leyendo datos del almacenamiento, ya que las páginas requeridas no están en la memoria compartida. Esto es habitual cuando las tablas sobrecargadas superan la caché de búfer.

  • Bloqueo: eventos de espera, como, por ejemplo, Lock:transactionid y Lock:tuple, indican una contención entre sesiones. Las conexiones inactivas durante las transacciones pueden mantener bloqueos que impidan otras consultas y la opción de autovacuum.

  • Cliente: eventos de espera, como, por ejemplo, Client:ClientRead, indican que la base de datos está esperando a que la aplicación envíe datos. Los eventos de espera de clientes pueden indicar una pérdida de conexión o una latencia de la red.

Para obtener una referencia completa de los eventos de espera que suelen indicar problemas de rendimiento y sus medidas correctivas recomendadas, consulte Eventos de espera de Aurora PostgreSQL.

Ajuste de la opción de autovacuum

La opción de autovacuum es el proceso en segundo plano que reclama las tuplas inactivas, evita la sobrecarga de tablas e índices, actualiza las estadísticas del planificador y protege contra el reinicio del identificador de transacción. La configuración predeterminada de autovacuum es conservadora y se ha diseñado para bases de datos pequeñas. Las cargas de trabajo de producción con un alto índice de escritura casi siempre requieren ajustes.

Cuando la opción de autovacuum no puede hacer frente a la carga de trabajo de escritura, la sobrecarga se acumula, las estadísticas del planificador se quedan obsoletas y el riesgo de reinicio del identificador de transacción aumenta. Si age(relfrozenxid) se acerca a los 2000 millones, la base de datos se cierra para evitar que los datos se dañen.

Para obtener orientación detallada sobre el ajuste de los parámetros de la opción de autovacuum, la supervisión de la actividad de limpieza y la configuración de las anulaciones por tabla, consulte Uso de autovacuum de PostgreSQL en Aurora PostgreSQL.

Información relacionada