Diagnosticar a sobrecarga na tabela e no índice

Você pode usar o controle de simultaneidade de várias versões (MVCC) do PostgreSQL para ajudar a preservar a integridade dos dados. O PostgreSQL MVCC funciona salvando uma cópia interna das linhas atualizadas ou excluídas (também chamadas de tuplas) até que uma transação seja confirmada ou revertida. Essa cópia interna salva é invisível para os usuários. No entanto, pode ocorrer sobrecarga da tabela quando essas cópias invisíveis não são limpas regularmente pelos utilitários VACUUM ou AUTOVACUUM. Se não houver controle, a sobrecarga da tabela pode acarretar maiores custos de armazenamento e diminuir a velocidade de processamento.

Em muitos casos, as configurações padrão para VACUUM ou AUTOVACUUM no Aurora são suficientes para lidar com a sobrecarga indesejada da tabela. No entanto, convém conferir se há sobrecarga se sua aplicação estiver enfrentando as seguintes condições:

Para começar, reúna as informações mais precisas sobre quanto espaço é usado por tuplas mortas e quanto você pode recuperar limpando a sobrecarga de tabela e índice. Para fazer isso, use a extensão pgstattuple para coletar estatísticas sobre o cluster do Aurora. Para ter mais informações, consulte pgstattuple. Os privilégios para usar a extensão pgstattuple são limitados aos superusuários do banco de dados e perfil pg_stat_scan_tables.

Para criar a extensão pgstattuple no Aurora, conecte uma sessão do cliente ao cluster, por exemplo, psql ou pgAdmin, e use o seguinte comando:

CREATE EXTENSION pgstattuple;

Crie a extensão em cada banco de dados para o qual você deseja criar o perfil. Depois de criar a extensão, use a interface de linha de comando (CLI) para medir a quantidade de espaço inutilizável que você pode recuperar. Antes de coletar estatísticas, modifique o grupo de parâmetros do cluster definindo AUTOVACUUM como 0. Uma configuração de 0 impede que o Aurora limpe automaticamente quaisquer tuplas mortas deixadas pela aplicação, o que pode afetar a precisão dos resultados. Use o seguinte comando para criar uma tabela simples:

postgres=> CREATE TABLE lab AS SELECT generate_series (0,100000);
SELECT 100001

No exemplo a seguir, executamos a consulta com o AUTOVACUUM ativado para o cluster de banco de dados. O dead_tuple_count é 0, o que indica que o AUTOVACUUM removeu dados obsoletos ou tuplas do banco de dados PostgreSQL.

Para usar pgstattuple para coletar informações sobre a tabela, especifique o nome de uma tabela ou um identificador de objeto (OID) na consulta:

postgres=> SELECT * FROM pgstattuple('lab');

    
    
table_len  | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056    | 100001      | 2800028   | 77.16         | 0                | 0              | 0                  | 16616      | 0.46
(1 row)
        
    

Na consulta a seguir, desativamos o AUTOVACUUM e usamos um comando que exclui 25 mil linhas da tabela. Como resultado, a dead_tuple_count aumenta para 25 mil.

postgres=> DELETE FROM lab WHERE generate_series < 25000;    

DELETE 25000  
   

SELECT * FROM pgstattuple('lab');
    

        
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
3629056 | 75001 | 2100028 | 57.87 | 25000 | 700000 | 19.29 | 16616 | 0.46
(1 row)        
    
    

Para recuperar essas tuplas mortas, inicie um processo do VACUUM.

Observar a sobrecarga sem interromper sua aplicação

As configurações em um cluster do Aurora são otimizadas para oferecer as práticas recomendadas para a maioria das workloads. No entanto, talvez você queira otimizar um cluster para melhor atender às suas aplicações e padrões de uso. Nesse caso, você pode usar a extensão pgstattuple sem interromper uma aplicação ocupada. Para fazer isso, execute as seguintes etapas:

Depois de executar suas aplicações e visualizar o resultado, use pg_repack ou VACUUM FULL na cópia restaurada e compare as diferenças. Se você observar uma queda significativa no dead_tuple_count, dead_tuple_len ou dead_tuple_percent, ajuste o cronograma de vácuo em seu cluster de produção para minimizar a sobrecarga.

Evitar a sobrecarga em tabelas temporárias

Se a aplicação criar tabelas temporárias, garanta que a aplicação as remova quando elas não forem mais necessárias. Os processos do Autovacuum não localizam tabelas temporárias. Se não forem conferidas, as tabelas temporárias poderão criar sobrecarga rapidamente no banco de dados. Além disso, a sobrecarga pode se estender às tabelas do sistema, que são as tabelas internas que rastreiam objetos e atributos do PostgreSQL, como pg_attribute e pg_depend.

Quando uma tabela temporária não é mais necessária, você pode usar uma instrução TRUNCATE para esvaziá-la e liberar espaço. Depois, limpe manualmente as tabelas pg_attribute e pg_depend. A limpeza dessas tabelas garante que as operações de criar e truncar/excluir tabelas temporárias não esteja adicionando tuplas continuamente e contribuindo para a sobrecarga do sistema.

Você pode evitar esse problema ao criar uma tabela temporária incluindo a seguinte sintaxe, que exclui as novas linhas quando o conteúdo é confirmado:

CREATE TEMP TABLE IF NOT EXISTS table_name(table_description) ON COMMIT DELETE ROWS;

A cláusula ON COMMIT DELETE ROWS trunca a tabela temporária quando a transação é confirmada.

Evitar a sobrecarga nos índices

Quando você altera um campo indexado em uma tabela, a atualização do índice ocasiona uma ou mais tuplas mortas nesse índice. Por padrão, o processo de limpeza automática elimina a sobrecarga nos índices, mas essa limpeza consome uma quantidade significativa de tempo e recursos. Para especificar as preferências de limpeza do índice ao criar uma tabela, inclua a cláusula vacuum_index_cleanup. Por padrão, no momento da criação da tabela, a cláusula é definida como AUTO, o que significa que o servidor decide se seu índice precisa ser limpo ao limpar a tabela. Você pode definir a cláusula como ATIVADA, para ativar a limpeza do índice para uma tabela específica, ou DESATIVADA, para desativar a limpeza do índice dessa tabela. Lembre-se de que desativar a limpeza do índice pode economizar tempo, mas ocasionar um índice sobrecarregado.

Você pode controlar manualmente a limpeza do índice ao limpar uma tabela na linha de comando. Para limpar uma tabela e remover tuplas mortas dos índices, inclua a cláusula INDEX_CLEANUP com um valor ATIVADO e o nome da tabela:

acctg=> VACUUM (INDEX_CLEANUP ON) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM

Para limpar uma tabela sem limpar os índices, especifique um valor DESATIVADO:

acctg=> VACUUM (INDEX_CLEANUP OFF) receivables;
        
INFO: aggressively vacuuming "public.receivables"
VACUUM