Обсуждение: Cannot allocate memory
Hi,
We have a problem where PostgreSQL will restart or shutdown when calling it through PDAL write filter. This was after we applied pgtune on the postgresql.conf.
These are the settings on the machine:
Virtual Machine – Ubuntu 13.10
1.92GB Memory
2 Parallel Processors
And these are the configurations from pgtune:
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
default_statistics_target = 50 # pgtune wizard 2013-11-14
maintenance_work_mem = 112MB # pgtune wizard 2013-11-14
constraint_exclusion = on # pgtune wizard 2013-11-14
checkpoint_completion_target = 0.9 # pgtune wizard 2013-11-14
effective_cache_size = 1408MB # pgtune wizard 2013-11-14
work_mem = 11MB # pgtune wizard 2013-11-14
wal_buffers = 8MB # pgtune wizard 2013-11-14
checkpoint_segments = 16 # pgtune wizard 2013-11-14
shared_buffers = 448MB # pgtune wizard 2013-11-14
max_connections = 80 # pgtune wizard 2013-11-14
I have also set the shmmax to a higher value to adapt to the new configurations but it does not seem to solve the problem.
Below is a snippet of the postgresql.log:
2013-11-15 11:02:35 CET LOG: could not fork autovacuum worker process: Cannot allocate memory
2013-11-15 11:02:36 CET LOG: could not send data to client: Broken pipe
2013-11-15 11:02:36 CET LOG: unexpected EOF on client connection
Thanks
Zhi Feng
"Heng Zhi Feng (zheng@hsr.ch)" <zheng@hsr.ch> wrote: > Virtual Machine – Ubuntu 13.10 > 1.92GB Memory > 2 Parallel Processors > work_mem = 11MB > shared_buffers = 448MB > max_connections = 80 > 2013-11-15 11:02:35 CET LOG: could not fork autovacuum worker process: Cannot allocate memory > 2013-11-15 11:02:36 CET LOG: could not send data to client: Broken pipe > 2013-11-15 11:02:36 CET LOG: unexpected EOF on client connection Before you start PostgreSQL, what does `free -m` show? On such a tiny machine, some of the usual advice needs to be modified a bit. Sure, people say to start with shared_buffers at 25% of machine RAM, but if the (virtual) machine has so little RAM that the OS is already taking a significant percentage, I would say to go with 25% of what is free (excluding OS cache). Likewise, the advice I usually give to start with work_mem at 25% of machine RAM divided by max_connections should be based on *available* RAM. So 4MB to 5MB is probably going to be more appropriate than 11MB. You will probably need to reduce temp_buffers to 2MB or less -- right now 1/3 of your machine RAM could be tied up in space reserved for caching temporary table data, not released until connections close. Since this VM is tight on resources and only has two cores, you might want to use pgbouncer, configured in transaction mode with a pool limited to something like 5 connections, so that you can increase work_mem and avoid over-taxing the resources you have. http://wiki.postgresql.org/wiki/Number_Of_Database_Connections -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company