2014-06-19 7:35 GMT+02:00 Huang, Suya <suya.hu...@au.experian.com>: > From: Pavel Stehule [mailto:pavel.steh...@gmail.com] > Sent: Thursday, June 19, 2014 3:28 PM > To: Huang, Suya > Cc: pgsql-performance@postgresql.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.hu...@au.experian.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 > >
