Re: huge pgstat.stat file on PostgreSQL 8.3.24

2014-06-19 7:35 GMT+02:00 Huang, Suya <Suya(dot)Huang(at)au(dot)experian(dot)com>:

> From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
> Sent: Thursday, June 19, 2014 3:28 PM
> To: Huang, Suya
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24
>
> Hello
>
> The size of statfile is related to size of database objects in database.
> Depends on PostgreSQL version this file can be one per database cluster or
> one per database (from 9.3),
> These statistics should by reset by call pg_stat_reset()
> http://www.postgresql.org/docs/9.2/static/monitoring-stats.html
> Autovacuum on large stat files has significant overhead - it can be
> increasing by using new PostgreSQL (9.3) and by migration stat directory to
> ramdisk - by setting stats_temp_directory to some dir on ramdisk (tmpfs on
> Linux)
> Regards
>
> Pavel
>
> 2014-06-19 6:38 GMT+02:00 Huang, Suya <Suya(dot)Huang(at)au(dot)experian(dot)com>:
> Hi group,
>
> We’ve found huge pgstat.stat file on our production DB boxes, the size is
> over 100MB. autovacuum is enabled. So my question would be:
> 1. What’s a reasonable size of pgstat.stat file, can it be estimated?
> 2. What’s the safest way to reduce the file size to alleviate the IO
> impact on disk?
> 3. If need to drop all statistics, would a “analyze DB” command
> enough to eliminate the performance impact on queries?
>
> Thanks,
> Suya
>
>
>
>
> Hi Pavel,
>
> our version is 8.3.24, not 9.3. I also want to know the impact caused by
> run pg_stat_reset to application, is that able to be mitigated by doing an
> analyze database command?
>

your version is too old - you can try reset statistics. ANALYZE statement
should not have a significant impact on these runtime statistics.

Pavel

Attention: PostgreSQL 8.3 is unsupported now

> Thanks,
> Suya
>
>