Administración de archivos temporales con PostgreSQL
En PostgreSQL, una consulta que realiza operaciones de ordenación y hash utiliza la memoria de la instancia para almacenar los resultados hasta el valor especificado en el parámetro work_memFreeStorageSpace publicada en CloudWatch para asegurarse de que la instancia de base de datos tenga suficiente espacio de almacenamiento libre. Para obtener más información, consulte FreeStorageSpace
Recomendamos utilizar instancias de lecturas optimizadas para Amazon RDS para las cargas de trabajo que implican numerosas consultas simultáneas que aumentan el uso de archivos temporales. Estas instancias utilizan almacenamiento local a nivel de bloque basado en unidades de estado sólido (SSD) de memoria rápida no volátil (NVMe) para colocar los archivos temporales. Para obtener más información, consulte Lecturas optimizadas para Amazon RDS.
Puede utilizar los siguientes parámetros y funciones para administrar los archivos temporales de la instancia.
-
temp_file_limit: este parámetro cancela cualquier consulta que supere el tamaño de temp_files en KB. Este límite evita que cualquier consulta se ejecute de forma indefinida y consuma espacio en disco con archivos temporales. Puede calcular el valor utilizando los resultados del parámetro log_temp_files. Como práctica recomendada, examine el comportamiento de la carga de trabajo y establezca el límite de acuerdo con la estimación. En el siguiente ejemplo, se cancela una consulta cuando se supera el límite.postgres=> select * from pgbench_accounts, pg_class, big_table;ERROR: temporary file size exceeds temp_file_limit (64kB) log_temp_files: este parámetro envía mensajes a postgresql.log cuando se eliminan los archivos temporales de una sesión. Este parámetro produce registros después de que la consulta se complete correctamente. Por lo tanto, puede que no ayude a solucionar problemas de consultas activas y de larga ejecución. El ejemplo siguiente muestra que, cuando la consulta se completa correctamente, las entradas se registran en el archivo postgresql.log y se limpian los archivos temporales.
2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.5", size 140353536 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10; 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp31236.4", size 180428800 2023-02-06 23:48:35 UTC:205.251.233.182(12456):adminuser@postgres:[31236]:STATEMENT: select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid limit 10;pg_ls_tmpdir: esta función que está disponible desde RDS para PostgreSQL 13 y versiones posteriores proporciona visibilidad sobre el uso actual de los archivos temporales. La consulta completada no aparece en los resultados de la función. En el siguiente ejemplo, puede ver los resultados de esta función. postgres=> select * from pg_ls_tmpdir();name | size | modification -----------------+------------+------------------------ pgsql_tmp8355.1 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.0 | 1072250880 | 2023-02-06 22:54:43+00 pgsql_tmp8327.0 | 1072250880 | 2023-02-06 22:54:56+00 pgsql_tmp8351.1 | 703168512 | 2023-02-06 22:54:56+00 pgsql_tmp8355.0 | 1072250880 | 2023-02-06 22:54:00+00 pgsql_tmp8328.1 | 835031040 | 2023-02-06 22:54:56+00 pgsql_tmp8328.0 | 1072250880 | 2023-02-06 22:54:40+00 (7 rows)postgres=> select query from pg_stat_activity where pid = 8355;query ---------------------------------------------------------------------------------------- select a.aid from pgbench_accounts a, pgbench_accounts b where a.bid=b.bid order by a.bid (1 row)El nombre del archivo incluye el ID de procesamiento (PID) de la sesión que generó el archivo temporal. Una consulta más avanzada, como en el ejemplo siguiente, realiza una suma de los archivos temporales de cada PID.
postgres=> select replace(left(name, strpos(name, '.')-1),'pgsql_tmp','') as pid, count(*), sum(size) from pg_ls_tmpdir() group by pid;pid | count | sum ------+------------------- 8355 | 2 | 2144501760 8351 | 2 | 2090770432 8327 | 1 | 1072250880 8328 | 2 | 2144501760 (4 rows)-
pg_stat_statements: si activa el parámetro pg_stat_statements, puede ver el uso medio de archivos temporales por llamada. Puede identificar el query_id de la consulta y usarlo para examinar el uso de archivos temporales, como se muestra en el siguiente ejemplo.postgres=> select queryid from pg_stat_statements where query like 'select a.aid from pgbench%';queryid ---------------------- -7170349228837045701 (1 row)postgres=> select queryid, substr(query,1,25), calls, temp_blks_read/calls temp_blks_read_per_call, temp_blks_written/calls temp_blks_written_per_call from pg_stat_statements where queryid = -7170349228837045701;queryid | substr | calls | temp_blks_read_per_call | temp_blks_written_per_call ----------------------+---------------------------+-------+-------------------------+---------------------------- -7170349228837045701 | select a.aid from pgbench | 50 | 239226 | 388678 (1 row) -
Performance Insights: en el panel de Información sobre el rendimiento, puede ver el uso temporal de los archivos activando las métricas temp_bytes y temp_files. A continuación, puede ver la media de estas dos métricas y cómo se corresponden con la carga de trabajo de la consulta. La vista de Información sobre el rendimiento no muestra específicamente las consultas que generan los archivos temporales. Sin embargo, al combinar Información sobre el rendimiento con la consulta que se muestra parapg_ls_tmpdir, puede solucionar problemas, realizar análisis y determinar los cambios en la carga de trabajo de la consulta.Para obtener más información sobre cómo analizar las métricas y las consultas con Información sobre el rendimiento, consulte Análisis de métricas mediante el panel de Performance Insights.
Para ver el uso de archivos temporales con Información sobre el rendimiento
-
En el panel de Información sobre el rendimiento, elija Administrar métricas.
-
Elija las Métricas de la base de datos y seleccione las métricas temp_bytes y temp_files como se muestra en la siguiente captura de pantalla.
-
En la pestaña Principales SQL, seleccione el icono Preferencias.
-
En la ventana Preferencias, active las siguientes estadísticas para que aparezcan en la pestaña Principales SQL y seleccione Continuar.
-
Escrituras temporales por segundo
-
Lecturas temporales por segundo
-
Escritura temporal en bloque por llamada
-
Lectura temporal en bloque por llamada
-
-
El archivo temporal se divide cuando se combina con la consulta mostrada para
pg_ls_tmpdir, como se observa en el siguiente ejemplo.
-
Los eventos IO:BufFileRead y IO:BufFileWrite se producen cuando las consultas principales de la carga de trabajo crean archivos temporales a menudo. Puede utilizar la Información de rendimiento para identificar las principales consultas pendientes en IO:BufFileRead e IO:BufFileWrite mediante la revisión del promedio de sesiones activas (AAS) en las secciones de carga de base de datos y SQL principales.
Para obtener más información sobre cómo analizar las consultas principales y cargar mediante eventos de espera con Información de Rendimiento, consulte Información general sobre la pestaña Top SQL (SQL principal). Debe identificar y ajustar las consultas que provocan el aumento del uso de archivos temporales y los eventos de espera relacionados. Para obtener más información sobre estos eventos de espera y su corrección, consulte IO:BufFileRead e IO:BufFileWrite.
nota
El parámetro work_mem
Como práctica recomendada, cuando tenga un informe de gran tamaño con múltiples combinaciones y ordenaciones, defina este parámetro en el nivel de sesión mediante el comando SET work_mem. Por tanto, el cambio solo se aplica a la sesión actual y no cambia el valor globalmente.
