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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers