Re: huge pgstat.stat file on PostgreSQL 8.3.24

2014-06-20 1:44 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:41 PM
> To: Huang, Suya
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] 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
>
>
> Thanks Pavel, to be more clear, what does " pg_stat_reset "really reset?
> In the document it says " Reset all statistics counters for the current
> database to zero(requires superuser privileges) ". I thought it would
> reset all statistics of all tables/indexes, thus why I am thinking of
> re-run analyze database to gather statistics. Because if table/indexes
> don't have statistics, the query plan would be affected which is not a good
> thing to a production box... I'm not so sure if I understand "run
> statistics" you mentioned here.
>

you have true - anyway you can clean a content of this directory - but if
your database has lot of database objects, your stat file will have a
original size very early

Pavel

>
> Thanks,
> Suya
>
>
>
>