Tenho instâncias RDS de produção e preparação na amazon, e os dados de preparação são uma cópia direta da produção, portanto, ambas as instâncias têm dados duplicados.
Fazendo um EXPLAIN ANALYZE SELECT * from my_table WHERE my_col=true;resultou nisso:
Seq Scan on my_table (cost=0.00..142,775.73 rows=1 width=1,436) (actual time=18,170.294..18,170.294 rows=0 loops=1) Filter: my_col Rows Removed by Filter: 360275
Onde, como na produção, foi:
Seq Scan on my_table (cost=0.00..62,145.88 rows=1 width=1,450) (actual time=282.487..282.487 rows=0 loops=1) Filter: my_col Rows Removed by Filter: 366442
ao correrselect pg_total_relation_size('my_table'::regclass);
Achei que o tamanho da encenação era quase o dobro da produção. Pelo que li, vejo que o MVCC do postgresql é responsável por isso, pois mantém várias versões de linhas ao redor. Corri manualmente VACUUM FULLe depois vi que o tamanho da encenação havia sido reduzido em 2/3. A execução da mesma análise de explicação agora mostra:
Seq Scan on my_table (cost=0.00..56094.75 rows=1 width=1436) (actual time=1987.340..1987.340 rows=0 loops=1) Filter: my_col Rows Removed by Filter: 360287 Total runtime: 1987.547 ms
O que é ótimo - mas o que não entendo é que a documentação sugere que o aspirador automático deveria entrar em ação e limpar essas linhas mortas, mas claramente isso não estava acontecendo.
Eu li vários lugares falando sobre "não deixe seus índices ficarem inchados" e não entendo muito bem 1) como um índice fica inchado e 2) como evitar que um índice fique inchado.
Como posso evitar que isso aconteça novamente no futuro?
ATUALIZAR
Aqui estão minhas configurações de autovacuum:
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline
-------------------------------------+-----------+------+------------+-------------------------------------------------------------------------------------------+------------+------------+---------+---------+-----------+------------+----------+-----------+-----------+------------+------------
autovacuum | on | | Autovacuum | Starts the autovacuum subprocess. | | sighup | bool | default | | | | on | on | |
autovacuum_analyze_scale_factor | 0.1 | | Autovacuum | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. | | sighup | real | default | 0 | 100 | | 0.1 | 0.1 | |
autovacuum_analyze_threshold | 50 | | Autovacuum | Minimum number of tuple inserts, updates, or deletes prior to analyze. | | sighup | integer | default | 0 | 2147483647 | | 50 | 50 | |
autovacuum_freeze_max_age | 200000000 | | Autovacuum | Age at which to autovacuum a table to prevent transaction ID wraparound. | | postmaster | integer | default | 100000000 | 2000000000 | | 200000000 | 200000000 | |
autovacuum_max_workers | 3 | | Autovacuum | Sets the maximum number of simultaneously running autovacuum worker processes. | | postmaster | integer | default | 1 | 8388607 | | 3 | 3 | |
autovacuum_multixact_freeze_max_age | 400000000 | | Autovacuum | Multixact age at which to autovacuum a table to prevent multixact wraparound. | | postmaster | integer | default | 10000000 | 2000000000 | | 400000000 | 400000000 | |
autovacuum_naptime | 60 | s | Autovacuum | Time to sleep between autovacuum runs. | | sighup | integer | default | 1 | 2147483 | | 60 | 60 | |
autovacuum_vacuum_cost_delay | 20 | ms | Autovacuum | Vacuum cost delay in milliseconds, for autovacuum. | | sighup | integer | default | -1 | 100 | | 20 | 20 | |
autovacuum_vacuum_cost_limit | -1 | | Autovacuum | Vacuum cost amount available before napping, for autovacuum. | | sighup | integer | default | -1 | 10000 | | -1 | -1 | |
autovacuum_vacuum_scale_factor | 0.2 | | Autovacuum | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. | | sighup | real | default | 0 | 100 | | 0.2 | 0.2 | |
autovacuum_vacuum_threshold | 50 | | Autovacuum | Minimum number of tuple updates or deletes prior to vacuum. | | sighup | integer | default | 0 | 2147483647 | | 50 | 50 | |
A aspiração automática deve, eventualmente, limpá-lo (supondo que você não o tenha desativado), mas pode não ser suficiente para seus propósitos. Existem muitas configurações que podem controlar a aspiração automática e como/quando é feita, que podem ser interessantes: aqui e aqui .
Isso pode ser especialmente verdadeiro em tabelas com alta rotatividade. Ou seja, tabelas com muitas inserções e exclusões. Transações de execução longa e ociosas também podem ser um fator aqui, pois o MVCC entrará em ação e impedirá que as tuplas mortas sejam recuperadas. O fato de fazer manualmente uma
VACUUMliberação das tuplas mortas sugere que esse não é o seu caso, e pode ser o problema anterior.Em geral, não é recomendado fazer um
VACUUM FULL, pois isso remove um bloqueio exclusivo da tabela, principalmente quando a maioria das linhas de uma tabela foi atualizada/excluída.Do documento :
Seu padrão de uso é tal que seria esse o caso? Você mencionou uma "cópia direta" envolvida, mas não está claro exatamente como isso está sendo feito.
Já tive casos com tabelas de alta rotatividade em que a taxa padrão de vácuo automático simplesmente não era suficiente e mesmo quantidades relativamente pequenas de tuplas mortas afetariam muito a velocidade da consulta (isso ocorreu em uma tabela grande que foi consultada com muita frequência e onde a consulta precisava ser extremamente rápida e, como tal, era altamente afetada por tuplas mortas).
Para ajudar com isso, configurei um manual
VACUUM ANALYZEda tabela (para liberar as tuplas e ajudar o planejador de consultas atualizando as estatísticas) em um cron job que foi configurado para ser executado a cada 5 minutos. Como não havia tantas tuplas mortas, oVACUUMfoi bem rápido, e a limpeza constante mantém a contagem de tuplas mortas baixa o suficiente para manter as consultas dessa tabela rápidas.Edite em resposta ao comentário do OP:
No doc VACUUM , diz que:
O doc então diz que (ênfase minha):
Portanto, ele definitivamente recupera tuplas mortas.
a documentação sugere que o vácuo automático deveria entrar em ação e limpar essas linhas mortas, mas claramente isso não estava acontecendo.
Para saber se o autovacuum processa uma tabela, veja
pg_catalog.pg_stat_user_tables.last_autovacuum.Você parece acreditar que o autovacuum não funciona porque um manual
VACUUM FULLencolhe a mesa e o autovacuum não. Mas isso é normal, já que o autovacuum não deve encolher as tabelas, ele apenas sinaliza o espaço ocupado por linhas mortas como reutilizável.1) como um índice fica inchado e 2) como evitar que um índice fique inchado.
O inchaço do índice acontece assim que as gravações acontecem, não pode ser evitado. O que precisa ser verificado é se o inchaço cresce indefinidamente ou permanece estável e quão ruim é.
O wiki do postgres fornece uma consulta para isso: https://wiki.postgresql.org/wiki/Show_database_bloat