pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
out of memory error with large insert
3+ messages / 2 participants
[nested] [flat]

* out of memory error with large insert
@ 2006-03-21 22:17 Sriram Dandapani <sdandapani@counterpane.com>
  2006-03-21 22:38 ` Re: out of memory error with large insert Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Sriram Dandapani @ 2006-03-21 22:17 UTC (permalink / raw)
  To: pgsql-admin

Hi

 

On a large transaction involving an insert of 8 million rows, after a
while Postgres complains of an out of memory error.

 

Failed on request of size 32

 

I get no other message.

 

Shmmax is set to 1 Gb

Shared_buffers set to 50000

 

Max memory on box is 4Gb..Postgres is the only major application running
other than a jvm with minimal heap.

 

Other than breaking this transaction into smaller bits(which may not be
possible as this scenario is not encountered often), am I missing
something.

 

Sriram

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: out of memory error with large insert
  2006-03-21 22:17 out of memory error with large insert Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-03-21 22:38 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2006-03-21 22:38 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> On a large transaction involving an insert of 8 million rows, after a
> while Postgres complains of an out of memory error.

If there are foreign-key checks involved, try dropping those constraints
and re-creating them afterwards.  Probably faster than retail checks
anyway ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: out of memory error with large insert
@ 2006-03-22 01:03 Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Sriram Dandapani @ 2006-03-22 01:03 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Some more interesting information.

The insert statement is issued with a jdbc callback to the postgres
database (because the application requires partial commits...equivalent
of autonomous transactions)

What I noticed was that the writer process when using the jdbc insert
was very active consuming a lot of memory

When I attempted the same insert within pgadmin manually, the writer
process was not on the top's list of processes.

Wonder if the jdbc callback causes Postgres to allocate memory
differently.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Tuesday, March 21, 2006 2:38 PM
To: Sriram Dandapani
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] out of memory error with large insert 

"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> On a large transaction involving an insert of 8 million rows, after a
> while Postgres complains of an out of memory error.

If there are foreign-key checks involved, try dropping those constraints
and re-creating them afterwards.  Probably faster than retail checks
anyway ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-03-22 01:03 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-21 22:17 out of memory error with large insert Sriram Dandapani <sdandapani@counterpane.com>
2006-03-21 22:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-22 01:03 Re: out of memory error with large insert Sriram Dandapani <sdandapani@counterpane.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox