[HACKERS] Cannot allocate memory

[Heng Zhi Feng (zh...@hsr.ch)]

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

Re: [HACKERS] Cannot allocate memory

[Kevin Grittner]

Heng Zhi Feng (zh...@hsr.ch) zh...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers