Thread: ERROR: Memory exhausted in AllocSetAlloc(188)
Dear Postgresql gurus, I have a problem (7.3.1 on linux) with a query eating all my memory. First it take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory issues, could my problem solved by upgrading to 7.3.2? Thanks a lot, description follows Tilo (the query involves a table with 33925848 rows, but only a few thousand rows should be returned) tschwarz=# explain select * from feature_point_delta_avg where sequence_id =325058; QUERY PLAN -------------------------------------------------------------------------------- -------------------------- Subquery Scan feature_point_delta_avg (cost=1541301.27..1551163.80 rows=43833 width=28) -> Aggregate (cost=1541301.27..1551163.80 rows=43833 width=28) -> Group (cost=1541301.27..1544588.78 rows=438334 width=28) -> Sort (cost=1541301.27..1542397.11 rows=438334 width=28) Sort Key: trace.sequence_id, trace.trace_id -> Hash Join (cost=3424.87..1488310.69 rows=438334 width=28) Hash Cond: ("outer".image_id = "inner".image_id) -> Hash Join (cost=2797.82..1383310.62 rows=13185846 width=20) Hash Cond: ("outer".trace_id = "inner".trace_id) -> Seq Scan on d_kalman (cost=0.00..1046060.48 rows=33925848 width=12) -> Hash (cost=2782.76..2782.76 rows=6025 width=8) -> Seq Scan on trace (cost=0.00..2782.76 rows=6025 width=8) Filter: (sequence_id = 325058) -> Hash (cost=624.26..624.26 rows=1111 width=8) -> Seq Scan on label_data ld (cost=0.00..624.26 rows=1111 width=8) Filter: ((right_eye IS NULL) AND (visible_features(ld.*) > 0)) (16 rows) Time: 80.24 ms tschwarz=# select * from feature_point_delta_avg where sequence_id =325058; [... waiting for ca. 10 minutes ...] ERROR: Memory exhausted in AllocSetAlloc(188) tschwarz=# show sort_mem ; sort_mem ---------- 16384 (1 row)
On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote: > Dear Postgresql gurus, > > I have a problem (7.3.1 on linux) with a query eating all my memory. First it > take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory > issues, could my problem solved by upgrading to 7.3.2? > > Thanks a lot, description follows > > Tilo > > (the query involves a table with 33925848 rows, but only a few thousand rows > should be returned) Please send the definition of the view and of your tables. I couldn't immediatly puzzle out your reply email address so you'll just have to read this in the archive. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
On Sat, May 17, 2003 at 01:29:10PM +1000, Martijn van Oosterhout wrote: > On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote: > > Dear Postgresql gurus, > > > > I have a problem (7.3.1 on linux) with a query eating all my memory. First it > > take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory > > issues, could my problem solved by upgrading to 7.3.2? > > > > Thanks a lot, description follows > > > > Tilo > > > > (the query involves a table with 33925848 rows, but only a few thousand rows > > should be returned) > I'm having the same problem... INSERT INTO zip4 (carrt_id , add_on_low , add_on_high) SELECT cr.carrt_id , to_number( CASE WHEN coalesce(zip_add_on_low_no, zip_add_on_high_no) LIKE '%ND' THEN '-1' ELSE coalesce(zip_add_on_low_no, zip_add_on_high_no) END , '0') , to_number( CASE WHEN zip_add_on_high_no LIKE '%ND' THEN '-1' ELSE zip_add_on_high_no END , '0') FROM zip_carrt zc, postal_code pc, carrt cr WHERE pc.postal_code = zc.zip_code AND cr.postal_code_id = pc.postal_code_id AND cr.car_rt_code = zc.carrier_route_id ; ERROR: Memory exhausted in AllocSetAlloc(108) usps=# \d zip_carrt Table "public.zip_carrt" Column | Type | Modifiers --------------------+----------------------+----------- zip_code | character varying(5) | carrier_route_id | character varying(4) | zip_add_on_low_no | character varying(4) | zip_add_on_high_no | character varying(4) | usps=# \d postal_code Table "public.postal_code" Column | Type | Modifiers ----------------+-----------------------+------------------------------------------------------------------------- postal_code_id | integer | not null default nextval('public.postal_code_postal_code_id_seq'::text) postal_code | character varying(10) | not null state_code | character(2) | Indexes: postal_code_pkey primary key btree (postal_code_id), postal_code_postal_code_key unique btree (postal_code) Foreign Key constraints: $1 FOREIGN KEY (state_code) REFERENCES state(state_code) ON UPDATE NO ACTION ON DELETE NO ACTION usps=# \d carrt Table "public.carrt" Column | Type | Modifiers ----------------+----------------------+------------------------------------------------------------- carrt_id | integer | not null default nextval('public.carrt_carrt_id_seq'::text) postal_code_id | integer | not null car_rt_code | character varying(5) | not null Indexes: carrt_pkey primary key btree (carrt_id), carrt_postal_code_id_key unique btree (postal_code_id, car_rt_code) Foreign Key constraints: $1 FOREIGN KEY (postal_code_id) REFERENCES postal_code(postal_code_id) ON UPDATE NO ACTION ON DELETENO ACTION usps=# select count(*) from postal_code; count ------- 42678 (1 row) usps=# select count(*) from carrt; count -------- 627814 zip_carrt is ~35M rows, and zip4 is empty. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
I happened to catch pgsql is the act: 14692 jnasby 1 10 0 1471M 738M cpu3 52:18 48.02% postgres Seems like there's definetly some kind of memory leak. shared_buffers = 5000 # min max_connections*1 or 16, 8KB each #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 64 # min 10 wal_buffers = 10 sort_mem = 30000 # min 64, size in KB vacuum_mem = 16000 # min 1024, size in KB effective_cache_size = 100000 # typically 8KB each This is on a sun box with 1G of memory. Also, forgot to describe the table I'm inserting into: Table "public.zip4" Column | Type | Modifiers -------------+----------+----------------------------------------------------------- zip4_id | integer | not null default nextval('public.zip4_zip4_id_seq'::text) carrt_id | integer | not null add_on_low | smallint | not null add_on_high | smallint | not null Indexes: zip4_pkey primary key btree (zip4_id), zip4_carrt_id_key unique btree (carrt_id, add_on_low, add_on_high) Check constraints: "zip4_add_on_low" ((add_on_low >= -1) AND (add_on_low <= 9999)) "zip4_add_on_high" ((add_on_high >= -1) AND (add_on_high <= 9999)) "$1" (add_on_low <= add_on_high) Foreign Key constraints: $2 FOREIGN KEY (carrt_id) REFERENCES carrt(carrt_id) ON UPDATE NO ACTION ON DELETE NO ACTION On Mon, May 19, 2003 at 02:15:17PM -0500, Jim C. Nasby wrote: > On Sat, May 17, 2003 at 01:29:10PM +1000, Martijn van Oosterhout wrote: > > On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote: > > > Dear Postgresql gurus, > > > > > > I have a problem (7.3.1 on linux) with a query eating all my memory. First it > > > take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory > > > issues, could my problem solved by upgrading to 7.3.2? > > > > > > Thanks a lot, description follows > > > > > > Tilo > > > > > > (the query involves a table with 33925848 rows, but only a few thousand rows > > > should be returned) > > > > I'm having the same problem... > > INSERT INTO zip4 (carrt_id > , add_on_low > , add_on_high) > SELECT cr.carrt_id > , to_number( > CASE WHEN coalesce(zip_add_on_low_no, zip_add_on_high_no) LIKE '%ND' > THEN '-1' > ELSE coalesce(zip_add_on_low_no, zip_add_on_high_no) > END > , '0') > , to_number( > CASE WHEN zip_add_on_high_no LIKE '%ND' > THEN '-1' > ELSE zip_add_on_high_no > END > , '0') > FROM zip_carrt zc, postal_code pc, carrt cr > WHERE pc.postal_code = zc.zip_code > AND cr.postal_code_id = pc.postal_code_id > AND cr.car_rt_code = zc.carrier_route_id > ; > ERROR: Memory exhausted in AllocSetAlloc(108) > > usps=# \d zip_carrt > Table "public.zip_carrt" > Column | Type | Modifiers > --------------------+----------------------+----------- > zip_code | character varying(5) | > carrier_route_id | character varying(4) | > zip_add_on_low_no | character varying(4) | > zip_add_on_high_no | character varying(4) | > > usps=# \d postal_code > Table "public.postal_code" > Column | Type | > Modifiers > ----------------+-----------------------+------------------------------------------------------------------------- > postal_code_id | integer | not null default > nextval('public.postal_code_postal_code_id_seq'::text) > postal_code | character varying(10) | not null > state_code | character(2) | > Indexes: postal_code_pkey primary key btree (postal_code_id), > postal_code_postal_code_key unique btree (postal_code) > Foreign Key constraints: $1 FOREIGN KEY (state_code) REFERENCES state(state_code) ON UPDATE NO ACTION ON DELETE NO ACTION > > usps=# \d carrt > Table "public.carrt" > Column | Type | > Modifiers > ----------------+----------------------+------------------------------------------------------------- > carrt_id | integer | not null default > nextval('public.carrt_carrt_id_seq'::text) > postal_code_id | integer | not null > car_rt_code | character varying(5) | not null > Indexes: carrt_pkey primary key btree (carrt_id), > carrt_postal_code_id_key unique btree (postal_code_id, > car_rt_code) > Foreign Key constraints: $1 FOREIGN KEY (postal_code_id) REFERENCES postal_code(postal_code_id) ON UPDATE NO ACTION ONDELETE NO ACTION > > usps=# select count(*) from postal_code; > count > ------- > 42678 > (1 row) > > usps=# select count(*) from carrt; > count > -------- > 627814 > > zip_carrt is ~35M rows, and zip4 is empty. > -- > Jim C. Nasby (aka Decibel!) jim@nasby.net > Member: Triangle Fraternity, Sports Car Club of America > Give your computer some brain candy! www.distributed.net Team #1828 > > Windows: "Where do you want to go today?" > Linux: "Where do you want to go tomorrow?" > FreeBSD: "Are you guys coming, or what?" -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Are you also using 7.3.1? Jim C. Nasby wrote: > I happened to catch pgsql is the act: > 14692 jnasby 1 10 0 1471M 738M cpu3 52:18 48.02% postgres > > Seems like there's definetly some kind of memory leak. > > shared_buffers = 5000 # min max_connections*1 or 16, 8KB each > #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes > #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes > #max_locks_per_transaction = 64 # min 10 > wal_buffers = 10 > sort_mem = 30000 # min 64, size in KB > vacuum_mem = 16000 # min 1024, size in KB > effective_cache_size = 100000 # typically 8KB each > > This is on a sun box with 1G of memory. Also, forgot to describe the > table I'm inserting into: > > Table "public.zip4" > Column | Type | Modifiers > -------------+----------+----------------------------------------------------------- > zip4_id | integer | not null default > nextval('public.zip4_zip4_id_seq'::text) > carrt_id | integer | not null > add_on_low | smallint | not null > add_on_high | smallint | not null > Indexes: zip4_pkey primary key btree (zip4_id), > zip4_carrt_id_key unique btree (carrt_id, add_on_low, add_on_high) > Check constraints: "zip4_add_on_low" ((add_on_low >= -1) AND (add_on_low <= 9999)) > "zip4_add_on_high" ((add_on_high >= -1) AND (add_on_high <= 9999)) > "$1" (add_on_low <= add_on_high) > Foreign Key constraints: $2 FOREIGN KEY (carrt_id) REFERENCES carrt(carrt_id) ON UPDATE NO ACTION ON DELETE NO ACTION > > On Mon, May 19, 2003 at 02:15:17PM -0500, Jim C. Nasby wrote: > >>On Sat, May 17, 2003 at 01:29:10PM +1000, Martijn van Oosterhout wrote: >> >>>On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote: >>> >>>>Dear Postgresql gurus, >>>> >>>>I have a problem (7.3.1 on linux) with a query eating all my memory. First it >>>>take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory >>>>issues, could my problem solved by upgrading to 7.3.2? >>>> >>>>Thanks a lot, description follows >>>> >>>> Tilo >>>> >>>>(the query involves a table with 33925848 rows, but only a few thousand rows >>>>should be returned) >>> >>I'm having the same problem... >> >>INSERT INTO zip4 (carrt_id >> , add_on_low >> , add_on_high) >> SELECT cr.carrt_id >> , to_number( >> CASE WHEN coalesce(zip_add_on_low_no, zip_add_on_high_no) LIKE '%ND' >> THEN '-1' >> ELSE coalesce(zip_add_on_low_no, zip_add_on_high_no) >> END >> , '0') >> , to_number( >> CASE WHEN zip_add_on_high_no LIKE '%ND' >> THEN '-1' >> ELSE zip_add_on_high_no >> END >> , '0') >> FROM zip_carrt zc, postal_code pc, carrt cr >> WHERE pc.postal_code = zc.zip_code >> AND cr.postal_code_id = pc.postal_code_id >> AND cr.car_rt_code = zc.carrier_route_id >>; >>ERROR: Memory exhausted in AllocSetAlloc(108) >> >>usps=# \d zip_carrt >> Table "public.zip_carrt" >> Column | Type | Modifiers >>--------------------+----------------------+----------- >> zip_code | character varying(5) | >> carrier_route_id | character varying(4) | >> zip_add_on_low_no | character varying(4) | >> zip_add_on_high_no | character varying(4) | >> >>usps=# \d postal_code >> Table "public.postal_code" >> Column | Type | >>Modifiers >>----------------+-----------------------+------------------------------------------------------------------------- >> postal_code_id | integer | not null default >>nextval('public.postal_code_postal_code_id_seq'::text) >> postal_code | character varying(10) | not null >> state_code | character(2) | >>Indexes: postal_code_pkey primary key btree (postal_code_id), >> postal_code_postal_code_key unique btree (postal_code) >>Foreign Key constraints: $1 FOREIGN KEY (state_code) REFERENCES state(state_code) ON UPDATE NO ACTION ON DELETE NO ACTION >> >>usps=# \d carrt >> Table "public.carrt" >> Column | Type | >>Modifiers >>----------------+----------------------+------------------------------------------------------------- >> carrt_id | integer | not null default >>nextval('public.carrt_carrt_id_seq'::text) >> postal_code_id | integer | not null >> car_rt_code | character varying(5) | not null >>Indexes: carrt_pkey primary key btree (carrt_id), >> carrt_postal_code_id_key unique btree (postal_code_id, >>car_rt_code) >>Foreign Key constraints: $1 FOREIGN KEY (postal_code_id) REFERENCES postal_code(postal_code_id) ON UPDATE NO ACTION ONDELETE NO ACTION >> >>usps=# select count(*) from postal_code; >> count >>------- >> 42678 >>(1 row) >> >>usps=# select count(*) from carrt; >> count >>-------- >> 627814 >> >>zip_carrt is ~35M rows, and zip4 is empty. >>-- >>Jim C. Nasby (aka Decibel!) jim@nasby.net >>Member: Triangle Fraternity, Sports Car Club of America >>Give your computer some brain candy! www.distributed.net Team #1828 >> >>Windows: "Where do you want to go today?" >>Linux: "Where do you want to go tomorrow?" >>FreeBSD: "Are you guys coming, or what?" > > -- Joseph Shraibman joseph@xtenit.com Increase signal to noise ratio. http://xis.xtenit.com
On Mon, May 19, 2003 at 08:18:56PM -0400, Joseph Shraibman wrote: > Are you also using 7.3.1? 7.3.2 -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <jim@nasby.net> writes: > I'm having the same problem... How many rows would you expect this command to insert? You might be running into the fact that pending-trigger-call lists are kept in memory, but it's hard to tell without knowing the number of rows involved... regards, tom lane
"Jim C. Nasby" <jim@nasby.net> writes: > On Tue, May 20, 2003 at 12:58:41AM -0400, Tom Lane wrote: >> How many rows would you expect this command to insert? >> >> You might be running into the fact that pending-trigger-call lists are >> kept in memory, but it's hard to tell without knowing the number of >> rows involved... > 35M Each pending deferred-trigger action takes about 40 bytes + palloc overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so that's exactly where your problem is. I'd suggest trying to commit the changes in smaller batches ... regards, tom lane
On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > On Tue, May 20, 2003 at 12:58:41AM -0400, Tom Lane wrote: > >> How many rows would you expect this command to insert? > >> > >> You might be running into the fact that pending-trigger-call lists are > >> kept in memory, but it's hard to tell without knowing the number of > >> rows involved... > > > 35M > > Each pending deferred-trigger action takes about 40 bytes + palloc > overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so > that's exactly where your problem is. I'd suggest trying to commit the > changes in smaller batches ... Ugh... would two triggers double that? Where can I get more info on what's happening under the covers here, especially on what a deferred trigger is? -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Tue, May 20, 2003 at 12:58:41AM -0400, Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > I'm having the same problem... > > How many rows would you expect this command to insert? > > You might be running into the fact that pending-trigger-call lists are > kept in memory, but it's hard to tell without knowing the number of > rows involved... 35M I re-wrote the query to do a subselect instead of a full join (which changed the access plan from a hash-join to a subquery), and it looks like it might finish, but it's still using a heck of a lot of memory... 18252 jnasby 1 40 0 1135M 442M cpu1 290:03 48.07% postgres 19168 jnasby 1 60 0 62M 44M sleep 2:35 8.03% postgres The second engine is typical of the other engines in terms of memory usage. Also, I've seen some engines not releasing memory until termination; there was one that was using 1.6G just sitting at the prompt. If it helps, that snapshot is with ~16M tuples in the table, according to vacuum verbose (started with 0 tuples). -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <jim@nasby.net> writes: > On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote: >> Each pending deferred-trigger action takes about 40 bytes + palloc >> overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so >> that's exactly where your problem is. I'd suggest trying to commit the >> changes in smaller batches ... > Ugh... would two triggers double that? Where can I get more info on > what's happening under the covers here, especially on what a deferred > trigger is? A deferred trigger is an AFTER trigger. If you can do your work in BEFORE triggers, you should. Two triggers firing on the same row action (insert/update/delete) do not double the memory --- there's one trigger queue entry per action. It looks like it costs about 8 more bytes for each additional deferred trigger that needs to be fired on the same row action. Beyond that, read the code --- it's in backend/commands/trigger.c. regards, tom lane
FYI, TODO has: * Add deferred trigger queue file (Jan) so it is a known issue. --------------------------------------------------------------------------- Tom Lane wrote: > "Jim C. Nasby" <jim@nasby.net> writes: > > On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote: > >> Each pending deferred-trigger action takes about 40 bytes + palloc > >> overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so > >> that's exactly where your problem is. I'd suggest trying to commit the > >> changes in smaller batches ... > > > Ugh... would two triggers double that? Where can I get more info on > > what's happening under the covers here, especially on what a deferred > > trigger is? > > A deferred trigger is an AFTER trigger. If you can do your work in > BEFORE triggers, you should. > > Two triggers firing on the same row action (insert/update/delete) do not > double the memory --- there's one trigger queue entry per action. It > looks like it costs about 8 more bytes for each additional deferred > trigger that needs to be fired on the same row action. > > Beyond that, read the code --- it's in backend/commands/trigger.c. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Martijn van Oosterhout writes: > On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote: > > Dear Postgresql gurus, > > > > I have a problem (7.3.1 on linux) with a query eating all my memory. > > First it take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses > > some memory issues, could my problem solved by upgrading to 7.3.2? > > > > Thanks a lot, description follows > > > > Tilo > > > > (the query involves a table with 33925848 rows, but only a few thousand > > rows should be returned) > > Please send the definition of the view and of your tables. To prevent nasty line breaks, I put the definitions in a text file at: http://www.tilo-schwarz.de/OutOfMem.txt In the meantime I read Toms' comments regarding the "pending-trigger-call lists" - does that apply to my problem too (I have just a SELECT)? Thanks a lot! Tilo PS: > I couldn't immediatly puzzle out your reply email address so you'll just > have to read this in the archive. I "encrypted" my mail address (obviously too complicated), because since I posted a few times on this list, I get about ten times the amount of spam than I got before.
Tilo Schwarz <mail@tilo-schwarz.de> writes: > I have a problem (7.3.1 on linux) with a query eating all my memory. > http://www.tilo-schwarz.de/OutOfMem.txt You didn't tell us much about the functions involved in these views, but I suspect the problem has to do with the fact that you're passing whole-row references (foo.*) to the functions. The mechanism for handling whole-row references is horrid --- it leaks memory that isn't reclaimed till end of query. We need to redesign it someday. In the meantime, try passing just the columns you need, explicitly. regards, tom lane