Ajuste fino no autovacuum PostgreSQL por Fábio Telles Rodriguez 

No content

No content

autovacuum (PG >= 8.2) auto ANALYZE + VACUUM 

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 

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

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 

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 

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[] | | | 

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(); 

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; 

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 | | | 

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 

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; 

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% 

Disparo do autoanalyze n_mod_since_analyze >= autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * n_live_tup 

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; 

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 

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) 

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 

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; 

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); 

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 

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; 

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 

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 

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; 

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; 

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) 

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); ○ 

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(); 

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