Thread: ERROR: Memory exhausted in AllocSetAlloc(68)

Hello all,

I can't understand why my server, having 1 Gb of the RAM can't reproduce one simple
SELECT on two not so very large tables:

isbs=# SELECT count(i.*),sum(i.param1),sum(60-i.param1) FROM inlog i, object ob WHERE i.dttm>='1-4-2003' AND
i.dttm<'1-5-2003' AND i.param1<60 AND i.sgrid=10 AND i.servid<>1496 AND ob.dton<=i.dttm AND i.dttm<=ob.dtoff
AND not ob.obid IN (292,746);
ERROR:  Memory exhausted in AllocSetAlloc(68)



There are server's error log:

TopMemoryContext: 32796 total in 4 blocks; 14036 free (5 chunks); 18760 used
TopTransactionContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DeferredTriggerXact: 0 total in 0 blocks; 0 free (0 chunks); 0 used
TransactionCommandContext: 531619840 total in 74 blocks; 6872 free (75 chunks); 531612968 used
PlanExprContext: 8192 total in 1 blocks; 8120 free (0 chunks); 72 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
AggExprContext2: 8192 total in 1 blocks; 8008 free (3 chunks); 184 used
AggExprContext1: 8192 total in 1 blocks; 8008 free (3 chunks); 184 used
PlanExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
QueryContext: 57344 total in 3 blocks; 17144 free (2 chunks); 40200 used
DeferredTriggerSession: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalMemory: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
CacheMemoryContext: 2088960 total in 8 blocks; 1003504 free (1 chunks); 1085456 used
object_devid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
object_oid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
object_obid_dton_dtoff: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
inlog_servid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
inlog_state: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
inlog_dttm_servid: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
inlog_oid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_type_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
person_pkey: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
person_obid: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_index_indrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
ttprefixes_pkey: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_tgconstrrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_tgconstrname_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_constraint_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_depend_reference_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_depend_depender_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_attrdef_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_user: 5120 total in 5 blocks; 420 free (0 chunks); 4700 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_group_sysid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_cast_source_target_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_class_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_conversion_default_index: 2076 total in 2 blocks; 700 free (0 chunks); 1376 used
pg_conversion_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_namespace_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_language_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_opclass_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_shadow_usename_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_operator_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_proc_oid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_amop_opc_opr_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_group_name_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_language_name_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_proc_proname_args_nsp_index: 2076 total in 2 blocks; 700 free (0 chunks); 1376 used
pg_operator_oprname_l_r_n_index: 2076 total in 2 blocks; 700 free (0 chunks); 1376 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
MdSmgr: 8192 total in 1 blocks; 6116 free (0 chunks); 2076 used
DynaHash: 8192 total in 1 blocks; 6668 free (0 chunks); 1524 used
DynaHashTable: 8192 total in 1 blocks; 5068 free (0 chunks); 3124 used
DynaHashTable: 8192 total in 1 blocks; 6104 free (0 chunks); 2088 used
DynaHashTable: 8192 total in 1 blocks; 5068 free (0 chunks); 3124 used
DynaHashTable: 8192 total in 1 blocks; 6104 free (0 chunks); 2088 used
DynaHashTable: 8192 total in 1 blocks; 3496 free (0 chunks); 4696 used
DynaHashTable: 8192 total in 1 blocks; 3496 free (0 chunks); 4696 used
DynaHashTable: 24576 total in 2 blocks; 9112 free (4 chunks); 15464 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
DynaHashTable: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
2003-05-27 16:37:07 ERROR:  Memory exhausted in AllocSetAlloc(68)


isbs=# select count(*) from inlog;
  count
---------
 1227532

isbs=# select count(*) from object;
 count
-------
  1580

isbs=# select relation_size('inlog');
 relation_size
---------------
     114278400          (about 100 Mb)

isbs=# select relation_size('object');
 relation_size
---------------
        532480          (about 500 Kb)


There are some memory-related parameters from postgresql.conf file:

> shared_buffers = 20000
> max_fsm_relations = 100
> max_fsm_pages = 10000
> max_locks_per_transaction = 64
> wal_buffers = 32
>
> sort_mem = 40960
> vacuum_mem = 8192
>
isbs=# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-unknown-freebsd4.7, compiled by GCC 2.95.4



Is this a bug or I really need more physical RAM especially for this query?
Thanks a lot for any comments.



--
  best reagards,
Ruslan A Dautkhanov

Achilleus Mantzios wrote:

> What does swapinfo before/after the select show??

Before:
bash-2.05b# swapinfo
Device          1K-blocks     Used    Avail Capacity  Type
/dev/ar0s1b       1048448      296  1048152     0%    Interleaved

After:
bash-2.05b# swapinfo
Device          1K-blocks     Used    Avail Capacity  Type
/dev/ar0s1b       1048448      296  1048152     0%    Interleaved

>
>
> Also what does vmstat show during the execution of the query?

I run it twice dureing query execution:
1. bash-2.05b# vmstat
 procs      memory      page                    disks     faults      cpu
 r b w     avm    fre  flt  re  pi  po  fr  sr ad4 ad6   in   sy  cs us sy id
 2 0 0  254024 312564   82   0   0   0  49  12   0   0  659  231  46  2  0 98

2. bash-2.05b# vmstat
 procs      memory      page                    disks     faults      cpu
 r b w     avm    fre  flt  re  pi  po  fr  sr ad4 ad6   in   sy  cs us sy id
 2 0 0  500124  60492   82   0   0   0  49  12   0   0  659  231  46  2  0 98

>
>
> Furthermore is the join near a cartesian product??
> (In that case the output relation whould be rather big)

    Sorry, but I don't understand what "join near a cartesian product" mean, but
    output is only _one_ row since query return only aggregates:

        SELECT count(i.*),sum(i.param1),sum(60-i.param1)

>
>
> Im curious since i run freebsd myself.

    Our company use FreeBSD for many years (and 4.7 version tested very well).
    I think it's not FreeBSD problem, but PostgreSQL. We use dedicated server for
    PostgreSQL, so I don't understand why 1Gb of physical RAM exhausted for this simple query...



--
 best regards,
Ruslan A Dautkhanov  rusland@scn.ru

Ruslan A Dautkhanov <rusland@scn.ru> writes:
> isbs=# SELECT count(i.*),sum(i.param1),sum(60-i.param1) FROM inlog i,
                ^^^^^^^^^^

Make that just count(*).  I think you're running into the poor handling
of whole-row references.

            regards, tom lane

Tom Lane wrote:

> Ruslan A Dautkhanov <rusland@scn.ru> writes:
> > isbs=# SELECT count(i.*),sum(i.param1),sum(60-i.param1) FROM inlog i,
>                 ^^^^^^^^^^
>
> Make that just count(*).  I think you're running into the poor handling
> of whole-row references.

Yes, after changing COUNT(i.*) for COUNT(*) the problem dissappear.
Thanks for advance, but I don't understand difference between this two
examples... :-|


--
 best regards,
Ruslan A Dautkhanov  rusland@scn.ru