Out of memory...

From: "Cassiano, Marco" <mcassiano(at)manord(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Out of memory...
Date: 2012-02-17 15:41:52
Message-ID: BC53C974C3B9E542BC0A9BD4C5B1168A0E9326E0@NEWMAIL.manord.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

This morning my db experienced 10 minutes of "out of memory" condition with the log filled up of messages like :

TopMemoryContext: 90856 total in 13 blocks; 7936 free (6 chunks); 82920 used
  TopTransactionContext: 24576 total in 2 blocks; 21360 free (15 chunks); 3216 used
  TOAST to main relid map: 24576 total in 2 blocks; 11872 free (5 chunks); 12704 used
  AV worker: 24576 total in 2 blocks; 19312 free (9 chunks); 5264 used
    Autovacuum Portal: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used
      Vacuum: 8192 total in 1 blocks; 8080 free (0 chunks); 112 used
  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  smgr relation table: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used
  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
  Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
  Relcache by OID: 24576 total in 2 blocks; 13872 free (3 chunks); 10704 used
  CacheMemoryContext: 817840 total in 20 blocks; 151408 free (2 chunks); 666432 used
    mmfg.cc_records_2_mmfg_nome_file: 2048 total in 1 blocks; 776 free (0 chunks); 1272 used
    mmfg.cc_records_1_societa,cliente,anno,stagione,collezione,clas: 2048 total in 1 blocks; 224 free (0 chunks); 1824 used
    cc_records_pkey: 2048 total in 1 blocks; 224 free (0 chunks); 1824 used
    pg_index_indrelid_index: 2048 total in 1 blocks; 728 free (0 chunks); 1320 used
..
  pg_authid_rolname_index: 3072 total in 2 blocks; 1768 free (4 chunks); 1304 used
  MdSmgr: 8192 total in 1 blocks; 7968 free (0 chunks); 224 used
  LOCALLOCK hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used
  Postmaster: 57344 total in 3 blocks; 48640 free (331 chunks); 8704 used
  ErrorContext: 8192 total in 1 blocks; 8160 free (4 chunks); 32 used
2012-02-17 11:08:47 CET    0 4f3e272f.65db - ERROR:  out of memory
2012-02-17 11:08:47 CET    0 4f3e272f.65db - DETAIL:  Failed on request of size 16731918.
2012-02-17 11:08:47 CET    0 4f3e272f.65db - CONTEXT:  automatic vacuum of table "mdn.mmfg.cc_records"
2012-02-17 11:08:53 CET    0 4f3e272f.65db - LOG:  automatic vacuum of table "mdn.mmfg.mo7_records": index scans: 0

OR

TopMemoryContext: 164632 total in 22 blocks; 12328 free (44 chunks); 152304 used
  TopTransactionContext: 8192 total in 1 blocks; 7328 free (0 chunks); 864 used
  RI compare cache: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
  RI query cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
  TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used
  Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
  Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used
  MessageContext: 524288 total in 7 blocks; 48672 free (6 chunks); 475616 used
  Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  smgr relation table: 57344 total in 3 blocks; 17872 free (10 chunks); 39472 used
  TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used
  Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
  PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used
    PortalHeapMemory: 1024 total in 1 blocks; 824 free (0 chunks); 200 used
      ExecutorState: 122880 total in 4 blocks; 8152 free (2 chunks); 114728 used
        HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext: 3170352 total in 10 blocks; 304 free (5 chunks); 3170048 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
..
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        AggContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
          TupleHashTable: 253952 total in 5 blocks; 119344 free (16 chunks); 134608 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
  Relcache by OID: 24576 total in 2 blocks; 5552 free (3 chunks); 19024 used
  CacheMemoryContext: 4618048 total in 36 blocks; 986456 free (5 chunks); 3631592 used

AND ALSO

   movcor.movcor_pf_dett_1_mod,var: 2048 total in 1 blocks; 664 free (0 chunks); 1384 used
    CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
    CachedPlanSource: 3072 total in 2 blocks; 1832 free (1 chunks); 1240 used
    SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
    CachedPlan: 3072 total in 2 blocks; 768 free (0 chunks); 2304 used
..
..
    CachedPlanSource: 3072 total in 2 blocks; 1968 free (2 chunks); 1104 used
    SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
    CachedPlan: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
    CachedPlanSource: 3072 total in 2 blocks; 1736 free (1 chunks); 1336 used

AND

2012-02-17 11:19:31 CET    0 4f05cb33.1c0d - LOG:  could not fork new process for connection: Cannot allocate memory

There was an autovaccum running on a big table saying it was "to avoid xid wraparound"

It happened only once till this morning, and that time Postgresql finally crashed closing all the connections and restarting automatically with a recovery.

My configuration is :

Postgresql 9.1.2 compiled and running on Redhat 5 64 bit
DB Size : about 100 GB

RAM 5 GB
Shared Buffers 1 GB
temp_buffers = 8MB                             
work_mem = 12MB                               
maintenance_work_mem = 300MB         

Could you help to understand

1) How can I understand what is filling up memory ?
2) How can I avoid (or limit) this kind of situations ?

Thank you very much!

Marco

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Steve Crawford 2012-02-17 18:00:36 Re: Big difference in databasesize compared with disksize
Previous Message Jan Nielsen 2012-02-17 15:37:22 Monitor without superuser?