Zelinskiy Alexander <alexander.zelinskiy@gmail.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