С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
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
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
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?
--
С уважением Селявка Евгений
С уважением Селявка Евгений