Hi, On 17 Březen 2014, 11:45, basti wrote: > 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. You're missing >18 months of fixes on the master (slightly less on the slave). > 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? How much swap do you have? > I'am not shure if work_mem, maintenance_work_mem and > effective_cache_size is set correct. That's hard to say. I don't see any immediate issue there, but it really depends on your application. For example 200 connections with work_mem=192MB may be dangerous if many connections are active at the same time. > > /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 So what does the query do? Show us explain plan (explain analyze would be nice, but if it's running so slow). Which kernel is this? When the OOM strikes, it should print detailed into into the log - what does it say? When you look at "top" output, which processes consume most memory? Are there multiple backends consuming a lot of memory? What queries are they running? Assuming you have a monitoring system in place, collecting memory stats (you should have that), what does it say about history? Is there a sudden increase in consumed memory or something suspicious? regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
