Re: Stats collector eats my CPU

I have a large number of tables as well (though nowhere near 25000)
and looked into autovacuum configuration:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-autovacuum.html

The autovacuum daemon runs ANALYZE commands when necessary which will
collect stats.

To test whether this is the cause, you could try to disable autovacuum
temporarily and see if there's any effect. If so, read & understand
the config. options and see what's relevant given how your tables are
used. In my case I just increased autovacuum_naptime from 1min to
10min which reduced my processor usage from very little to practically
negligible with no adverse effect. I believe you can define table-
specific options if necessary.

That default_statistics_target parameter is larger than the default of
10, presumably by design? It'll also create more processing.

Regards
Oliver Kohll

On 8 Oct 2008, at 12:07, wstrzalka wrote:

> This is top of my 'top':
>
> 15483 postgres 15 0 147m 31m 284 R 17 0.8 29033:23 postgres
> 24599 postgres 15 0 1293m 274m 231m S 2 6.9 0:02.05 postgres
> 24598 postgres 15 0 1258m 99m 88m S 1 2.5 0:00.62 postgres
>
> the 15483 process is stats collector. At the moment server is almost
> idle but the stats collector is constantly taking 15-17% of CPU.
>
> My statistics related settings looks like this:
>
> track_activities on
> track_counts on
> default_statistics_target 100
>
>
> I don't know if it matters at all, but maybe the reason is that the
> cluster is very large in the term of relation number (many schemes
> with identical table set).
>
> ------------------------------------------------------------------------------------
> select count(*) from pg_class;
> count
> --------
> 257477
> ------------------------------------------------------------------------------------
>
> I'm using PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu, compiled by GCC
> gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
>
>
> The 15% isn't much and I can afford it but what's strange there is
> really small trafic at the moment (in average about 5%), and the DML's
> are about 5% of it. So what is the stat collector doing? I'm affraid
> what will happend if the real query traffic will increase.
>
> Maybe the PGSTAT_STAT_INTERVAL is the issue in my case (vary large
> schema)? The PG is from yum.
>
>
> Any suggestions? Should I worry? Can I do anything about it?
> (adjusting schema will not be easy :D )
>
>
> Wojtek Strzalka
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

oliver(at)gtwm(dot)co(dot)uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product