Dan Boeriu wrote:
> The second will not finish in 24h but the CPU stays at 100% and iostat
> shows no IO other than the checkpoints.
> IF I cancel the second UPDATE (pg_cancel_backend) and restart it (new
> pgsql invocation) ASAP it goes through in 20 secs.
OK, that's interesting when combined with the information provided.
(Though, in general, it's preferable that you quote command output as
well as your interpretation; I think we all miss things sometimes.)
You might want to connect to the problem process (the one pegged at 100%
CPU) with gdb and see if you can get a backtrace showing what's going on.
Where the pid of the problem process is shown as 9999 here:
gdb -p 9999
(gdb) bt
(gdb) cont
^C
(gdb) bt
(gdb) cont
^C
(gdb) bt
(gdb) cont
^C
(gdb) bt
(gdb) q
y
In other words: connect to the process with gdb. Issue the "bt" command
(backtrace) to get a backtrace at the point you interrupted it at by
connecting with gdb, then tell it to continue execution normally. After
a little while (a few seconds/minutes/whatever, just tell us vaguely how
long) hit control-C, request another backtrace, and tell the program to
continue. Repeat a few times, then quit gdb and tell it to detach from
the process.
Then send the ENTIRE, EXACT output to the mailing list.
If the output mostly looks like this:
#0 0xb7f6a410 in ?? ()
#1 0xbfe75578 in ?? ()
#2 0x00000000 in ?? ()
then your Linux distro has stripped debug info out of the Pg binaries.
You may need to install debuginfo RPMs for PostgreSQL and glibc at the
very least, then repeat the process of collecting backtrace information.
--
Craig Ringer