Обсуждение: Re: 0ut of Memory Error during Vacuum Analyze and

No special data types. The table is pretty large one with over 15GB. The
index is about 1.5 GB. Here's the table structure :

     Column      |         Type          | Modifiers
-----------------+-----------------------+-----------
 county_id       | numeric(5,0)          | not null
 batch_dt        | numeric(8,0)          | not null
 batch_seq       | numeric(5,0)          | not null
 mtg_seq_nbr     | numeric(1,0)          | not null
 mtg_rec_dt      | numeric(8,0)          |
 mtg_doc_nbr     | character varying(12) |
 mtg_rec_bk      | character varying(6)  |
 mtg_rec_pg      | character varying(6)  |
 mtg_amt         | numeric(11,0)         |
 lndr_cd         | character varying(10) |
 lndr_nm         | character varying(30) |
 mtg_assm_ind    | character(1)          |
 mtg_typ         | character varying(5)  |
 adj_rate_ind    | character(1)          |
 mtg_term_nbr    | numeric(5,0)          |
 mtg_term_cd     | character varying(4)  |
 mtg_due_dt      | numeric(8,0)          |
 mtg_deed_typ    | character varying(6)  |
 reverse_mtg_ind | character(1)          |
 refi_ind        | character(1)          |
 conform_ind     | character(1)          |
 cnstr_ln_ind    | character(1)          |
 title_co_cd     | character varying(5)  |
 state_id        | numeric(5,0)          |
 msa             | numeric(4,0)          |
Indexes:
    "uq_mortgage" UNIQUE, btree (county_id, batch_dt, batch_seq,
mtg_seq_nbr)
    "mortgage_idxc_county_id_mtg_rec_dt" btree (county_id, mtg_rec_dt)
    "mortgage_idxc_state_id_mtg_rec_dt" btree (state_id, mtg_rec_dt)

---------

 Here's the test I did with maintenance_work_mem = 1GB:


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.               <===

............ Then I changed the parameter to 512 MB:


mtrac=# show maintenance_work_mem ;
 maintenance_work_mem
----------------------
 524288         <===
(1 row)

mtrac=#  create index mort_ht_512 on mortgage(county_id,mtg_rec_dt);
CREATE INDEX
-----------------------------------------------



Regards,
----

    Husam

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

"Tomeh, Husam" <htomeh@firstam.com> writes:
> I have run pg_dump and had no errors. I also got this error when
> creating one index but not another. When I lowered my
> maintenance_work_mem, the create index succeeded.

Create index too?  Hm.  That begins to sound more like a memory leak.
Do you have any custom data types or anything like that in this
table?  Can you put together a self-contained test case using dummy
data?

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

Thank you.

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

"Tomeh, Husam" <htomeh@firstam.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