postgresql 9.1 out of memory

From: Евгений Селявка <evg(dot)selyavka(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: postgresql 9.1 out of memory
Date: 2013-10-26 12:54:27
Message-ID: CAKPhvNY+LJV1MDTW=1KHX+igoUyyPVW8_V1-5+T18qBQ3CJGrw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Сolleagues can you help me with advice. I have

PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6
20120305 (Red Hat 4.4.6-4), 64-bit

on

Centos 6.3 - 2.6.32-279.11.1.el6.x86_64 #1 SMP Tue Oct 16 15:57:10 UTC 2012
x86_64 x86_64 x86_64 GNU/Linux

kernel.shmmax = 68719476736
kernel.shmall = 4294967296
vm.swappiness = 15
vm.overcommit_memory = 2
vm.overcommit_ratio = 90

Server HW:

Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 1
Core(s) per socket: 4
CPU socket(s): 2
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 23
Stepping: 6
CPU MHz: 3000.105
BogoMIPS: 6000.04
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 6144K

32GB RAM

HDD 10k rpm SAS.

today my database crashed with out of memory
Server is standalone for DB, no other application work on that server.

Before server crash i have this parameters in config:

max_connections = 350
shared_buffers = 24GB
temp_buffers = 128MB
max_prepared_transactions = 350
work_mem = 2GB
maintenance_work_mem = 1GB
max_stack_depth = 4MB
effective_io_concurrency = 2
fsync = on
full_page_writes = off
checkpoint_segments = 64
checkpoint_timeout = 15min
checkpoint_completion_target = 0.75
checkpoint_warning = 50s
random_page_cost = 3.0
effective_cache_size = 14GB
autovacuum_max_workers = 16
autovacuum_vacuum_threshold = 900
autovacuum_analyze_threshold = 350
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

After crash i change this parameters:

shared_buffers = 8GB 1/4 from all available RAM
effective_cache_size = 16GB 50% of RAM
work_mem = 1GB

DB size is 25GB

What other steps can I take to avoid such behavior in the future?

--
С уважением Селявка Евгений

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2013-10-26 13:59:27 Re: postgresql 9.1 out of memory
Previous Message Patrick Dung 2013-10-26 05:24:23 Re: ZFS-FreeBSD + postgresql performance