Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Ajuste fino no Autovacuum

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.

Fábio Telles Rodriguez

July 16, 2020
Tweet

More Decks by Fábio Telles Rodriguez

Other Decks in Technology

Transcript

  1. 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
  2. ANALYZE ANALYZE [ ( option [, ...] ) ] [

    table_and_columns [, ...] ] ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ] where option can be one of: VERBOSE [ boolean ] SKIP_LOCKED [ boolean ] (PG >= 12)
  3. 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
  4. 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
  5. 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[] | | |
  6. 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();
  7. 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;
  8. 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 | | |
  9. 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
  10. 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%
  11. 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;
  12. 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
  13. 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)
  14. 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
  15. 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;
  16. 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);
  17. 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
  18. 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
  19. 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
  20. 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;
  21. 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;
  22. 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)
  23. 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); ◦
  24. 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();
  25. 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