pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe out of memory error with large insert
3+ messages / 2 participants
[nested] [flat]
* Re out of memory error with large insert
@ 2006-03-22 00:16 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sriram Dandapani @ 2006-03-22 00:16 UTC (permalink / raw)
To: pgsql-admin
-----Original Message-----
From: Sriram Dandapani
Sent: Tuesday, March 21, 2006 4:01 PM
To: 'Tom Lane'
Subject: RE: [ADMIN] out of memory error with large insert
Upon monitoring with top, the insert statement process keeps eating up
memory until there is nothing left on the linux box
Why doesn't Postgres use swap in this situation
Can we control this behavior thru postgresconf.sql
-----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
* Re: Re out of memory error with large insert
@ 2006-03-22 03:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-03-22 03:24 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> Why doesn't Postgres use swap in this situation
Perhaps you need to adjust the ulimit settings that the postmaster is
started under.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Re out of memory error with large insert
@ 2006-03-23 19:16 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 3+ messages in thread
From: Sriram Dandapani @ 2006-03-23 19:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
What are the recommended ulimit settings. A single postgres connection
that does an insert(with no other database activity) of say 6 million
rows into a 15 column table (all integer columns) cannot complete due to
an out of memory error
This happens on a 4Gb Linux box with shmmax set to 1gb and about 50000
shared_buffers
I would really appreciate any help ...
Thanks
Sriram
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, March 21, 2006 7:24 PM
To: Sriram Dandapani
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Re out of memory error with large insert
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> Why doesn't Postgres use swap in this situation
Perhaps you need to adjust the ulimit settings that the postmaster is
started under.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-03-23 19:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-22 00:16 Re out of memory error with large insert Sriram Dandapani <sdandapani@counterpane.com>
2006-03-22 03:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-23 19:16 Re: 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