Gerenciar arquivos temporários com o PostgreSQL
No PostgreSQL, uma consulta que executa operações de classificação e hash utiliza a memória da instância para armazenar resultados até o valor especificado no parâmetro work_memFreeStorageSpace publicada no CloudWatch para garantir que a instância de banco de dados tenha espaço de armazenamento livre suficiente. Para obter mais informações, consulte FreeStorageSpace
Recomendamos o uso de instâncias de Leituras otimizadas pelo Amazon RDS para workloads com várias consultas simultâneas que aumentam o uso de arquivos temporários. Essas instâncias usam o armazenamento em bloco de unidade de estado sólido (SSD) local baseado em memória expressa não volátil (NVMe) para guardar os arquivos temporários. Para obter mais informações consulte Leituras otimizadas pelo Amazon RDS.
Você pode usar os parâmetros e as funções a seguir para gerenciar os arquivos temporários em sua instância.
-
temp_file_limit: esse parâmetro cancela qualquer consulta que exceda o tamanho de temp_files em KB. Esse limite impede que qualquer consulta seja executada indefinidamente e consuma espaço em disco com arquivos temporários. Você pode estimar o valor utilizando os resultados do parâmetro log_temp_files. Como prática recomendada, examine o comportamento da workload e defina o limite de acordo com a estimativa. O exemplo a seguir mostra como uma consulta é cancelada quando ela excede o limite.postgres=> select * from pgbench_accounts, pg_class, big_table;ERROR: temporary file size exceeds temp_file_limit (64kB) log_temp_files: esse parâmetro envia mensagens ao postgresql.log quando os arquivos temporários de uma sessão são removidos. Esse parâmetro produz logs após a conclusão bem-sucedida de uma consulta. Portanto, isso pode não ajudar na solução de problemas de consultas ativas e de longa duração. O exemplo a seguir mostra que, quando a consulta é concluída com êxito, as entradas são registradas no arquivo postgresql.log enquanto os arquivos temporários são limpos.
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: essa função que está disponível no RDS para PostgreSQL 13 e versões posteriores oferece visibilidade sobre o uso atual de arquivos temporários. A consulta concluída não aparece nos resultados da função. No exemplo a seguir, você pode visualizar os resultados dessa função. 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)O nome do arquivo inclui o ID de processamento (PID) da sessão que gerou o arquivo temporário. Uma consulta mais avançada, como no exemplo a seguir, executa uma soma dos arquivos temporários para 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: se você ativar o parâmetro pg_stat_statements, poderá visualizar o uso médio de arquivos temporários por chamada. Você pode identificar o query_id da consulta e usá-lo para examinar o uso do arquivo temporário, conforme mostrado no exemplo a seguir.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: no painel do Performance Insights, você pode visualizar o uso temporário de arquivos ativando as métricas temp_bytes e temp_files. Depois, você pode ver a média dessas duas métricas e como elas correspondem à workload da consulta. A exibição no Performance Insights não mostra especificamente as consultas que estão gerando os arquivos temporários. No entanto, ao combinar o Performance Insights com a consulta mostrada parapg_ls_tmpdir, você pode solucionar problemas, analisar e determinar as alterações em sua workload de consulta.Para ter mais informações sobre como analisar as métricas e as consultas com o Performance Insights, consulte Análise de métricas usando o painel do Performance Insights
Como visualizar o uso de arquivos temporários com o Performance Insights
-
No painel do Performance Insights, selecione Gerenciar métricas.
-
Escolha Métricas de banco de dados e selecione as métricas temp_bytes e temp_files como mostrado na imagem a seguir.
-
Na guia Top SQL, selecione o ícone Preferências.
-
Na janela Preferências, ative as estatísticas a seguir para serem exibidas na guia Top SQL e selecione Continuar.
-
Gravações temporárias/segundo
-
Leituras de temperatura/segundo
-
Gravação/chamada em bloco temporário
-
Leitura/chamada em bloco temporário
-
-
O arquivo temporário é dividido quando combinado com a consulta mostrada para
pg_ls_tmpdir, conforme exibido no exemplo a seguir.
-
Os eventos IO:BufFileRead e IO:BufFileWrite ocorrem porque as principais consultas na workload geralmente criam arquivos temporários. Você pode usar o Insights de Performance para identificar as principais consultas que aguardam IO:BufFileRead e IO:BufFileWrite revisando “Média de sessões ativas (AAS)” nas seções “Carga do banco de dados” e “SQL principal”.
Para obter mais informações sobre como usar o Insights de Performance para analisar as principais consultas e a carga por eventos de espera, consulte Visão geral da guia Top SQL (SQL principal) Você deve identificar e ajustar as consultas que aumentam o uso de arquivos temporários e os eventos de espera correspondentes. Para obter mais informações sobre esses eventos de espera e a correção, consulte IO:BufFileRead e IO:BufFileWrite.
nota
O parâmetro work_mem
Como prática recomendada, quando você tem um relatório grande com várias junções e classificações, defina esse parâmetro no nível da sessão usando o comando SET work_mem. Depois, a alteração é aplicada somente à sessão atual e não altera o valor globalmente.
