Re: Idle processes chewing up CPU? - Mailing list pgsql-general

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 ]:


You're better off using Process Explorer in conjunction with the
Debugging Tools for Windows. Install the Debugging Tools for Windows:

http://www.microsoft.com/whdc/devtools/debugging/installx86.mspx

and Process Explorer:

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx


Now launch Process Explorer (procexp.exe) and in the Options menu choose
"Configure symbols". Replace the dbghelp.dll path with:

   C:\Program Files\Debugging Tools for Windows (x86)\dbghelp.dll

In the same dialog put this string in the symbol path field (all one
line, the only space being between "Program" and "Files"):

   C:\Program
Files\PostgreSQL\8.4\symbols;SRV*c:\localsymbols*http://msdl.microsoft.com/download/symbols;

(Change the path to your PostgreSQL symbols directory as appropriate for
your version). This will tell Process Explorer where to look for
PostgreSQL debug symbols, and tell it to use the Microsoft symbol server
to get windows symbols, caching them in c:\localsymbols, which it will
create if required.

Now you can choose "Properties" on a process in Process Explorer and in
the Thread tab see the active threads and what they're doing. You should
see something like:

TID      CSwitch delta       Start Address
1260     8                   postgres.exe!mainCRTStartup
2792                         postgres.exe!pg_signal_thread

(If you see lots of references to "pg_init" or similar instead, your
symbol path is wrong and Process Explorer can't find the PostgreSQL
symbols.)

Now you need to identify the active thread and get a stack trace from
it. It'll usually have a non-zero cswitch delta. Select it and click
"Stack". After a short delay, a stack trace will be shown. Select it
all, and click "Copy".

Here's a stack trace obtained from PostgreSQL 8.4 while it's executing:

    select generate_series(1,100000000000000);

... just so you have some idea what to expect:

ntkrnlpa.exe!NtInitialUserProcessBuffer+0x26
ntkrnlpa.exe!RtlFreeHeapSlowly+0x88
ntkrnlpa.exe!NtCallbackReturn+0x29
ntkrnlpa.exe!MiGrowWsleHash+0xb0
ntkrnlpa.exe!MiTrimWorkingSet+0xc4
hal.dll!HalpApcInterrupt+0xc6
postgres.exe!ExecProcNode+0x5
postgres.exe!ExecutePlan+0x93
postgres.exe!standard_ExecutorRun+0x7a
postgres.exe!PortalRunSelect+0x6a
postgres.exe!PortalRun+0x14f
postgres.exe!exec_simple_query+0x381
postgres.exe!PostgresMain+0xc67
postgres.exe!BackendRun+0x204
postgres.exe!SubPostmasterMain+0x224
postgres.exe!main+0x177
postgres.exe!__tmainCRTStartup+0x10f
kernel32.dll!BaseProcessStart+0x23


You can also, if you prefer, use windbg.exe from the Debugging Tools for
Windows to get a stack trace. This is widely documented.

--
Craig Ringer