Hello again,
Sorry for the deal with my answer it was realy hectic week so I
couldn't even check my mail.
2007/10/3, Richard Huxton <dev@xxxxxxxxxxxx>:
> Sergey Konoplev wrote:
> >> Don't forget to cc: the list.
> >> Try not to top-post replies, it's easier to read if you reply below the
> >> text you're replying to.
> >
> > Thanx for your advice. I'm just absolutely worned out. Sorry.
>
> Know that feeling - let's see if we can't sort this out.
>
> >>>> 1. Is it always the same query?
> >>>> 2. Does the client still think it's connected?
> >>>> 3. Is that query using up CPU, or just idling?
> >>>> 4. Anything odd in pg_locks for the problem pid?
> >>> 1. No it isn't. I have few functions (plpgsql, plpython) that cause
> >>> such situations more often than another but they are called more often
> >>> also.
> >> OK, so there's no real pattern. That would suggest it's not a particular
> >> query-plan that's got something wrong.
> >>
> >> Do you always get this problem inside a function?
> >
> > As far as I remember I do.
>
> Hmm - check Magnus' thoughts on pl/python. Can't comment on Python
> myself. Are you sure it's not always the same few function(s) that cause
> this problem?
Yes I'm shure. I've noticed about 10 queries and procedure calls
buzzing at least.
>
> >>> 2. The client just waits for query and buzz.
> >>> 3. They are using CPU in usual way and their pg_lock activity seems normal.
> >> So the backend that appears "stuck" is still using CPU?
> >
> > Yes but the metter is that this procedures usualy use CPU just a
> > little so I can't find out if there is some oddity or not.
>
> OK, so it's not that it's stuck in a loop wasting a lot of CPU
>
> >> So - the symptoms are:
> [snip]
> > Exactly.
>
> So - we need to solve two mysteries
> 1. Why are these functions not returning?
> 2. Why does SIGINT not interrupt them?
>
> >> Are you happy that your hardware and drivers are OK? There aren't
> >> problems with any other servers on this machine?
> >
> > Yes I'm quite happy. My hardware is: 2 double-core Xeon, 8Gb RAM,
> > RAID5. What about other software... it's dedicated PG server so I have
> > no problem with it.
>
> Well, the places I'd look would be:
> 1. Hardware (you're happy that's fine, and it's not quite the problems
> I'd expect)
> 2. Drivers (same as #1)
> 3. Client connectivity (but you say the client is fine)
> 4. External interactions (see below)
> 5. Bug in PG extension (pl/python)
I think it's not only lp/python problem cos I saw pl/pgsql and simple
queries also. For example:
select * from (
select d.*, cto.full_name, cast(st.name || ', ' || r.name as
varchar) as cityname
from
drivers d
join cars_trailers_owners cto on
d.cars_trailers_owner_id = cto.id
join settles st on
d.settle_id = st.id
join regions r on
st.region_id = r.id
order by sname, name, pname
) as sq
> 6. Bug in PG core code
>
> Do any of your functions interact with the outside world - fetch
> webpages or similar? It could be they're waiting for that. If you're
> using a library that could hang waiting for a response and also block
> SIGINT at the same time that would explain everything.
No, most of them don't.
--
Regards,
Sergey Konoplev
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
