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
4. autovacuum (PG >= 8.2) auto ANALYZE + VACUUM
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
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)
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
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
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[] | | |
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();
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;
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 | | |
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
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;
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%
16. Disparo do autoanalyze n_mod_since_analyze >= autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * n_live_tup
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;
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
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)
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
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;
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);
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
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;
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
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
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;
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;
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)
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); ◦
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();
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