2014-06-20 1:44 GMT+02:00 Huang, Suya <suya.hu...@au.experian.com>: > > > From: Pavel Stehule [mailto:pavel.steh...@gmail.com] > Sent: Thursday, June 19, 2014 3:41 PM > To: Huang, Suya > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] huge pgstat.stat file on PostgreSQL 8.3.24 > > > > 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 > > > 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 > > > >
