Skip site navigation (1) Skip section navigation (2)

Re: Postgres 9.1.4 - high stats collector IO usage

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: David Barton <dave(at)oneit(dot)com(dot)au>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
Date: 2012-07-28 07:07:48
Message-ID: CAFj8pRB=Tq-xYeMHSE=52Oqkpx=miazcqEgwHa+m3cFxTo7i2g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello

I had same problem with large numbers of tables - you can move
pg_stat_tmp to tmpfs filesystem - it was solution for us

Regards

Pavel

2012/7/28 David Barton <dave(at)oneit(dot)com(dot)au>:
> Hi,
>
> I am running postgres 9.1.4 on Ubuntu 12.04 and the stats collector is
> generating very high IO usage even when nothing appears to be happening on
> the system.
>
> I have roughly 150 different databases, each of which is running in 1 of
> roughly 30 tablespaces.  The databases are small (the dump of most is are
> under 100M, and all but 3 are under 1G, nothing larger than 2G).
>
> Previously iotop reported the disk write speed, at ~6MB / second.  I went
> and reset the stats for every database and that shrunk the stats file and
> brought the IO it down to 1MB / second.  I still think this is too high for
> an idle database.  I've now noticed it is growing.
>
> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
> -rw------- 1 postgres postgres 3515080 Jul 28 11:58
> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>
> <reset of stats>
>
> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
> -rw------- 1 postgres postgres 514761 Jul 28 12:11
> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>
> <watch the file grow>
>
> ls -l /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
> -rw------- 1 postgres postgres 776711 Jul 28 12:25
> /var/lib/postgresql/9.1/main/pg_stat_tmp/pgstat.stat
>
> In the 15 minutes since the reset, IO has nearly doubled to 1.6+ MB /
> second.
>
> FWIW, I just migrated all these databases over to this new server by
> restoring from pg_dump  I was previously experiencing this on 8.3, which was
> why I upgraded to 9.1 and I also have another server with similar problems
> on 9.1.
>
> Any help would be sincerely appreciated.
>
>
> David Barton dave(at)oneit(dot)com(dot)au

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2012-07-28 16:13:54
Subject: Re: Postgres 9.1.4 - high stats collector IO usage
Previous:From: David BartonDate: 2012-07-28 04:33:17
Subject: Postgres 9.1.4 - high stats collector IO usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group