Re: statistics collector process is thrashing my cpu

William Temperley wrote:
> Dear All
>
> Sometimes postgres.exe will thrash one of the cores and won't stop
> until I kill the process. I know it's the statistics collector as I
> get this message when I kill the process:
> "statistics collector process (PID 172) exited with exit code 1"
> Nothing other than this app is accessing my PG server.
>
> I'm developing a web mapping application which retrieves point data
> via multiple statements like this:
>
> select xmlelement(name "amr:mapFeatureType",
> xmlattributes(featuretype as name),xmlagg(
> xmlelement(name "amr:mapFeature",
> xmlforest(y(the_geom) as "amr:latitude", x(the_geom) as
> "amr:longitude", lable as "amr:description"))
> ))
> from (select featuretype, the_geom, lable from poi
> WHERE the_geom && setsrid(
> 'BOX3D(-5.625 52.482780222078205,0
> 55.7765730186677)'::box3d, 4326)
> and featureclass = 'layer3'
> limit 15) as ss
> group by ss.featuretype
>
> I don't even know why this process would run anyway, as I haven't
> updated the database for weeks (it's only a dummy dataset).
>
> I'm also getting hundreds of these messages in a few hours work:
> 2008-05-08 09:22:56 BST LOG: loaded library
> "$libdir/plugins/plugin_debugger.dll"
> 2008-05-08 09:22:56 BST LOG: loaded library
> "$libdir/plugins/plugin_debugger.dll"
> Why would it load it twice within the same second?

You get one of those everytime you start a new backend.

> Select version() - "PostgreSQL 8.3.1, compiled by Visual C++ build
> 1400" Machine - XP sp2, core 2 duo 7250 laptop
>
> The only non 'out of the box' setting I've changed in postgresql.conf
> is: log_statement = 'all'
>
> Any ideas why this might be happening, and how I can stop it?

It'd be interesting to know what the stats collector is actually doing.
Could you, using Process Explorer or a debugger, get a stack trace from
that process while it's in the trashing state?

//Magnus