Thread: Cannot allocate memory
<div class="WordSection1"><p class="MsoNormal"><span lang="EN-US">Hi,</span><p class="MsoNormal"><span lang="EN-US"> </span><pclass="MsoNormal"><span lang="EN-US">We have a problem where PostgreSQL will restart or shutdown whencalling it through PDAL write filter. This was after we applied pgtune on the postgresql.conf.</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><u><span lang="EN-US">These are the settings on the machine:</span></u><pclass="MsoNormal"><span lang="EN-US">Virtual Machine – Ubuntu 13.10</span><p class="MsoNormal"><spanlang="EN-US">1.92GB Memory</span><p class="MsoNormal"><span lang="EN-US">2 Parallel Processors</span><pclass="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><span lang="EN-US">And these are theconfigurations from pgtune:</span><p class="MsoNormal"><span lang="EN-US">#------------------------------------------------------------------------------</span><p class="MsoNormal"><spanlang="EN-US"># CUSTOMIZED OPTIONS</span><p class="MsoNormal"><span lang="EN-US">#------------------------------------------------------------------------------</span><p class="MsoNormal"><spanlang="EN-US">default_statistics_target = 50 # pgtune wizard 2013-11-14</span><pclass="MsoNormal"><span lang="EN-US">maintenance_work_mem = 112MB # pgtune wizard 2013-11-14</span><pclass="MsoNormal"><span lang="FR-CH">constraint_exclusion = on # pgtune wizard2013-11-14</span><p class="MsoNormal"><span lang="EN-US">checkpoint_completion_target = 0.9 # pgtune wizard 2013-11-14</span><pclass="MsoNormal"><span lang="EN-US">effective_cache_size = 1408MB # pgtune wizard 2013-11-14</span><pclass="MsoNormal"><span lang="EN-US">work_mem = 11MB # pgtune wizard2013-11-14</span><p class="MsoNormal"><span lang="EN-US">wal_buffers = 8MB #pgtune wizard 2013-11-14</span><p class="MsoNormal"><span lang="EN-US">checkpoint_segments = 16 # pgtune wizard 2013-11-14</span><p class="MsoNormal"><span lang="EN-US">shared_buffers = 448MB # pgtune wizard 2013-11-14</span><p class="MsoNormal"><span lang="EN-US">max_connections= 80 # pgtune wizard 2013-11-14</span><p class="MsoNormal"><spanlang="EN-US"> </span><p class="MsoNormal"><spanlang="EN-US">I have also set the shmmax to a higher value to adapt to the new configurations but itdoes not seem to solve the problem.</span><p class="MsoNormal"><span lang="EN-US"> </span><p class="MsoNormal"><u><spanlang="EN-US">Below is a snippet of the postgresql.log:</span></u><p class="MsoNormal"><span lang="EN-US">2013-11-1511:02:35 CET LOG: could not fork autovacuum worker process: Cannot allocate memory</span><p class="MsoNormal"><spanlang="EN-US">2013-11-15 11:02:36 CET LOG: could not send data to client: Broken pipe</span><p class="MsoNormal">2013-11-1511:02:36 CET LOG: unexpected EOF on client connection<p class="MsoNormal"> <p class="MsoNormal">Thanks<pclass="MsoNormal"> <p class="MsoNormal">Zhi Feng</div>
"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