IO:BufFileRead y IO:BufFileWrite

Los eventos IO:BufFileRead e IO:BufFileWrite ocurren cuando RDS para PostgreSQL crea archivos temporales. Cuando las operaciones requieren más memoria de la que los parámetros de la memoria de trabajo definen actualmente, escriben datos temporales en el almacenamiento persistente. Esta operación se llama a veces “derramamiento en el disco”

Versiones del motor admitidas

Esta información de eventos de espera es compatible con todas las versiones de RDS para PostgreSQL.

Contexto

IO:BufFileRead e IO:BufFileWrite se relacionan con el área de memoria de trabajo y el área de memoria de trabajo de mantenimiento. Para obtener más información acerca de estas áreas de memoria locales, consulte el punto Resource Consumption (Consumo de recursos) en la documentación de PostgreSQL.

El valor predeterminado de work_mem es de 4 MB. Si una sesión ejecuta operaciones en paralelo, cada proceso de trabajo que maneja el paralelismo utiliza 4 MB de memoria. Por esta razón, configure work_mem con cuidado. Si el valor es demasiado alto, una base de datos con muchas sesiones puede consumir demasiada memoria. Si establece el valor demasiado bajo, RDS para PostgreSQL crea archivos temporales en el almacenamiento local. La E/S del disco para estos archivos temporales puede reducir el rendimiento.

Si se observa la siguiente secuencia de eventos, es posible que la base de datos genere archivos temporales:

También puede observar un patrón de “motosierra”. Este patrón puede indicar que la base de datos crea archivos pequeños de forma constante.

Causas probables del aumento de las esperas

En general, estos eventos de espera son causados por operaciones que consumen más memoria de la que asignan los parámetros work_mem o maintenance_work_mem. Para compensar, las operaciones se escriben en archivos temporales. Las causas más comunes de los eventos IO:BufFileRead y IO:BufFileWrite son las siguientes:

Acciones

Recomendamos diferentes acciones en función de las causas del evento de espera.

Identifique el problema

Imagine una situación en la que Información sobre rendimiento no está activado y sospecha que IO:BufFileRead e IO:BufFileWrite se producen con más frecuencia de lo normal. Para identificar el origen del problema, puede configurar el parámetro log_temp_files para registrar todas las consultas que generen más KB de archivos temporales que el umbral especificado. De forma predeterminada, log_temp_files se establece en -1, lo que desactiva esta función de registro. Si establece este parámetro como 0, RDS para PostgreSQL registra todos los archivos temporales. Si lo establece en 1024, RDS para PostgreSQL registra todas las consultas que produzcan archivos temporales de más de 1 MB. Para más información sobre log_temp_files, consulte Error Reporting and Logging en la documentación de PostgreSQL.

Examine sus consultas de unión (join)

Es probable que la consulta utilice combinaciones. Por ejemplo, la siguiente consulta une cuatro tablas.

SELECT * 
       FROM "order" 
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = order.customer_id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

Una posible causa de los picos de uso de archivos temporales es un problema en la propia consulta. Por ejemplo, una cláusula rota podría no filtrar las uniones correctamente. Considere la segunda unión interna en el siguiente ejemplo.

SELECT * 
       FROM "order"
 INNER JOIN order_item 
       ON (order.id = order_item.order_id)
 INNER JOIN customer 
       ON (customer.id = customer.id)
 INNER JOIN customer_address 
       ON (customer_address.customer_id = customer.id AND 
           order.customer_address_id = customer_address.id)
 WHERE customer.id = 1234567890;

La consulta anterior une por error customer.id con customer.id, lo que genera un producto cartesiano entre cada cliente y cada pedido. Este tipo de unión accidental genera grandes archivos temporales. Según el tamaño de las tablas, una consulta cartesiana puede incluso llenar el almacenamiento. Es posible que la aplicación tenga uniones cartesianas cuando se den las siguientes condiciones:

Para ver si las tablas se unen con las claves adecuadas, inspeccione las directivas de consulta y de asignación objeto-relacional. Tenga en cuenta que algunas consultas de la aplicación no se llaman todo el tiempo, y algunas consultas se generan de forma dinámica.

Examinar las consultas ORDER BY y GROUP BY

En algunos casos, una cláusula ORDER BY puede dar lugar a un exceso de archivos temporales. Tenga en cuenta estas directrices:

Una consulta que utiliza una cláusula GROUP BY también puede requerir archivos temporales. Las consultas GROUP BY resumen los valores con funciones como las siguientes:

Para ajustar las consultas GROUP BY, siga las recomendaciones para las consultas ORDER BY.

Evite utilizar la operación DISTINCT

Si es posible, evite utilizar la operación DISTINCT para eliminar las filas duplicadas. Cuantas más filas innecesarias y duplicadas devuelva la consulta, más cara será la operación DISTINCT. Si es posible, agregue filtros en la cláusula WHERE, incluso si utiliza los mismos filtros para diferentes tablas. Filtrar la consulta y unirla correctamente mejora su rendimiento y reduce el uso de recursos. Además, evita que los informes y resultados sean incorrectos.

Si necesita utilizar DISTINCT para varias filas de una misma tabla, considere la posibilidad de crear un índice compuesto. Agrupar varias columnas en un índice puede mejorar el tiempo de evaluación de las filas distintas. Además, si utiliza RDS para PostgreSQL versión 10 o posterior, puede correlacionar estadísticas entre varias columnas con el comando CREATE STATISTICS.

Considere la posibilidad de utilizar funciones de ventana en lugar de funciones GROUP BY

Al utilizar GROUP BY, se modifica el conjunto de resultados y luego se recupera el resultado agregado. Con las funciones de ventana, se agregan los datos sin cambiar el conjunto de resultados. Una función de ventana utiliza la cláusula OVER para efectuar cálculos a través de los conjuntos definidos por la consulta, correlacionando una fila con otra. Puede utilizar todas las funciones GROUP BY en las funciones de ventana, pero también puede utilizar funciones como las siguientes:

Para minimizar el número de archivos temporales generados por una función de ventana, elimine las duplicaciones para el mismo conjunto de resultados cuando necesite dos agregaciones distintas. Analice la siguiente consulta.

SELECT sum(salary) OVER (PARTITION BY dept ORDER BY salary DESC) as sum_salary
     , avg(salary) OVER (PARTITION BY dept ORDER BY salary ASC) as avg_salary
  FROM empsalary;

Puede volver a escribir la consulta con la cláusula WINDOW de la siguiente manera.

SELECT sum(salary) OVER w as sum_salary
         , avg(salary) OVER w as_avg_salary
    FROM empsalary
  WINDOW w AS (PARTITION BY dept ORDER BY salary DESC);

De forma predeterminada, el planificador de ejecución de RDS para PostgreSQL consolida nodos similares para no duplicar operaciones. Sin embargo, al utilizar una declaración explícita para el bloque de la ventana, puede actualizar la consulta más fácilmente. También puede mejorar el rendimiento al evitar la duplicación.

Investigar las vistas materializadas y las instrucciones CTA

Cuando una vista materializada se actualiza, ejecuta una consulta. Esta consulta puede contener una operación como GROUP BY, ORDER BY o DISTINCT. Durante una actualización, es posible que observe un gran número de archivos temporales y los eventos de espera IO:BufFileWrite e IO:BufFileRead. Del mismo modo, cuando se crea una tabla basada en una instrucción SELECT, la instrucción CREATE TABLE ejecuta una consulta. Para reducir los archivos temporales necesarios, optimice la consulta.

Utilizar pg_repack al reconstruir índices

Cuando se crea un índice, el motor ordena el conjunto de resultados. A medida que las tablas aumentan de tamaño y los valores de la columna indexada se diversifican, los archivos temporales requieren más espacio. En la mayoría de los casos, no se puede evitar la creación de archivos temporales para tablas grandes sin modificar el área de memoria de trabajo de mantenimiento. Para obtener más información acerca de maintenance_work_mem, consulte https://www.postgresql.org/docs/current/runtime-config-resource.html en la documentación de PostgreSQL.

Una posible solución para recrear un índice grande es utilizar la extensión pg_repack. Para más información, consulte Reorganize tables in PostgreSQL databases with minimal locks en la documentación de pg_repack. Para obtener información sobre la configuración de la extensión en su instancia de base de datos de RDS para PostgreSQL, consulte Reducción de la sobrecarga en tablas e índices con la extensión pg_repack.

Aumentar maintenance_work_mem al hacer un clúster de tablas

El comando CLUSTER hace un clúster de la tabla especificada por table_name basado en un índice existente especificado por index_name. RDS para PostgreSQL recrea físicamente la tabla para que coincida con el orden de un índice determinado.

Cuando el almacenamiento magnético era frecuente, los clústeres eran comunes porque el rendimiento del almacenamiento era limitado. Ahora que el almacenamiento basado en SSD es común, los clústeres son menos populares. Sin embargo, si se hacen clústeres en las tablas, se puede aumentar ligeramente el rendimiento en función del tamaño de la tabla, índice, consulta, etc.

Si ejecuta el comando CLUSTER y observa los eventos de espera IO:BufFileWrite e IO:BufFileRead, ajuste maintenance_work_mem. Aumente el tamaño de la memoria a una cantidad bastante grande. Un valor alto significa que el motor puede utilizar más memoria para la operación de clusterización.

Ajustar la memoria para evitar IO:BufFileRead e IO:BufFileWrite

En algunas situaciones, es necesario ajustar la memoria. Su objetivo es equilibrar la memoria en las siguientes áreas de consumo mediante los parámetros adecuados, de la siguiente manera.

Para obtener más información sobre el ajuste de la memoria, consulte el punto Resource Consumption (Consumo de recursos) en la documentación de PostgreSQL.

Aumentar el tamaño del área de memoria de trabajo

En algunas situaciones, la única opción es aumentar la memoria que utiliza la sesión. Si las consultas están correctamente escritas y se utilizan las claves correctas para las uniones, considere aumentar el valor work_mem.

Para saber cuántos archivos temporales genera una consulta, establezca log_temp_files en 0. Si aumenta el valor de work_mem hasta el valor máximo identificado en los registros, evitará que la consulta genere archivos temporales. Sin embargo, work_mem establece el máximo por nodo del plan para cada conexión o proceso de trabajo paralelo. Si la base de datos tiene 5000 conexiones, y si cada una utiliza 256 MiB de memoria, el motor necesita 1.2 TiB de RAM. Esto significa que la instancia podría quedarse sin memoria.

Reservar suficiente memoria para el grupo de búferes compartidos

La base de datos utiliza áreas de memoria como el grupo de búferes compartidos, no solo el área de memoria de trabajo. Tenga en cuenta los requisitos de estas áreas de memoria adicionales antes de aumentar work_mem.

Por ejemplo, supongamos que su clase de instancia de RDS para PostgreSQL es db.r5.2xlarge. Esta clase tiene 64 GiB de memoria. De forma predeterminada, el 25 por ciento de la memoria se reserva para el grupo de búferes compartidos. Después de restar la cantidad asignada al área de memoria compartida, quedan 16 384 MB. No asigne la memoria restante exclusivamente al área de memoria de trabajo porque el sistema operativo y el motor también necesitan memoria.

La memoria que puedes asignar a work_mem depende de la clase de instancia. Si utiliza una clase de instancia más grande, habrá más memoria disponible. Sin embargo, en el ejemplo anterior, no puedes usar más de 16 GiB. De lo contrario, la instancia dejará de estar disponible cuando se agote la memoria. Para recuperar la instancia del estado no disponible, los servicios de automatización de RDS para PostgreSQL se reinician automáticamente.

Administrar el número de conexiones

Supongamos que la instancia de su base de datos tiene 5 000 conexiones simultáneas. Cada conexión utiliza al menos 4 MiB de work_mem. El alto consumo de memoria de las conexiones puede degradar el rendimiento. Para ello, tiene las siguientes opciones:

En el caso de los proxies, considere Amazon RDS Proxy, pgBouncer o un grupo de conexiones acorde con su aplicación. Esta solución alivia la carga de la CPU. También reduce el riesgo cuando todas las conexiones requieren el área de memoria de trabajo. Cuando hay menos conexiones a la base de datos, puede aumentar el valor de work_mem. De esta manera, se reduce la ocurrencia de los eventos de espera IO:BufFileRead y IO:BufFileWrite. Además, las consultas que esperan el área de memoria de trabajo se aceleran de forma significativa.