Re: Stats collector eats my CPU

[Oliver Kohll <oliver@xxxxxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

oliver@xxxxxxxxxx / 0845 456 1810 / 07814 828608

www.gtwm.co.uk - company

www.gtportalbase.com - product