Re: stats collector suddenly causing lots of IO

On Thu, Apr 15, 2010 at 6:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris <lists@deksai.com> writes:
>> I have a lot of centos servers which are running postgres.  Postgres isn't used
>> that heavily on any of them, but lately, the stats collector process keeps
>> causing tons of IO load.  It seems to happen only on servers with centos 5.
>
> Say, I just realized that both of you are complaining about stats
> collector overhead on centos 5 servers.  I hadn't been thinking in terms
> of OS-specific causes, but maybe that is what we need to consider.
> Can you tell me the exact kernel versions you are seeing these problems
> with?

uname -a says "... 2.6.18-92.1.13.el5 #1 SMP ... x86_64", and it's CentOS 5.2.

I'm not sure whether this is related to the stats collector problems
on this machine, but I noticed alarming table bloat in the catalog
tables pg_attribute, pg_attrdef, pg_depend, and pg_type. Perhaps this
has happened slowly over the past few months, but I discovered the
bloat when I ran the query from:
http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html

on the most-active database on this server (OID 16389 from the
pgstat.stat I sent in). See attached table_bloat.txt. The autovacuum
settings for this server haven't been tweaked from the default; they
probably should have been, given the heavy bulk updates/inserts done.
Maybe there's another cause for this extreme catalog bloat, besides
the weak autovacuum settings, though.

Table sizes, according to pg_size_pretty(pg_total_relation_size(...)):
 * pg_attribute: 145 GB
 * pg_attrdef: 85 GB
 * pg_depend: 38 GB
 * pg_type: 3465 MB

I'll try to send in strace outputs later today.

Josh