Hello,
we have a database master Version:
PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-2) 4.7.2, 64-bit
and a WAL-Replication Slave with hot-standby version:
PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.7.2-5) 4.7.2, 64-bit.
Since a few days we had problems with the Linux OOM-Killer.
Some simple query that normally take around 6-7 minutes now takes 5 hours.
We did not change any configuration values the last days.
First of all I have set
vm.overcommit_memory=2
vm.overcommit_ratio=80
Here are some values of my DB-Master config, the Server has 32GB RAM and
is only for database, no other service.
Did anybody see some mistakes?
I'am not shure if work_mem, maintenance_work_mem and
effective_cache_size is set correct.
/etc/postgresql/9.1/main/postgresql.conf
max_connections = 200
ssl = true
shared_buffers = 6GB # min 128kB
work_mem = 192MB # min 64kB
maintenance_work_mem = 1GB # min 1MB
wal_level = hot_standby
synchronous_commit = off
wal_buffers = 16MB
checkpoint_segments = 16
checkpoint_completion_target = 0.9
archive_mode = on
archive_command = 'rsync -a %p -e "ssh -i
/var/lib/postgresql/.ssh/id_rsa"
postgres@my_postgres_slave:/var/lib/postgresql/9.1/wals/dolly_main/%f
</dev/null'
max_wal_senders = 1
wal_keep_segments = 32
random_page_cost = 2.0
effective_cache_size = 22GB
default_statistics_target = 100
constraint_exclusion = off
join_collapse_limit = 1
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 40000
log_lock_waits = on
track_counts = on
autovacuum = on
log_autovacuum_min_duration = 5000
autovacuum_max_workers = 4
datestyle = 'iso, dmy'
deadlock_timeout = 1s
Thanks a lot!
Basti