"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