Re: Postgres 9.1.4 - high stats collector IO usage

That's not a good way of doing it, since you loose persistent storage.

Instead, you should set the stats_temp_dir paramter to a filesystem
somewhere else that is tmpfs. Then PostgreSQL will automatically move
the file to and from the main data directory on startup and shutdown,
so you get both the performance of tmpfs and the persistent
statistics.

//Magnus

On Sat, Jul 28, 2012 at 9:07 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 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@oneit.com.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@oneit.com.au
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance



--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/