# IO:BufFileRead and IO:BufFileWrite
Os eventos `IO:BufFileRead` e `IO:BufFileWrite` ocorrem quando o Aurora 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](#apg-waits.iobuffile.context.supported)
+ [Contexto](#apg-waits.iobuffile.context)
+ [Possíveis causas do maior número de esperas](#apg-waits.iobuffile.causes)
+ [Ações](#apg-waits.iobuffile.actions)
## Versões compatíveis do mecanismo
Essas informações de eventos de espera têm suporte para todas as versões do Aurora 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 saber mais sobre essas áreas de memória local, consulte [Área de memória de trabalho](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem) e [Área de memória de trabalho para manutenção](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.maintenance_work_mem).
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 Aurora 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](#apg-waits.iobuffile.actions.problem)
+ [Examinar suas consultas de junção](#apg-waits.iobuffile.actions.joins)
+ [Examinar suas consultas ORDER BY e GROUP BY](#apg-waits.iobuffile.actions.order-by)
+ [Evite utilizar a operação DISTINCT](#apg-waits.iobuffile.actions.distinct)
+ [Considere utilizar funções de janela em vez de funções GROUP BY](#apg-waits.iobuffile.actions.window)
+ [Investigar visualizações materializadas e instruções CTAS](#apg-waits.iobuffile.actions.mv-refresh)
+ [Usar pg\$1repack ao criar índices](#apg-waits.iobuffile.actions.pg_repack)
+ [Aumentar maintenance\$1work\$1mem ao agrupar tabelas](#apg-waits.iobuffile.actions.cluster)
+ [Ajustar a memória para evitar IO:BufFileRead e IO:BufFileWrite](#apg-waits.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 solucionar problemas, faça o seguinte:
1. Examine a métrica `FreeLocalStorage` no Amazon CloudWatch.
1. Observe se há um padrão de motosserra, ou seja, uma série de picos irregulares.
Um padrão de motosserra indica um rápido consumo e liberação de armazenamento, muitas vezes associados a arquivos temporários. Se você perceber esse padrão, ative o Performance Insights. Ao utilizar o Performance Insights, é possível identificar quando os eventos de espera ocorrem e quais consultas estão associadas a eles. A solução depende da consulta específica que está causando os eventos.
Ou defina o parâmetro `log_temp_files`. Esse parâmetro registra todas as consultas que estão gerando mais do que o limite de KB de arquivos temporários. Se o valor for `0`, o Aurora PostgreSQL registrará todos os arquivos temporários. Se o valor for `1024`, o Aurora 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/10/runtime-config-logging.html), na documentação do PostgreSQL.
### Examinar suas consultas de junção
Sua aplicação provavelmente utiliza 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 Amazon Aurora 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 Aurora 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.
### Usar pg\$1repack ao criar í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, consulte [Área de memória de trabalho para manutenção](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.maintenance_work_mem).
Uma possível solução alternativa ao recriar um índice grande é utilizar a ferramenta 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.
### 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 Aurora PostgreSQL recria fisicamente essa 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 uma determinada situação, você precisa ajustar a memória. A meta é equilibrar os seguintes requisitos:
+ O valor de `work_mem` (consulte [Área de memória de trabalho](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.work_mem))
+ A memória restante após descontar o valor de `shared_buffers` (consulte [Grupo de buffer](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool))
+ As conexões máximas abertas e em uso, o que é limitado por `max_connections`
#### 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 obter mais informações, consulte [Área de memória de trabalho](AuroraPostgreSQL.Tuning.concepts.md#AuroraPostgreSQL.Tuning.concepts.local.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`. Para obter mais informações sobre o grupo de buffer, consulte [Grupo de buffer](AuroraMySQL.Managing.Tuning.concepts.md#AuroraMySQL.Managing.Tuning.concepts.memory.buffer-pool).
Por exemplo, suponha que a sua classe de instância do Aurora PostgreSQL seja db.r5.2xlarge. Essa classe tem 64 GiB de memória. Por padrão, 75% 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 Aurora 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.