IO:BufFileRead and IO:BufFileWrite

Os eventos IO:BufFileRead e IO:BufFileWrite ocorrem quando o RDS para PostgreSQL cria arquivos temporários. Quando as operações requerem mais memória do que os parâmetros de memória de trabalho definidos atualmente, elas gravam dados temporários no armazenamento persistente. Essa operação é chamada às vezes de “derramamento no disco”.

Versões compatíveis do mecanismo

Essas informações de eventos de espera são compatíveis com todas as versões do RDS para PostgreSQL.

Contexto

IO:BufFileRead e IO:BufFileWrite estão relacionados à área de memória de trabalho e a área de memória de trabalho de manutenção. Para obter mais informações sobre essas áreas de memória local, consulte Resource Consumption (Consumo de recursos) na documentação do PostgreSQL.

O valor padrão para work_mem é 4 MB. Se uma sessão executar operações em paralelo, cada operador que lidar com o paralelismo usará 4 MB de memória. Por essa razão, defina work_mem com cautela. Se você aumentar demais esse valor, um banco de dados que execute muitas sessões poderá consumir muita memória. Se você definir um valor muito baixo, o RDS para PostgreSQL criará arquivos temporários no armazenamento local. A E/S de disco desses arquivos temporários pode reduzir a performance.

Se você observar a seguinte sequência de eventos, é possível que seu banco de dados esteja gerando arquivos temporários:

1. Redução súbita e acentuada na disponibilidade

2. Recuperação rápida para o espaço livre

Você também pode observar um padrão de “motosserra”. Esse padrão pode indicar que o banco de dados está criando arquivos pequenos constantemente.

Possíveis causas do maior número de esperas

Em geral, esses eventos de espera são causados por operações que consomem mais memória do que é alocado pelos parâmetros work_mem ou maintenance_work_mem. Para compensar isso, as operações gravam em arquivos temporários. Causas comuns dos eventos IO:BufFileRead e IO:BufFileWrite incluem:

Consultas que necessitam de mais memória do que existe na área de memória de trabalho

Consultas com as seguintes características utilizam a área de memória de trabalho:

• Junções de hash

• ORDER BYCláusula

• GROUP BYCláusula

• DISTINCT

• Funções de janela

• CREATE TABLE AS SELECT

• Atualização de visualizações materializadas

Instruções que necessitam de mais memória do que existe na área de memória do trabalho de manutenção

As seguintes instruções usam a área de memória do trabalho de manutenção:

• CREATE INDEX

• CLUSTER

Ações

Recomenda-se ações distintas, dependendo dos motivos do evento de espera.

Tópicos

• Identificar o problema

• Examinar suas consultas de junção

• Examinar suas consultas ORDER BY e GROUP BY

• Evite utilizar a operação DISTINCT

• Considere utilizar funções de janela em vez de funções GROUP BY

• Investigar visualizações materializadas e instruções CTAS

• Utilizar pg_repack ao recriar índices

• Aumentar maintenance_work_mem ao agrupar tabelas

• Ajustar a memória para evitar IO:BufFileRead e IO:BufFileWrite

Identificar o problema

Imagine uma situação na qual o Performance Insights não está ativado e você suspeita de que IO:BufFileRead e IO:BufFileWrite estejam ocorrendo com mais frequência que o normal. Para identificar a origem do problema, você pode definir o parâmetro log_temp_files para registrar todas as consultas que geram mais do que o limite especificado de KB de arquivos temporários. Por padrão,log_temp_files está definido como -1, o que desativa esse recurso de registro em log. Se você definir esse parâmetro como 0, o RDS para PostgreSQL registrará todos os arquivos temporários. Se você defini-lo como 1024, o RDS para PostgreSQL registrará todas as consultas que geram arquivos temporários maiores que 1 MB. Para obter mais informações sobre log_temp_files, consulte o tópico sobre Relatórios de erros e registro em log, na documentação do PostgreSQL.

Examinar suas consultas de junção

É provável que sua consulta use junções. Por exemplo, a consulta a seguir une quatro tabelas.

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;

Uma causa possível dos picos no uso temporário de arquivos é um problema na própria consulta. Por exemplo, uma cláusula quebrada talvez não esteja filtrando as junções corretamente. Considere a segunda junção interna no exemplo a seguir.

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;

A consulta anterior junta customer.id com customer.id por engano, gerando um produto cartesiano entre cada cliente e cada pedido. Esse tipo de junção acidental gera arquivos temporários grandes. Dependendo do tamanho das tabelas, uma consulta cartesiana pode até mesmo lotar o armazenamento. Sua aplicação pode ter junções cartesianas quando as seguintes condições são atendidas:

• Você percebe reduções grandes e acentuadas na disponibilidade do armazenamento, seguidas de uma rápida recuperação.

• Nenhum índice está sendo criado.

• Nenhuma instrução CREATE TABLE FROM SELECT está sendo emitida.

• Nenhuma visualização materializada está sendo atualizada.

Para verificar se as tabelas estão sendo unidas utilizando as chaves apropriadas, inspecione suas diretivas de mapeamento de consultas e objetos relacionais. Lembre-se de que certas consultas da sua aplicação não são chamadas o tempo todo e que algumas consultas são geradas dinamicamente.

Examinar suas consultas ORDER BY e GROUP BY

Em alguns casos, uma cláusula ORDER BY pode resultar no excesso de arquivos temporários. Considere as seguintes diretrizes:

• Inclua somente colunas em uma cláusula ORDER BY quando elas precisarem ser ordenadas. Essa orientação é especialmente importante para consultas que retornam milhares de linhas e especificam muitas colunas na cláusula ORDER BY.

• Considere criar índices para acelerar cláusulas ORDER BY quando elas correspondem a colunas que tenham a mesma ordem crescente ou decrescente. Índices parciais são preferíveis, pois são menores. Índices menores são lidos e percorridos com mais rapidez.

• Se você criar índices para colunas que podem aceitar valores nulos, considere se deseja que esses valores nulos sejam armazenados no final ou no início dos índices.

  Se possível, reduza o número de linhas que precisam ser ordenadas, filtrando o conjunto de resultados. Se você usar instruções de cláusula WITH ou subconsultas, lembre-se de que uma consulta interna gera um conjunto de resultados e o transmite à consulta externa. Quanto mais linhas uma consulta puder remover, menos ordenação ela precisará fazer.

• Se não precisar obter o conjunto completo de resultados, utilize a cláusula LIMIT. Por exemplo, se quiser apenas as cinco principais linhas, uma consulta utilizando a cláusula LIMIT não continuará gerando resultados. Dessa forma, essa consulta requer menos memória e arquivos temporários.

Uma consulta que usa uma cláusula GROUP BY também pode exigir arquivos temporários. Consultas GROUP BY resumem valores utilizando funções como as seguintes:

• COUNT

• AVG

• MIN

• MAX

• SUM

• STDDEV

Para ajustar consultas GROUP BY, siga as recomendações para consultas ORDER BY.

Evite utilizar a operação DISTINCT

Se possível, evite utilizar a operação DISTINCT para remover linhas duplicadas. Quanto mais linhas desnecessárias e duplicadas sua consulta retornar, mais cara a operação DISTINCT se tornará. Se possível, adicione filtros à cláusula WHERE mesmo que você utilize os mesmos filtros para tabelas diferentes. Filtrar a consulta e a junção corretamente melhora a performance e reduz o uso de recursos. Isso também evita relatórios e resultados incorretos.

Se precisar usar DISTINCT para várias linhas de uma mesma tabela, considere criar um índice composto. O agrupamento de várias colunas em um índice pode melhorar o tempo para avaliar linhas distintas. Além disso, se utilizar o RDS para PostgreSQL versão 10 ou superior, você poderá correlacionar estatísticas entre várias colunas utilizando o comando CREATE STATISTICS.

Considere utilizar funções de janela em vez de funções GROUP BY

Usando GROUP BY, você altera o conjunto de resultados e, em seguida, recupera o resultado agregado. Usando funções de janela, você agrega dados sem modificar o conjunto de resultados. Uma função de janela usa a cláusula OVER para fazer cálculos entre os conjuntos definidos pela consulta, correlacionando uma linha com outra. Você pode utilizar todas as funções GROUP BY em funções de janela, mas também utilizar funções como as seguintes:

• RANK

• ARRAY_AGG

• ROW_NUMBER

• LAG

• LEAD

Para minimizar o número de arquivos temporários gerados por uma função de janela, remova duplicatas do mesmo conjunto de resultados quando precisar de duas agregações distintas. Considere a seguinte 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;

Você pode reescrever essa consulta com a cláusula WINDOW da seguinte maneira.

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);

Por padrão, o planejador de execução do RDS para PostgreSQL consolida nós semelhantes para que ele não duplique operações. No entanto, utilizando uma declaração explícita para o bloco de janelas, é possível manter a consulta com mais facilidade. Também é possível melhorar a performance ao evitar a duplicação.

Investigar visualizações materializadas e instruções CTAS

Quando uma visualização materializada é atualizada, ela executa uma consulta. Essa consulta pode conter uma operação como GROUP BY, ORDER BY ou DISTINCT. Durante uma atualização, é possível observar um grande número de arquivos temporários e os eventos de espera IO:BufFileWrite e IO:BufFileRead. Da mesma forma, quando você cria uma tabela com base em uma instrução SELECT, a instrução CREATE TABLE executa uma consulta. Para reduzir os arquivos temporários necessários, otimize a consulta.

Utilizar pg_repack ao recriar índices

Quando você cria um índice, o mecanismo ordena o conjunto de resultados. À medida que o tamanho das tabelas aumenta e à medida que os valores na coluna indexada se tornam mais diversificados, os arquivos temporários exigem mais espaço. Na maioria dos casos, não é possível impedir a criação de arquivos temporários para tabelas grandes sem modificar a área de memória do trabalho de manutenção. Para obter mais informações sobre maintenance_work_mem, consulte https://www.postgresql.org/docs/current/runtime-config-resource.html na documentação do PostgreSQL.

Uma possível solução alternativa ao recriar um índice grande é utilizar a extensão pg_repack. Para obter mais informações, consulte o tópico sobre como Reorganizar tabelas em bancos de dados PostgreSQL com bloqueios mínimos, na documentação de pg_repack. Para obter informações sobre como configurar a extensão em sua instância de banco de dados do RDS para PostgreSQL, consulte Reduzir o inchaço em tabelas e índices com a extensão pg_repack.

Aumentar maintenance_work_mem ao agrupar tabelas

O comando CLUSTER agrupa a tabela especificada por table_name com base em um índice existente especificado por index_name. O RDS para PostgreSQL recria fisicamente a tabela para corresponder à ordem de um determinado índice.

Quando o armazenamento magnético era predominante, o agrupamento era comum, pois a taxa de transferência de armazenamento era limitada. Agora que o armazenamento baseado em SSD é comum, o agrupamento tornou-se menos popular. No entanto, se você agrupar tabelas, ainda poderá aumentar a performance ligeiramente, dependendo do tamanho da tabela, do índice, da consulta e assim por diante.

Se você executar o comando CLUSTER e observar os eventos de espera IO:BufFileWrite e IO:BufFileRead, ajuste maintenance_work_mem. Aumente o tamanho da memória para uma quantidade relativamente grande. Um valor alto significa que o mecanismo pode utilizar mais memória para a operação de agrupamento.

Ajustar a memória para evitar IO:BufFileRead e IO:BufFileWrite

Em algumas situações, você precisa ajustar a memória. Seu objetivo é equilibrar a memória nas seguintes áreas de consumo usando os parâmetros apropriados, da forma a seguir.

• O valor work_mem

• A memória restante após descontar o valor shared_buffers

• As conexões máximas abertas e em uso, o que é limitado por max_connections

Para obter mais informações sobre ajuste da memória, consulte Resource Consumption (Consumo de recursos) na documentação do PostgreSQL.

Aumentar o tamanho da área de memória de trabalho

Em algumas situações, a única opção é aumentar a memória utilizada pela sessão. Se as consultas estiverem gravadas corretamente e utilizando as chaves corretas para junções, considere aumentar o valor de work_mem.

Para descobrir quantos arquivos temporários são gerados por uma consulta, defina log_temp_files como 0. Se você aumentar o valor de work_mem para o valor máximo identificado nos logs, impedirá que a consulta gere arquivos temporários. No entanto, work_mem define o máximo por nó de plano para cada conexão ou operador paralelo. Se o banco de dados tiver 5.000 conexões e cada uma utilizar 256 MiB de memória, o mecanismo precisará de 1,2 TiB de RAM. Portanto, sua instância pode ficar sem memória.

Reservar memória suficiente para o grupo de buffer compartilhado

Seu banco de dados usa áreas de memória, como o grupo de buffer compartilhado, e não apenas a área de memória de trabalho. Considere os requisitos dessas áreas de memória adicionais antes de aumentar work_mem.

Por exemplo, suponha que sua classe de instância do RDS para PostgreSQL seja db.r5.2xlarge. Essa classe tem 64 GiB de memória. Por padrão, 25% da memória são reservados para o grupo de buffer compartilhado. Depois de subtrair a quantidade alocada à área de memória compartilhada, permanecem 16.384 MB. Não aloque a memória restante exclusivamente à área de memória de trabalho, pois o sistema operacional e o mecanismo também precisam de memória.

A memória que é possível alocar a work_mem depende da classe da instância. Se você utilizar uma classe de instância maior, mais memória estará disponível. No entanto, no exemplo anterior, não é possível utilizar mais de 16 GiB. Caso contrário, sua instância estará indisponível quando ficar sem memória. Para recuperar a instância e retirá-la do estado indisponível, os serviços de automação do RDS para PostgreSQL são reiniciados automaticamente.

Gerenciar o número de conexões

Imagine que a sua instância de banco de dados tenha 5.000 conexões simultâneas. Cada conexão usa pelo menos 4 MiB de work_mem. O alto consumo de memória das conexões provavelmente diminuirá a performance. Em resposta, existem as seguintes opções:

• Faça upgrade para uma classe de instância maior.

• Diminua o número de conexões de banco de dados simultâneas utilizando um proxy de conexão ou pooler.

Para proxies, considere o Amazon RDS Proxy, o pgBouncer ou um pooler de conexão baseado na sua aplicação. Essa solução alivia a carga da CPU. Ela também reduz o risco quando todas as conexões exigem a área de memória de trabalho. Quando há menos conexões de banco de dados, é possível aumentar o valor de work_mem. Dessa forma, você reduz a ocorrência dos eventos de espera IO:BufFileRead e IO:BufFileWrite. Além disso, as consultas que aguardam a área de memória de trabalho são aceleradas significativamente.