postgresql 9.1 out of memory

Поиск
Список
Период
Сортировка
От Евгений Селявка
Тема postgresql 9.1 out of memory
Дата
Msg-id CAKPhvNY+LJV1MDTW=1KHX+igoUyyPVW8_V1-5+T18qBQ3CJGrw@mail.gmail.com
обсуждение исходный текст
Ответы Re: postgresql 9.1 out of memory
Re: postgresql 9.1 out of memory
Re: postgresql 9.1 out of memory
Re: postgresql 9.1 out of memory
Список 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?

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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Scott Ribe
Дата:
Сообщение: Re: failover and switchover in postgresql
Следующее
От: Tom Lane
Дата:
Сообщение: Re: postgresql 9.1 out of memory