Re: Postgresql 8.2.4 on linux-sparc problem

Zelinskiy Alexander <alexander(dot)zelinskiy(at)gmail(dot)com> writes:
> I understand that this description can't give you enough information
> to give an advice what to do. But I don't know what to collect from OS/
> PG when it will happen again. Could you give me an advice what should
> I do when next time one of process will use all CPU time?

Before you get into that, try reducing these parameters:

> maintenance_work_mem = 1572864
> work_mem = 1048576

They are way too high, especially the second one. But if that doesn't
help:

> I guess that I can collect stack from this process at that time by
> using GDB or something like it. But I have no experience how exactly
> to do it.

Yup, that's the thing to do when you don't know what's happening.
The procedure is:

Determine PID of the troublesome backend (use ps or whatever).

As root or the postgres user, do

$ gdb /path/to/postgres-executable PID
... introductory noise, but the list of libraries can be useful ...
gdb> bt
... useful info here ...
gdb> quit
OK to detach? y
$

Necessary advance preparation: be sure gdb is installed and you have a
debug-enabled postgres (install debuginfo RPM if needed). I'd suggest
practicing and making sure that the backtrace shows routine names and
parameter values. As an example, a backtrace I was just looking at
looked in part like this:

#0 0x000000310202e174 in xmlCleanupCharEncodingHandlers ()
from /usr/lib64/libxml2.so.2
#1 0x0000003102036725 in xmlCleanupParser () from /usr/lib64/libxml2.so.2
#2 0x0000000000660e39 in xpath (fcinfo=<value optimized out>) at xml.c:3452
#3 0x000000000053601e in ExecMakeFunctionResult (fcache=0xb32f60,
econtext=0xb32e68, isNull=0xb33ed8 "", isDone=0xb33f90) at execQual.c:1351
#4 0x00000000005343c6 in ExecProject (projInfo=<value optimized out>,
isDone=0x0) at execQual.c:4601
#5 0x000000000053f5a5 in ExecAgg (node=0xb32b58) at nodeAgg.c:989
#6 0x0000000000533eaa in ExecProcNode (node=0xb32b58) at execProcnode.c:394

I do not have debuginfo installed for libxml2, thus the first two lines
provide very little information. The rest look more like what a
developer would like to see.

regards, tom lane