Обсуждение: Crash in PostgreSQL-8.2.4 while executing query
Hi,
When I run the following query Postmaster crashes. For your reference I have attached information regarding the query (query plan, table , index, server log).
My observation is that when Postmaster starts taking lot of memory because of which system runs out of memory and OS kills the process. System specs are as: SUSE 10.1, 2GB RAM , P- IV.
Thanks in advance,
Rupesh Bajaj
QUERY
select * from semtable as t1, semtable as t2 where exists (select multilingual.english_hopi_lin.element from multilingual.english_hopi_lin, multilingual.english_hopi_lout, multilingual.crosslink where multilingual.english_hopi_lin.node = t1.scategory and multilingual.english_hopi_lout.node = multilingual.crosslink.equivalent and multilingual.english_hopi_lout.element = multilingual.english_hopi_lin.element and multilingual.crosslink.word = t2.scategory) or exists (select multilingual.hindi_hopi_lin.element from multilingual.hindi_hopi_lin, multilingual.hindi_hopi_lout, multilingual.crosslink where multilingual.hindi_hopi_lin.node = t1.scategory and multilingual.hindi_hopi_lout.node = multilingual.crosslink.equivalent and multilingual.hindi_hopi_lout.element = multilingual.hindi_hopi_lin.element and multilingual.crosslink.word = t2.scategory);
TABLES
semtable (stitle varchar, sauthor varchar, scategory varchar) having 100,000 tuples.
English_hopi_lin (node varchar, element varchar) having 196533 tuples.
English_hopi_lout (node varchar, element varchar) having 154837 tuples.
Crosslink (word varchar, equivalent varchar, lang1 varchar, lang2 varchar) having 302269 tuples.
Hindi_hopi_lin (node varchar, element varchar) having 61497 tuples.
Hindi_hopi_lout (node varchar, element varchar) having 17414 tuples.
INDEX
e_lin_node on English_hopi_lin(node);
e_lout_node on English_hopi_lout(node);
c_w on crosslink(word);
c_q on crosslink(equivalent);
h_lin_node on Hindi_hopi_lin (node);
h_lout_node on Hindi_hopi_lout (node);
Query Plan:
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1576180832775.25 rows=4800000000 width=144)
Join Filter: ((subplan) OR (subplan))
-> Seq Scan on semtable t1 (cost=0.00..1888.00 rows=80000 width=72)
-> Seq Scan on semtable t2 (cost= 0.00..1888.00 rows=80000 width=72)
SubPlan
-> Hash Join (cost=8.49..36.62 rows=1 width=21)
Hash Cond: ((hindi_hopi_lout.node)::text = (crosslink.equivalent)::text)
-> Nested Loop (cost= 0.00..28.06 rows=6 width=44)
-> Index Scan using h_lin_node on hindi_hopi_lin (cost=0.00..8.30 rows=2 width=21)
Index Cond: ((node)::text = ($0)::text)
-> Index Scan using h_lout_element on hindi_hopi_lout (cost= 0.00..9.86 rows=2 width=44)
Index Cond: ((hindi_hopi_lout.element)::text = (hindi_hopi_lin.element)::text)
-> Hash (cost=8.42..8.42 rows=6 width=24)
-> Index Scan using c_w on crosslink (cost= 0.00..8.42 rows=6 width=24)
Index Cond: ((word)::text = ($1)::text)
-> Nested Loop (cost=0.00..209.62 rows=1 width=14)
Join Filter: ((english_hopi_lout.element)::text = (english_hopi_lin.element)::text)
-> Nested Loop (cost=0.00..201.63 rows=14 width=15)
-> Index Scan using c_w on crosslink (cost=0.00..8.42 rows=6 width=24)
Index Cond: ((word)::text = ($1)::text)
-> Index Scan using e_lout_node on english_hopi_lout (cost=0.00..31.86 rows=27 width=29)
Index Cond: ((english_hopi_lout.node)::text = (crosslink.equivalent)::text)
-> Index Scan using e_lin_node on english_hopi_lin (cost= 0.00..0.48 rows=7 width=14)
Index Cond: ((node)::text = ($0)::text)
(24 rows)
SERVER LOG
LOG: background writer process (PID 5808) was terminated by signal 9
LOG: terminating any other active server processes
LOG: statistics collector process (PID 5809) was terminated by signal 9
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2007-07-07 01:24:27 IST
LOG: checkpoint record is at 0/6DB4E9C
LOG: redo record is at 0/6DB4E9C; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 0/891; next OID: 24576
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in progress
LOG: record with zero length at 0/6DB4EE4
LOG: redo is not required
LOG: database system is ready
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
When I run the following query Postmaster crashes. For your reference I have attached information regarding the query (query plan, table , index, server log).
My observation is that when Postmaster starts taking lot of memory because of which system runs out of memory and OS kills the process. System specs are as: SUSE 10.1, 2GB RAM , P- IV.
Thanks in advance,
Rupesh Bajaj
QUERY
select * from semtable as t1, semtable as t2 where exists (select multilingual.english_hopi_lin.element from multilingual.english_hopi_lin, multilingual.english_hopi_lout, multilingual.crosslink where multilingual.english_hopi_lin.node = t1.scategory and multilingual.english_hopi_lout.node = multilingual.crosslink.equivalent and multilingual.english_hopi_lout.element = multilingual.english_hopi_lin.element and multilingual.crosslink.word = t2.scategory) or exists (select multilingual.hindi_hopi_lin.element from multilingual.hindi_hopi_lin, multilingual.hindi_hopi_lout, multilingual.crosslink where multilingual.hindi_hopi_lin.node = t1.scategory and multilingual.hindi_hopi_lout.node = multilingual.crosslink.equivalent and multilingual.hindi_hopi_lout.element = multilingual.hindi_hopi_lin.element and multilingual.crosslink.word = t2.scategory);
TABLES
semtable (stitle varchar, sauthor varchar, scategory varchar) having 100,000 tuples.
English_hopi_lin (node varchar, element varchar) having 196533 tuples.
English_hopi_lout (node varchar, element varchar) having 154837 tuples.
Crosslink (word varchar, equivalent varchar, lang1 varchar, lang2 varchar) having 302269 tuples.
Hindi_hopi_lin (node varchar, element varchar) having 61497 tuples.
Hindi_hopi_lout (node varchar, element varchar) having 17414 tuples.
INDEX
e_lin_node on English_hopi_lin(node);
e_lout_node on English_hopi_lout(node);
c_w on crosslink(word);
c_q on crosslink(equivalent);
h_lin_node on Hindi_hopi_lin (node);
h_lout_node on Hindi_hopi_lout (node);
Query Plan:
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..1576180832775.25 rows=4800000000 width=144)
Join Filter: ((subplan) OR (subplan))
-> Seq Scan on semtable t1 (cost=0.00..1888.00 rows=80000 width=72)
-> Seq Scan on semtable t2 (cost= 0.00..1888.00 rows=80000 width=72)
SubPlan
-> Hash Join (cost=8.49..36.62 rows=1 width=21)
Hash Cond: ((hindi_hopi_lout.node)::text = (crosslink.equivalent)::text)
-> Nested Loop (cost= 0.00..28.06 rows=6 width=44)
-> Index Scan using h_lin_node on hindi_hopi_lin (cost=0.00..8.30 rows=2 width=21)
Index Cond: ((node)::text = ($0)::text)
-> Index Scan using h_lout_element on hindi_hopi_lout (cost= 0.00..9.86 rows=2 width=44)
Index Cond: ((hindi_hopi_lout.element)::text = (hindi_hopi_lin.element)::text)
-> Hash (cost=8.42..8.42 rows=6 width=24)
-> Index Scan using c_w on crosslink (cost= 0.00..8.42 rows=6 width=24)
Index Cond: ((word)::text = ($1)::text)
-> Nested Loop (cost=0.00..209.62 rows=1 width=14)
Join Filter: ((english_hopi_lout.element)::text = (english_hopi_lin.element)::text)
-> Nested Loop (cost=0.00..201.63 rows=14 width=15)
-> Index Scan using c_w on crosslink (cost=0.00..8.42 rows=6 width=24)
Index Cond: ((word)::text = ($1)::text)
-> Index Scan using e_lout_node on english_hopi_lout (cost=0.00..31.86 rows=27 width=29)
Index Cond: ((english_hopi_lout.node)::text = (crosslink.equivalent)::text)
-> Index Scan using e_lin_node on english_hopi_lin (cost= 0.00..0.48 rows=7 width=14)
Index Cond: ((node)::text = ($0)::text)
(24 rows)
SERVER LOG
LOG: background writer process (PID 5808) was terminated by signal 9
LOG: terminating any other active server processes
LOG: statistics collector process (PID 5809) was terminated by signal 9
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2007-07-07 01:24:27 IST
LOG: checkpoint record is at 0/6DB4E9C
LOG: redo record is at 0/6DB4E9C; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 0/891; next OID: 24576
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system was not properly shut down; automatic recovery in progress
LOG: record with zero length at 0/6DB4EE4
LOG: redo is not required
LOG: database system is ready
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
"rupesh bajaj" <rupesh.bajaj@gmail.com> writes:
> When I run the following query Postmaster crashes.
> ...
> LOG: background writer process (PID 5808) was terminated by signal 9
> LOG: terminating any other active server processes
> LOG: statistics collector process (PID 5809) was terminated by signal 9
Signal 9 is not Postgres' fault. That means the kernel OOM killer has
decided to kill these processes as a result of a system-wide
out-of-memory condition. (Given that the bgwriter never gets very
large, these seem to be particularly misdirected OOM kills, but the
entire "feature" is pretty broken anyway.)
The planner seems to think that the query will return 4.8 billion rows;
does that have anything to do with reality? If so, what's probably
happening is that the client side is running out of memory to buffer the
result, and the kernel is killing some other process instead of the one
that actually ate all the memory. (Not an unusual behavior at all for
the OOM killer.)
Suggestions:
1. Turn off memory overcommit in your kernel settings.
2. Reconsider whether this query is what you actually wanted.
3. If it is, use a cursor to fetch the result in segments.
regards, tom lane
On 7/7/07, rupesh bajaj <rupesh.bajaj@gmail.com> wrote: > Hi, > When I run the following query Postmaster crashes. For your reference I have > attached information regarding the query (query plan, table , index, server > log). > My observation is that when Postmaster starts taking lot of memory because > of which system runs out of memory and OS kills the process. System specs > are as: SUSE 10.1, 2GB RAM , P- IV. Formatting your queries makes it much easier for people reading it to try and help you. This query looks like an unconstrained join. Judging by the query plan that explain returns, I'd sau that is the case. What, exactly, are you trying to do here? I'd think that a union of two queries with those where exists clauses as where clauses and a proper join on the semtable might work beyyer.