Ajuste fino no Autovacuum

Você sabe que não deveria nunca desligar o autovacuum, certo? Bom, quase sempre. E quando o autovacuum me sacaneia, o que eu faço? Aprenda a ajustar objetos individuais e configurar o autovacuum de forma eficiente para diferentes cenários nesta palestra do Fábio Telles.

Transcript

1. Ajuste fino no autovacuum
   PostgreSQL
   por Fábio Telles Rodriguez
   

   View Slide

2. View Slide

3. View Slide

4. autovacuum (PG >= 8.2)
   auto
   ANALYZE + VACUUM
   

   View Slide

5. ANALYZE
   • Atualiza estatísticas das tabelas
   • Estatísticas são armazenadas na pg_catalog.pg_statistic
   • Utilizar a view pg_stats para ver a pg_statistic
   • Influencia diretamente o otimizador de consultas
   

   View Slide

6. ANALYZE
   ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
   ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]
   where option can be one of:
   VERBOSE [ boolean ]
   SKIP_LOCKED [ boolean ] (PG >= 12)
   

   View Slide

7. pg_statistc
   • Populada pela rotina de ANALYZE
   • Fornece informações para o otimizador de consultas calcular o custo
   de um plano de execução
   • Mais fácil olhar a view pg_stats
   • default_statistics_target (100)
   • Controla o limite de entradas nas colunas most_common_vals e
   histogram_bounds
   • https://www.postgresql.org/docs/current/planner-stats-details.html
   

   View Slide

8. pg_statistic_ext_data
   ➔ Populada pela rotina de ANALYZE
   ➔ CREATE STATISTICS
   ◆ https://www.postgresql.org/docs/current/sql-createstatistics.html
   ➔ Estatísticas onde os dados de 2 ou mais colunas de uma
   tabela estão relacionados
   ◆ n-distinct
   ◆ funcional depency
   ◆ most-common values
   ➔ https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED
   

   View Slide

9. pg_stats
   View "pg_catalog.pg_stats"
   Column | Type | Collation | Nullable | Default
   ------------------------+----------+-----------+----------+---------
   schemaname | name | | |
   tablename | name | | |
   attname | name | | |
   inherited | boolean | | |
   null_frac | real | | |
   avg_width | integer | | |
   n_distinct | real | | |
   most_common_vals | anyarray | | |
   most_common_freqs | real[] | | |
   histogram_bounds | anyarray | | |
   correlation | real | | |
   most_common_elems | anyarray | | |
   most_common_elem_freqs | real[] | | |
   elem_count_histogram | real[] | | |
   

   View Slide

10. pg_stat_*_tables
    • São populadas quando os parâmetros a seguir estiverem habilitados:
    track_activities, track_counts
    • pg_stat_all_tables - Todas tabelas (incluindo toast)
    • pg_stat_sys_tables - Apenas tabelas de sistema
    • pg_stat_user_tables - Apenas tabelas de usuários
    • Estatísticas acumuladas desde o último pg_stat_database.stats_reset
    • Você pode zerar as estatísticas a qualquer momento com:
    • SELECT pg_stat_reset();
    

    View Slide

11. pg_stat_user_tables
    • Coleta informações sobre uso de tabelas
    • Os dados são acumulados desde data do último reset em
    pg_stat_database
    • Você pode zerar novamente com o função pg_stat_reset()
    • track_activities: liga ou desliga a coleta
    • n_live_tup: número de linhas na tabela;
    • n_mod_since_analyze: número de linhas atualizadas (INSERT +
    UPDATE + DELETE) desde o último ANALYZE;
    

    View Slide

12. pg_stat_user_tables (8.2)
    \d pg_stat_user_tables
    View "pg_catalog.pg_stat_user_tables"
    Column | Type | Collation | Nullable | Default
    ------------------+--------------------------+-----------+----------+---------
    relid | oid | | |
    schemaname | name | | |
    relname | name | | |
    seq_scan | bigint | | |
    seq_tup_read | bigint | | |
    idx_scan | bigint | | |
    idx_tup_fetch | bigint | | |
    n_tup_ins | bigint | | |
    n_tup_upd | bigint | | |
    n_tup_del | bigint | | |
    last_vacuum | timestamp with time zone | | |
    last_autovacuum | timestamp with time zone | | |
    last_analyze | timestamp with time zone | | |
    last_autoanalyze | timestamp with time zone | | |
    

    View Slide

13. pg_stat_user_tables (current)
    • n_tup_hot_upd
    • n_live_tup
    • n_dead_tup
    • n_mod_since_analyze
    • n_ins_since_vacuum (PG >= 13)
    • vacuum_count
    • autovacuum_count
    • analyze_count
    • autoanalyze_count
    

    View Slide

14. pg_stat_user_tables
    SELECT
    relname,
    n_live_tup,
    n_mod_since_analyze,
    last_analyze,
    last_autoanalyze,
    analyze_count,
    autoanalyze_count
    FROM pg_stat_user_tables
    ORDER BY relname;
    

    View Slide

15. Parâmetros do ANALYZE
    • autovacuum_analyze_threshold: número de linhas
    modificadas para disparar o autovacuum_analyze;
    • Evita disparos muito frequentes em tabelas pequenas
    • Valor padrão: 50
    • autovacuum_analyze_scale_factor: proporção de linhas
    modificadas para disparar o autovacuum_analyze;
    • Evita disparos muito frequentes em tabelas grandes
    • Valor padrão 10%
    

    View Slide

16. Disparo do autoanalyze
    n_mod_since_analyze >=
    autovacuum_analyze_threshold +
    autovacuum_analyze_scale_factor * n_live_tup
    

    View Slide

17. Quando ajustar o autoanalyze?
    • autovacuum_analyze_scale_factor:
    • Em tabelas muito grandes, com bilhões de linhas, 10% pode
    levar muito tempo para ocorrer;
    • Rodar um ANALYZE em toda base pelo menos 1x por dia ou por
    semana;
    

    View Slide

18. VACUUM
    - Proteger o banco contra um ID wraparound
    - Recuperar ou reutilizar espaço ocupado por linhas mortas
    - Atualizar o visibility map utilizado pelo INDEX ONLY SCAN
    - https://www.postgresql.org/docs/current/routine-vacuuming.html
    

    View Slide

19. VACUUM wraparound
    ➢ https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
    ○ “Nenhuma tabela deve ficar sem um VACUUM após 2 bilhões de
    transações - vacuum_freeze_min_age”
    ○ “Ocorre mesmo com o autovacuum desligado”
    ■ autovacuum: VACUUM public.t (to prevent wraparound)
    ○ Se não ocorrer…. sua base vai congelar!
    ➢ vacuum_freeze_min_age (50.000.000)
    ➢ vacuum_freeze_table_age (150.000.000)
    ➢ vacuum_multixact_freeze_min_age (5.000.000)
    ➢ vacuum_multixact_freeze_table_age (150.000.000)
    

    View Slide

20. AUTOVACUUM wraparound
    ➢ autovacuum_freeze_max_age (200.000.000)
    ○ pg_class.relfrozenxid
    ➢ autovacuum_multixact_freeze_max_age (400.000.000)
    ○ pg_class.relminmxid
    ➢ Rodar o VACUUM quando:
    ○ relfrozenxid BETWEEN vacuum_freeze_table_age AND autovacuum_freeze_max_age
    ○ relminmxid BETWEEN vacuum_multixact_freeze_table_age AND
    autovacuum_multixact_freeze_max_age
    

    View Slide

21. AUTOVACUUM wraparound
    SELECT
    nspname AS "Esquema",
    relname AS "Tabela",
    to_char(to_number(current_setting('
    vacuum_freeze_table_age'),'999999999999'),'999G999G999G999')
    AS "Min Age",
    to_char(age(
    relfrozenxid),'999G999G999G999') AS "Age",
    to_char(to_number(current_setting('
    autovacuum_freeze_max_age'),'999999999999'),'999G999G999G999')
    AS "Max Age",
    pg_size_pretty(pg_table_size(c.oid)) AS "Tamanho"
    FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE
    relkind = 'r' AND
    nspname NOT IN ('information_schema', 'pg_catalog') AND
    pg_table_size(c.oid) > 67108864 AND -- > 64MB
    age(relfrozenxid) >= to_number(current_setting('vacuum_freeze_table_age'),'999999999999')
    ORDER BY age(relfrozenxid) DESC;
    

    View Slide

22. AUTOVACUUM wraparound (SOCORRO)
    ➢ Aumente provisóriamente o autovacuum_freeze_max_age
    ○ ALTER TABLE t SET (autovacuum_freeze_max_age = 250000000);
    ➢ Mate o processo do autovacuum;
    ○ SELECT pg_terminate_backend(pid_autovacuum);
    ➢ Rode um VACUUM na tabela assim que possível;
    ○ VACUUM FREEZE t;
    ➢ Diminua novamente o autovacuum_freeze_max_age
    ○ ALTER TABLE t RESET (autovacuum_freeze_max_age);
    

    View Slide

23. AUTOVACUUM dead tuples
    ➢ pg_stat_user_tables.n_dead_tuples
    ➢ Não deixar acumular um volume muito grande
    ➢ Quando maior o volume mais tempo o autovacuum leva para
    limpar
    ➢ Em tabelas grandes o valor padrão de disparo (20%) pode
    representar um volume colosal
    

    View Slide

24. pg_stat_user_tables
    SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    vacuum_count,
    autovacuum_count
    FROM pg_stat_user_tables
    ORDER BY relname;
    

    View Slide

25. AUTOVACUUM dead tuples
    ★ Em tabelas grandes, diminuir o autovacuum_vacuum_scale_factor
    ○ Se ainda assim o autovacuum_vacuum levar muito tempo:
    ■ diminuir o autovacuum_vacuum_cost_delay
    ■ aumentar o autovacuum_work_mem
    ★ Em tabelas muito pequenas, aumentar o
    autovacuum_vacuum_threshold
    ★ Se houverem tabelas pequenas com muitas atualizações, que incham
    muito rápido, diminuir o autovacuum_naptime
    

    View Slide

26. AUTOVACUUM dead tuples
    ★ Em tabelas grandes, diminuir o autovacuum_vacuum_scale_factor
    ○ Se ainda assim o autovacuum_vacuum levar muito tempo:
    ■ diminuir o autovacuum_vacuum_cost_delay
    ■ aumentar o autovacuum_work_mem
    ★ Em tabelas muito pequenas, aumentar o
    autovacuum_vacuum_threshold
    ★ Se houverem tabelas pequenas com muitas atualizações, que incham
    muito rápido, diminuir o autovacuum_naptime
    

    View Slide

27. AUTOVACUUM long transactions
    ★ DETAIL: 435549 dead row versions cannot be removed yet,
    oldest xmin: 2660439608
    ★ SELECT * FROM pg_stat_activity WHERE backend_xmin = 2660439608;
    ★ Observar o xact_start da transação;
    

    View Slide

28. Parâmetros novos (PG13)
    ❖ https://www.postgresql.org/docs/13/runtime-config-autovacuum.html
    ❖ autovacuum_vacuum_insert_threshold (1000)
    ❖ autovacuum_vacuum_insert_scale_factor (20%)
    ❖ Antes do PG13 era comum sugerir rodar um VACUUM numa
    tabela após uma carga de dados ou em toda a base após
    importar um dump;
    

    View Slide

29. Parâmetros globais
    ★ autovacuum (on)
    ★ log_autovacuum_min_duration (-1) → 0
    ★ autovacuum_max_workers (3)
    ★ autovacuum_naptime (1min)
    ★ autovacuum_vacuum_cost_delay (2ms)
    ★ autovacuum_vacuum_cost_limit (200)
    

    View Slide

30. Quando desligar o autovacuum?
    ➢ Em uma ou mais tabelas
    ○ Durante uma rotina de carga pesada em uma tabela:
    ■ ALTER TABLE t SET (autovacuum_enabled = off);
    ■ Realizar a carga
    ■ VACUUM ANALYZE t;
    ■ ALTER TABLE t RESET (autovacuum_enabled);
    ○
    

    View Slide

31. Quando desligar o autovacuum?
    ➢ Em toda a instância (cluster)
    ○ Durante a importação de um dump
    ■ ALTER SYSTEM SET autovacuum = off;
    SELECT pg_reload_conf();
    ■ Importar o dump
    ■ VACUUM ANALYZE;
    ■ ALTER SYSTEM RESET autovacuum;
    SELECT pg_reload_conf();
    

    View Slide

32. Extensões
    ➢ pg_visibility (PG >= 9.6)
    ○ pg_visibility_map_summary
    ➢ pg_freespacemap (PG >= 8.3)
    ○ pg_freespace
    ➢ pgstattuple (PG >= 8.3)
    ○ pgstattuple_approx
    ➢ pg_repack
    ○ https://github.com/reorg/pg_repack
    

    View Slide