Re: Idle processes chewing up CPU?

Hi all,

We managed to trap further details of the problem today. Some large queries
were terminated prematurely, perhaps causing a dirty SSL or TCP disconnect,
and they seem to have left a few runaway processes. SELECT * FROM
pg_stat_activity showed each process was <IDLE>, while Process Explored
showed them each chewing up 25% CPU time (ie. one core on a quad core
system).

I copied a few of the stack traces (at the end of this email), it kept
changing each time I looked. Mostly LIBEAY32.DLL related, suggestion some
connection to SSL - our bin\libeay32.DLL is version 0.9.8.5, last modified
2007/02/28.

In case it was simply a dirty SSL disconnect, I tried running a large query,
unplugged my network cable, and monitored the process. Rather than running
into a loop, the child process just shut down at the end of the query.

So I'd appreciate any advice you have on what may be causing this and how we
can get around it in future. If necessary, we'll write a temporary program
to poll the pg_stat_activity and currently running processes, and alert us
if one goes <IDLE>/chewing CPU, but obviously this isn't a long term
solution.

Thanks for your help,
-Brendan

ntkrnlpa.exe+0x8dafe
ntkrnlpa.exe+0x29a82
ntkrnlpa.exe+0x33198
hal.dll+0x6199
hal.dll+0x63d9
hal.dll+0x6577
hal.dll+0x3902
mswsock.dll+0x1445
WSOCK32.dll!recv+0x31
LIBEAY32.dll!BIO_sock_should_retry+0x57

ntkrnlpa.exe+0x8dafe
ntkrnlpa.exe+0x29a82
ntkrnlpa.exe+0x33198
hal.dll+0x6199
hal.dll+0x63d9
hal.dll+0x6577
hal.dll+0x3902
postgres.exe!process_implied_equality+0x18d50e

ntkrnlpa.exe+0x8dafe
ntkrnlpa.exe+0x29a82
ntkrnlpa.exe+0x33198
hal.dll+0x6199
hal.dll+0x63d9
hal.dll+0x6577
hal.dll+0x3902
LIBEAY32.dll!ERR_get_state

ntkrnlpa.exe+0x8dafe
ntkrnlpa.exe+0x29a82
ntkrnlpa.exe+0x33198
hal.dll+0x6199
hal.dll+0x63d9
hal.dll+0x6577
hal.dll+0x3902
ntkrnlpa.exe+0x89751
ntdll.dll!KiFastSystemCallRet
WS2_32.dll!WSARecv+0x65
WSOCK32.dll!recv+0x31
LIBEAY32.dll!BIO_sock_should_retry+0x57

ntkrnlpa.exe+0x8dafe
ntkrnlpa.exe+0x29a82
ntkrnlpa.exe+0x33198
hal.dll+0x6199
hal.dll+0x63d9
hal.dll+0x6456
ntkrnlpa.exe+0x312be
ntkrnlpa.exe+0x2ab9b
ntkrnlpa.exe+0x1e257
afd.sys+0x11905
afd.sys+0x10978
afd.sys+0xf097
ntkrnlpa.exe+0x1df85
ntkrnlpa.exe+0xf5437
ntkrnlpa.exe+0xf61bf
ntkrnlpa.exe+0xeed08
ntkrnlpa.exe+0x897bc
ntdll.dll!KiFastSystemCallRet
WS2_32.dll!WSARecv+0x65
WSOCK32.dll!recv+0x31
LIBEAY32.dll!BIO_sock_should_retry+0x57

-----Original Message-----
From: Craig Ringer [mailto:craig(at)postnewspapers(dot)com(dot)au]
Sent: Wednesday, 29 July 2009 8:09 PM
To: Brendan Hill
Cc: 'Tom Lane'; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Idle processes chewing up CPU?

Craig Ringer wrote:
> Brendan Hill wrote:
>> Hi Tom,
>>
>> Given it's on Windows, any suggestion for how I would get hold of this?
>> (Process Monitor tool perhaps?)
>
> I think you can get stack traces from Process Monitor using "Tools ->
> Stack Summary". I find it a bit hard to interpret this data, though, and
> I'm not sure how useful it is for this sort of thing.
>
>
>
> [ The following instructions may be put on the PostgreSQL wiki as advice
> for getting debugging details for runaway PostgreSQL processes on
> Windows if desired ]:

Actually, I've expanded on the instructions and done it. See:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQ
L_backend_on_Windows

Accessible from "General Articles and Guides" -> "Troubleshooting" ->
"Generating_a_stack_trace_of_a_PostgreSQL_backend".

It'd be rather helpful if others could fill in the equivalent for gdb on
Linux/bsd/other unix as linked to here:

http://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_bac
kend

--
Craig Ringer