Thread
-
Periodic freezing of backend processes
Andrew McMillan <andrew@catalyst.net.nz> — 2000-07-09T10:42:39Z
PostgreSQL 7.0.2 Debian 2.3 (woody) Linux Kernel 2.2.15 Hi, I am finding that I periodically have a backend process just 'freeze' on me. It's not like it's doing something that all of a sudden get's wildly inefficient because this can be when I'm repetitively processing in a loop, and where responsiveness is normally sub-second I have waited hours to see if these terminate and they don't. What can I do to tickle the backend processes to get it to tell me where it is at? I have found that if I kill the backend process that is locked up, then do exactly the same query, it locks up again. BUT if I shut down and restart the postmaster and then do exactly the same query (reload my web page, in fact) the response is immediate again. A couple of other points to note: - the webserver, database server and all are on my laptop - I am the only user and there is only one request active at the time this happens (although I have seen it happen on our production machine, running 6.5.3 as well). - I have seen the freeze happening from PHP scripts as well as from Perl scripts. - A 'ps' does not show the process as 'waiting': $ps flaxww | grep postgres | grep -v grep 000 31 15629 1 0 0 5824 1152 select S pts/2 0:00 /usr/lib/postgresql/bin/postmaster -b /usr/lib/postgresql/bin/postgres -B 256 -N 16 -D /var/lib/postgres/data -d 0 -o -F -S 4096 040 31 15634 15629 0 0 6432 4380 select S pts/2 11:14 \_ /usr/lib/postgresql/bin/postgres localhost andrew newsfeed UPDATE That's right now, so it's frozen on an 'UPDATE' and looking at my (perl) program the only 'UPDATE' is this one: UPDATE story SET wcount=$count WHERE story_id=$story_id; With story_id being the table's primary key, of course. Up until it froze it was processing one of these UPDATE's every second or two (amongst many other SQL statements). Earlier in the evening a similar thing happened except the statement was an 'INSERT' and the program had been running fine for about three hours before it locked up. If I kill the backend process that has locked up I may get messages when I next do a VACUUM that say I need to recreate the indexes on some table too. Sorry if this is a bit vague, but if there's some signal I can send to the locked process to try and tell where it is or what it's trying to do, perhaps I can find out more next time it happens. I've tried kill -3 and kill -5 but don't seem to get any core files. I'd crank up the logging except that whenever I do that I tend to run out of disk space :-( Thanks, Andrew. -- _____________________________________________________________________ Andrew McMillan, e-mail: Andrew@cat-it.co.nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 -
Re: Periodic freezing of backend processes
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-09T17:44:26Z
Andrew McMillan <Andrew@catalyst.net.nz> writes: > I am finding that I periodically have a backend process just 'freeze' on > me. Needs to be looked at, for sure. > What can I do to tickle the backend processes to get it to tell me where > it is at? What I'd do is attach to it with gdb and poke around. gdb path-of-postgres-executable attach PID-of-target-process bt ... It helps if you've compiled the backend with -g. It might also be useful to enable Asserts. > I have found that if I kill the backend process that is locked up, then > do exactly the same query, it locks up again. BUT if I shut down and > restart the postmaster and then do exactly the same query (reload my web > page, in fact) the response is immediate again. Sounds like the problem is associated with some particular configuration of shared memory. Perhaps some prior query has failed to release a lock, or some such? regards, tom lane