# 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*. Para obter mais informações sobre os arquivos temporários e o uso, consulte [Gerenciar arquivos temporários com o PostgreSQL](PostgreSQL.ManagingTempFiles.md).
**Topics**
+ [Versões compatíveis do mecanismo](#wait-event.iobuffile.context.supported)
+ [Contexto](#wait-event.iobuffile.context)
+ [Possíveis causas do maior número de esperas](#wait-event.iobuffile.causes)
+ [Ações](#wait-event.iobuffile.actions)
## 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](https://www.postgresql.org/docs/current/runtime-config-resource.html) (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
1. 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 BY`Cláusula
+ `GROUP BY`Clá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.
**Topics**
+ [Identificar o problema](#wait-event.iobuffile.actions.problem)
+ [Examinar suas consultas de junção](#wait-event.iobuffile.actions.joins)
+ [Examinar suas consultas ORDER BY e GROUP BY](#wait-event.iobuffile.actions.order-by)
+ [Evite utilizar a operação DISTINCT](#wait-event.iobuffile.actions.distinct)
+ [Considere utilizar funções de janela em vez de funções GROUP BY](#wait-event.iobuffile.actions.window)
+ [Investigar visualizações materializadas e instruções CTAS](#wait-event.iobuffile.actions.mv-refresh)
+ [Utilizar pg\$1repack ao recriar índices](#wait-event.iobuffile.actions.pg_repack)
+ [Aumentar maintenance\$1work\$1mem ao agrupar tabelas](#wait-event.iobuffile.actions.cluster)
+ [Ajustar a memória para evitar IO:BufFileRead e IO:BufFileWrite](#wait-event.iobuffile.actions.tuning-memory)
### Identificar o problema
Você pode visualizar o uso de arquivos temporários diretamente no Insights de Performance. Para obter mais informações, consulte [Visualizar o uso de arquivos temporários com o Insights de Performance](PostgreSQL.ManagingTempFiles.Example.md). Quando o Insights de Performance está desabilitado, é possível observar um aumento nas operações `IO:BufFileRead` e `IO:BufFileWrite`.
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](https://www.postgresql.org/docs/current/runtime-config-logging.html), 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\$1repack 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](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\$1repack. Para obter mais informações, consulte o tópico sobre como [Reorganizar tabelas em bancos de dados PostgreSQL com bloqueios mínimos](https://reorg.github.io/pg_repack/), na documentação de pg\$1repack. 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\$1repack](Appendix.PostgreSQL.CommonDBATasks.pg_repack.md).
### Aumentar maintenance\$1work\$1mem ao agrupar tabelas
O comando `CLUSTER` agrupa a tabela especificada por *table\$1name* com base em um índice existente especificado por *index\$1name*. 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](https://www.postgresql.org/docs/current/runtime-config-resource.html) (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.