title: Postgres gets out of memory errors despite having plenty of free memory
body: <p>I have a server running Postgres 9.1.15. The server has 2GB of RAM and no swap. Intermittently Postgres will start getting "out of memory" errors on some SELECTs, and will continue doing so until I restart Postgres <em>or</em> some of the clients that are connected to it. What's weird is that when this happens, <code>free</code> still reports over 500MB of free memory.</p>
  
  <p><code>select version();</code>:</p>
  
  <pre><code>PostgreSQL 9.1.15 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
  </code></pre>
  
  <p><code>uname -a</code>:</p>
  
  <pre><code>Linux db 3.2.0-23-virtual #36-Ubuntu SMP Tue Apr 10 22:29:03 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
  </code></pre>
  
  <p>Postgresql.conf (everything else is commented out/default):</p>
  
  <pre><code>max_connections = 100
  shared_buffers = 500MB
  work_mem = 2000kB
  maintenance_work_mem = 128MB
  wal_buffers = 16MB
  checkpoint_segments = 32
  checkpoint_completion_target = 0.9
  random_page_cost = 2.0
  effective_cache_size = 1000MB
  default_statistics_target = 100
  log_temp_files = 0
  </code></pre>
  
  <p>I got these values from <a href="http://pgtune.leopard.in.ua/">pgtune</a> (I chose "mixed type of applications") and have been fiddling with them based on what I've read, without making much real progress. At the moment there's 68 connections, which is a typical number (I'm not using pgbouncer or any other connection poolers yet).</p>
  
  <p><code>/etc/sysctl.conf</code>:</p>
  
  <pre><code>kernel.shmmax=1050451968
  kernel.shmall=256458
  
  vm.overcommit_ratio=100
  vm.overcommit_memory=2
  </code></pre>
  
  <p>I first changed <code>overcommit_memory</code> to 2 about a fortnight ago after the OOM killer killed the Postgres server. Prior to that the server had been running fine for a long time. The errors I get now are less catastrophic but much more annoying because they are much more frequent.</p>
  
  <p>I haven't had much luck pinpointing the first event that causes postgres to run "out of memory" - it seems to be different each time. The most recent time it crashed, the first three lines logged were:</p>
  
  <pre><code>2015-04-07 05:32:39 UTC ERROR:  out of memory
  2015-04-07 05:32:39 UTC DETAIL:  Failed on request of size 125.
  2015-04-07 05:32:39 UTC CONTEXT:  automatic analyze of table "xxx.public.delayed_jobs"
  TopMemoryContext: 68688 total in 10 blocks; 4560 free (4 chunks); 64128 used
  [... snipped heaps of lines which I can provide if they are useful ...]
  
  ---
  
  2015-04-07 05:33:58 UTC ERROR:  out of memory
  2015-04-07 05:33:58 UTC DETAIL:  Failed on request of size 16.
  2015-04-07 05:33:58 UTC STATEMENT:  SELECT oid, typname, typelem, typdelim, typinput FROM pg_type
  2015-04-07 05:33:59 UTC LOG:  could not fork new process for connection: Cannot allocate memory
  2015-04-07 05:33:59 UTC LOG:  could not fork new process for connection: Cannot allocate memory
  2015-04-07 05:33:59 UTC LOG:  could not fork new process for connection: Cannot allocate memory
  TopMemoryContext: 396368 total in 50 blocks; 10160 free (28 chunks); 386208 used
  [... snipped heaps of lines which I can provide if they are useful ...]
  
  ---
  
  2015-04-07 05:33:59 UTC ERROR:  out of memory
  2015-04-07 05:33:59 UTC DETAIL:  Failed on request of size 1840.
  2015-04-07 05:33:59 UTC STATEMENT:  SELECT... [nested select with 4 joins, 19 ands, and 2 order bys]
  TopMemoryContext: 388176 total in 49 blocks; 17264 free (55 chunks); 370912 used
  </code></pre>
  
  <p>The crash before that, a few hours earlier, just had three instances of that last query as the first three lines of the crash. That query gets run <em>very</em> often, so I'm not sure if the issues are <em>because</em> of this query, or if it just comes up in the error log because it's a reasonably complex SELECT getting run all the time. That said, here's an EXPLAIN ANALYZE of it: <a href="http://explain.depesz.com/s/r00">http://explain.depesz.com/s/r00</a></p>
  
  <p>This is what <code>ulimit -a</code> for the postgres user looks like:</p>
  
  <pre><code>core file size          (blocks, -c) 0
  data seg size           (kbytes, -d) unlimited
  scheduling priority             (-e) 0
  file size               (blocks, -f) unlimited
  pending signals                 (-i) 15956
  max locked memory       (kbytes, -l) 64
  max memory size         (kbytes, -m) unlimited
  open files                      (-n) 1024
  pipe size            (512 bytes, -p) 8
  POSIX message queues     (bytes, -q) 819200
  real-time priority              (-r) 0
  stack size              (kbytes, -s) 8192
  cpu time               (seconds, -t) unlimited
  max user processes              (-u) 15956
  virtual memory          (kbytes, -v) unlimited
  file locks                      (-x) unlimited
  </code></pre>
  
  <p>I'll try and get the exact numbers from <code>free</code> next time there's a crash, in the meantime this is a braindump of all the info I have.</p>
  
  <p>Any ideas on where to go from here?</p>