Re: 0ut of Memory Error during Vacuum Analyze and

Thank for looking into this Tom. Here's the output from PostgreSQL log:

*** Postgresql Log:

TopMemoryContext: 32768 total in 4 blocks; 7232 free (9 chunks); 25536
used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks); 3256
used
TopTransactionContext: 8192 total in 1 blocks; 6816 free (0 chunks);
1376 used
MessageContext: 8192 total in 1 blocks; 7104 free (1 chunks); 1088 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: 1077575324 total in 115158 blocks; 1860896 free
(115146 chunks); 1075714428 used
ExecutorState: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
Relcache by OID: 8192 total in 1 blocks; 3896 free (0 chunks); 4296 used
CacheMemoryContext: 516096 total in 6 blocks; 198480 free (2 chunks);
317616 used
mort_ht: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
pg_depend_depender_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 used
pg_depend_reference_index: 1024 total in 1 blocks; 256 free (0 chunks);
768 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: 8192 total in 1 blocks; 7504 free (0 chunks); 688 used
LockTable (locallock hash): 8192 total in 1 blocks; 3912 free (0
chunks); 4280 used
Timezones: 47592 total in 2 blocks; 5968 free (0 chunks); 41624 used
ErrorContext: 8192 total in 1 blocks; 8176 free (4 chunks); 16 used
[2006-02-23 08:46:26 PST|[local]|mtrac|postgres] ERROR: out of memory
[2006-02-23 08:46:26 PST|[local]|mtrac|postgres] DETAIL: Failed on
request of size 134217728.

-------------------------

*** Stack trace:

I'm not having luck generating a stack trace so far. Following the gdb
instructions, the create index statement never comes back with either
the I/O error or a success (created index). I'm still trying to figure
this out. Hopefully, the above from the server log may shed some light
on the problem.

Thanks again,

----

Husam Tomeh

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, February 14, 2006 3:49 PM
To: Tomeh, Husam
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] 0ut of Memory Error during Vacuum Analyze and
Create Index

"Tomeh, Husam" <htomeh(at)firstam(dot)com> writes:
> mtrac=# show maintenance_work_mem ;
> maintenance_work_mem
> ----------------------
> 1048576 <======
> (1 row)

> mtrac=#
> mtrac=#
> mtrac=# create index mort_ht on mortgage(county_id,mtg_rec_dt);
> ERROR: out of memory
<===
> DETAIL: Failed on request of size 134217728. <===

It would be useful to look at the detailed allocation info that this
(should have) put into the postmaster log. Also, if you could get
a stack trace back from the error, that would be even more useful.
To do that,
* start psql
* determine PID of connected backend (use pg_backend_pid())
* in another window, as postgres user,
gdb /path/to/postgres backend-PID
gdb> break errfinish
gdb> cont
* issue failing command in psql
* when breakpoint is reached,
gdb> bt
... stack trace printed here ...
gdb> q

regards, tom lane
**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

FADLD Tag
**********************************************************************