Pessoal, bom dia! Estou com alguns problemas e dificuldade em identificar causas momentanias de lentidão do banco, penso que talvez seja alguma coisa ligada as configurações do banco e semafaros do Linux, gostaria da experiência de vocês para orientação para tentar chegar numa configuração mais equalizada a demanda e equipamento.
Demanda pesada de consultas e transações de manutenção, aplicação web (30%) e client Server (70%). O Vacuum é executado toda madrugada automaticamente via script: su root -c "vacuumdb -U $vU -h $vH -z -f -v -d $vB" >> /home/postgresql/logs/vacuumdb2.txt Dell PowerEdge 1800 Xeon 8GB - HD scsi (dedicado) OpenSuse 10.3 Postgresql 8.2.4 ============================================================================ == Sysctl.conf ============================================================================ == kernel.shmall = 2147483648 kernel.shmmax = 2147483648 kernel.shmmni = 309329920 kernel.sem = 250 32000 100 128 #kernel.disable_cap_mlock = 1 fs.file-max = 65536 #net.ipv4.ip_local_port_range = 1024 65000 #net.core.rmem_default = 16777216 #1048576 #net.core.rmem_max = 16777216 #1048576 #net.core.wmem_default = 16777216 #262144 net.core.wmem_max = 16777216 #262144 vm.overcommit_memory = 2 vm.overcommit_ratio = 70 ============================================================================ == Postgresql.conf ============================================================================ == max_connections = 220 # (change requires restart) shared_buffers = 1024MB # min 128kB or max_connections*16kB max_prepared_transactions = 3 # can be 0 or more work_mem = 512MB # min 64kB maintenance_work_mem = 1024MB # min 1MB max_fsm_pages = 204800 # min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 100 # 0-1000 milliseconds //GS 200 fsync = on # turns forced synchronization on or off wal_sync_method = fsync # the default is the first option full_page_writes = on # recover from partial page writes wal_buffers = 128kB # min 32kB commit_delay = 500 # range 0-100000, in microseconds //GS 1000 commit_siblings = 5 # range 1-1000 checkpoint_segments = 32 # in logfile segments, min 1, 16MB each //GS 8 checkpoint_timeout = 5min # range 30s-1h checkpoint_warning = 30s # 0 is off enable_bitmapscan = on enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on enable_tidscan = on random_page_cost = 2.0 # same scale as above //GS 1 - antes 4.0 effective_cache_size = 2GB stats_command_string = on update_process_title = on stats_start_collector = on # needed for block or row stats stats_block_level = off stats_row_level = on autovacuum = off # enable autovacuum subprocess? deadlock_timeout = 1s max_locks_per_transaction = 64 # min 10 _______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
