Re: Postgres 9.1.4 - high stats collector IO usage - Mailing list pgsql-performance

2012/8/6 Magnus Hagander <magnus@hagander.net>:
> 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.

we had to do it because our read/write of  stat file created really
high IO - and it was problem on Amazon :( - probably we had not this
issue elsewhere

Regards

Pavel



>
> //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/