Re: stats collector suddenly causing lots of IO

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Chris <lists(at)deksai(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: stats collector suddenly causing lots of IO
Date: 2010-04-16 14:39:07
Message-ID: z2s4ec1cf761004160739w3ac5511jd39263dd043e2da6@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, Apr 15, 2010 at 6:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Chris <lists(at)deksai(dot)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

Attachment Content-Type Size
table_bloat.txt text/plain 2.5 KB

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-04-16 15:23:52 Re: stats collector suddenly causing lots of IO
Previous Message Tom Lane 2010-04-16 14:19:47 Re: Planner not using column limit specified for one column for another column equal to first