Skip site navigation (1) Skip section navigation (2)

out of memory error

From: Vincent Dautremont <vincent(at)searidgetech(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: out of memory error
Date: 2012-05-22 16:44:41
Message-ID: CAA4Vp4_pD3tc8dT8rtdPjO4vQfW+eyuNpYb72HPYYRokdPHS7A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
H,
 after a few days, i'm seeing the following logs in a database (postgresql
8.3.15 on Windows)
running with rubyrep 1.2.0 for syncing a few table small that have frequent
update / insert/ delete.

I don't understand it and I'd like to know what happens and why. How to get
rid of it.
I've seen in the same time from Windows Task Manager that the memory of a
postregres.exe process goes constantly up (I've seen it up to 1.4 GB) then
I suppose the process must crash since I can't see it anymore.

log :
---------------------
TopMemoryContext: 197681816 total in 24131 blocks; 394104 free (24179
chunks); 197287712 used
  TopTransactionContext: 8192 total in 1 blocks; 7792 free (0 chunks); 400
used
  PL/PgSQL function context: 8192 total in 1 blocks; 2912 free (3 chunks);
5280 used
  PL/PgSQL function context: 8192 total in 1 blocks; 2912 free (3 chunks);
5280 used
  PL/PgSQL function context: 8192 total in 1 blocks; 3168 free (4 chunks);
5024 used
  PL/PgSQL function context: 8192 total in 1 blocks; 3168 free (4 chunks);
5024 used
  PL/PgSQL function context: 8192 total in 1 blocks; 3184 free (4 chunks);
5008 used
  PL/PgSQL function context: 8192 total in 1 blocks; 3184 free (4 chunks);
5008 used
  RI compare cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
  RI query cache: 24576 total in 2 blocks; 14136 free (5 chunks); 10440 used
  CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
  Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks);
4344 used
  PLpgSQL function cache: 24328 total in 2 blocks; 5904 free (0 chunks);
18424 used
  Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
  Record information cache: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
  Prepared Queries: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used
  Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344
used
  Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks);
10504 used
  MessageContext: 8192 total in 1 blocks; 7144 free (1 chunks); 1048 used
  smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks); 6416
used
  TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks);
16 used
  Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
  PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
    PortalHeapMemory: 4096 total in 1 blocks; 1888 free (0 chunks); 2208
used
      ExecutorState: 122880 total in 4 blocks; 46296 free (5 chunks); 76584
used
        TupleSort: 24600 total in 2 blocks; 7512 free (0 chunks); 17088 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
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 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
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 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
        ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
  Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
  CacheMemoryContext: 8531792 total in 24 blocks; 282000 free (617 chunks);
8249792 used
    unnamed prepared statement: 253952 total in 5 blocks; 10664 free (7
chunks); 243288 used
    unnamed prepared statement: 253952 total in 5 blocks; 86232 free (1
chunks); 167720 used
    CachedPlan: 1024 total in 1 blocks; 576 free (0 chunks); 448 used
    CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
    SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
    CachedPlan: 1024 total in 1 blocks; 248 free (0 chunks); 776 used
    CachedPlanSource: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
    SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
    CachedPlan: 1024 total in 1 blocks; 576 free (0 chunks); 448 used
    CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
    SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
    CachedPlan: 1024 total in 1 blocks; 248 free (0 chunks); 776 used
    CachedPlanSource: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
    SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used

.
.
. there it goes for about 80MB of log then it ends
.
.
 SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
    CachedPlan: 1024 total in 1 blocks; 248 free (0 chunks); 776 used
    CachedPlanSource: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
    SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
    CachedPlan: 1024 total in 1 blocks; 576 free (0 chunks); 448 used
    CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
    SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
    CachedPlan: 1024 total in 1 blocks; 248 free (0 chunks); 776 used
    CachedPlanSource: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
    SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
    CachedPlan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
    CachedPlanSource: 1024 total in 1 blocks; 840 free (0 chunks); 184 used
    CachedPlan: 3072 total in 2 blocks; 392 free (0 chunks); 2680 used
    CachedPlanSource: 3072 total in 2 blocks; 1904 free (1 chunks); 1168
used
    CachedPlan: 7168 total in 3 blocks; 4080 free (1 chunks); 3088 used
    CachedPlanSource: 3072 total in 2 blocks; 1384 free (0 chunks); 1688
used
    pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks);
936 used
    pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
    pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
    pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0 chunks);
936 used
    pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
    pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
    pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
  MdSmgr: 8192 total in 1 blocks; 7288 free (6 chunks); 904 used
  LOCALLOCK hash: 8192 total in 1 blocks; 1856 free (0 chunks); 6336 used
  Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 used
  ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2012-05-02 08:01:10 WET ERROR:  out of memory
2012-05-02 08:01:10 WET DETAIL:  Failed on request of size 8.
2012-05-02 08:01:10 WET STATEMENT:  SELECT
n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_catalog.pg_get_expr(def.adbin,
def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype  FROM
pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace =
n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  JOIN
pg_catalog.pg_type t ON (a.atttypid = t.oid)  LEFT JOIN
pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum =
def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid
AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON
(dc.oid=dsc.classoid AND dc.relname='pg_class')  LEFT JOIN
pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND
dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped  AND
n.nspname LIKE 'public'  AND c.relname LIKE 'rr_pending_changes'  ORDER BY
nspname,c.relname,attnum
2012-05-02 08:01:10 WET LOG:  could not send data to client: No connection
could be made because the target machine actively refused it.


2012-05-02 08:01:10 WET STATEMENT:  SELECT
n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_catalog.pg_get_expr(def.adbin,
def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype  FROM
pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace =
n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  JOIN
pg_catalog.pg_type t ON (a.atttypid = t.oid)  LEFT JOIN
pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum =
def.adnum)  LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid
AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON
(dc.oid=dsc.classoid AND dc.relname='pg_class')  LEFT JOIN
pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND
dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped  AND
n.nspname LIKE 'public'  AND c.relname LIKE 'rr_pending_changes'  ORDER BY
nspname,c.relname,attnum
2012-05-02 08:01:10 WET LOG:  could not receive data from client: No
connection could be made because the target machine actively refused it.
---------------------

Thank you for hints about that.
Vincent.

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2012-05-22 17:33:43
Subject: Re: out of memory error
Previous:From: Daryl StultzDate: 2012-05-19 13:24:25
Subject: Re: dangling connections

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group