265 lines
7.9 KiB
Plaintext
265 lines
7.9 KiB
Plaintext
############ CONFIGURAÇÃO OTIMIZADA POSTGRESQL 16 + TIMESCALEDB ############
|
|
# Otimizado para: 4 núcleos CPU, 8GB RAM
|
|
# Ambiente: Zabbix 7.0 LTS com ~1000 equipamentos
|
|
# Features: TimescaleDB Hypertables + Compressão nativa
|
|
# Objetivo: Máxima performance para time-series e retenção longa
|
|
############################################################################
|
|
|
|
# Adicione ao arquivo: /etc/postgresql/16/main/postgresql.conf
|
|
|
|
############################################################################
|
|
# CONEXÕES
|
|
############################################################################
|
|
|
|
listen_addresses = '*'
|
|
max_connections = 300
|
|
superuser_reserved_connections = 3
|
|
|
|
############################################################################
|
|
# MEMÓRIA - OTIMIZADO PARA 8GB RAM (3GB para PostgreSQL)
|
|
############################################################################
|
|
|
|
# Buffer compartilhado (25-30% da RAM disponível para PostgreSQL)
|
|
shared_buffers = 2GB
|
|
|
|
# Cache efetivo (estimativa de memória total disponível para cache)
|
|
# Inclui cache do SO + shared_buffers
|
|
effective_cache_size = 4GB
|
|
|
|
# Memória para operações de manutenção (VACUUM, CREATE INDEX)
|
|
maintenance_work_mem = 512MB
|
|
|
|
# Memória por operação de sort/hash (conexão individual)
|
|
work_mem = 32MB
|
|
|
|
# Memória para operações de autovacuum
|
|
autovacuum_work_mem = 256MB
|
|
|
|
############################################################################
|
|
# WAL (Write-Ahead Log) - OTIMIZADO PARA ALTA ESCRITA
|
|
############################################################################
|
|
|
|
# Tamanho do buffer WAL em memória
|
|
wal_buffers = 16MB
|
|
|
|
# Nível mínimo de WAL (minimal = máxima performance sem replicação)
|
|
wal_level = minimal
|
|
|
|
# Tamanhos máximo e mínimo de WAL em disco
|
|
max_wal_size = 4GB
|
|
min_wal_size = 1GB
|
|
|
|
# Compressão de WAL (reduz I/O de disco)
|
|
wal_compression = on
|
|
|
|
# Método de gravação no disco
|
|
wal_writer_delay = 200ms
|
|
|
|
############################################################################
|
|
# CHECKPOINTS - REDUZ I/O E AUMENTA PERFORMANCE
|
|
############################################################################
|
|
|
|
# Target de conclusão do checkpoint (0.9 = 90% do intervalo)
|
|
checkpoint_completion_target = 0.9
|
|
|
|
# Intervalo entre checkpoints automáticos
|
|
checkpoint_timeout = 15min
|
|
|
|
# Log de checkpoints (útil para tuning)
|
|
log_checkpoints = on
|
|
|
|
############################################################################
|
|
# PLANNER E QUERY OPTIMIZATION
|
|
############################################################################
|
|
|
|
# Custo estimado de leitura aleatória em disco
|
|
# SSD = 1.1, HDD = 4.0
|
|
random_page_cost = 1.1
|
|
|
|
# Custo de leitura sequencial
|
|
seq_page_cost = 1.0
|
|
|
|
# Capacidade de I/O paralelo (SSD)
|
|
effective_io_concurrency = 200
|
|
|
|
# Limite de custo para uso de índice
|
|
cpu_tuple_cost = 0.01
|
|
cpu_index_tuple_cost = 0.005
|
|
cpu_operator_cost = 0.0025
|
|
|
|
############################################################################
|
|
# PARALELISMO - APROVEITA 4 NÚCLEOS
|
|
############################################################################
|
|
|
|
# Máximo de workers para processos em background
|
|
max_worker_processes = 8
|
|
|
|
# Workers por query paralela
|
|
max_parallel_workers_per_gather = 2
|
|
|
|
# Total de workers paralelos simultâneos
|
|
max_parallel_workers = 4
|
|
|
|
# Mínimo de linhas para considerar paralelismo
|
|
min_parallel_table_scan_size = 8MB
|
|
min_parallel_index_scan_size = 512kB
|
|
|
|
############################################################################
|
|
# AUTOVACUUM - CRÍTICO PARA ZABBIX COM TIME-SERIES
|
|
############################################################################
|
|
|
|
# Habilitar autovacuum (OBRIGATÓRIO)
|
|
autovacuum = on
|
|
|
|
# Número de processos autovacuum simultâneos
|
|
autovacuum_max_workers = 4
|
|
|
|
# Intervalo entre varreduras de autovacuum
|
|
autovacuum_naptime = 10s
|
|
|
|
# Thresholds agressivos para Zabbix (muitas escritas)
|
|
autovacuum_vacuum_threshold = 50
|
|
autovacuum_analyze_threshold = 50
|
|
|
|
# Scale factors reduzidos (mais agressivo)
|
|
autovacuum_vacuum_scale_factor = 0.01
|
|
autovacuum_analyze_scale_factor = 0.005
|
|
|
|
# Custo e delay do autovacuum
|
|
autovacuum_vacuum_cost_delay = 10ms
|
|
autovacuum_vacuum_cost_limit = 1000
|
|
|
|
# Prevenir wraparound
|
|
autovacuum_freeze_max_age = 200000000
|
|
|
|
############################################################################
|
|
# VACUUM E MANUTENÇÃO
|
|
############################################################################
|
|
|
|
# Age máximo para vacuum de prevenção
|
|
vacuum_freeze_min_age = 50000000
|
|
vacuum_freeze_table_age = 150000000
|
|
|
|
# Custo de vacuum manual
|
|
vacuum_cost_delay = 0
|
|
vacuum_cost_limit = 200
|
|
|
|
############################################################################
|
|
# OTIMIZAÇÕES ESPECÍFICAS PARA ZABBIX/TIME-SERIES
|
|
############################################################################
|
|
|
|
# Commit assíncrono (aceita pequeno risco para MÁXIMA performance)
|
|
# ATENÇÃO: Em caso de crash, pode perder até 1 segundo de dados
|
|
synchronous_commit = off
|
|
|
|
# Desabilita full page writes (maior risco, muito mais performance)
|
|
# RECOMENDADO apenas com filesystem confiável (ext4, xfs, zfs)
|
|
full_page_writes = off
|
|
|
|
# Compressão de TOAST
|
|
default_toast_compression = lz4
|
|
|
|
############################################################################
|
|
# TIMESCALEDB - EXTENSÃO PARA TIME-SERIES
|
|
############################################################################
|
|
|
|
# Preload da extensão TimescaleDB
|
|
shared_preload_libraries = 'timescaledb,pg_stat_statements'
|
|
|
|
# Configurações TimescaleDB
|
|
timescaledb.max_background_workers = 8
|
|
|
|
############################################################################
|
|
# LOGGING - ANÁLISE E TROUBLESHOOTING
|
|
############################################################################
|
|
|
|
# Destino dos logs
|
|
log_destination = 'stderr'
|
|
logging_collector = on
|
|
log_directory = 'log'
|
|
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
|
|
log_rotation_age = 1d
|
|
log_rotation_size = 100MB
|
|
|
|
# Formato e informações dos logs
|
|
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
|
|
log_timezone = 'America/Sao_Paulo'
|
|
|
|
# Logs de queries lentas (> 1 segundo)
|
|
log_min_duration_statement = 1000
|
|
|
|
# Logs de conexões e locks
|
|
log_connections = on
|
|
log_disconnections = on
|
|
log_lock_waits = on
|
|
|
|
# Log de tabelas temporárias grandes
|
|
log_temp_files = 0
|
|
|
|
# Log de comandos DDL
|
|
log_statement = 'ddl'
|
|
|
|
# Autovacuum logging
|
|
log_autovacuum_min_duration = 0
|
|
|
|
############################################################################
|
|
# CONFIGURAÇÕES DE LOCALE E ENCODING
|
|
############################################################################
|
|
|
|
# Encoding padrão (UTF-8)
|
|
client_encoding = UTF8
|
|
|
|
# Locale
|
|
lc_messages = 'pt_BR.UTF-8'
|
|
lc_monetary = 'pt_BR.UTF-8'
|
|
lc_numeric = 'pt_BR.UTF-8'
|
|
lc_time = 'pt_BR.UTF-8'
|
|
|
|
# Timezone
|
|
timezone = 'America/Sao_Paulo'
|
|
|
|
# Comportamento de strings vazias
|
|
default_text_search_config = 'pg_catalog.portuguese'
|
|
|
|
############################################################################
|
|
# SEGURANÇA E LIMITES
|
|
############################################################################
|
|
|
|
# Timeout de statement (evita queries runaway)
|
|
statement_timeout = 0
|
|
|
|
# Timeout de lock
|
|
lock_timeout = 0
|
|
|
|
# Timeout de idle in transaction
|
|
idle_in_transaction_session_timeout = 0
|
|
|
|
############################################################################
|
|
# ESTATÍSTICAS
|
|
############################################################################
|
|
|
|
# Nível de estatísticas coletadas
|
|
track_activities = on
|
|
track_counts = on
|
|
track_io_timing = on
|
|
track_functions = all
|
|
|
|
# Estatísticas de queries (requer pg_stat_statements)
|
|
pg_stat_statements.max = 10000
|
|
pg_stat_statements.track = all
|
|
|
|
############################################################################
|
|
# OUTROS
|
|
############################################################################
|
|
|
|
# Número de buffers de disco sujos antes de fsync
|
|
bgwriter_delay = 200ms
|
|
bgwriter_lru_maxpages = 100
|
|
bgwriter_lru_multiplier = 2.0
|
|
|
|
# JIT compilation (PostgreSQL 11+)
|
|
jit = on
|
|
jit_above_cost = 100000
|
|
jit_inline_above_cost = 500000
|
|
jit_optimize_above_cost = 500000
|