Re: out of memory during query execution - Mailing list pgsql-general

Hello,

Thank you for all the answers I've got on this problem.

Tom, I've checked this morning if the logfile contains lines like
 %s: %ld total in %ld blocks; %ld free (%ld chunks); %ld used
and it is the case.

As I launch a series of queries, I hope that the following lines are the
exact lines for the query which has finished with an "out of memory" :
TopMemoryContext: 40960 total in 4 blocks; 11248 free (9 chunks); 29712 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks);
6328 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336
used
MessageContext: 253952 total in 5 blocks; 7784 free (8 chunks); 246168 used
smgr relation table: 8192 total in 1 blocks; 2872 free (0 chunks); 5320 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: 1024 total in 1 blocks; 880 free (0 chunks); 144 used
ExecutorState: 21586616 total in 25 blocks; 14624 free (33 chunks);
21571992 used
HashTableContext: 8192 total in 1 blocks; 8128 free (2 chunks); 64 used
HashBatchContext: 134152352 total in 22 blocks; 14859064 free (55434
chunks); 119293288 used
HashTableContext: 8192 total in 1 blocks; 8128 free (0 chunks); 64 used
HashBatchContext: 108527800 total in 16 blocks; 1016512 free (24
chunks); 107511288 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: 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; 8128 free (0 chunks); 64 used
AggContext: 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
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used
CacheMemoryContext: 516096 total in 6 blocks; 108608 free (0 chunks);
407488 used
i_o_orderkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
i_ps_partkey_suppkey: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
i_l_orderkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_index_indrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_type_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_proc_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_operator_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 256 free (0
chunks); 768 used
pg_namespace_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_namespace_nspname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632
used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_authid_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_authid_rolname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_database_datname_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks);
696 used
pg_class_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used
pg_cast_source_target_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 328 free (0
chunks); 696 used
pg_amproc_opc_proc_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_amop_opc_strat_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks);
632 used
MdSmgr: 24576 total in 2 blocks; 9712 free (1 chunks); 14864 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0
chunks); 4280 used
Timezones: 48408 total in 2 blocks; 5968 free (0 chunks); 42440 used
ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ERROR:  out of memory
DETAIL:  Failed on request of size 148.

I hope this will answer to your question.
Thank you for your help.

Regards,
Alexandra DANTE

Tom Lane a écrit :

>Martijn van Oosterhout <kleptog@svana.org> writes:
>
>
>>On Tue, Dec 20, 2005 at 01:35:03PM +0100, DANTE ALEXANDRA wrote:
>>
>>
>>>You will find below the explain plan of one of the queries which has
>>>finished with "out of memory". This query contains aggregate and a
>>>sub-select with 6 joins :
>>>
>>>
>
>
>
>>1. Firstly, it could be the Hash node. Does the estimated number of
>>matches in part (96000 rows) match reality?
>>
>>
>
>Actually, the hash on "supplier" (3000000 rows) looks like a bigger
>risk.  But if this is 8.1 then there is code in there to spill oversize
>hash tables to disk, so I don't understand where the memory is going.
>
>The "out of memory" failure should have provoked a MemoryContextStats
>report in the postmaster log.  Are there a bunch of lines like
>    %s: %ld total in %ld blocks; %ld free (%ld chunks); %ld used
>and if so could we see 'em?
>
>            regards, tom lane
>
>
>